Price volume chart [3] – Excel 2010

Version 3

The price volume chart is created from a sample of one month of data for the Bank of Queensland Ltd – Australian stock exchange code – BOQ.AX. The data is indexed on date in descending order, and we illustrate how to create the price volume chart, with date series in ascending order for the Price, AND Volume series.

Preliminary

    The Defined Names used in this example – are similar to those used in the Stock Analyser project:

  • DateV – Refers to: =$A$2:$A$24
  • VolumeV – Refers to: =OFFSET(INDEX(DateV,1,1),0,5,ROWS(DateV))
  • AdjCloseV – Refers to: =OFFSET(INDEX(DateV,1,1),0,6,ROWS(DateV))
  • PriceVolumeData – Refers to: =DateV,VolumeV,AdjCloseV

Note: In this example, each of the data vectors xxxV include the header row label.

Creating the chart – steps

Steps

  1. Select the data by typing the name PriceVolumeData in the Name Box
  2. Select Insert > Charts > Line > 2-D Line > Line on the ribbon
  3. Remove the non trading day gaps from the primary date axis – from the Chart Tools tab, select Layout > Current Selection > Chart Elements box then the Horizontal (Category) Axis element. The Axis is now the Selection
  4. In the same Current Selection group, select Format Selection
  5. In the Format Axis dialog box, select Axis Options (on the left), then set Axis Type: to Text Axis, and tick the Check Box Categories in reverse order. Click Close
  6. From the Chart Tools tab, select Layout > Current Selection > Chart Elements box then the Series “Adj Close” element
  7. In the same Current Selection group, select Format Selection
  8. In the Format Data Series dialog box, select Series Options (on the left), then set Series Options, Plot Series On by selecting the Secondary Axis item. Click Close
  9. In the Chart Elements box select the Series “Volume” element
  10. From the Chart Tools tab, select Design > Type > Change Chart Type to display the Change Chart Type dialog box
  11. In the Change Chart Type dialog box, select Column (on the left), then the Clustered Column item from the Column group. Click OK
  12. Set the trading day order for the secondary date axis – from the Chart Tools tab, select Layout > Axes > Secondary Horizontal Axis then select the Show Axis without labeling element
  13. After appropriate Axis formatting, the final chart is shown in figure 1 (Excel Web App #1) and figure 2 (an image of the chart)
  14. Fig 1: Excel Web App #1: Price volume chart developed in Excel 2010 – with non trading gaps eliminated
  15. xlf-price-volume-chart-3
    Fig 2: Price volume chart developed in Excel 2010 – with non trading gaps eliminated

Creating a chart template

To save the formats applied in steps 1 to 13 above, you can create a chart template.

To create the template in Excel 2010:

  1. Select the chart. Its names will appear in the Name Box
  2. On the Chart Tools > Design tab, select Type > Save As Template to display the Save Chart Template dialog box. In the File name: box enter the name xlf-price-volume, and select Save as type: Chart Template Files
  3. The template file will be stored in your ../AppData/Roaming/Microsoft/Templates/Charts folder (or similar). This is the only location where Excel will retrieve the file. Click Save
  4. You can copy the file to another computer. Ensure its assigned to the correct folder or directory

Using the template in Excel 2013

To use the template in Excel 2013:

  1. Select the chart data
  2. On the Insert tab, click the Charts group dialog box launcher to display the Insert Chart dialog box
  3. Click the All Charts tab, then select Templates in the left column. If the template is not visible, use the Manage Templates… button to locate the template
  4. A copy of the template file is available here: xlf-price-volume.crtx [5 KB]
  5. Select the template, then click OK
  6. The chart, with correct format should now be complete
  7. Important notes: the template data series are – Series 1: Volume, and Series 2: Adj Price
  8. You may need to Edit the series details with the Chart Tools > Data > Select Data Select Data Source dialog box, to incorporate the Stock Analyser specifications


  • This example was developed in Excel 2010 32 bit.
  • Last modified: 5 Sep 2015, 11:16 am [Australian Eastern Standard Time (AEST)]