Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.charting
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   Report Post  
Posted to microsoft.public.excel.charting
external usenet poster
 
Posts: 461
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.

Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Stock Market Calculation Stock Market Calculation Excel Worksheet Functions 2 February 5th 08 03:34 PM
Cannot combine stock charts with other chart types in Excel 2007 rrr Charts and Charting in Excel 2 January 15th 07 07:40 PM
Stock Market Challenge mevetts Excel Discussion (Misc queries) 7 January 17th 06 09:55 PM
Excel should have an open/close high/low chart for stock market u. Harold Hommes Charts and Charting in Excel 2 March 24th 05 05:08 PM
access stock market quotations Buzz Bennett Excel Worksheet Functions 3 February 9th 05 02:40 PM


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

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

About Us

"It's about Microsoft Excel"