A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Excel » Charts and Charting
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Excel 2007 Stock market Charts



 
 
Thread Tools Display Modes
  #1  
Old April 30th, 2010, 08:18 PM posted to microsoft.public.excel.charting
ElisabethJacques
external usenet poster
 
Posts: 1
Default Excel 2007 Stock market Charts

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.
  #2  
Old May 1st, 2010, 06:59 PM posted to microsoft.public.excel.charting
Jon Peltier[_2_]
external usenet poster
 
Posts: 386
Default Excel 2007 Stock market Charts

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/sto...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
-------
Jon Peltier
Peltier Technical Services, Inc.
774-275-0064
http://peltiertech.com/


On 4/30/2010 3:18 PM, ElisabethJacques wrote:
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.

 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 07:23 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.