This is part five 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 

Today we will be looking at bar charts and the two built in Excel versions of bar charts. Normally a bar chart will display open, high, low and close however in Excel the built in bar charts only display high low and close. 

Bar Chart 

The image shows a single bar for a given time period. The horizontal line on the left hand side of the bar shows the price at which the given security opens at. The horizontal line on the right side of the bar shows the price that the security has closed at. The height of the bar shows the range that the security traded between for a given period.

In the standard bar chart in Excel 2007 the closing price is highlighted with a triangle or an X rather then a horizontal line.

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 bar chart the data must be arranged in a specific column layout structure. The structure required is High-Low-Close, this is highlighted in the image below. The next step is to select or highlight the 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 bar chart. Select Stock from the left hand window and then bar 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 BarChart. You can now tweak the formatting as desired. You should end up with a chart similar to this chart below.

Extending our Bar Chart to add volume data is now a very simple process. With the bar 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 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.

Our Random Articles

• Wordless Triple Swap Wednesday - Brooklyn Bridge
• Money, Finance, Stock Market Links
• Trading does not have to be bad for your health
• First Wordless Wednesday - Bunny
• Anti Book Review: The Black Swan: The Impact of the Highly Improbable

Sponsored Links