Two Open-High-Low-Close series in a single Chart

[Tj]

I am trying to do some (what seems to me as) advanced
charting in Excel.

I need to have two series of Open-High-Low-Close charts,
within a single chart (side-by-side in pairs).

So far as I can tell, this doesnt appear to be possible in
Excel. I can get two series of Stacked-Bar charts to
appear as I want, but then they dont show the negative
values as I want (like the OHLC charts do).

I have an example workbook that would probably make it
pretty clear what I am trying to achieve.

 

[Jon Peltier]

TJ -

Do the stacked parts have to span an axis? You could try the technique
on my web page to accomplish this:

http://peltiertech.com/Excel/Charts/StackedColumnsAboveAndBelow.html

Another approach is to make an XY Scatter chart, which allows you to
stagger the values about the date: subtract a constant (e.g., 0.2) from
the date for one series, and add it to the other. Use this approach to
make a left tick for each Open series:

http://peltiertech.com/Excel/Charts/StockChartLeftTickOpen.html

and use error bars for the high-low lines. Use a couple helper columns
to compute the difference between high and open and between low and
open, and use these as custom positive and negative error bars on the
Open series. Choose the error bar format without the cross on the end.

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

 

[Tj]

Hey Jon,
First off.... top notch work, I really appreciate the
knowledge you've shared on your site (I came across it in
my google'ing while trying to find an answer to this). I
don't think the solutions you pointed me to would work for
my needs, as they arent 0 based (and the chart wouldnt be
useful if it were).

Here is my problem. If you look at the attached worksheet
(in the email I sent directly to you), there are
essentially two sets of Datasheets and Chartsheets. The
first uses an Open-High-Low-Close chart. This gives us the
negative "growth" depiction (red). However, I have yet to
find a way of adding more than one series to a OHLC chart
(Excel seems to restrict it to only one set of four series).

The 2nd set is a Stacked Bar chart. This does meet the
requiment of showing the two series side-by-side, but
doesnt adequately display negative "growth" (they are below
the X axis line).

Do you know of any way that we could get both the negative
growth to be shown (as in the OHLC chart), and still have
the two series shown side-by-side (as in the stacked bar
chart)?

I would really appreciate any info/direction you would have...
Regards,
Tj