Creating a Line Chart to Show Closing Prices in Excel 2007:-


A line chart is the easiest type of stock chart to create. You don’t have to use Excel’s built-in stock charting types; you will simply use a line chart. You follow these steps to create a line chart:

1. Download data for the security from Yahoo finance.

2. Sort the data into ascending sequence by date.

3. Insert a blank column B after the Date column.

4. Copy the Adjusted Close column from column H to the new column B.

5. Delete the extra column H.

6. Clear cell A1. (Whenever your row labels contain dates, the top-left cell of the chart range should be blank.)

7. Select your data in columns A and B.

8. On the Insert ribbon, choose Line, 2-D Line, Line. Excel creates the chart shown in the Figure (given below).



(Excel creates a line chart showing closing prices.)

9. On the Layout ribbon, choose Legend, None. Excel removes the legend from the chart.

10. Click the chart title. Type a new title, such as Microsoft 2006 Closing Prices.

11. The value axis currently runs from a low of 0 to a high of 35. During 2006, the security closing prices ran from a low of $21.36 to a high of $30.19. If you want to show more detail in the chart, right-click the value axis and choose Format Axis. Change the Minimum setting to Fixed, 20.

12. The dates in the horizontal axis are trying to show month, day, and year, as in the original dataset. To display one label for each month, do the following:

• Right-click the labels along the horizontal axis and choose Format Axis.

• On the Axis Options dialog, choose Major Unit, Fixed, 1, Month.

• Choose Axis Type, Date Axis.

• Click the Number category in the left navigation bar.

• Click the Custom category.

• Type a custom format of mmmmm. Click Add.

• Uncheck the Linked to Source check box.

• Select mmmmm from the Type box. Click OK. The mmmmm custom type displays a single letter for each month. In the English version of Excel, it displays JFMAMJJASOND, a format regularly seen in The Wall Street Journal.

13. Resize the chart so it is narrower than the default chart. Click the chart border to select the chart. Drag the right resizing handle to the left. The resulting chart is shown in the Figure (given below).



(When you zoom in on the $20–$30 price range, more details are visible.)