Version 2
A pricevolume chart shows two series in the one Excel chart, and is often referred to as a combination or combo chart. Price is the primary \(y\) axis, Volume is the secondary \(y\) axis, and the Date vector in on the horizontal \(x\) axis.
Two particular features of the pricevolume time series dates are addressed in this module:
 The Date vector of the source data is in descending order. It is customary, however, that the chart date axis be in ascending order, and
 The Excel chart date axis automatically includes all calendar days. In practice, however, the axis should include actual trading days only
Example: this module is based on one month of stock price volume data for the company Bank of Queensland Ltd – Australian stock exchange code – BOQ.AX. The data source for the BOQ.AX price volume series for the month of July 2014, is Yahoo Finance.
The data has seven fields, labeled \(Date, … , Adj \ Close\). You can download the sample file from the Excel Web App download link in figure 1.
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_{t1})\) is the log transform of the adjusted closing price series using the Excel LN function.
Important: Normally the issues described in points 1 and 2 above can be solved by converting the \(x\) axis date vector to text, and displaying the series in reverse order. This works in the case of a single chart series, but fails in the case of a combination chart, as only one of the series reverses to match the dates. The second series does not reverse its order to match the dates.
The obvious solution is to sort the data on the date column in ascending order, or construct a specific range with dates in ascending order for the chart series. The latter method is adopted here and described in item 2 below.
Create a price volume chart in Excel 2013
 Data series (noncontiguous) sorted on Date vector: in the original data series on the Data BOQ.AX worksheet, the Date range, A2:H25 is not adjacent to the Volume / Price range. This means that the Date, Volume and Price series are noncontiguous and cannot be selected as a block.
 Select the noncontiguous 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 noncontiguous range in a cell of your work area,
$A$2:$A$24,$F$2:$G$24
, then copy and paste to the Name Box, or the F5 dialog. This method is useful when the series range is large, and may save retyping the address.
 Data series (contiguous) workarea range: Create a linked reverse order target,
PriceVolumeChart
as shown in figure 2. This allows the structure of the source data on the Data BOQ.AX worksheet to remain unaltered, ie. in descending order. Let the Date vector in column A of the Data BOQ.AX worksheet have the name DateV.
 Use the Excel
INDEX
function to link the DateV source to the reverse order target vector revDateV
 Syntax:
INDEX(array,row_num,column_num)
whereINDEX
returns the value in the cell at the intersection of row_num and column_num The equation to link the reversed target is
=INDEX(DateV,ROWS(DateV)(ROW()ROW(revDref)2),1)
 This is then wrapped in an
IFERROR
function=IFERROR(INDEX(DateV,ROWS(DateV)(ROW()ROW(revDref)2),1),"")
to handle the case where therevDateV
target is longer than theDateV
source. Create a revDateData range with enough rows to accommodate the maximum number of observations in the analysis window. In other words, the maximum length of the DateV range plus 1.
 The equation to link the reversed target is

 In figure 2, cell A11, marked ? has the name revDref, and the item ? displays the formula in cell A13. Attaching the anchor to a label rather than a formula avoids the first row of the formulas for ChtVal and ChtAdCl being linked to a name reference with absolute addressing
 Items ? ? and ? are the components of the
row_num
parameter to theINDEX
function  The revDateV vector starts in row 13, ie. cell A13. The formula is cell A12, linking to the 30 June 2014 allows for inclusion of a returns vector at a later stage.
 How it Works: a discussion of the equation in cell A15, denoted ? in figure 2. Looking at the DateV vector in figure 1, the date 3 July 2014 is element number 21 with base 0. This is also the 3rd last element in the source (figure 1), and it is the 3rd element in the revDref target (figure 2).
 In ?, item ? returns the number of rows in the DateV vector. The value 23.
 Item ? returns the row number of the formula. The value 15 which is easily verified.
 Item 3 returns the row number of the revDref cell. The value 11.
 Thus, the value of items ? ? and ?, is \(23(1592)=19\) where the 2 is an adjustment to align the relevant bases.
 Assign dynamic range names to the three vectors in figure 2
 Name revDateV; Refers to
=OFFSET(revDref,2,0,COUNT(DateV))
. This vector is the anchor (reference) for the ChtVol and ChtAdCl.  Name ChtVol; Refers to
=OFFSET(revDateV,0,1)
. One (1) column to the right of revDateV.  Name ChtAdCl; Refers to
=OFFSET(revDateV,0,2)
. Two (2) columns to the right of revDateV.
 Name revDateV; Refers to
 Select the contiguous revDateV data range for July 2014: select the range
WorkArea!A13:C35
in figure 2. Selecting columns A to C is important, but the number of rows selected is simply a temporary place holder to be replaced by the dynamic range names.
 Insert (the chart): On the ribbon, select the sequence to display the Insert Chart dialog. The See all charts item is in the bottom right corner of the ribbon Charts group.
 Then, on the dialog, select the tab, then select the item at the bottom of the left column as shown in figure 3. Then on the upper right, select the item. Click the button.
 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 box at the right of Series Name: Series1, Chart Type: Clustered Column in figure 2. This also activates the Custom Combination item, the forth image to the right of Clustered ColumnLine. Click OK. The resultant chart is shown in figure 3.
 Assign the dynamic range names to the chart series by following the steps in the xlfImageGallery. Click an image to view the details.
 Step 1: Right click the chart background and select the
Series1
, and click . item, then select  Follow the remaining steps 2 to 5 inclusive.
 Step 5: The dynamic range for the ChtVol series is
'xlfchartdata2.xlsx'!ChtVol
. Note: The address could have been typed directly in step 2. The single quotes around the workbook name are required because the name includes non alphanumeric minus characters.  Repeat the five steps to apply the
ChtAdCl
name and therevDateV
name.
 Step 1: Right click the chart background and select the
 To eliminate the non trading day gaps in the date vector, select the Horizontal (Category) axis, then click the Format Axis item. See figure 4 for details.
Format the chart
 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 . To reduce chart clutter, remove the Chart Title by selecting . Remove the Series labels by selecting .
 Format the Primary and Secondary axes. This entry was posted on .