Welcome to NexusFi: the best trading community on the planet, with over 150,000 members Sign Up Now for Free
Genuine reviews from real traders, not fake reviews from stealth vendors
Quality education from leading professional traders
We are a friendly, helpful, and positive community
We do not tolerate rude behavior, trolling, or vendors advertising in posts
We are here to help, just let us know what you need
You'll need to register in order to view the content of the threads and start contributing to our community. It's free for basic access, or support us by becoming an Elite Member -- see if you qualify for a discount below.
-- Big Mike, Site Administrator
(If you already have an account, login at the top of the page)
How can I convert a .csv file to text for import into NT?
I have a pair of .csv historical VIX data files I downloaded from the CBOE website that I am trying to convert to text in the correct format for NinjaTrader. The correct format for day bars is:
As you can see below, the .csv Daily file has this format:
Date Open High Low Close
1/2/2004 17.96 18.68 17.54 18.22
1/5/2004 18.45 18.49 17.44 17.49
1/6/2004 17.66 17.67 16.19 16.73
There is no volume as this is an index. NT doesn't really specify how to handle that.
I also have what look like 5 minute bars that look like this:
Symbol,"Date","Time","Open","High","Low","Close","Volume","Open Interest","Contract Volume","Contract Open Interest"
$VIX,20030922,0935,"19,07","19,07","19,05","19,05",0,-999999,-999999,-999999
$VIX,20030922,0940,"19,08","19,08","19,06","19,06",0,-999999,-999999,-999999
$VIX,20030922,0945,"19,09","19,09","19,04","19,04",0,-999999,-999999,-999999
NT requires minute bars that are in this format:
yyyyMMdd HHmmss;open price;high price;low price;close price;volume
20061023 004400;1377.25;1377.25;1377.25;1377.25;86
I'd be delighted to get these converted to the right format. I found the following converter but I cannot figure out how to use it:
Converter program for csv files format to nt65 file format.
CSV files ( OIH.csv 5min)
Date,Time,Open,High,Low,Close,Vol
03/10/06,11:30,12744.00,12744.00,12711.36,12725.76,506
NT 6.5 files
20060310 113000;12744.00;12744.00;12711.36;12725.76;506
java …
Any help would be greatly appreciated.
G&M
Can you help answer these questions from other members on NexusFi?
This is a common problem with NinjaTrader. They should do something to improve their import data function. MultiCharts lets you select columns on import, seems would take about an hour of programming time and solve a lot of frustration.
Since @NinjaTrader themselves must get this question regularly, you might ask them or wait for their reply. Other than that, in the user data sharing threads in Elite section people have posted various spreadsheets and written various converters over the years to try and solve this problem.
Steps required for converting Excel .XLXS to Word .CSV to Text .txt.
1. Open Excel
2. Open the saved .xlxs file
3. Highlight the Trade Date columnàRight mouse clickàFormat cells
4. Number tab
5. Select Custom
6. Within the Type: window input yyyyMMdd
7. Click Ok
8. Select the Time column if requiredàRight mouse clickàFormat cells
9. Number tab
10. Select Custom
11. Within the Type: window input HHmmss
12. Click Ok
13. File
14. Save As
15. Select Other Formats
16. Name the File for example ES 12-11
17. Save as type: select CSV (Comma delimited) (*.csv)
18. Save
19. If prompted “the file may contain features that are not compatible with CSV (Comma delimited). Do you want to keep the workbook in this format” Select Yes
20. Open Word
21. Open the saved .csv file
22. Ctrl F
23. Replace tab
24. Find what: ,
25. Replace with ;
26. Click Replace All
27. File
28. Save As
29. Other Formats
30. File name: example ES 12-11
31. Plain Text (*.txt)
32. Save
Next you may import this .txt file to NinjaTrader by going to ToolsàHistorical Data Managerà Import tab.
I was able to convert the one file using NT instructions above, but the other file ($VIX,20030922,0935,"19,07","19,07","19,05","19,05",0,-999999,-999999,-999999) has commas between the data instead of periods so I don't know how to do a replace. and the comma separating the date and time needs to be a space. any ideas?
Broker: Tradestation/Tradestation, NinjaTrader, FXCM and Tallinex
Trading: ES, CL, EUR/USD, TF
Posts: 173 since Aug 2009
Thanks Given: 105
Thanks Received: 61
HI,
yeah, I had the same problem. I was using historical data for the dow from 01/01/1920 to 01/20/2012. I got it from Genesis Financial using Trade Navigator which did give me a lot of formatting options. So, it required less programming on my part. All I had to do was to use an old program I made for downloading Yahoo Data and modify it. However, I later realized that if I used something like notepad++, I could just select all, and replace a comma as delimeter with a semicolon. It worked fine. However, if I had intraday data, perhaps it would have taken longer.
This is the program I used. I wrote it in Java through using eclipse as my IDE. I used the jar file add in from Java CSV.
Basically, the base class lists the data series (the headers like "Date", "open", "high", etc....)
The base class also creates get methods as public as well as a constructor
The next class reads the file, separates everything into a vector that holds each data series, the writes the data in any format you want. You specify the delimeter.
If you change the name of the files, just remember that in Java, the name of the class has to be the same as the name of the file.
Since I already have the program, I might just work on making a program that does all of this with the click of a few buttons rather than dealing with the hassle of installing the java compiler, eclipse, etc..
Broker: Tradestation/Tradestation, NinjaTrader, FXCM and Tallinex
Trading: ES, CL, EUR/USD, TF
Posts: 173 since Aug 2009
Thanks Given: 105
Thanks Received: 61
Hi all
The code I wrote before was a little overkill. This code is a lot less complex. For java and C# less than 100 lines is not that long.
I think the same logic can be done for C# and visual studio using the StreamReader and StreamWriter methods.
Otherwise it is not that hard to install the JDK for java, add a path for Environmental variables, download the library, download eclipse.
If you want to learn how to set up JDK and eclipse watch this kids tutorials on you tube. He taught me.
After you write a hello world program, just create a new project, create a class called basicNinjaParse. Copy and past the code from above over the whole thing replacing everything.
Then create a folder called lib one level under the project name right next to the src folder. Put the jar file in there you got from the Java CSV
Then right click over JRE System Library==> Buld Path ==> configure Build Path. Then hit Add JAR's on the right. click on the jar in the lib folder and press OK. It should be added. All the red dots should go away
Make sure your file is in the same format as listed in the comments on top.
If not, you will have to play with the code.
btw, this code takes about 4 minutes to completely parse 1 min data of the DIA for 10 years which is 900k lines in the text file. not sure if that is good or bad, but it works.
take care
Spencer
thanks for taking the time to post this useful material. When trying to replicate what you are doing I have an issue. When I run the code in basicNinjaParse.jave , I get the following message error:
Error: Main method not found in class basicNinjaParse, please define the main method as:
public static void main(String[] args)
Are you able to help me to understand what I'm doing wrong