# Price volume chart [2] - Excel 2013

## Version 2

A price-volume 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 price-volume 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_{t-1})\) 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)`

where`INDEX`

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 the`revDateV`

target is longer than the`DateV`

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 the`INDEX`

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 ③ returns the row number of the
**revDref**cell. The value 11. - Thus, the value of items ① ② and ③, is \(23-(15-9-2)=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
**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 Column-Line. 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 item, then select`Series1`

, and click .- Follow the remaining
**steps 2 to 5**inclusive.

**Step 5:**The dynamic range for the**ChtVol**series is`'xlf-chart-data-2.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 the`revDateV`

name.

- 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. More >>- The completed chart is shown in figure 5.

## The BOQ price volume chart

**Published:**15 November 2014**Revised:**Friday 14th of February 2020 - 12:16 PM, [Australian Eastern Standard Time (EST)]