E
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.
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.
