Version 1

A price volume chart shows 2 series in the one Excel chart, and is often referred to as a combination or combo chart. The price volume chart in this module is based on one month of stock price volume data for the Bank of Queensland Ltd – Australian stock exchange code – BOQ.AX.

The data source for the BOQ.AX price volume series – the month of July 2014 – is Yahoo Finance. The data has seven fields, labeled \(Date, … , Adj \ Close\). The Date vector is sorted in descending order.

A log return vector labeled Returns has been added to column H in the Data BOQ.AX worksheet. The log return \(r_t=log(P_t/P_{t-1})\) is the Excel LN function transform of the adjusted closing price series.

You can download the sample file from the Excel Web App #1 download link in figure 1.

Create a price volume chart in Excel 2013

1. Data series: the Date range is not adjacent to the Volume / Price range. This means that the Date, Volume and Price series are non-contiguous and cannot be selected as a block.
2. Select the series:
   • Using the mouse – press Ctrl + click and drag to select the Date vector (column A), and then the Volume / Price array (columns F and G), or
   • Enter the non contiguous range in a cell of your work area, $A$2:$A$25,$F$2:$G$25, then copy and paste to the Name Box, or use the F5 dialog. This method is useful when the series range is large, and may save retyping the address.

3. Insert (the chart): On the ribbon, select the Insert > Charts > See all charts sequence to display the Insert Chart dialog. The See all charts item is in the bottom right corner of the ribbon Charts group.
4. Insert chart: On the Insert Chart dialog, select the All Charts tab, then select the Combo item at the bottom of the left column as shown in figure 2. Then on the upper right, select the Clustered Column – Line item.
5. 

   Fig 2: On the All charts tab, select Combo in the left column, then the Clustered Column - Line item

5. Chart type and axis for series: Currently Volume is Series1 (blue), and Price is Series2 (orange) as identified by the colours. Place the Volume axis on the left, by ticking the Secondary Axis box at the right of Series Name: Series1, Chart Type: Clustered Column in figure 2. This also activates the Custom Combination item, the fourth image to the right of Clustered Column-Line. Click OK. The resultant chart is shown in figure 3.
6. 

   Fig 3: Draft version of the Price Volume chart - the format needs improvement.

7. Note: to eliminate the non trading day gaps in the date vector, you should follow the procedure described in …..

Format the chart

6. Remove the Chart Title and Series labels: Ensure that the Chart is selected – its name will appear in the Excel Name Box. On the ribbon select the sequence Chart tools > Design > Chart Layouts > Add Chart Element. To reduce chart clutter, remove the Chart Title by selecting Chart Title > None. Remove the Series labels by selecting Legend > None.
7. Format the Price axis: To show that the y-axis is a price series, add a currency symbol to the values. Click the Axis to select it, then right click, and select Format Axis – see figure 4.
8. 

   Fig 4: Select the left axis, named the Vertical (Value) Axis, then right click to display the short cut menu - then select the Format Axis item.

8. Price Axis Options: Change the value grid to 50 cent increments by setting Units > Major to 0.5. Include the dollar symbol by setting the Number > Category to Currency – see figure 5.
9. 

   Fig 5: Axis options Set the Units > Major, and Number > Category

9. Volume Axis: Select the Volume axis, then change the number format to millions, by changing its Number > Format Code to #.0,, “M”, and Axis Options > Units > Major to 1.0E6.
10. DateAxis: Select the Date axis, then change the units to weeks, by changing Axis Options > Units > Major to 7 days, and Axis Options > Number > Type to 14-Mar-01.
11. The completed chart is shown in figure 6.

The BOQ price volume chart