Adding Volume as a Column Chart to the Line Chart in Excel 2007:-

A popular option in stock charts is to add a column chart that shows volume of shares traded. This chart usually appears at the bottom of the trend chart.

Continuing with the example from the preceding section, to plot prices in the $20–$30 range with volumes in the 50–100 million ranges, the volumes have to be plotted on a secondary axis. One trick is to artificially inflate the maximum for the secondary axis by a factor of three or four in order to keep the volume chart in the lower portion (that is, the lower quarter to third) of the chart.

You follow these steps to create a chart that shows closing prices and volume:

1. Download data from Yahoo finance.

2. Sort the data into ascending sequence by date.

3. Insert blank columns B and C after the Date column.

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

5. Copy the Volume column from column H to the new column C.

6. Delete the extra columns H and I.

7. Select your data in columns A;C.

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



(Don’t be alarmed that you can see only volumes.)

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

10. Choose Series Volume from the Current Selection drop-down.

11. On the Design ribbon, click Change Chart Type. Select the first column chart type. Click OK. (Any time you change the chart type, Excel annoyingly unselects the data series.)

12. On the Layout ribbon, choose Series Volume from the Current Selection drop-down. Click the Format Selection button. Excel displays the Format Data Series dialog.

13. In the Series Options panel of the Format Data Series dialog, change the Plot Series On value from Primary Axis to Secondary Axis. Click Close to dismiss the Format dialog box. For the first time, you can see both the closing price and the volume.

14. On the Layout ribbon, choose Chart Title, Centered Overlay. Type MSFT 2006 and press Enter to change the title.

15. The value axis currently runs from a low of $0 to a high of $35. Although prices of Microsoft (MSFT) stock never dipped below $20 in 2006, you can leave that space to hold the volume portion of the chart. The highest volume was about 600 million shares traded. If you scale the secondary axis to have a maximum value of 1,200 million shares traded, the volume portion of the chart occupies the lower half of the chart. Right-click the secondary value axis and choose Format Axis. Change Maximum to Fixed, 1.2E9. The tallest column in the volume area of the chart stays below the gridline for $20.

16. Usually, the analyst doesn’t care how many shares are traded; he or she is interested in the relative scale of the shares being traded. From the chart, you can tell that something remarkable happened when Microsoft traded six times more shares than usual. Thus, you don’t need to have any volume numbers along the right side of the chart, so right-click the numbers along the right value axis and choose Format Axis. Click the Number category in the left navigation bar of the Format Axis dialog. In the Category column, choose Custom. Type a new custom format of ;;; and then click Add. In the Type box, choose the ;;; entry. This custom format code is the code to hide the values, and Excel removes the numbers from the right side of the chart.

17. 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.

18. Resize the chart so that 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).

The process of creating line charts is fairly straightforward. Although a certain amount of tweaking needs to happen, it is about normal for a chart.



(The final chart shows closing price as a line chart and volumes as columns at the bottom of the chart.)