Trainee Trader

In the previous article we went through the basics of importing company data from Yahoo Finance. If you are not familiar with Microsoft Excel and have not read the previous article I suggest you read that before continuing. In today’s article will be using Microsoft Excel to generate charts using market data from Yahoo Finance.

Excel Built-In Chart Types 

Excel 2007 does a fairly good job of generating stock charts automatically, given the data is laid out in the correct order. In the next few articles we will go through the basics of generating five different charts, the charts we will be generated include:

High-Low-Close bar chart.

Open-High-Low-Close candlestick chart.

Volume-High-Low-Close bar chart.

Volume-Open-High-Low-Close candlestick chart.

Adjusted close line chart.

These are just a small number of charts possible in Microsoft Excel however they are the most useful for charting stock data. Today’s focus will be on generating an adjusted close line chart.The first step in the process is to open the spreadsheet you created in the previous article. This spreadsheet contains the Microsoft End of Day data from 11/01/2008 to 8/02/2008.

Creating your first chart

The process involved in inserting a chart can be broken down into two steps and is shown below graphically:

1. Click on the Insert tab of the office ribbon.
2. Click on the create chart icon on the Insert ribbon.

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 first graph we are going to create a simple adjusted close line graph. Select line from the left hand window and then line in the preview pane and click OK (As shown below).

You will see a chart hovering over your data, Excel has tried to guess which data to use for each axis and which series to include. This is highlighted below:

As you can see there are several series that we need to delete. 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 and remove every series but Adjusted close, as highlighted below.

 

You will now notice that the line chart displays only the adjusted close series. The next step is to edit 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 LineChart. You can now tweak the formatting as desired. You should end up with a chart similar to this.

 

We have now created a very simple line chart that shows graphically the performance of the Microsoft stock. In the next article we will use the same data to create a bar chart. I would recommend you follow the instructions in this article and try it out with different data sets before moving on to the next article you should be very comfortable with creating line charts, modifying axis scale and adding and removing series.  

Popularity: 24% [?]