Export OHLCV data for a range of dates

akar

#1

3/11/2019 2:55 AM

I want to export OHLCV data for a range of dates to CSV files. One csv file will hold OHLCV data for a particular day e.g. ALL_SYMS-2019-02-07.csv will hold OHLCV data for every stock only for Feb 7th

I got it working for a single date but it is not working for the range of dates. Can you please look at the code and tell me what I'm doing wrong.

CODE:
Please log in to see this code.

Size:

Color:

I want to export OHLCV data for a range of dates to  CSV files. One csv file will hold OHLCV data for a particular day e.g. ALL_SYMS-2019-02-07.csv will hold  OHLCV data for every stock only for Feb 7th

I got it working for a single date but it is not working for the range of dates. Can you please look at the code and tell me what I'm doing wrong.

[code]using System;
using System.Collections.Generic;
using System.Text;
using WealthLab;
using System.IO;

public class Export2CSV : WealthScript
{
	private List<DateTime> ListOfDates;
	private string OutFile;
	private DateTime today;
	private string dtefmt = "yyyy-MM-dd";
	const string sep = ",";
	const string fmt = "0.00########";
	
	public void AppendToFile (string strs)
	{
		using (StreamWriter w = File.AppendText(OutFile))
		{
			w.WriteLine(strs);
		}
	}
	public void setDates () 
	{
		DateTime startDate = new DateTime(2019,02,01); 
		DateTime endDate = new DateTime(2019,02,15);
		
		for (DateTime date = startDate; date <= endDate; date = date.AddDays(1))
		{
			
			if ((date.DayOfWeek == DayOfWeek.Saturday) || (date.DayOfWeek == DayOfWeek.Sunday))
			{
				string errmsg = "Skipping weekend: " + date;
				PrintDebug(errmsg);
				continue;
			}
			ListOfDates.Add(date); 
		}
	}
	
	protected override void Execute()
	{	
		setDates();
		foreach (DateTime day in ListOfDates)
		{
			if (DataSetSymbols[0] == Bars.Symbol)
			{
			
				today = DateTime.Today;
				string Outpath = @"P:\\WEALTHLAB_EXPORT\\2019\\";
				OutFile = Outpath + "ALL_SYMS-" + today.ToString(dtefmt) + ".csv";

if (!Directory.Exists(Outpath))
					throw new Exception("You must create the directory " + Outpath);
			
				if(File.Exists(OutFile)) 
					throw new Exception("File already exists " + Outpath);
			
				//List<string> datalist = new List<string>();
				AppendToFile("Date,Open,High,Low,Close,Volume");
			}
			
		
			for(int bar = 0; bar < Bars.Count; bar++)
			{
				DateTime today = DateTime.Today;
			
				if (Date[bar].ToString(dtefmt) == today.ToString(dtefmt))
				{
				
					string csv = Bars.Symbol + sep 
						+ Open[bar].ToString(fmt) + sep 
						+ High[bar].ToString(fmt) + sep 
						+ Low[bar].ToString(fmt) + sep 
						+ Close[bar].ToString(fmt) + sep 
						+ Volume[bar].ToString("0");
					//string outstring = "matches Bar: " + Date[bar].ToString(dtefmt) + " Today:" + today.ToString(dtefmt);
					//PrintDebug(csv);
					AppendToFile(csv);
				}
			}
		
		
			if (DataSetSymbols[DataSetSymbols.Count -1] == Bars.Symbol)
			{
				PrintStatusBar("Complete!");
			}
		}
	}
}[/code]

Eugene

#2

3/11/2019 5:45 AM

Matching dates should be done using the .Date property rather than via string comparison but why complicate things at all? Simply choose a "Date Range" in data loading options e.g. 2019/2/1 to 2019/2/15. Case closed.

akar

#3

3/12/2019 5:34 AM

It's not working. I'm getting the following error:

Error processing symbol AAPL Object reference not set to an instance of an object.

Can you please try this and see what I'm missing ?

Eugene

#4

3/12/2019 6:10 AM

What should be working? Am I overlooking some updated code here without the ListOfDates and a .ToDate-based comparison? Can't find any.

As for the error in general please refer to the Knowledge Base (Wiki) article: Errors | Strategy > Object reference not set to an instance of an object.

akar

#5

3/18/2019 4:31 AM

I rewrote the code and it' still giving the "Object reference not set to an instance of an object. ". So something somewhere is not getting initialized and I can't figure it out. I ran the GetDataRange after compiling it with csc.exe and it's returning the list. Can you please tell me what's going on or what's the best way to debug this:

CODE:
Please log in to see this code.

Size:

Color:

I rewrote the code and it' still giving the "Object reference not set to an instance of an object. ". So something somewhere is not getting initialized and I can't figure it out. I ran the GetDataRange after compiling it with csc.exe and it's returning the list. Can you please tell me what's going on or what's the best way to debug this:

[code]using System;
using System.Collections.Generic;
using System.Text;
using WealthLab;
using System.IO;

/**********************************************************************************************

This program creates a file for each day given a range of dates. Each of the file contains 
the Open,High,Low,Close,Volume, data for every  symbol in the dataset. It's similar to the
EOD data that you can purchase from different vendors

**********************************************************************************************/

public class Export2ASCII : WealthScript
{

private string OutFile;
	private static DateTime StartingDate = DateTime.Parse("03/01/2019");
	private static DateTime EndingDate   = DateTime.Parse("04/06/2019");
	private string Outpath = @"P:\\personal2\\TRADING\\STOCKS\\DATA\\histdata-csv\\WEALTHLAB_EXPORT\\2019\\";
	private string dtefmt = "yyyy-MM-dd";
	const string sep = ",";
	const string fmt = "0.00########";
	
	public void AppendToFile (string strs)
	{
		using (StreamWriter w = File.AppendText(OutFile))
		{
			w.WriteLine(strs);
		}
	}

private List<DateTime> GetDateRange(DateTime StartingDate, DateTime EndingDate)
	{
		if (StartingDate > EndingDate)
		{
			return null;
		}

List<DateTime> rv = new List<DateTime>();
		DateTime tmpDate = StartingDate;
		do
		{
			if ((tmpDate.DayOfWeek == DayOfWeek.Saturday) || (tmpDate.DayOfWeek == DayOfWeek.Sunday))
			{
				string infomsg = "Skipping weekend: " + tmpDate.ToString(dtefmt);
				PrintDebug(infomsg);
			}
			else
			{
				rv.Add(tmpDate);
				PrintDebug(tmpDate.ToString(dtefmt));
			}
			tmpDate = tmpDate.AddDays(1);

} while (tmpDate <= EndingDate);

return rv;
	}

protected override void Execute()
	{	
		Export2ASCII datelist = new Export2ASCII();

foreach (DateTime pdate in datelist.GetDateRange(StartingDate,EndingDate))
		{
			string OutFile = Outpath + "ALL_SYMS-" + pdate.ToString(dtefmt) + ".csv";

if (!Directory.Exists(Outpath))
			{
				string exitMsg = Outpath + " - Directory does not exist";
				PrintDebug(exitMsg);
				System.Environment.Exit(1);
			}
			
			if (DataSetSymbols[0] == Bars.Symbol)
			{
				if(File.Exists(OutFile))
				{
					throw new Exception("File already exists " + Outpath);			
				}
				AppendToFile("Date,Open,High,Low,Close,Volume");
			}
			
		
			for(int bar = 0; bar < Bars.Count; bar++)
			{
				if (Date[bar].ToString(dtefmt) == pdate.ToString(dtefmt))
				{
				
					string csv = Bars.Symbol + sep 
						+ Open[bar].ToString(fmt) + sep 
						+ High[bar].ToString(fmt) + sep 
						+ Low[bar].ToString(fmt) + sep 
						+ Close[bar].ToString(fmt) + sep 
						+ Volume[bar].ToString("0") + sep
						+ pdate.ToString(dtefmt);
					AppendToFile(csv);
				}
			}
		
		
			if (DataSetSymbols[DataSetSymbols.Count -1] == Bars.Symbol)
			{
				PrintDebug("Complete!");
			}
		}
	}
}

[/code]

Eugene

#6

3/18/2019 4:59 AM

Not sure if you've overlooked my reply #2 with the obvious solution. Since I haven't seen an explanation as to why the code has to work the way you're trying it to make then it's up to you to do your troubleshooting:

How can I debug my trading strategies in Wealth-Lab?

akar

#7

3/19/2019 6:02 AM

I overlooked the #2 comment. What you suggested is a much better solution. I changed the code (listed below). However, the code only prints the data for the last date. I set the Date Range in WL to 2019-01-01 to 2019-01-04. However it only prints data for 2019-01-04.

CODE:
Please log in to see this code.

Size:

Color:

I overlooked the #2 comment. What you suggested is a much better solution. I changed the code (listed below). However, the code only prints the data for the last date. I set the Date Range in WL to 2019-01-01 to 2019-01-04. However it only prints data for 2019-01-04.

[code]using System;
using System.Collections.Generic;
using System.Text;
using WealthLab;
using System.IO;

public class Export2ASCII : WealthScript
{

private string OutFile;
	private string Outpath = @"P:\\personal2\\TRADING\\STOCKS\\DATA\\histdata-csv\\WEALTHLAB_EXPORT\\2019\\";
	private string dtefmt = "yyyy-MM-dd";
	const string sep = ",";
	const string fmt = "0.00########";
	
	public void AppendToFile (string strs)
	{
		using (StreamWriter w = File.AppendText(OutFile))
		{
			w.WriteLine(strs);
		}
	}

protected override void Execute()
	{	
		Export2ASCII datelist = new Export2ASCII();
		string td ="";

if (!Directory.Exists(Outpath))
		{
			string exitMsg = Outpath + " - Directory does not exist";
			PrintDebug(exitMsg);
			System.Environment.Exit(1);
		}
			
		if (DataSetSymbols[0] == Bars.Symbol)
		{
			td = Date[Bars.Count - 1].Date.ToString(dtefmt);
			string OutFile = Outpath + "ALL_SYMS-" + td + ".csv";
			if(File.Exists(OutFile))
				throw new Exception("File already exists " + Outpath);	
			}
			PrintDebug(td);
			PrintDebug(OutFile);
			
			AppendToFile("Date,Open,High,Low,Close,Volume");
		}
		
		for(int bar = 1; bar < Bars.Count; bar++)
		{
			string csv = Bars.Symbol + sep 
				+ Open[bar].ToString(fmt) + sep 
				+ High[bar].ToString(fmt) + sep 
				+ Low[bar].ToString(fmt) + sep 
				+ Close[bar].ToString(fmt) + sep 
				+ Volume[bar].ToString("0") + sep
				+ td;
			PrintDebug(csv);
		}
		
		
		
		if (DataSetSymbols[DataSetSymbols.Count -1] == Bars.Symbol)
		{
				PrintDebug("=========");
		}
	}
}[/code]

Eugene

#8

3/19/2019 7:21 AM

QUOTE:
However, the code only prints the data for the last date.

No, it prints the data for the last two bars. There are 3 bars in that time span but for some reason you've modified the working code from Exporting to ASCII comma-separated files (CSV) to exclude the first bar:

CODE:
Please log in to see this code.

If you retract the unnecessary change it will output all the 3 bars. Run this in Multi-Symbol mode:

CODE:
Please log in to see this code.

Size:

Color:

[quote]However, the code only prints the data for the last date.[/quote]
No, it prints the data for the last two bars. There are 3 bars in that time span but for some reason you've modified the working code from [link=http://www2.wealth-lab.com/WL5WIKI/kbExportDataFromWL5.ashx]Exporting to ASCII comma-separated files (CSV)[/link] to exclude the first bar:

[code]//WAS: correct
for(int bar = 0; bar < Bars.Count; bar++)
//IS: incorrect
/for(int bar = 1; bar < Bars.Count; bar++)[/code]
If you retract the unnecessary change it will output all the 3 bars. Run this in Multi-Symbol mode:

[code]using System;
using System.Collections.Generic;
using System.Text;
using System.Drawing;
using WealthLab;
using WealthLab.Indicators;
using System.IO;

namespace WealthLab.Strategies
{
	public class Export2ASCII : WealthScript
	{

private string OutFile;
		private string Outpath = @"P:\\personal2\\TRADING\\STOCKS\\DATA\\histdata-csv\\WEALTHLAB_EXPORT\\2019\\";
		private string dtefmt = "yyyy-MM-dd";
		const string sep = ",";
		const string fmt = "0.00########";
   
		public void AppendToFile (string strs)
		{
			using (StreamWriter w = File.AppendText(OutFile))
			{
				w.WriteLine(strs);
			}
		}

protected override void Execute()
		{   
			Export2ASCII datelist = new Export2ASCII();
			string td ="";

if (!Directory.Exists(Outpath))
			{
				string exitMsg = Outpath + " - Directory does not exist";
				PrintDebug(exitMsg);
				System.Environment.Exit(1);
			}
         
			if (DataSetSymbols[0] == Bars.Symbol)
			{
				td = Date[Bars.Count - 1].Date.ToString(dtefmt);
				OutFile = Outpath + "ALL_SYMS-" + td + ".csv";
				if(File.Exists(OutFile))
					throw new Exception("File already exists " + Outpath);   
				AppendToFile("Date,Open,High,Low,Close,Volume");
			}
			PrintDebug(td);
			PrintDebug(OutFile);
      
			for(int bar = 0; bar < Bars.Count; bar++)
			{
				string csv = Bars.Symbol + sep 
					+ Open[bar].ToString(fmt) + sep 
					+ High[bar].ToString(fmt) + sep 
					+ Low[bar].ToString(fmt) + sep 
					+ Close[bar].ToString(fmt) + sep 
					+ Volume[bar].ToString("0") + sep
					+ td;
				AppendToFile(csv);
				PrintDebug(csv);
			}
      
			if (DataSetSymbols[DataSetSymbols.Count -1] == Bars.Symbol)
			{
				PrintDebug("=========");
			}
		}
	}
}[/code]

akar

#9

3/20/2019 6:12 AM

Oops my bad! Thanks for pointing it out.

Only one problem remains. All the data is getting written into the same file i.e. the one with the last date. How do I change the program to write the data to separate files ?

Thanks again for all your comments and help.

Eugene

#10

3/20/2019 6:36 AM

QUOTE:
All the data is getting written into the same file i.e. the one with the last date.

That's how I gather your intention was. The file name clearly indicates that everything gets dumped into one file:

CODE:
Please log in to see this code.

QUOTE:
How do I change the program to write the data to separate files ?

Just revisit the Wiki article for the code example which already does just that - export to separate files:

Exporting to ASCII comma-separated files (CSV)

akar

#11

3/21/2019 12:17 AM

I'm trying to get EOD data for every symbol on a file named after that date
i.e. 2019-01-05.csv will have the OHLCV data for every stock in the dataset for Jan 5, 2019

The challenge I'm facing is to how to write the output to the correct files i.e. how to match the file name with the date
e.g. let's say there are 2 symbols in the dataset AAPL FB

I want the Jan 03.2019 data for both FB and AAPL to go to 2019-01-03.csv.

AAPL
Jan 04, 2019 144.53 148.55 143.80 148.26 147.63 58,607,100 -> 2019-01-04.csv
Jan 03, 2019 143.98 145.72 142.00 142.19 141.58 91,312,200 -> 2019-01-03.csv
Jan 02, 2019 154.89 158.85 154.23 157.92 157.25 37,039,700 -> 2019-01-02.csv

FB
Jan 04, 2019 134.01 138.00 133.75 137.95 137.95 29,002,100 -> 2019-01-04.csv
Jan 03, 2019 134.69 137.17 131.12 131.74 131.74 22,717,900 -> 2019-01-03.csv
Jan 02, 2019 128.99 137.51 128.56 135.68 135.68 28,146,200 -> 2019-01-02.csv