date formating

[Lucinek]

Hello, I dont know, where to post my question, so I ask in this part of forum.

I have a big problem with formating date and time in list of trades.
When I save list of trades from Strategy analyzer or from live account, I get this format of CSV file:

233,FDAX 09-09,Back101,SampleMACrossOver,Long,1,4814.0,4824.0, 6/30/2009 9:33:05 PM,6/30/2009 9:59:57 PM,Buy,Exit on close,250.00,3450.00,0.00,0.00,250.00,0.00,11,
234,FDAX 09-09,Back101,SampleMACrossOver,Short,1,4812.0,4824.0 ,7/1/2009 8:09:04 AM,7/1/2009 8:40:08 AM,Sell short,Close position,-300.00,3150.00,0.00,350.00,100.00,400.00,15,
235,FDAX

There are two problems:
1. My excel doesn't understand format "6/30/2009 9:33:05 PM", it needs format "30/6/2009 9:33:05 PM". So I need to get file with dd/mm/yyyy hh:mm:ss format and i prefer 13:30 rather then 1:30 PM.
Currently this text is just "text" and it is impossible to convert it to date.
Acording this problem, string "7/1/2009 8:09:04 AM" is considered as date, but not as July 1st, but as January 7th. It is reason, why I can't convert this "bad" format to some valuable one.

2. My excel requires ';' as separator between values instead of ',' , so I need to get this format:
234;FDAX 09-09;Back101;SampleMACrossOver;Short;1;4812.0;4824.0 ;7/1/2009 8:09:04 AM;7/1/2009 8:40:08 AM;Sell short;Close position;-300.00;3150.00;0.00;350.00;100.00;400.00;15;


How to control output format on this list? Thanks for your advice ...

[Baruch]

Try renaming it to *.txt and then importing into excel. You will get to choose the delimiter and the day format.

Baruch

[Lucinek]

No, it doesn't work.
Using localized Czech version of Excel 2007, when I do that, excel opens it as text file and each line is filled in first cell on sheet. No import possibilities.

There are some ways how to open it using OpenOffice, but I don't want to do any magic with "bad" CSV file, I want to create "correct" CSV file and use it without any file changes or tricks.

[Baruch]

If you change the name from abc.csv to abc.txt excel will try to import it and not open it. It does work!

[NinjaTrader_Bertrand]

Lucinek, unfortunately you can't change the output format, you would need to continue playing around with modifying it in Excel or a similiar program to your liking.

[Lucinek]

If you change the name from abc.csv to abc.txt excel will try to import it and not open it. It does work!

No, it doesn't. I'm sorry. In my version of Excel 2007 it simply doesn't work.

[Lucinek]

Lucinek, unfortunately you can't change the output format, you would need to continue playing around with modifying it in Excel or a similiar program to your liking.

Oh, bad news. But i remember very well, that sometime in the past NT has been generating correct type of this list. So I suppose, that it is according to some location settings in Windows or something like it. It is not true? In this case format has been changed about half year ago?

[Lucinek]

OK, if no possibilities, I'll do my own output directly in strategy code, so every trade will be written immediately after execution into my special file. I guess this could be a way how to solve this problem ...

[Lucinek]

Or another possibility to write console utility (parser) which convert into acceptable format.
But question to You, Bertrand - in NT7 it will be customizable, woun't be?

[Baruch]

I just did it and it WORKS.
In strategy analyzer on the trade tab click the right mouse button and press save as.. Put in abc.txt and save it on your desktop. Now open Excel (2007 or any other) and press file> open and in "Files of Type" choose "*.*". Now you will see the Import screen. For the date fields choose MDY as a format and thats it.
I believe its much simpler than to write your own export.

[Lucinek]

OK, You are right. it works, but only if opened using "Files of Type" choose "*.*".
I used CSV choice before.
But - I chosen MDY format for Entry time and Exit time and result is still the same - "6/30/2009 9:33:05 PM" is just text cell and "7/1/2009 9:33:05 PM" is still January 7th. I have no clue why, but it seems not import it corretly. I have suspicion that there is some relation to national setting used in OS Windows.

[NinjaTrader_Bertrand]

Lucinek, great you got it, I'm not aware of changes in this regard for NinjaTrader 7

[Lucinek]

Bertrand, still not very succesfuly. But never mind, I'll find out any way how to do it.
Baruch, thanks a lot ...

[Baruch]

I toled you to change the file extension to "txt" and of course in excel you need to open "txt" file. You could chose "Text files" in Files of Type also.
If you chose MDY and not DMY then it means that you local setting are MDY. Any way now its a date field and not text! You can validate it by right clicking the cell and choosing "Format Cells".

[Lucinek]

I toled you to change the file extension to "txt" and of course in excel you need to open "txt" file. You could chose "Text files" in Files of Type also.
If you chose MDY and not DMY then it means that you local setting are MDY. Any way now its a date field and not text! You can validate it by right clicking the cell and choosing "Format Cells".

I suppose, that You choose format in CSV file, not your local settings format.
Although I trid to set DMY for Enter time and MDY for Exit time in the same file, there is no difference. Every date with day > 12 is considered as TEXT by Excel and date with day <= 12 has replaced month and day.

Have no clue how to manipulate Excel accept this format correctly. The only way which works seems to be change default local setting in Windows to M/d/yyyy. Yeah, it works ... but I need to use MY COUNTRY settings, not American one. And in my country default setting is d.M.yyyy H:mm.ss