Building an Excel candlestick chart

Excel is a great way to get your feet wet building a chart. It also gives you some experience as to what's behind the chart. Commercial packages that do all the work for you don't give you that type of experience.

At the time of this writing, I used Excel 2003, but I was just starting to work with the 2007 version. The 2007 version is quite a change from the 2003 version and is a little different to use. However, there are added features in the 2007 version that make it worth your while. Also, eventually, this new version will be the standard, and you won't have a choice. For now, as most people still seem to have the 2003 version, the examples use that version.

Now it's time to build a candlestick chart:

1. Highlight all the data in your spreadsheet, without highlighting the headers.

2. Click Insert, and from the available choices select Chart. Step 1 of 4 of the Chart Wizard appears.

3. Under Chart Type, scroll down and highlight Stock.

To the right of this box appear four chart choices; click the top right one, which looks like a little candlestick chart.

4. Click Next at the bottom of the dialog box, and then click Next at Step 2 of 4.

That brings you to Step 3.

5. Click the Axes tab, and under Category (X) axis, click Category.

Also, make sure the Category (Y) axis button is checked. This step eliminates missing days (such as weekends and holidays) from your chart.

6. Click the Gridlines tab.

Make sure there are no check marks in any of the boxes.

7. Click the Legend tab and make sure that the box next to Show Legend is deselected.

8. Click Next to open Step 4 of 4.

9. Click the button next to As new sheet. 10. Click Finish.

A new sheet with a candlestick chart has been added to your spreadsheet!

Congratulations! You're now the proud owner of an Excel candlestick chart. Feel free to right-click the chart to view all the options available for your tinkering pleasure. For example, when you right-click one of the black candles, a Format Down Bars box opens, and you can change the color of all down bars (bars for days when the close was lower than the open). The opportunities for modification on Excel candlestick charts are many and varied.

For a point of reference, Figure 4-5 is a chart I created in Excel following the above instructions. If you made all the correct clicks, your chart should look very much like this example.

Although Excel's candlestick charting is a very nice feature, it doesn't allow for much flexibility. If you want to change time frames, for instance, you may need to completely rebuild your chart. But you can add in some additional information to make your charts more functional. How? Read on through the rest of this section to find out.

0 -1

Post a comment