OHLC Data for downloading to Excel.csv

September 12th, 2009, 08:40 AM

Hey BigMike,

Looking for a way to download OHLC data to Excel.csv format...

Any help you can give is greatly appreciated!

Kirk

September 12th, 2009, 08:45 AM

zeller4

Hey BigMike,

Looking for a way to download OHLC data to Excel.csv format...

Any help you can give is greatly appreciated!

Kirk

This is a text file (same as csv for Excel).

the code is just a few lines, but I will upload it to File Sharing too

Code

                            

        protected override void OnBarUpdate()
        {
            
        string logfile = @"c:\users\mike\desktop\ninja.txt";
        
        string logprefix = Time[0].Month.ToString("00") + "/" + Time[0].Day.ToString("00") + "/" + Time[0].Year + ", " + Time[0].Hour.ToString("00") + ", " + Time[0].Minute.ToString("00") + ", ";
        
        StreamWriter log;
        log=File.AppendText(logfile);
        
        // Date, Hour, Minute, Open, High, Low, Close
        
        log.WriteLine(logprefix + Close[0].ToString("0.00") + ", " + Open[0] + ", " + High[0] + ", " + Low[0] + ", " + Close[0]);
        log.Close();
                        
        }

Download it here:

Download: Bardata Export (like for Excel)

This is a very simple strategy that will export the OHLC to a text file. The file path is hard coded because I was lazy and wrote this a long time ago, so open the code and change it or it won't work for you. While you're in there, you'll see it is …

Big Mike

Mike

November 18th, 2009, 12:19 AM

Hi Big Mike,

I have a question regarding this code. I appreciate the effort you have put into this and had no problem setting this up but I was hoping you could answer a question for me.

As the data is stored in a .txt file do you have any suggestions as to how I could access this file and use the data within Excel in real-time for further analysis?

Regards,
suprsnipes

November 18th, 2009, 12:50 AM

The closest I did personally was with my MySQL project, I was writing tick-by-tick data from Ninja to my MySQL server, and then ran queries from Excel.

But it is far more complex.

You should try asking Hapster, he has a very extensive Excel spreadsheet he trades from, and I feel certain he has incorporated a live data feed.

Mike

November 18th, 2009, 03:17 AM

Incorporating real-time data to Excel from NT is not really NT job, their is no good way to do this.
This is the job of the data feed itself, adding some layers (NT here) between the feed and Excel is not a good idea.
With the IB API, there is an Excel sheet, with DDE stuff, and you can have real-times quotes, position, etc, etc.

November 18th, 2009, 03:21 AM

Aww c'mon Sam, just write a quick Zen DDE interface...

Or you could email Matt maybe @ Zen, he might have one already.. ?

Zen-Fire: Zen-Fire API

Mike

November 20th, 2009, 01:05 PM

Mike, your forum is amazing. This morning I decided to try to export data from NT into text files so I could work on coding simple patterns into TS in order to do easy P-L analysis. I find the NT strategy writing and analysis interface too hard at this point.

I just could not find a way to open any of the txt files in TS2000i which itself is tricky with that stuff. So I come on your forum and bingo, there is a solution.

BUT: I cannot get the hours to separate from the seconds. The NT way of exporting it blends date hours minutes seconds into one cell. Fine, I split it. But then it tracks minutes with seconds which TS cannot handle.

So your code is perfect. Just hours and minutes. But it separates hours and minutes which TS cannot handle. It just want 0734 for 734 am.

So I take your original line for logprefix and get rid of the comma between the hours and minutes but this is what it prints out:

10/26/2009, 00, 23, 1056.00, 1056, 1056.1, 1056, 1056

Here is the altered line from your code:
string logprefix = Time[0].Month.ToString("00") + "/" + Time[0].Day.ToString("00") + "/" + Time[0].Year + ", " + Time[0].Hour.ToString("00") + Time[0].Minute.ToString("00") + ", ";

I cannot for the life of me see why it keeps putting in the comma. I closed down strategy analyser, loaded different instrument, different strategies, then put barclose back in but still spits out the commas between hours and seconds despite the code not telling it to.

Any suggestions?
Buy a new computer to clear the cache?
Start over next week after installation of new OS?

November 20th, 2009, 01:18 PM

cclsys

It just want 0734 for 734 am.

try Time[0].ToString("HHmm")

November 20th, 2009, 01:51 PM

cclsys, your code is correct.
Did you remove the 1st .txt file generated ? (because it's an AppendText())...

November 20th, 2009, 01:53 PM

BM. Apologies. I didn't realise that it was only appending to existing file so in fact it was working fine.

I have spent a while looking for how to tell it to first erase all previous data before appending but can't find the lingo. Could you please tell me what to write in first to get it to flush out the earlier stuff then start again any time the strat is run?

OHLC Data for downloading to Excel.csv

Discussion in NinjaTrader

OHLC Data for downloading to Excel.csv