Excel 2007 Stock market Charts

[ElisabethJacques]

I teach a class in financial modeling. Some of my students use Excel 2003 and
some 2007 (They are required to consult the company from whom they have
accepted an offer and use the version of excel the company uses).

In 2003 we generate a candlestick (Open High Low Close) Stock Chart for a
company. We then add Volume. What is a good exercise in 2003 is a disaster in
2007. The candlestick has a fixed gap (unless you go into VBA (why would you
change this?)).

When you add volume to the chart it appears that the stock price movements
take as the category labels the dates of the stock price (as they should),
but when you add volume it sets the category axis to 1,2,3 ... We have tried
forcing the category axis to be equal to the dates, but now it sets the
volume to days and the prices to months. By setting the base to months the
volume goes to the year 2057 and by setting the base to days the stock prices
cram themselves into the first 20% of the chart. Changing one blows up the
other. I suspect that they are kept in a hidden secondary category axis that
you cannot access in any way - we've tried.

The excel 2007 preset Volume Open High Low Close Chart has two major
problems. One, nobody in Finance does Volume Open High Low Close. It's Open
High Low Close Volume. This means adjusting every data set in finance.

Even if you relent and reorder all your data, the volume numbers are
reversed. The Volume is on the primary axis and the stock price on the
secondary axis. Why would anyone ever do this? So you have to change them
manually. More importantly the chart is wrong. If the stock price and volume
both increase over time then the chart shows the stock price increasing but
the volume decreasing.

To make matters worse, if you go back to excel 2003 and generate the chart
that actually works and then open that same file in 2007 the chart reverses
the volume bars so that once more the volume looks like it is decreasing.
This is a major problem. If you generate a chart with volume if you open it
in 2003 the volume is increasing and if you open the very same file in 2007
the volume is decreasing. It makes a great class session because the moral of
the story is "Just because you do it right doesn't mean it is right." In
financial modeling that's an important lesson, but PLEASE FIX EXCEL 2007.

Is there some way to begin a dialog with Microsoft that addresses these
kinds of problems. There are so many things the charts should be doing but
don't. All we got in 2007 was artisitry and non-standard colors. What we
really need is accuracy and useability.

 

[Jon Peltier]

I don't know whether it helps your situation, but I've written up the
protocol for adding series to Excel 2003 and 2007 stock charts. See

Stock Charts in Excel 2007
http://peltiertech.com/WordPress/stock-charts-in-excel-2007/

I think the candlestick gap width was an oversight: neither the folks
writing specs for the "upgraded" chart engine in 2007 nor those doing
the programming or testing make Excel charts in real life, so they
missed details like this.

- Jon

 

[jakes12345]

ElisabethJacques wrote on 04/30/2010 15:18 ET

I teach a class in financial modeling. Some of my students use Excel 2003 an
some 2007 (They are required to consult the company from whom they hav
accepted an offer and use the version of excel the company uses)

In 2003 we generate a candlestick (Open High Low Close) Stock Chart for
company. We then add Volume. What is a good exercise in 2003 is a disaster i
2007. The candlestick has a fixed gap (unless you go into VBA (why would yo
change this?))

When you add volume to the chart it appears that the stock price movement
take as the category labels the dates of the stock price (as they should)
but when you add volume it sets the category axis to 1,2,3 ... We have trie
forcing the category axis to be equal to the dates, but now it sets th
volume to days and the prices to months. By setting the base to months th
volume goes to the year 2057 and by setting the base to days the stock price
cram themselves into the first 20% of the chart. Changing one blows up th
other. I suspect that they are kept in a hidden secondary category axis tha
you cannot access in any way - we've tried

The excel 2007 preset Volume Open High Low Close Chart has two majo
problems. One, nobody in Finance does Volume Open High Low Close. It's Ope
High Low Close Volume. This means adjusting every data set in finance

Even if you relent and reorder all your data, the volume numbers ar
reversed. The Volume is on the primary axis and the stock price on th
secondary axis. Why would anyone ever do this? So you have to change the
manually. More importantly the chart is wrong. If the stock price and volum
both increase over time then the chart shows the stock price increasing bu
the volume decreasing

To make matters worse, if you go back to excel 2003 and generate the char
that actually works and then open that same file in 2007 the chart reverse
the volume bars so that once more the volume looks like it is decreasing
This is a major problem. If you generate a chart with volume if you open i
in 2003 the volume is increasing and if you open the very same file in 200
the volume is decreasing. It makes a great class session because the moral o
the story is "Just because you do it right doesn't mean it is right.
I
financial modeling that's an important lesson, but PLEASE FIX EXCEL 2007

Is there some way to begin a dialog with Microsoft that addresses thes
kinds of problems. There are so many things the charts should be doing bu
don't. All we got in 2007 was artisitry and non-standard colors. What w
really need is accuracy and useability

In my opinion you are mistaking a bit while transporting the data from MS 3 t
7. Try to use some company software that is dedicated to provide bette
importing and exporting facility from your daily analysis report to exce
sheet
Market data excel is one of the best tool to handle this type of work
See this first : http://www.livevol.com/options-excel.html

 

[Martin Brown]

ElisabethJacques wrote on 04/30/2010 15:18 ET :
In my opinion you are mistaking a bit while transporting the data from MS 3 to
7. Try to use some company software that is dedicated to provide better
importing and exporting facility from your daily analysis report to excel
sheet.

There remains the problem that XL2007 is gratuitously incompatible with
all previous versions of Excel - particularly so if you access shapes
from VBA. The chart graphics are also incredibly much slower on XL2007
and subject to some nasty race conditions. XL2007 = MFU

The default chart settings also look like they were drawn by a three
year old with thick wax crayons in dodgy indistinct colours. Exactly
what you do not want when plotting large volumes of data - and to add
insult to injury it slows down even more when you customise the lines.

XL2010 is slightly better although some of the corrections break the
work-arounds put in to make existing XL2003 code work on XL2007

YMMV but I'd say XL2007 was best avoided.

Regards,
Martin Brown