Excel Chart Add-Ins | Training | Custom Solutions | Charts and Tutorials | Peltier Tech Blog

Peltier Tech
Chart Add-Ins

Excel Dashboards

Books at Amazon.com

Excel Stock Charts with Added Series.

A common problem people have is adding an extra series to a stock chart. It would be very helpful to add a market index, for example, or another moving value to a stock chart to see how the stock price moves with respect to another factor. This can be done, but you need to follow a few extra steps. The extra series can be added to any of Excel's built-in stock chart subtypes. The High-Low-Close (HLC) and Open-High-Low-Close (OHLC) charts follow a straightforward procedure; the Volume-High-Low-Close (VHLC) and Volume-Open-High-Low-Close (VOHLC) charts are only slightly more complicated because the Volume series is plotted on the primary axes, and the other parameters on the secondary axes. This technique works for stock charts with either time-scale or category type X axes.

Arrangement of the Data

Before constructing a stock chart, you must first arrange the data neatly in your worksheet, so Excel can readily construct the chart. (This is always a good idea, but especially for a stock chart.) It is easier if the columns are contiguous (without intervening gaps), but this is not necessary. To select discontiguous data, select one range, then hold down the Ctrl key while selecting each subsequent area.

If you remember what Excel calls each chart type, you can remember how to arrange the data. The HLC chart needs High, Low, and Closing prices in adjacent columns in that order; the OHLC chart needs Opening, High, Low, and Closing prices; VHLC and VOHLC need Volume before HLC and OHLC data. The column with Dates should come to the left of these, and the column with additional series should come to the right. The data can be arranged in rows if preferred; replace left-to-right order with top-to-bottom order. Instead of dates, you could use labels (e.g., company names) for category labels. If you leave out labels entirely, Excel will use integer values 1, 2, 3,....

Below is the data used in these examples. It consists of columns for Date, Volume, Open, High, Low, and Close, plus Index for the series to be added.

	A	B	C	D	E	F	G
1	Date	Volume	Open	High	Low	Close	Index
2	6/5/02	2,608,525	103.46	105.76	92.38	100.94	105.52
3	6/6/02	5,231,455	100.26	102.45	90.14	93.45	121.52
4	6/7/02	7,030,896	98.05	102.11	85.01	99.89	102.50
5	6/8/02	3,340,173	100.32	106.01	94.04	99.45	114.67
6	6/9/02	8,750,073	99.74	101.24	98.16	99.28	111.87
7	6/10/02	3,375,692	92.11	107.70	91.02	102.17	98.11
8	6/11/02	6,573,736	107.80	110.36	101.62	110.07	89.03
9	6/12/02	4,566,834	107.56	115.97	106.89	112.39	75.95
10	6/13/02	4,781,567	112.86	120.32	112.15	117.52	87.17
11	6/14/02	7,675,944	115.02	122.03	114.67	114.75	95.90
12	6/15/02	2,349,222	108.53	120.46	106.21	116.85	111.02
13	6/16/02	5,612,790	114.97	118.08	113.55	116.69	98.48
14	6/17/02	8,114,218	127.14	128.23	110.91	117.25	93.30
15	6/18/02	4,638,410	118.89	120.55	108.09	112.52	91.97
16	6/19/02	5,100,549	105.57	112.58	105.42	109.12	87.49
17	6/20/02	5,728,098	112.29	118.94	106.15	106.30	85.63
18	6/21/02	6,560,044	98.11	106.89	97.62	101.37	75.70

Construct the Stock Chart

The first step in our procedure is to construct a stock chart. With the mouse, select the chart's source data (not including the added series), then start the Chart Wizard to create the chart (Insert menu, Chart..., or use the Chart Wizard button). Select one of the stock chart types in the Chart Wizard. Excel will choose one for you based on the size of the range you'd selected, but you have a limited ability to override Excel.

The result, after a little format tweaking, is shown below for OHLC and VOHLC chart types. I will describe the complete procedure using just these two of the four stock chart types, but the types neglected here behave in the same way.

Add the Additional Series

The next step is to add the extra series to the chart. The Copy-and-Paste-Special procedure works most easily:

Select the range containing the dates (including the header row), then hold down the Ctrl button while selecting the range containing the data values (also including the header). It is important that the two ranges selected contain the same number of rows, start on the same row, and end on the same row.
With this discontiguous data range selected, go to the Edit menu and Copy it (or use Ctrl-C). The range will be highlighted by the "marching ants" outline.
Select the chart. From the Edit menu, select Paste Special... (below) and choose the options shown (Add Cells as New Series, Names in First Row, Categories in First Column).

An alternative method to add a series to a chart is to right click on the chart and choose Source Data from the context menu that pops up.

Choose the Series tab in the Source Data dialog box, click the Add button.
Click in the Name box, and click in the worksheet cell with the series name ("Index").
Replace the ={1} in the Values box by selecting the Y Values data range for the new series.
Leave the Category (X) Labels entry alone.

The somewhat troubling result is shown below. It looks like we've mucked up our chart, and I think most people give up here. But we'll soon have it straight again.

The data from the added series has stretched some of the high-low lines and candlesticks in our stock chart.

The price markers of this chart have been distorted too.

The extra series was added to this chart using the Source Data dialog. The series is column style, clustered next to the Volume series. The columns do not show because their heights are dwarfed by the large Volume values.

Format the New Series

Now select this new series you've added. It is most easily selected using the Chart Objects drop down box on the Chart command bar, which on my machine is docked at the bottom of the Excel window and really "drops up" (see below).

With this new series selected, go to the Chart menu, select Chart Type..., and choose an XY Scatter type from the dialog box. It is important to use the XY Scatter chart type, because a Line chart type interferes with the candlesticks and high-low lines of the stock chart. Select this new series again, right click and select Format Data Series... and on the Patterns tab, format it the way you like. In these examples I've used blue diamonds with a blue connecting line. The series may not yet fit where we want it, and there may even be some severe distortion.

The steps needed to finish up depend on the chart subtype you've selected, on the X axis type (time scale or category), and on the method used to add the new series. Use the handy table below to determine the steps you need to fix the chart.

OHLC-HLC	Time Scale X Axis	Category X Axis
Copy-and- Paste-Special	Go Here	Go Here
Source Data	Go Here	Finished!

VOHLC-VHLC	Time Scale X Axis	Category X Axis
Copy-and- Paste-Special	Go Here	Go Here
Source Data	Go Here	Go Here

HLC and OHLC, Time Scale X Axis, New Series Pasted Onto the Chart

This chart is easy to fix:

Right click on the series, select Format Series from the popup menu;
On the Axis tab, select Primary Axis;
You will probably have to reset the X axis scaling parameters.

Click here to see the finished result.

HLC and OHLC, Category Type X Axis, New Series Pasted Onto the Chart

This chart is fairly easy to fix:

Right click on the series, select Format Series from the popup menu;
On the Axis tab, select Primary Axis.

It looks like we've just completely trashed the chart, but Excel just got confused about the series' dueling category data:

Right click on the Chart, select Source Data from the popup menu;
On the Series tab, select Index from the Series list box, and delete the contents of the X Values box.

Click here to see the finished result.

HLC and OHLC, Time Scale X Axis, New Series Added via Source Data

Right click on the Chart, select Source Data from the popup menu;
On the Series tab, select Index from the Series list box, click in the X Values box, and select the Dates range in the worksheet with your mouse.

Click here to see the finished result.

VHLC and VOHLC, Time Scale X Axis, New Series Pasted Onto the Chart

This chart is easy to fix:

Right click on the series, select Format Series from the popup menu;
On the Axis tab, select Secondary Axis.

Click here to see the finished result.

VHLC and VOHLC, Time Scale X Axis, New Series Added via Source Data

Right click on the Chart, select Source Data from the popup menu;
On the Series tab, select Index from the Series list box, click in the X Values box, and select the Dates range in the worksheet with your mouse;
Right click on the Index series, select Format Series from the popup menu;
On the Axis tab, select Secondary Axis.

Click here to see the finished result.

VHLC and VOHLC, Category X Axis, New Series Pasted Onto the Chart

Right click on the series, select Format Series from the popup menu;
On the Axis tab, select Secondary Axis;
Right click on the Chart, select Source Data from the popup menu;
On the Series tab, select Index from the Series list box, and delete the contents of the X Values box.

Click here to see the finished result.

VHLC and VOHLC, Category X Axis, New Series Added via Source Data

This chart is fairly easy to fix:

Right click on the series, select Format Series from the popup menu;
On the Axis tab, select Secondary Axis.

Click here to see the finished result.

Conclusion

You're finished! The end result is magnificent, as promised.

�

Peltier Technical Services, Inc.

Excel Chart Add-Ins | Training | Custom Solutions | Charts and Tutorials | PTS Blog

Peltier Technical Services, Inc., Copyright © 2010. All rights reserved.
You may link to this article or portions of it on your site, but copying is prohibited without permission of Peltier Technical Services.

Microsoft Most Valuable Professional

My MVP Profile