Error: "to create this stock chart arrange the data on your sheet in this order"

[persson121]

Hi.

I have made a big list of numbers.
But i can't make a candlestick chart of it.

A row contains Open
B row contains High
C row contains Low
D row contains Close

But when i click "create candlestick" picture it's saying "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"

Here is an example of my numbers:

Please Login or Register  to view this content.

What is the problem?

[martindwilson]

have you read this
http://www.officetooltips.com/excel/...ock_chart.html

[MarvinP]

Hi persson121,

See if this attached sample helps. I think you need to select the cells before clicking on Insert Chart.??

[persson121]

Yes. I have read that.
It didn't help me.

[persson121]

But i've slecting the cells but it's the same story when i press "candlestick" button.
But I might slecting at wrong way. How do you mean?

[MarvinP]

In my example I select from A1 to E5 and leave it selected. Then I click on Insert Chart of type Open-High-Low-Close and a chart appears.

[persson121]

In my example I select from A1 to E5 and leave it selected. Then I click on Insert Chart of type Open-High-Low-Close and a chart appears.

But i use csv file. You use xlxs file.
I have convert from csv to xlsx now.

But it's the same.

[MarvinP]

Check the numbers in your file or attach a sample. I'd bet they are TEXT and not real numbers. Excel will barf if you try to use text instead of numbers for a chart. Try to add two of them together and see what happens. OR - Convert them in another range by adding zero to all of them to make the numeric.

Also - don't trust spaces if you are importing into XL. I've seen too many CHR(160) that look like spaces but not. You may have this problem also.

[persson121]

Try this. I have also tested to remove the date row then make a chart. I didn't work neither.

http://uppit.com/l50w67sggkah/Copy_o...ge_USDJPY.xlsx

[martindwilson]

persson121 no one is or should click on that link, you can attach a workbook directly to this forum
go advanced and click the paperclip icon

[persson121]

Copy of exchange_USDJPY.xlsx

Here.

I haveing also to made a script who can delete the date row and makes rows like this
=TODAY()
=A1-1
=A2-2
etc

[martindwilson]

you obviously didn't check the numbers to see if they were text see sheet 1
fixed on sheet 2 by adding 0 to all cells try using that as your source

[persson121]

you obviously didn't check the numbers to see if they were text see sheet 1
fixed on sheet 2 by adding 0 to all cells try using that as your source

So in datecolumn it cannot be 2002-03-04 or ######?
I need the datenumbers. That's why im gonna zoom in to month or years or weeks with it.

exchange_USDJPY.xlsx

It's not the same file, but this won't work with a candlestick too. Why?

[MarvinP]

Hi persson121,

Using your file above I added zero to each of the cells and then did an Open, close, high, low chart. See the attached. It seems the entire problem is that your data (or some of it) IS NOT numbers. Excel knows numbers from text and will not do charts with text. See the attached for the answer.

[persson121]

Okej. Thanks.

But how did you write the =A2611+0 etc?`
Did you use VBA or is it a function at Excel who can do that?
In A column i write date numbers like 2004-03-20
In B column i write Open numbers like 234.3
In C column i write High numbers like 254.3
In D column i write Low numbers like 233.7
In E column i write Close numbers like 244.3

Then i mark it and press "candlestick" button and i get an error. But when you do it, it works.
I don't realy understanding what you are trying to say with " I added zero to each of the cells ". I cannot se any zero numbers.

I didn't have any text in my file.xlsx as i know/see.

[martindwilson]

put 0 in a spare cell, copy it select all your numbers, right click paste special /add

[MarvinP]

The whole answer is that you have text instead of numbers in your cells. They look like numbers but Excel knows the difference. You need to learn how to change or convert those text cells to numbers. Then your candelstick charts will work.

It is like magic!! If you start with a text of 120 (this is text not a value to excel) and you add zero to it, excel will then know it is a value and change it to a value from text. It looks exactly the same but excel knows it is now a number and not text. When you try to do a chart that has text in it, Excel knows and gives an error message. This is what you are running into.

Martin is suggesting you use method 4 from http://support.microsoft.com/kb/291047 to convert your text to numbers.

[persson121]

It worked half ways.
I have add 0 to the OHLC cloumns, but it's not working.
I have not add 0 to date columns and the element Open, High, Low, Close

But still, no candlestick

[martindwilson]

I have not add 0 to date columns and the element Open, High, Low, Close

why not?
how many times do you have to be to add 0 to everything.
test the cells as i did use =istext(a1) if its a number it should say false. come back when all your values show false

[persson121]

why not?
how many times do you have to be to add 0 to everything.
test the cells as i did use =istext(a1) if its a number it should say false. come back when all your values show false

I have done exactly as this one
http://www.excelforum.com/attachment...9&d=1340730533

I have only marked the "numbers" like the picture. Then ad it to 0.

[martindwilson]

post your workbook again but have you tested the cells?

[persson121]

post your workbook again but have you tested the cells?

Yes. I have tested the cells.
exchange_USDJPY.xlsx

[martindwilson]

where is your test ?
well they are still text, look here are the results for the first 48 b2:e13 , they should all be false
now add 0 to d2 the formula in i2 should change to false
you can see anyway all those aligned left are text all those aligned right are numbers

[persson121]

where is your test ?
well they are still text, look here are the results for the first 48 b2:e13 , they should all be false
now add 0 to d2 the formula in i2 should change to false
you can see anyway all those aligned left are text all those aligned right are numbers

This is hard! Why cant excel accept this "numbers" just as they are?

Waint!!!!
120.34 = text
120,34 = number

Im a python coder and i find it more easier to change the text into numbers, but i will learn VBA but it's also hard for me beacuse im new into it. I will upload a new one.

Now it WORKS!
It's the same numbers but i change '.' to ','
But i cannot upload it right now cuz the attachments says "error. Wait an hour".

[martindwilson]

ah it must be your regional settings you should be able to find . replace with ,
then again i didnt know hell used , for decimal places, there is a reason that you are asked for your location this is one of them

[persson121]

ah it must be your regional settings you should be able to find . replace with ,
then again i didnt know hell used , for decimal places, there is a reason that you are asked for your location this is one of them

By the way.
When i download the the source i get it from here
http://www.global-view.com/forex-tra...ory/index.html

I download EUR/USD
http://www.global-view.com/forex-tra...0Daily%20Stats

then i run this code:

Please Login or Register  to view this content.

This gives only Date-High-Low-Close
But in Forex, close = open if it's a daily chart.
Can i do this in VBA too?

Here is my new source:
exchange.xlsx

How can i zoom into the candlestick? I can only se years in candlesticks.

[martindwilson]

now you are getting into stuff way out my depth but i think those are 2 new questions really and you should start a thread for each in the appropriate sub forum

[persson121]

now you are getting into stuff way out my depth but i think those are 2 new questions really and you should start a thread for each in the appropriate sub forum

I have ready did start a new thread
But thanks for your help. Your long Blueyellow neighborcountry thanking you.

[jrp1982]

Hi, im having the same problem; i can't get an OHLC chart up. I've converted the cells and data to numbers, and tested like was said on this thread, but still i get the message "To create this stock chart, arrange the data on your sheet....". Can someone please help me with this, i'm really at a loss here.

Thanks.

[martindwilson]

jrp1982 please start your own thread linking to this one if necessary.also if you have read this thread you will see a workbook would be helpful

[jrp1982]

Hi Martin, ive done as suggested. Please have a look when you've got a free min, thanks. JRP