Importing ASCII data from stoxx.com

AbsoluteReturn

#1

8/15/2013 7:41 AM

Hello

I would like to ask if it is possible to download ASCII data from the following Website into WL:

http://www.stoxx.com/download/historical_values/hmsppe.txt

At this site the data for all STOXX 600 Supersector indices are updated every day and I would like to import the data for analysis.

Thanks in advance

Eugene

#2

8/15/2013 8:15 AM

Sorry, it's not possible.

Wealth-Lab's ASCII provider only supports "one symbol, one file" scenario. The Database provider in ODBC mode could do for "many symbols, one file, single symbol per row" scenario. Unfortunately, this particular ASCII file follows a rare and unsupported convention: "many symbols, one file, several symbols per row". You need to find the data in a supported format.

AbsoluteReturn

#3

8/18/2013 6:49 AM

Thanks for the explanation.

Because it is not possible to download this file with many symbols per row, I search for an other possibility to get this data into WL.
Is there possibly a way with the aid of an additional WL script which is parsing the ASCII rows and then storing the data for each symbol in a seperate ASCII file with a supported format?

Eugene

#4

8/18/2013 9:55 AM

Okay, got a complete solution for you:

1. downloads the file to your disk (required for FillSeriesFromFile to work),
2. parses the file, creating DataSeries for each index
3. exports them to CSV files (to create an ASCII DataSet)
4. puts them in Wealth-Lab's global memory so you can access them w/o setting up a DataSet (see below)
5. plots them on the chart

Here you go (but read NOTES below):

CODE:
Please log in to see this code.

NOTE:

1. Requires LATEST version of Community Components installed
2. Run in single symbol mode.
3. Adjust the folder where the downloaded data file will be saved:

CODE:
Please log in to see this code.

A new folder "ASCII" will be created here with CSV files containing exported data

4. After getting the file downloaded, comment this line out - you don't want to download it over and over (perhaps only once a day)!

CODE:
Please log in to see this code.

5. Finally, to access the downloaded DataSeries in any strategy w/o having to set up an ASCII DataSet, use this code (each index has a name like SXAP etc.):

CODE:
Please log in to see this code.

Size:

Color:

Okay, got a complete solution for you:

1. downloads the file to your disk (required for FillSeriesFromFile to work), 
2. parses the file, creating DataSeries for each index
3. exports them to CSV files (to create an ASCII DataSet)
4. puts them in Wealth-Lab's global memory so you can access them w/o setting up a DataSet (see below)
5. plots them on the chart

Here you go (but read NOTES below):

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

namespace WealthLab.Strategies
{
	public class MyStrategy : WealthScript
	{
		// Put series in the global storage
		void SetGlobalSeries( string sName, DataSeries series )
		{
			SetGlobal( sName, series );
		}

// Export to CSV
		void ExportToASCII( string name, DataSeries ds, string folder, string asciiSubfolder )
		{
			Directory.CreateDirectory(folder + asciiSubfolder);
			string file = folder + asciiSubfolder + name + ".csv";
			
			using( StreamWriter csv = new StreamWriter( file ) )
			{
				for(int bar = 0; bar < ds.Count; bar++)
				{
					csv.WriteLine( ds.Date[bar].ToString("dd.MM.yyyy") + "," + ds[bar] );
				}
			}
		}
		
		protected override void Execute()
		{
			// Path to put the downloaded data file to
			string folder = @"c:\Users\AbsoluteReturn\Downloads\";
			string ascii = @"ASCII\";
			string file = "hmsppe.txt";
			string path = Path.Combine( folder,file );
			
			// URL to the data file containing external series on stoxx.com
			string url = @"http://www.stoxx.com/download/historical_values/hmsppe.txt";
			WebClient webClient = new WebClient();

// To avoid downloading the file ON EVERY SCRIPT RUN, comment out this line!
			webClient.DownloadFile(url, path);
			
			// Derive symbol names and their count from string #3
			int count = 0;
			List<string> names = new List<string>();
			foreach (var line in File.ReadLines(path))
			{
				string symbols = line.ToString().TrimStart();
				if( symbols.ToString().TrimStart().StartsWith(";"))	// Line #3 starts with a ";"
				{
					string[] split = symbols.Split(new char[]{ ';' });
					foreach( string s in split )
					{
						// Ignoring the first empty element, put symbol name in a separate list
						if( !string.IsNullOrEmpty(s) )	
							names.Add(s);
					}
					
					count = names.Count;	// Symbol count
					break;
				}
			}

// Create an array of DataSeries capable of holding as many series as the data file contains
			DataSeries[] ds = new DataSeries[count];
			for(int i = 0; i < count; i++)
			{
				string name = names[ i ];
				DataSeries d = new DataSeries( Bars, name );	// Take DataSeries name from the List of names
				ds[ i ] = d;
			}
			
			// Let FillSeriesFromFile fill your array of DataSeries
			ds.FillSeriesFromFile( this, path, Char.Parse(";"), "dd.MM.yyyy", true );
			
			// Put to global memory and export to ASCII
			for(int i = 0; i < count; i++)
			{
				string name = names[ i ];
				DataSeries d = ds[ i ];							// Take DataSeries name from the List of names
				SetGlobalSeries( name, d );						// Put DataSeries on Wealth-Lab's global memory
				ExportToASCII( name, d, folder, ascii );		// Export DataSeries to ASCII file
			}
			
			// Plot it
			HideVolume();
			ChartPane MyPane = CreatePane( 100, true, true ); 
			KnownColor[] color = (KnownColor[]) Enum.GetValues(typeof(KnownColor));
			
			for(int i = 0; i < count; i++)
			{
				Random randomGen = new Random();
				KnownColor randomColorName = color[randomGen.Next(color.Length)];
				Color randomColor = Color.FromKnownColor(randomColorName);
				System.Threading.Thread.Sleep(10);
				PlotSeries( MyPane, ds[ i ], randomColor, LineStyle.Solid, 1 );
			}
			
			ClearDebug();			
		}
	}
}
[/code]

[color=#f73ed2][b]NOTE:[/b][/color]

1. Requires LATEST version of Community Components installed
2. Run in single symbol mode.
3. Adjust the [i]folder[/i] where the downloaded data file will be saved:
[code]string folder = @"c:\Users\AbsoluteReturn\Downloads\";[/code]
A new folder "ASCII" will be created here with CSV files containing exported data

4. After getting the file downloaded, comment this line out - you don't want to download it over and over (perhaps only once a day)!
[code]// To avoid downloading the file ON EVERY SCRIPT RUN, comment out this line!
webClient.DownloadFile(url, path);[/code]

5. Finally, to access the downloaded DataSeries in any strategy w/o having to set up an ASCII DataSet, use this code (each index has a name like SXAP etc.):

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

namespace WealthLab.Strategies
{
	public class MyStrategy : WealthScript
	{
		protected override void Execute()
		{
			ChartPane MyPane = CreatePane( 50, true, true ); 
			PlotSeries( MyPane, (DataSeries)GetGlobal("SXAP"), Color.Black, LineStyle.Solid, 1 );
		}
	}
}[/code]

AbsoluteReturn

#5

8/19/2013 2:48 PM

Hello Eugene

Thank you very much for this great support.

AbsoluteReturn

#6

8/21/2013 4:38 AM

Hello Eugene

Unfortunately the following problem occurs with the data stored in the created CSV files:
The price is always stored in Integer format, the decimal separator is ignored. Because the original data file contains data with mostly 2, but sometimes with only 1 or even no any decimal places, the Integer values in the CSV files have mostly 5, but sometimes only 4 or 3 numbers. Therefore using these CSV files for analysis is not possible.

I would like to ask if you could modify the script in order to get the data in decimal format instead of integer numbers.

Thanks in advance

Eugene

#7

8/21/2013 6:39 AM

My files always come out correct, and I can not reproduce this issue here. There's no restriction and/or error.

As German/Austrian users usually do, you probably have that "," set as the default decimal separator in Windows. I can imagine that you won't be able to import ASCII files separated with "," properly with this setup. My decimal separator is "." which is highly recommended.

So your job is to adjust separator character in the ASCII export routine from comma (line 31) to anything else like ";" and then re-create the ASCII DataSet (use the uber-helpful "Copy format from a DataSet" feature but specify a different separator char). It's super easy and you can do it yourself.