Historical Data

[rackspace]

can you please tell me where can i get historical data from ?

[NinjaTrader_Eamon]

can you please tell me where can i get historical data from ?

Hello,

Thank you for your post.

Please view our help guide here for a complete listing of all supported datafeeds and types of data they support: http://www.ninjatrader-support.com/HelpGuideV6/HistoricalData.html

Currently Barchart.com, eSignal, and DTN IQfeed are available as data only vendors. This means that a brokerage account is not needed when using one of the listed feeds.

[rd_falcon]

I have just found a source with historical data going back to 2000 for all currency pairs. The website is http://ratedata.gaincapital.com/. Now the thing is, the data isn't in a format that Ninja Trader likes, so you have to do a bit of work before it is ready to be imported. Here is what I have found to be the easiest way to go about it.

Save one of the week's data to your hard drive, and then open the file in excel. Copy the following formula:

=IF(ISBLANK($A1)=TRUE,"",TEXT($C1,"yyyymmdd hhmmss")&";"&$D1&";1")

Select column G (or any other blank column) in the excel file and paste the formula. If you have done it correctly, the formula should now appear for every row of the worksheet. The data is now in the format that Ninja Trader wants it in. Select column G again, and select copy.

Next open Notepad and paste the data from column G into the notepad file. Once you are done with that, save the file as the currency pair that you are working with. For example if you are importing the Euro/USD pair, save the file as EURUSD. You will have to create a new folder on your hard drive for each week's data because Ninja Trader requires each file to have the name being the same. This would mean create a folder such as May 2008 week 1, and then save the week one txt file in there, and then create a new folder for week 2.

Open Ninja Trader and go to tools | historical data | import, and navigate to your txt file that you created. When it asks if you want to construct minute bars off of the ticks, select yes. Once it finishes, you will now have the data available for back testing when you run the strategy annalyzer.

Repeat this process for as many weeks worth of data that you would like.


Edit: Alright, it appears that for all months prior to May of 2008, they have the data seperated by a comma instead of in seperate columns like it is for May of 2008. The following is instructions for converting this data to the format necessary for the original process described above. I am using excel 2007, so I am not sure if this process changes at all for earlier versions.

Select column A (which contains all of the data), and then select "text to column" under the data tab. Be sure that you have delimited selected in the first dialogue box, and then click next. On the next screen, select "comma" under the delimiters heading. Make sure nothing else is selected. Click next, and then finish. The data should now be in the correct format for using the formula that I provided above.


*Note: If you go to past the formula into excel and it says it is a different size than the selected area, first paste the formula into another cell such as H1, and then copy it from there and then select column G and paste. It should then insert the formula for all of the cells.

[rackspace]

thanks, for such a great help and reference, i am currently downloading the historical data. Once i arrange the sheets as per NT requirement i will share it.

[rd_falcon]

I figured out that if you write a macro in excel to insert the formula and copy the cells to your clipboard, you can greatly reduce the amount of time required to convert the data. Another plus is that it will save your arm, because I can feel carpel tunnel syndrom coming after doing this 300 plus times for inputing the data all the way back to 2000.

If you copy this into the VB editor in excel, it should work. Just assign it a hotkey in the macro screen (I am using ctrl+A right now).

Sub Forex()
'
' Forex Macro
'
'
Range("H1").Select
Selection.FormulaR1C1 = _
"=IF(ISBLANK(RC1)=TRUE,"""",TEXT(RC3,""yyyymmd d hhmmss"")&"";""&RC4&"";1"")"
Selection.Copy
Columns("G:G").Select
ActiveSheet.Paste
Application.CutCopyMode = False
Selection.Copy
End Sub


Do you happen to know any place that we would be able to host the data so that others can just download a single file instead of having to go through all of this? Would the folks at Ninja Trader happen to be interested in hosting it?

[rackspace]

i have many domains... i will host it .. but currently i am not able not to download data in full ...

[rackspace]

downloading data for majors only ... lets see ...

[rd_falcon]

Were you able to get the data to download? What pair were you trying to get that was giving you troubles?

[rackspace]

i was trying to leech the complete site :P

[rackspace]

here is the file for one week only, it is more than 1 MB so you can guess that actual file will around 100 MBs

http://www.hostse.com/Forex.txt

[Joerg]

Hello,

I have´nt done it already because at the moment I´ve got very little time but there should be a may to minimize manual work:

1.) automate the download of all the files with a download manager like "offline explorer" http://www.metaproducts.com/OE.html . Propably there are also other tools for auto-downloading webpages up to x links depth. This will take a while because the server seems to be very slow.

2.) Merge the files via cmd-window and "copy/b" (binary copy) command. f.e. copy all EURUSD files of one year (because the file names don´t contain the year for seperating them) into one folder (maybe use the search function of windows for not having to go into each folder) and then:
copy/b C:\EURUSD\*.csv C:\EURUSD\EURUSD-all.csv

3.) Use Excel 2007 (up to 1 Mio Lines) and the formula below of rd_falcon for formatting into NT-format. Don´t forget to sort the date, maybe the order is wrong after the merging. If the whole year does´nt fit into one sheet you also have to repeat this step but you will save > 95% of the manual work.

Greetings

Joerg

[rackspace]

thats what i was doing... using http offline browser, but their site stop resolving for me ... but was perfectly working fine from proxies... may be they monitor ...

thanks for the command ... addition to my knowledge ...

i am using excel 2007, its my bread and butter ... its is the reason i get paid from my company so dont worry about macros and automation ...

but like always ... problem is time ...

[rd_falcon]

Ah, that would make things much easier. I was about to ask how you were planning on doing all of the currency pairs since it has been taking me an hour and a half just to do 1 year for 1 pair. I will have to try out that automated system. Thanks for the tip Joerg.

[rackspace]

sorry for late reply... was on a week end :P

you may find the data for EURUSD at the following links ...

http://www.hostse.com/forex/Eur-Usd%20(2000%2c%202001%2c%202002).rar
http://www.hostse.com/forex/Eur-Usd%20(2007).rar
http://www.hostse.com/forex/Eur-USD%20(2008).rar

these stats are for 2000, 2001, 2002, 2007 and 2008

i currently don't intend to do any more conversion to NT format ... and will only try the data i have for now ...

but i can tell how i did, and so it would be easier for any one else who want to do it ...

[rackspace]

following is the macro ... for those who know VBA, it wont be difficult to understand ...

you may only have to correct the file path and replace the file name to the currency pair you want to convert.


i am using currentregion function to calculate number of rows, this is a slightly different approach than what was indicated by rd_falcon

merging files is of no use as data exceeds limits of EXCEL 2007, so the macro open every file and then do the required operations, save and close the file ...

the file has three macros

YEAR do the operations automatically on the files of the complete year ... you will have to indicate the correct path to the files and name months as 01,02,03 ....

Week_5 is same as year but you can use it to run on 5th week( as some month has 5 weeks, not 4)

CSV is the macro which is used on the files which could not be opened in the EXCEL 2007 correctly in first place ... these files will not have data in seperate columns and rather all the data will be present in the first column and will comma seperated... once you will check the files induvually after running the year macro ... and find such files not them down and use this macro on them ...


You can get macro at: http://www.hostse.com/forex/Historical%20Data.xlsm