How can I convert a .csv file to text for import into NT?

October 12th, 2011, 01:57 AM

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:

yyyyMMdd;open price;high price;low price;close price;volume
20061023;1377.25;1377.25;1377.25;1377.25;86

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:

Download: Converter to nt6.5

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 …

cmiranda

Any help would be greatly appreciated.

G&M

October 13th, 2011, 02:57 AM

- I can't help, but maybe someone else can.

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.

Mike

October 17th, 2011, 07:05 PM

@ gunsnmoney, it's less than 10 lines of Perl.
Just compress and post these two files, I'll convert them for you.

NinjaTrader · October 18th, 2011, 12:30 PM

These steps might help...

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.

October 18th, 2011, 02:14 PM

Problem with using Excel is that Excel won't load any big files with millions of ticks (years of data).

Mike

October 18th, 2011, 02:18 PM

NinjaTrader

These steps might help...

...

1. Open Excel
...
32. Save

Next you may import this .txt file to NinjaTrader by going to Tools�Historical Data Manager� Import tab.

WOW! 32 steps to do a single task. Hopefully, we are in 2011 and have computers to help

https://www.coolutils.com/Convert-CSV-to-Text

November 14th, 2011, 04:05 PM

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?

January 28th, 2012, 08:20 PM

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..

This is the base class

Code


public class NavigatorToNinjaDataBase {
	
	private String Date;
	private String Open;
	private String High;
	private String Low;
	private String Close;
	private String Volume;
	
	//default constructor
	public NavigatorToNinjaDataBase(){
		this.Date = null;
		this.Open = null;
		this.High = null;
		this.Low = null;
		this.Close = null;
		this.Volume = null;
		
	}//regular constructor
	public NavigatorToNinjaDataBase(String Date, String Open, String High, String Low, String Close, String Volume)
	{
		this.Date = Date;
		this.Open = Open;
		this.High = High;
		this.Low = Low;
		this.Close = Close;
		this.Volume = Volume;
	}
	public String getDate(){
		return this.Date;
	}
	public String getOpen(){
		return this.Open;
	}
	public String getHigh(){
		return this.High;
	}
	public String getLow(){
		return this.Low;
	}
	public String getClose(){
		return this.Close;
	}
	public String getVolume(){
		return this.Volume;
	}

}

second class

Code

import java.io.BufferedReader;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.io.InputStream;
import java.io.InputStreamReader;
import java.net.*;
import java.util.Vector;
import java.io.BufferedWriter;
import java.io.FileWriter;
import java.io.File;

//these will work once you import the jar file from www.csvreader.com 
import com.csvreader.CsvReader;
import com.csvreader.CsvWriter;


public class NavigatorToNinjaData {

	private static Vector<NavigatorToNinjaDataBase> dailyData;
	
	public void inputData(){
		//the vector to hold the data
		dailyData = new Vector<NavigatorToNinjaDataBase>();
		try
		{
		CsvReader products = new CsvReader("C:\\DJIA.txt");
		products.readHeaders();
			while(products.readRecord())
			{
				dailyData.add(new NavigatorToNinjaDataBase(
					products.get("Date"),
					products.get("Open"),
					products.get("High"),
					products.get("Low"),
					products.get("Close"),
					products.get("Volume")));
				
				
			}
		products.close();
		}
		catch(FileNotFoundException e){
			e.printStackTrace();
		}
		catch(IOException e){
			e.printStackTrace();
		}
		
	}
	public void outputData(){
		//nothing yet
		for(int i = 0; i<dailyData.size();i++){
			System.out.println(dailyData.get(i).getDate()+";"+dailyData.get(i).getOpen()+";"+dailyData.get(i).getHigh()+";"+dailyData.get(i).getLow()+";"+dailyData.get(i).getClose()+";"+dailyData.get(i).getVolume());
		}
		String outputFile = "DJIA_ninjaTrader.txt";
		//check if file exists
		boolean alreadyExists = new File(outputFile).exists();
		try
		{
			CsvWriter csvOutput = new CsvWriter(new FileWriter(outputFile, true), ';');
			
			//if the file didn't already exist, then we need to write out the header line
			if(!alreadyExists)
			{
				csvOutput.write("Date");
				csvOutput.write("Open");
				csvOutput.write("High");
				csvOutput.write("Low");
				csvOutput.write("Close");
				csvOutput.write("Volume");
				csvOutput.endRecord();
			}
			//else assume that the file already has the correct header line
			//write out a few records
			for(int i = 0; i<dailyData.size();i++){
				csvOutput.write(dailyData.get(i).getDate());
				csvOutput.write(dailyData.get(i).getOpen());
				csvOutput.write(dailyData.get(i).getHigh());
				csvOutput.write(dailyData.get(i).getLow());
				csvOutput.write(dailyData.get(i).getClose());
				csvOutput.write(dailyData.get(i).getVolume());
				
				csvOutput.endRecord();
			}
			csvOutput.close();
		}
	
		catch(IOException e)
		{
			e.printStackTrace();
		}
	}
	public static void main(String args []){
		NavigatorToNinjaData load = new NavigatorToNinjaData();
		load.inputData();
		load.outputData();
		
	}
}

hope this helps

Spencer

March 16th, 2012, 03:16 AM

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.

Code

/*Spencer Davis
 * cpsc 890
 * This file is built specifically to parse 1 min data from
 * pittrading.com to Ninja Trader
 
Reads Data From:
====================================================
"Date","Time","Open","High","Low","Close","Volume"
12/30/2002,0931,83.08,83.10,82.99,83.03,29000
12/30/2002,0932,83.04,83.12,82.99,83.04,41900
12/30/2002,0933,83.04,83.15,83.03,83.15,20200
12/30/2002,0934,83.11,83.14,83.05,83.11,26500
12/30/2002,0935,83.15,83.20,83.06,83.14,79800
12/30/2002,0936,83.13,83.14,83.08,83.12,48400
=====================================================
And outputs this:

DateTime;Open;High;Low;Close;Volume
20021230 093100;83.08;83.10;82.99;83.03;29000
20021230 093200;83.04;83.12;82.99;83.04;41900
20021230 093300;83.04;83.15;83.03;83.15;20200
20021230 093400;83.11;83.14;83.05;83.11;26500
20021230 093500;83.15;83.20;83.06;83.14;79800
20021230 093600;83.13;83.14;83.08;83.12;48400
20021230 093700;83.13;83.16;83.08;83.16;36600

This took me about 3 to 4 minutes to parse out 10 years of 1 minute data
Probably twice as long for something that trades all night and all day.

 * */

import java.io.File;
import java.io.FileNotFoundException;
import java.io.FileWriter;
import java.io.IOException;

//library from:	http://www.csvreader.com/java_csv.php
import com.csvreader.CsvReader;
import com.csvreader.CsvWriter;

public class basicNinjaParse {
	
	String date;
	String time;
	String open;
	String high;
	String low;
	String close;
	String volume;
	
	public void in(){
		try{
			
			CsvReader products = new CsvReader("C:\\DIA.TXT");
			products.readHeaders();
			while(products.readRecord())
			{
				date = products.get("Date");
				time = products.get("Time");
				open = products.get("Open");
				high = products.get("High");
				low = products.get("Low");
				close = products.get("Close");
				volume = products.get("Volume");
				
                                //now lets get it in Ninja format.  12/01/2012 needs to be split by '/' delimeter
                                // and then needs to be combined with time.  Time also needs 00 for ss placeholder
				String [] s = date.split("/");
				String month = s[0];
				String day = s[1];
				String year = s[2];
				//yyyyMMdd HHmmss
				date = s[2]+s[0]+s[1]+" "+time+"00";
				
				//now add to file.  since 800k lines it will
				//keep looping until it is done---should take 3 to 4 minutes
				out();
			}
			products.close();
			
		}catch(FileNotFoundException e){
			e.printStackTrace();
		}catch(IOException e){
			e.printStackTrace();
		}
	}
	public void out(){
		String outputFile = "DIA.TXT";
		boolean alreadyExists = new File(outputFile).exists();
		try{
			//use FileWriter constructor that specifies open for appending
			CsvWriter csvOutput = new CsvWriter(new FileWriter(outputFile, true), ';');
			
			//if the file didn't already exist then we need to write out the header line
			if(!alreadyExists)
			{
				csvOutput.write("DateTime");
				csvOutput.write("Open");
				csvOutput.write("High");
				csvOutput.write("Low");
				csvOutput.write("Close");
				csvOutput.write("Volume");
			}
			//else assume that the file already has the correct header line
			//write out a few records
			csvOutput.write(date);
			csvOutput.write(open);
			csvOutput.write(high);
			csvOutput.write(low);
			csvOutput.write(close);
			csvOutput.write(volume);
			csvOutput.endRecord();
			
			csvOutput.close();
			
		}catch(IOException e){
			e.printStackTrace();
		}
	}
	
	
	public static void main(String args []){
		basicNinjaParse p = new basicNinjaParse();
		p.in();
	}

}

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.

Java Programming Tutorial - 1 - Installing the JDK - YouTube

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

August 4th, 2012, 03:17 PM

spinnybobo

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.

Code

/*Spencer Davis
 * cpsc 890
 * This file is built specifically to parse 1 min data from
 * pittrading.com to Ninja Trader
 
Reads Data From:
====================================================
"Date","Time","Open","High","Low","Close","Volume"
12/30/2002,0931,83.08,83.10,82.99,83.03,29000
12/30/2002,0932,83.04,83.12,82.99,83.04,41900
12/30/2002,0933,83.04,83.15,83.03,83.15,20200
12/30/2002,0934,83.11,83.14,83.05,83.11,26500
12/30/2002,0935,83.15,83.20,83.06,83.14,79800
12/30/2002,0936,83.13,83.14,83.08,83.12,48400
=====================================================
And outputs this:

DateTime;Open;High;Low;Close;Volume
20021230 093100;83.08;83.10;82.99;83.03;29000
20021230 093200;83.04;83.12;82.99;83.04;41900
20021230 093300;83.04;83.15;83.03;83.15;20200
20021230 093400;83.11;83.14;83.05;83.11;26500
20021230 093500;83.15;83.20;83.06;83.14;79800
20021230 093600;83.13;83.14;83.08;83.12;48400
20021230 093700;83.13;83.16;83.08;83.16;36600

This took me about 3 to 4 minutes to parse out 10 years of 1 minute data
Probably twice as long for something that trades all night and all day.

 * */

import java.io.File;
import java.io.FileNotFoundException;
import java.io.FileWriter;
import java.io.IOException;

//library from:	http://www.csvreader.com/java_csv.php
import com.csvreader.CsvReader;
import com.csvreader.CsvWriter;

public class basicNinjaParse {
	
	String date;
	String time;
	String open;
	String high;
	String low;
	String close;
	String volume;
	
	public void in(){
		try{
			
			CsvReader products = new CsvReader("C:\\DIA.TXT");
			products.readHeaders();
			while(products.readRecord())
			{
				date = products.get("Date");
				time = products.get("Time");
				open = products.get("Open");
				high = products.get("High");
				low = products.get("Low");
				close = products.get("Close");
				volume = products.get("Volume");
				
                                //now lets get it in Ninja format.  12/01/2012 needs to be split by '/' delimeter
                                // and then needs to be combined with time.  Time also needs 00 for ss placeholder
				String [] s = date.split("/");
				String month = s[0];
				String day = s[1];
				String year = s[2];
				//yyyyMMdd HHmmss
				date = s[2]+s[0]+s[1]+" "+time+"00";
				
				//now add to file.  since 800k lines it will
				//keep looping until it is done---should take 3 to 4 minutes
				out();
			}
			products.close();
			
		}catch(FileNotFoundException e){
			e.printStackTrace();
		}catch(IOException e){
			e.printStackTrace();
		}
	}
	public void out(){
		String outputFile = "DIA.TXT";
		boolean alreadyExists = new File(outputFile).exists();
		try{
			//use FileWriter constructor that specifies open for appending
			CsvWriter csvOutput = new CsvWriter(new FileWriter(outputFile, true), ';');
			
			//if the file didn't already exist then we need to write out the header line
			if(!alreadyExists)
			{
				csvOutput.write("DateTime");
				csvOutput.write("Open");
				csvOutput.write("High");
				csvOutput.write("Low");
				csvOutput.write("Close");
				csvOutput.write("Volume");
			}
			//else assume that the file already has the correct header line
			//write out a few records
			csvOutput.write(date);
			csvOutput.write(open);
			csvOutput.write(high);
			csvOutput.write(low);
			csvOutput.write(close);
			csvOutput.write(volume);
			csvOutput.endRecord();
			
			csvOutput.close();
			
		}catch(IOException e){
			e.printStackTrace();
		}
	}
	
	
	public static void main(String args []){
		basicNinjaParse p = new basicNinjaParse();
		p.in();
	}

}

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.

Java Programming Tutorial - 1 - Installing the JDK - YouTube

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

Hi spinnybobo,

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

Thanks for your time, John

How can I convert a .csv file to text for import into NT?

Discussion in NinjaTrader

How can I convert a .csv file to text for import into NT?