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

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

Hi persson121,

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

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.

Originally Posted by 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.

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

But it's the same.

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 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

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

Originally Posted by 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

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?

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.

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.

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

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

post your workbook again but have you tested the cells?

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

Originally Posted by 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

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".

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

Originally Posted by 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

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:

f = open('exchange.csv', 'r')
k = f.read()
f.close()
k = k.replace(',', ";")
f = open('exchange.csv', 'w')
f.write(str(k))
f.close()

k = open('exchange.csv', 'r')
f = k.read()
f = f.replace('.',",")
f = f.replace('EUR/USD',"")
o = open('exchange.csv', 'w')
o.write(str(f))

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.

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

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