Creating a High-Low-Close chart with multiple series?

[Guest]

Hi Everyone,

I’m trying to create a High-Low-Close stock chart with 2 data series. Right
now, I can only get the chart to plot a single data series. I’ve spent some
time playing with the chart options and doing some internet research but
still I haven’t found any answers.

My data looks something like this:
Stock A
Date High Low Close
6/30/2005 5 3 4
7/30/2005 10 6 8
8/30/2005 8 7 7.5
9/30/2005 9 2 5.5
10/30/2005 6 3 4.5
Stock B
Date High Low Close
6/30/2005 50 30 40
7/30/2005 100 60 80
8/30/2005 80 70 75
9/30/2005 90 20 55
10/30/2005 60 30 45

Any thoughts?

Thanks!
The Chad

 

[Jon Peltier]

TC -

You can only have one set of high-low lines per axis group, and it's these high-low
lines which provide the vertical lines. What you need to do is put all six series
into the chart, then format one set of three to be plotted on the secondary axis
(double click a series, and on the Axis tab, select Secondary). Then format the
secondary axis Close series (select it using the drop down on the Chart toolbar,
because it's not visible) so it uses the short right pointing dash as its marker,
and on the Options tab of the formatting dialog, check High-Low Lines.

An alternative approach is to plot only the Close data for the stocks of interest,
and compute two sets of line lengths for each: one for High-Close and the other for
Close-Low. Use these ranges as the ranges for custom Y error bars.

Read about error bars here:

http://peltiertech.com/Excel/ChartsHowTo/ErrorBars.html

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
http://PeltierTech.com/
_______

 

[Guest]

Hi Jon -

Similar to TC, I want to create a high-low-close chart to compare 2 sets of
data (similar to how you can juxtapose 2 or more sets of data in bar charts).


I tried your suggestions below, but I couldn't get Excel to allow me to
create a stock chart with 6 series. Also, I am trying to get the 2 data sets
to be next to each other and not on top of one another on the x-axis.

Any ideas? Thanks!

~Sonz

 

[Jon Peltier]

Using a line chart, you can offset one set by half a day. Suppose it's the
secondary axis you want to offset. First, go to Chart menu > Chart Options >
Axes tab and check the box for secondary category axis (time scale or auto
option), Then double click the secondary X axis, change the minimum to a day
earlier, and uncheck the Value Axis Crosses Between Dates box.

The offset by 1/2 day is confusing, because the points from series B are
directly between those of series A, so it's hard to match them in your mind.
Here's another approach.

You have columns for Date, H-L-C of stock A, and H-L-C of stock B. Add a
column for high minus close of stock B and another column for close minus
low of stock B. Also add a fraction (e.g., 0.1) to the dates. (Type 0.1 in a
cell, copy the cell, select the dates, use Edit menu > Paste Special >
Operation > Add.)

Make a HLC chart using the stock A data and the dates. Even though the dates
have the added fraction, the chart plots the integer value of each date,
because a stock chart uses the line chart's category or date axis. Copy the
close data for stock B, select the chart, use Edit menu > Paste Special >
New Series. Select this series, go to Chart menu > Chart Type, and convert
it to an XY chart type, markers only. Double click this series and on the
Axis tab, choose Primary. The points are plotted on the date axis, but as an
XY series, the exact value of the date is plotted, so it's offset by 0.1
days from the other series. Format the marker as a small dash, and add the
(high minus close) column as positive and the (close minus low) column as
negative custom Y error bars. Format the error bars so they have no end cap.
Format the first stock with one color small dash marker and high-low lines,
and the other stock with a different color small dash marker and error bars.

- Jon