This is part six of our Microsoft Excel Tutorial Series, if you haven’t read the previous articles you can find them here:

An Introduction to Microsoft Excel 2007

The basic’s data entry and navigation

Importing Market Data from Yahoo Finance

The basic’s of charting market data the line chart

The basics of charting market data the bar chart

Today we will be looking at setting up candlestick charts in Excel. This tutorial will go through producing the two built in Excel versions of candlestick charts. Normally a candlestick chart will display open, high, low and close and the range for a given period. They are a great tool to quickly gauge price action.

Candlestick Chart

I am going to assume that you are familiar with candlestick charts, however if you are new to these type of charts I have included some useful resources on the topic of candlestick charting below.

Introduction to Candlesticks

The Art of Candlestick Charting

Books

As with the previous article we will be working with our Microsoft End of Day stock data imported from Yahoo Finance. The first step is to open the MSFT.xlsx spreadsheet in Excel 2007.

For the candlestick chart the data must be arranged in a specific column layout structure. The structure required is Open-High-Low-Close, this is highlighted in the image below. The next step is to select or highlight the Open-High-Low-Close data columns.

With the data selected click on the insert tab on the office ribbon and then click on the create chart icon on the Insert ribbon as shown below.

A dialog box will now be displayed giving you various chart options. On the left hand side of the dialog box are a list of templates, for our second chart we are going to create a simple candlestick chart. Select Stock from the left hand window and then candlestick chart in the preview pane and click OK (As shown below).

You will see a chart hovering over your data, Excel has tried to guess an appropriate scale for our data and has numbered the x axis or category axis sequentially. This is highlighted below:

As you can see the y axis scale and category axis needs to be fixed up. In Excel 2007 the context of the office ribbon will change when different objects are selected. In order to edit the data which is displayed on the chart you will need to click on “Select Data” on the modified office ribbon.  When the select data source window opens click edit axis labels and select the date column as category labels. Be careful to select only the date values and not the date label,  as highlighted below.

The next step is to edit the y axis or the axis that displays price, as the scale is to large to show any useful relationship in the chart.  To edit the axis scale simply right click on the price axis and then click on “Format Axis”. When the format axis dialog window opens edit the axis values as shown below.

In the final step click on “Move Chart Location” on the Office ribbon and choose New Sheet and name the sheet CandleStickChart. You can now tweak the formatting as desired. The one thing I like to do is change the colour of the candlesticks so I can quickly and easily see if the price is up or down for a given period. If the price is down I use a red candle and if the price is up I use a green candle. This can be achieved in Excel quite simply by right clicking on the candle in the chart and then select Format bars and choose your desired colours (as shown below).

You should end up with a chart similar to this chart below.

Extending our candlestick chart to add volume data is now a very simple process. With the candlestick chart object selected click on the select data icon in the office ribbon.  The select source dialog box will now open, click add series and move to the original spreadsheet and select the volume data and label. The volume data must be at the top of the series list so click on the move up icon as shown below.

The reason we moved the volume data to the top of the list was to conform to Excels requirement for the candlestick volume chart. For a candlestick chart with volume data the series must be on the following order: Volume-High-Low-Close. With the chart selected click on “Change Chart Type” and select the candlestick chart with volume data (as shown below).

The chart should now show the volume series however you will need to adjust the scale of both volume and price axis as previously discussed.  After you have formatted your chart it should look similar to the one below.

This has been a very basic introduction to Microsoft Excel. In the coming articles I will be looking at more intermediate topics as we move towards the goal of building a trading system in Microsoft Excel.

Popularity: 52% [?]