Excel 2007 Stock market Charts
in [Charts]
|
Prev: Bar width on existing chart changes when changing source data
Next: Worksheet tab names change back to default
From: ElisabethJacques on 30 Apr 2010 15:18 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.
From: Jon Peltier on 1 May 2010 13:59
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 ------- 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. |