Database static and streaming provider

Modified on 2017/10/02 15:31 by Eugene — Categorized as: Providers

What is it

Users have expressed demand to connect to databases, and Wealth-Lab listened: this provider helps connect to any ADO.NET compatible datasource. Out of the box, a typical PC with .NET framework supports Oracle, OleDb, ODBC, SQL Server and SQL Compact.

Features

Supports historical and real-time data
For static and streaming quotes, can query different databases
The provider is optimized for MS SQL Server (uses asynchronous methods)
If other database installs its .NET driver in the system, the provider can autodetect and support it - for example: SQLite, Firebird, MySQL etc.
Every data request is live, the data only exists in your database (and can't be edited in WL)
Supports Market Manager

Creating a new DataSet

To work with a database containing some data, you need to create a new static DataSet first. Pick the appropriate Provider from the list of available ones (e.g. System.Data.OleDb/ODBC for Excel, Access, text files and lots more, System.Data.SqlClient for SQL Server etc.), compose a connection string, and you're ready for the next step.

Usually, this is a no-brainer for the database guys, but should you feel lost in connection strings, clicking the bluish Connection string Help label will try to guess what kind of database you're trying to connect to, using the bits and pieces of your connection string and/or selected provider, and take you to the relevant page of the connectionstrings.com site.

Your connection string should include a database name.
Your connection string must not include curly braces i.e. { }

A rule of thumb is to first type in a connection string and only then proceed with selecting a provider. When you're finished with this step, the provider will populate the Table name dropdown box with the list of tables found. Select the one you need to make the provider fill the other boxes with available fields. Upon defining a field to serve as Security Name, the "Symbols" area is populated with found tickers. Note: when database contains thousands of symbols, this step may take a while (several minutes). Please be patient.

Match available fields to DateTime, Open, High, Low and Close. Volume and Named Series are optional. Note: for symbol names, you should use the varchar() type, not text. The provider won't let you confuse a DateTime field for prices and vice versa. To finish creation of your DataSet, select a bar scale and interval.

New DataSet Wizard

Setting up Streaming quotes

It's possible to use one source for static data while getting your streaming quotes from a different database (provided that the symbol exists in both). Setting up the provider for Streaming quotes is a two-step process:

At any time, only one data source for streaming quotes can be defined. It's a global setting. To specify a database to use for real-time quotes, open the Data Manager tool, switch to the Database provider's tab, and repeat there all steps taken when completing the "New DataSet Wizard". For streaming quotes, only the Close price is required, Volume is optional.

Configure streaming source
Finally, set "Database provider" as your current streaming provider in the Wealth-Lab's Preferences dialog, Streaming.

Notes:

Push "Clear" button before first-time configuration or when changing settings.
You must restart WL6 after changing Streaming settings.

Usage Notes and Limitations

Limitation: Paper Trading through Database provider is not supported
Limitation: Open/Change fields in Quotes windows are not supported (only Last Price)
Limitation: Wealth-Lab restart required after re-configuring provider's Data Manager tab (Streaming properties)
Limitation: Volume is disabled for Streaming charts
Limitation: Tick charts can't be compressed into higher time frames
Limitation: Table name can not start with a digit
Limitation: Using compressed intraday scale in Strategy Monitor is not supported (for example, if base timeframe of your table is 1-minute, don't select a higher timeframe in Strategy Activation settings e.g. 5-minute)
Compatibility: When Streaming quotes don't work and your SQL Server has some international settings, add this to your connection string (thanks Matthias):

Current Language=us_english
Compatibility: If you have trouble connecting to MySQL, add this to your connection string:

sql server mode=yes
Compatibility: If reading from a MySQL database generates an "Unable to convert MySQL date/time value to System.DateTime" error, add the attribute below to your connection string and make sure that date columns in the MySQL database either contains all valid values or contain NULL (i.e. are defined as allow Null value)

Allow Zero Datetime=true
Note to MySQL users. Do not install Connector/Net (MySql.Net) version 6.7.4 as it will not work due to a bug. Instead, install latest version (6.9.3 at the time of this update).
Note: when using MS Access tables for streaming quotes, it's highly recommended to work via OLE DB and not ODBC.
Note: Oracle Database Express 11g is a 32-bit application that is not compatible with 64-bit Wealth-Lab, so you must use 32-bit version.
Note: on 64-bit operating systems, make sure you have installed ODBC and OLEDB drivers of appropriate "bitness". 64-bit Wealth-Lab can't use 32-bit ODBC drivers, and vice versa. For 64-bit drivers, visit this link: Microsoft Access Database Engine 2010 Redistributable. More info: Jet for Access, Excel and Txt on 64 bit systems.
Note: if some driver for PostgreSQL doesn't seem to work for you, just try another: dotConnect by DevArt, NPGSQL, PostgreSQL ODBC...
Note: when building a connection string using a resource like connectionstrings.com, do not use curly brackets as this is not supported by the provider. Whenever suggested to use curly brackets, just skip them. Examples:

Driver={MySQL ODBC 5.1 Driver};	incorrect
Driver=MySQL ODBC 5.1 Driver;	correct
Driver={Microsoft Access Driver (.mdb, .accdb)};	incorrect
Driver=Microsoft Access Driver (.mdb, .accdb);	correct

Bonus: Bulk import of data in CSV files to an SQL Server database

This companion article illustrates how to use a Wealth-Lab Strategy to import the data from a large group of ASCII files into an SQL Server database.

Problem

You have a big bunch of ASCII CSV files from your data vendor containing DTOHLCV series for a large group of stocks. You would like to import all the files into an SQL Server database to run Strategies using the Database provider.

Here are a few lines from a data file:

Date,Open,High,Low,Close,Volume,Adj Close
2009-12-31,6.00,6.00,6.00,6.00,400,6.00
2009-12-30,5.91,5.91,5.89,5.89,13500,5.89
2009-12-29,5.83,5.96,5.83,5.90,12700,5.90
...

As we can see, the symbol is missing - it's a part of the file name e.g. TEST.CSV. Obviously, neither it makes sense to import the data for a group of symbols into a single table (the provider won't be able to tell one symbol from another), nor can we put each instrument in an individual table (too time consuming and inconvenient).

Creating an SQL Server database

First, set up a new database - for example, using Microsoft SQL Server Management Studio. Next, create a new Table with the following properties:

Symbol	varchar(10)	non-null
Date	datetime	non-null
Open	float	non-null
High	float	non-null
Low	float	non-null
Close	float	non-null
Volume	bigint	nullable
AdjClose	float	nullable

Note that symbol names have to be of type varchar. Now, it's time to index the table. Obviously, the Symbol field has to be indexed in any case or querying the database would be too slow. If you don't plan updating the table too often, use more indexes - otherwise, don't over-index your table.

Coding the bulk import routine

Although a command line utility could be created, why bother with a C# IDE or .NET SDK when we have the Wealth-Lab's built-in Strategy Editor?

One obvious problem is that the data files are missing the Symbol column that is the index of the table. We're going to get the current symbol from the file name (i.e. Ticker.CSV) and insert the missing piece on-the-fly.

Below you'll find the complete code of a WealthScript program that imports all .CSV files from a directory into a single table. Key parameters such as the connection string, table name and source folder are configurable. Follow the comments to understand the logic.

Note: before using this code, in the Wealth-Lab's Editor, open the "References..." dialog and tick two checkboxes - next to System.Data and System.Xml.


using System;
using System.Collections.Generic;
using System.Text;
using System.Drawing;
using WealthLab;
using WealthLab.Indicators;
using System.Data;
using System.Data.SqlClient;
using System.IO;
using System.Globalization;

namespace WealthLab.Strategies
{
	public class SQLBulkImportCSV : WealthScript
	{
		/// 
		/// Database name
		/// 
		public static string dbName = "Database";
		
		/// 
		/// Path to directory with source CSV files
		/// 
		public static string baseDir = @"C:\Data\";
		
		/// 
		/// Table name for bulk import
		/// 
		public static string dirName = "Table";
		
		/// 
		/// SQL Server connection string
		/// 
		public static string connString = @"server =.\MySQLServer;database=" + dbName;

		/// 
		/// SQL Bulk import routine
		/// 
		/// Symbol name
		/// Full path to the symbol's data file
		/// 
		static void ImportCSV(string ticker, string filepath)
		{
			using( SqlConnection con = new SqlConnection(connString) )
			{
				// Split the file line by line
				StreamReader sr = new StreamReader(filepath);
				string line = sr.ReadLine();
				// Here are the columns found in the data: Date, Open, High...
				string[] value = line.Split(',');
				string">0"> result = new string[0;
				// An extra column for the Symbol field			
				int newColumns = value.GetLength(0) + 1;

				// A DataTable for putting the CSV data in
				System.Data.DataTable dt = new System.Data.DataTable();
				DataRow row;
                
				// Insert new column "Symbol" in CSV file being parsed (missing in the data)
				dt.Columns.Add(new DataColumn("Symbol"));
				
				// Add the other columns (DOHLCV+custom series)
				foreach (string dc in value)
				{
					dt.Columns.Add(new DataColumn(dc));
				}

				// Prefill a string array with the Symbol name
				string[] sym = { ticker, "," };
				int i = 0;
				
				// Read the CSV file line by line
				while (!sr.EndOfStream)
				{
					string source = sr.ReadLine();
					
					// Skip empty lines and garbage
					if (source.Length <= 1)
						continue;
					
					// Treat the CSV comma-separated line as an array
					value = source.Split(',');
					
					// Resize the array to add space for the Symbol column
					Array.Resize(ref result, newColumns);
					
					// Insert the parsed row
					Array.Copy(value, 0, result, 1, value.Length);
					
					// Add the ticker to the very beginning
					result0 = ticker;
					DateTime _dt = DateTime.Now;

					if (result.Length == dt.Columns.Count)
					{
						row = dt.NewRow();
						
						// Assign the resulting string array to a new DataRow
						row.ItemArray = result;
						if (i != 0)
							dt.Rows.Add(row);
					}
					i++;
				}

				// Use the SqlBulkCopy class to write the DataTable made of a single stock's CSV file, to SQL Server
				SqlBulkCopy bc = new SqlBulkCopy(con.ConnectionString, SqlBulkCopyOptions.TableLock);
				bc.DestinationTableName = dirName;
				bc.BatchSize = dt.Rows.Count;
                
				con.Open();
				bc.WriteToServer(dt);
				bc.Close();
				con.Close();
			}            
		}
		
		protected override void Execute()
		{
			// Get the collection of Files from the Source Directory
			string destination = Path.Combine(baseDir, dirName);
			DirectoryInfo dirCustom = new DirectoryInfo(destination);
			FileInfo[] filCustom = dirCustom.GetFiles();

			// Loop by each file with .CSV extension, importing its data to SQL Server
			foreach (FileInfo f in filCustom) 
			{ 
				ImportCSV(f.Name.Replace(".csv",string.Empty), f.FullName); 
			}
		}
	}
}