Error when creating a stock chart from data in .CSV file

[Curious]

I want to create a chart containing high price, low price, and
closing
price for each day. Therefore, I have four columns in my Excel
sheet.

However, there's an error when I create a stock chart:

"To create this stock chart, arrange the data on your sheet in this
order: high price, low price, closing price. Use dates or stock names
as labels."

Anyone could tell me what is wrong and how to get this error fixed?

 

[Jon Peltier]

Is the csv file open?
Have you selected the data for the chart wizard?

- Jon

 

[Curious]

Yes, the .csv file is open.

I tried to select all of the data (including headers) in the sheet
before I select the chart wizard. I got the error message.

I also tried to select all of the data (without selecting the headers)
in the sheet before I select the chart wizard. I got the same error.

 

[Jon Peltier]

Reply to this post, with several rows of your data copied and pasted into
the reply. Excel may notice something irregular with your data. For example,
if it is not recognized as numerical, you may have problems creating a
chart. (Though I think I would expect the chart to come out wrong, and no
the error message....)

- Jon

 

[Curious]

Jon,

Here're the beginning part of the data in .CSV:

Date High Price Low Price Closing Price
19910717 171.38 170.85 171.31
19910718 177.27 171.29 177.27
19910719 172.87 172.2 172.87
19910722 172.97 172.19 172.42
19910723 172.66 171.12 171.24
19910724 171.25 170.17 170.18
19910725 170.57 169.84 170.56
19910726 170.71 170.4 170.65
19910729 170.71 170.3 170.66
19910730 171.56 170.67 171.54
19910731 172.78 171.49 172.76
19910801 172.77 172.24 172.24
19910802 173.18 171.69 173.05
19910805 173.06 172.37 172.49
19910806 172.92 171.96 172.91
19910807 173.28 172.93 173.06
19910808 173.49 172.94 173.21
19910809 173.39 173.05 173.32
19910812 173.57 173.01 173.55
19910813 174.54 173.55 174.44

Please let me know if you can create a stock chart for the data.

 

[Jon Peltier]

Those aren't dates in the first column, those are large numbers. Excel is
looking specifically for something it recognizes as date-formatted, or text
labels. It couldn't find it, so it gave you the error. You have two ways to
proceed:

1. Delete the label Dates in the top left cell, then select the entire range
and create the chart. The empty cell overrides Excel's own parsing of the
data.

2. Select the three right columns and create the chart. In step two of the
chart wizard, click on the Series tab, click in the Category (X) Axis Labels
box, and select your pseudo-dates, then click Finish.

- Jon

 

[Curious]

Hi Jon,

Many thanks for your valuable advice! I'll try what you suggest in the
evening.

 

[Curious]

Jon,

According to your suggestion, I was able to create a chart. Thanks for
that.

Now I have another question: The chart looks different from the sample
stock chart although I picked "Stock" chart option.

I wanted to, for each date, display its high price and low price
(closing price is really un-important to me). Therefore, I was hoping
to create a bar chart with each bar representing a high price on its
top while low price at its bottom. Now I've got a chart with a wave
type of line representing closing price only.

Any advice on how to get a bar chart to represent daily high price and
low price?

 

[Curious]

Jon,

I followed your instructions and I was able to create chart
successfully. Thanks for that.

Now I have another question - The chart looks like a wave type of line
representing "closing price" only. What I really care about are "high
price" and "low price" for each day. Therefore, I need to create a bar
chart with each bar representing each day - The top of the bar is high
price while the bottom of the bar is low price.

Any advice on how to create this kind of chart?

 

[Jon Peltier]

This tutorial may be helpful:

http://peltiertech.com/Excel/ChartsHowTo/StockChartsAndLineChartTricks.html

Make a line chart using the high and low series, omitting the close. Add
up/down bars, and remove the lines and markers from the series.

- Jon