## Adding a moving average to an Excel candlestick chart

If you're interested in dressing up your Excel candlestick charts with some added information, moving averages are a good place to start. A moving average is the average of the closing prices for today and looking back a certain number of days. For instance, a five-day moving average would be the closing today added up along with all the closing prices of the previous four days with that total divided by 5. The process is easy, and the result gives you that much more room to analyze and interpret. (Flip to Chapter 11 for more on moving averages.)

1. With your candlestick chart sheet open in Excel (see preceding section), select Chart on the menu bar, and a drop-down menu box opens.

Figure 4-5:

A basic candlestick chart created using Microsoft Excel.

Figure 4-5:

A basic candlestick chart created using Microsoft Excel.

"Vi^ "VP- W -VP5"

The Add Trendline box should open. There are six types of trendlines from which to choose. Highlight Moving Average at the bottom right.

3. In the Period box to the right of the Moving Average box, scroll the number up to 5.

This step provides you with a five-period moving average.

4. In the Based on series box at the bottom, highlight Close to ensure that your moving average is based on closing prices.

5. Click OK at the bottom of the box.

Your chart should now contain a moving average. If you're using the same IBM data that I'm using, your chart should look like Figure 4-6.

HftNG/ Notice that the moving average doesn't start until the fifth time period. This is not usually the case in charting-specific software, just a quirk that appears on Excel. The problem with this quirk is that if you try to add a moving average with a large number of data points, it may end up looking like Figure 4-7.

You have to search a bit for this moving average. See it? It's the small line on the right side of the chart. The line is pretty short because this is a 30-period moving average, and as you can see, it's not very useful because it only appears on a tiny portion of the chart.

Figure 4-6:

A basic candlestick chart with a five-period moving average created in Excel.

Figure 4-6:

A basic candlestick chart with a five-period moving average created in Excel.

Figure 4-7:

A basic candlestick chart with a 30-period moving average created in Excel.

Figure 4-7:

A basic candlestick chart with a 30-period moving average created in Excel.

^ ^ ^ ^
+2 -3