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
| 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
{
/// <summary>
/// Database name
/// </summary>
public static string dbName = "Database";
/// <summary>
/// Path to directory with source CSV files
/// </summary>
public static string baseDir = @"C:\Data\";
/// <summary>
/// Table name for bulk import
/// </summary>
public static string dirName = "Table";
/// <summary>
/// SQL Server connection string
/// </summary>
public static string connString = @"server =.\MySQLServer;database=" + dbName;
/// <summary>
/// SQL Bulk import routine
/// </summary>
/// <param name="ticker">Symbol name</param>
/// <param name="filepath">Full path to the symbol's data file</param>
/// <returns></returns>
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[] 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
result[0] = 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);
}
}
}
}