• Registered: 2011-12-04
  • Posts: 13

Topic: "Could not determine price columns order" error CSV File

Hello all

I have downloaded dukascopy candlestick data via dukascopier_v0.4,

http://eareview.net/download/dukascopier_v0.4.zip

It downloads the data and creates a CSV file for you for each time period you request and from what time period back you want.

As far as I can see to make the data compatible is,

1. Open the csv file in Notepad, Menu item - Edit, Replace. Top box press space bar for a space, bottom box add a comma, Find next, Replace all.  This will separate the date from the time into two columns. (Tab delimited) Save, Exit

2. Open the file in Excel, Select the Time Column, format cell, special, in the box where "general" is type hh:mm and press enter, this will put the time into an acceptable format. Select the four price columns, format cells, currency, no symbol, decimal places 5(for EURUSD etc) to round off all data to 5 digits. Select the volumes column, format cells, number, decimal places 0, to round off volume data to whole number.
NOW to save your file properly close the file using the close file option, it will ask you if you want to save hit yes then it will ask you if you want to keep the data in the type of file format also click yes.

3. Rename the file to EURUSD5.csv or whatever TF you need.

I cant see any problems, it is just a data file after all, editing it as can be done in Notepad and Excel.

But I get the error "Could not determine price column order" error message when loading FSB. "Error in data file".

Can someone enlighten me please or point the error of my ways.

**PS just watch Excel, when you open the file up again the time column will show the data as unformated as done above in Number 2, if you check it by opening it in Notepad it will be correct.

I attach a downloaded data file from Dukascopier.

  • footon
  • footon
  • ◄≡≡≡►
  • Offline
  • Registered: 2009-08-17
  • Posts: 2,969

Re: "Could not determine price columns order" error CSV File

Attachments are not working in this forum (the party responsible for that http://forexsb.com/forum/topic/2575/hey-there/), therefore I ask you to post a couple of lines of your data, both versions of formatted and unformatted.

I've had trouble with data formatting also, one thing I discovered was that after formatting the data in excel, I had to copy it to notepad and then save it, excel save messed things up.

Yeah, try the above and then post your lines.

  • krog
  • Licensed Member
  • Offline
  • Registered: 2010-02-26
  • Posts: 407

Re: "Could not determine price columns order" error CSV File

I've seen that error when columns 2 and 3  in the first line are equal. FSB checks which is larger or smaller by more than .1 pip to figure out which is high and low (if data is O-H-L-C or O-L-H-C).

Maybe this will work: look at the values in the following lines to see which is the High column, then in the first column, make it .2 pips or more higher than the low column.

  • Registered: 2011-12-04
  • Posts: 13

Re: "Could not determine price columns order" error CSV File

Thanks for the replies, Footon and Krog.

I checked the data as whether any data in the first line was equal for all files and its not, looks also to be O H L C, I had a couple of downloaded csv's that had zero data in all the columns at the start of the file but I had deleted those lines previously.

I haven't tried copying the data from excel back to notepad yet as the columns are separated by a space when copied and pasted, is this ok, to have the data separated by a space in the CSV? Spose that is what an interval is... a space
Actually when I do copy and paste the data back to notepad there is a lot of spaces behind the date before the time, wonder if that may be it? But in the formatted csv's the data looks good and is formatted correctly and separated by a comma .... hmmm!!

Interesting to note that when you open up the csv in excel after changing all the data that it is shown as unformatted except the volumes column.

Downloaded data from Dukascopier

2010.08.12 00:00:00,1.2849,1.28665,1.28455,1.2868,5184.7
2010.08.12 00:15:00,1.28665,1.28655,1.28605,1.2868,5107.8
2010.08.12 00:30:00,1.28655,1.2873,1.2865,1.2873,4672.7
2010.08.12 00:45:00,1.28725,1.28735,1.28725,1.28835,7385.9
2010.08.12 01:00:00,1.28735,1.2879,1.2866,1.28805,6472.1

My formatted data

2010.08.12,00:00,1.28490,1.28665,1.28455,1.28680,5185
2010.08.12,00:15,1.28665,1.28655,1.28605,1.28680,5108
2010.08.12,00:30,1.28655,1.28730,1.28650,1.28730,4673
2010.08.12,00:45,1.28725,1.28735,1.28725,1.28835,7386
2010.08.12,01:00,1.28735,1.28790,1.28660,1.28805,6472

MT4 History centre download csv data, not the same time and space but an idea of how it comes across from MT4

2011.04.12,06:00,1.43911,1.43922,1.43849,1.43904,664
2011.04.12,06:15,1.43904,1.44025,1.43892,1.44017,792
2011.04.12,06:30,1.44017,1.44070,1.44007,1.44063,983
2011.04.12,06:45,1.44062,1.44096,1.44001,1.44003,659
2011.04.12,07:00,1.44006,1.44042,1.43959,1.43973,1090
2011.04.21,05:30,119.745,119.857,119.742,119.743,590

Thanks again for any help!
Damn data is a bug bear to get hold of, in my mt4 platforms the data is shot, big holes, no July, no September, part of November is missing, cant get it by downloading from metaquotes or deleting the hst files and page up'ing to fill the chart and collect the data from my broker.

Cheers
Al

  • Registered: 2011-12-04
  • Posts: 13

Re: "Could not determine price columns order" error CSV File

I have just found that in my 15m csv of a little over 20000 rows, 4200 rows have zero volume as data and the same price for O H L C, this may be what Krog eluded to that the data cant be the same in the columns, although he thought only in the first couple of rows.

I am extremely disappointed as this data is meant to one of the best even it is free, but it turns out to just as ugly as my MT4 data. At this stage I refuse to convert tick data, what a headache, mans got a life as well.

Any ideas on how to get some decent data before I am divorced? big_smile

  • footon
  • footon
  • ◄≡≡≡►
  • Offline
  • Registered: 2009-08-17
  • Posts: 2,969

Re: "Could not determine price columns order" error CSV File

First of all, go ahead and copy to notepad, spaces are fine! Look at the data FSB comes with, it's with spaces and everything works! And about the "a lot of space" part - totally in order as well!

Volume as zero can't have an effect, as far as I understand data checks are performed only on first couple of lines, High is compared to Low in order to establish whether it's OHLC or OLHC.

The lines itself seem normal to me, maybe Krog can spot something.

Popov wrote a few months back that he upgraded the data checking part, in worst case scenario the fault may lie there.

If you can, post a part from your data, as attachments are not working, post 400 lines of formatted data as a code.

  • footon
  • footon
  • ◄≡≡≡►
  • Offline
  • Registered: 2009-08-17
  • Posts: 2,969

Re: "Could not determine price columns order" error CSV File

Background info - http://forexsb.com/forum/topic/2687/for … v265-beta/

  • krog
  • Licensed Member
  • Offline
  • Registered: 2010-02-26
  • Posts: 407

Re: "Could not determine price columns order" error CSV File

I think the column order is not correct:

Duskcopy (I added spaces to make it easier to read):
2010.08.12 00:00:00,  1.2849,  1.28665,  1.28455,  1.2868,  5184.7
It looks like it's Date,  Open,  Close, Low, High, Volume --- the 4th price value (1.2868) is the highest value of all them. But the data must be O-H-L-C or O-L-H-C.

To test this:
- Change 1.2868 to 1.2863 (anything lower than 1.28665 and higher than 1.28455).
- Try to load data.

See if it loads, or if you get a different error, then this is probably the cause.

Note MT4 data:
2011.04.12,  06:00,  1.43911,  1.43922,  1.43849,  1.43904,  664
Here, the 2nd and 3rd price values (1.43922 and 1.43849) are the highest and lowest values, it sounds like FSB is ok with them.

If this is the case:
Can you specify the column order in downloading from Duckascopy? This would be the best way to solve.

If not, then in Excel, swap price columns 2 and 4 when doing the formatting. (If you haven't already, I'd recommend recording the steps in a Macro, I've found if I get the steps general enough I can use a Macro for this kind of spreadsheet work).

  • krog
  • Licensed Member
  • Offline
  • Registered: 2010-02-26
  • Posts: 407

Re: "Could not determine price columns order" error CSV File

Now I see it -- from this link:
http://www.dukascopy.com/swiss/english/ … istorical/
I downloaded the csv, at the top it has a headers as:
Time,Open,High,Low,Close,Volume
That should be ok, but oh no, they sure are missing a lot of data, like about the first 80% of bars !!

Then this link here:
http://www.dukascopy.com/swiss/english/ … ta_export/
gives a header as:
DATE;TIME;VOLUME;OPEN;CLOSE;MIN;MAX
With Min and Max at the end, it won't load.

  • footon
  • footon
  • ◄≡≡≡►
  • Offline
  • Registered: 2009-08-17
  • Posts: 2,969

Re: "Could not determine price columns order" error CSV File

Krog the eagle eye!  big_smile

  • Registered: 2011-12-04
  • Posts: 13

Re: "Could not determine price columns order" error CSV File

Thanks Krog

Swapped columns 2 & 4 and Wallah! FSB accepts the data.

Thanks for the heads up on the Macro, never entered my head, but works great.

Now I will play with the data for a bit and see how sad it is, I'll have to see if I can fill some of those gaps on the bigger TF's for the periods I want.

Thanks Footon too for your input

Cheers

  • Registered: 2011-12-04
  • Posts: 13

Re: "Could not determine price columns order" error CSV File

Well, I have decided that the dukascopy data despite it's holes is very much better than what I had been using, the downloaded data through MT4.

If I can remember correctly if I put 5000 bars up before with the old data, the back tester would show me about a month, now I only get a week using M1 data.  If I open up the data window to a month it is over 50000 bars worth of data. Not only that, processing an optimization for a month considerably slows down the PC's computing power(it takes so much longer to process than before).

Just finished making a M1 strategy for the EURUSD trading only Wednesday to Friday that the back tester says I would have profited $560 last week on a $1000 account trading a max of 0.4 lots with only 6.61% drawdown from the new data, so I will chuck it on a demo and hold my breath till I am blue.

Did I mention the brokers margin of $500 a couple of times during that time?