Dataset handling

The following functions can be used for downloading and parsing historical or recent data from various sources, and storing them in binary datasets. Normally, every record in the dataset begins with a time stamp. The rest of the data can have arbitrary content, such as option chains, order book content, asset names, reports, earnings, interest rates, or any other formatted data.

dataDownload (string Code, int Mode, int Period): int

Downloads the dataset with the given Code from Quandl™ or other price sources, and stores it in CSV format in the History folder. Returns the number of data records. Data is only downloaded when it is more recent than the last downloaded data plus the given Period in minutes (at 0 the data is always downloaded). The Quandl Bridge or Zorro S is required for loading Quandl datasets.

dataParse (int Handle, string Format, string Filename, int Start, int Num): int

Parses a part or all data records from the CSV file Filename and appends them at the begin of the dataset with the given Handle number. Num records are parsed, beginning with the record Start. If both parameters are omitted or zero, the whole CSV file is parsed. Records can have time/date, floating point, integer, and text fields. CSV headers are skipped. Several CSV files can be appended to the same dataset when their record format is identical. The CSV file can be in ascending or descending chronological order, but the resulting dataset should normally be in descending order, i.e. the newest records are at the begin. Any record in the dataset begins with a time stamp field in DATE format; the other fields can be in arbitrary order determined by the Format string. The function returns the number of records read, or 0 when the file can not be read or has a wrong format.

dataParse (int Handle, string Format, string Filename, string Filter): int

As before, but parses only lines that contain the string Filter. The string is case senstitive and can cover several adjacent fields, including the delimiters. This way only lines with a certain asset name or field value are parsed.

dataSort (int Handle)

Sorts the dataset with the given Handle in descending time stamp order.

dataSave (int Handle, string Filename, int Start, int Num)

Stores a part or all records of the dataset with the given Handle number in a binary file in the History folder for faster access. Num records are stored, beginning with the record Start. If both parameters are omitted or zero, the whole dataset is stored.

dataSaveCSV (int Handle, string Format, string Filename, int Start, int Num)

The opposite to dataParse; stores a part or all of the dataset with the given Handle number in a CSV file with the given FileName. The type and order of the CSV fields can be defined by the Format string in the same way as for dataParse.

dataLoad (int Handle, string Filename, int Fields): int

Reads a dataset from a binary file. Fields is the number of fields per record, including the date/time field at the begin of any record. The function returns the number of records read, or 0 when the file can not be read or has a wrong size.

dataNew (int Handle, int Records, int Fields): void*

Deletes the given dataset and creates a new empty dataset with the given number of Records and Fields. If they are 0, the dataset is just deleted and the memory freed. Returns a pointer to the begin of the first record, or 0 when no new dataset was created.

dataAppend (int Handle1, int Handle2, int Start, int Num): int

Appends dataset Handle2 partially or completely at the end of the dataset Handle1. The Handle1 dataset must be either empty or have the same number of columns as Handle2. The number of rows may be different. Num records from Handle2 are stored, beginning with the record Start. If both parameters are omitted or zero, the whole dataset is stored. Returns the total number of records, or 0 when the datasets could not be appended.
 

dataFind (int Handle, var Date): int

Returns the number of the first record at or before the given Date in wdate format. Returns -1 when no matching record was found or when no dataset with the given Handle exists. The dataset must be sorted in descending time stamp order. Decrease the returned record number to retreive records with later dates; increase it to get records with earlier dates or with the same date.

dataVar (int Handle, int Row, int Column): var

Returns the value of the floating point field Column from the record Row. If Column is 0, the time stamp of the record is returned in wdate format. If Row is negative, the record is taken from the end of the dataset, i.e. Row = -1 accesses the oldest record. If the dataset is empty or if Row or Column exceed the number of records and fields, 0 is returned., 0 is returned.

dataInt (int Handle, int Row, int Column): int

As before, but returns the value of the integer field Column from the record Row.

dataStr (int Handle, int Row, int Column): string

As before, but returns a pointer to the field Column from the record Row. It it's a text field, the text string of up to 3, 7, or 11 characters is returned. If Column is 0, it returns a pointer to the timestamp field, i.e. the start of the record. For getting a pointer to the first record of the dataset, call dataStr(Handle,0,0)

dataSet (int Handle, int Row, int Column, var Value)

dataSet (int Handle, int Row, int Column, int Value)

Stores the Value in the floating point or integer field Column of the record Row. Can be used for modifying datasets f.i. for removing outliers or adding parameters. When modifying the time stamp field of the record (Column = 0), make sure to keep descending order of dates in the array.
 

dataFromQuandl (int Handle, string Format, string Code, int Column): var

Helper function for generating an indicator based on a Quandl™ time series. Works in live trading as well as in backtest mode, and returns the content of the field Column from the dataset Code in the given Format. Source code in contract.c, which must be included for using this function. Zorro S required. 

dataFromCSV (int Handle, string Format, string Filename, int Column): var

Helper function for generating an indicator based on a downloaded CSV file; for backtesting only. Returns the content of the field Column from the file Filename.csv in the given Format. Source code in contract.c, which must be included for using this function. 
 
 

Parameters:

Code The Google or Quandl code, f.i. "NYSE:AMZN" or "WIKI/AAPL". For selecting a ticker from a Quandl data table, add a colon and the ticker symbol, f.i. "ZACKS/ES:AAPL". The file is stored in the History folder under the Code name with ": /' characters replaced with "- _", plus "1" when only the most recent record was downloaded, plus ".csv".
Mode FROM_GOOGLE for downloading a time series from Google™
FROM_GOOGLE|1 for downloading only the last records, for live trading
FROM_QUANDL for downloading a time series from Quandl™
FROM_QUANDL|1 for downloading only the most recent record, for live trading
FROM_QTABLE for downloading a Quandl™ data table
Period Minimum time in minutes to keep the last downloaded file until a newer file is downloaded, or 0 for always downloading the file.
Handle A number from 1...800 that identifies the dataset. Handles above 800 are interally used for Zorro's pre-defined indicators.
FileName Name of the file. If no path is included, the file is expected in the History folder. If the name has no extension, ".csv" is added.
Format

Format string with placeholders, similar to the printf or DATE formats, for parsing CSV records into a dataset or for storing a dataset in CSV format. Fields are separated with the same delimiter as in the CSV file, either a comma or a semicolon. Any field can be either empty, or contain a placeholder that determines the field content. Empty fields are skipped in the CSV file. The following placeholders are supported:

+ at the begin of the format string - ascending date order in the .csv file, and appending the file to the end of the dataset.
0,1,2 after the + - the .csv file contains 0, 1 (default), or 2 header lines that are skipped.
f - for a floating point field, f.i. 123.456.
i - for an integer field. Nonnumerical characters are skipped, f.i. "07/21/16 13:57" is parsed to 721161357.
s - for a 3 characters text field.
ss - for a 7 characters text field, f.i. the name of an asset, that occupies 2 fields in the dataset.
sss - for a 11 characters text field that occupies 3 fields in the dataset.
% - for a date/time field in DATE format codes. Trailing seconds can have decimals and are converted with 1 microsecond precision. A record must contains at least one date/time field. If there are more, f.i. separate fields for date and time, they are summed up.

The f, i, s, placeholders are optionally followed by a field number in the destination dataset. Example: "+1%Y%m%d %H%M%S,f3,f1,f2,f4,f6" parses Histdata™ CSV files into a dataset in T6 format; "+1%Y-%m-%d,f" parses a .csv profit-and-loss curve generated by a backtest to a dataset in T1 format. If the number is omitted, the fields are parsed in ascending order. The number of fields in the format string can be different to the fields in the CSV record. The remaining fields are filled with 0.

Records Number of records in the dataset.
Fields Number of fields per record, including the date field.
Date Timestamp in Windows DATE format. Days are represented by whole number increments starting with 30 December 1899, midnight UTC. The time of the day is represented in the fractional part of the number.
Start, Num The first record and the number of records to be stored.
Row, Column The record and field number, starting with 0. The date is always the first field of the record. If Row is negative, the record is taken from the end of the file, i.e. Row = -1 accesses the oldest record.
Value New value of the addressed field.

Remarks:

Example:

// parse iVolatility historical option chain data and store the resulting array in a dataset 
void main()
{
  string Format = "+,,%m/%d/%y,,,i,f,s,s,f,f,f,,f";
  int records = dataParse(1,Format,"iVolatility_SPY_2014_1.csv");
  records += dataParse(1,Format,"iVolatility_SPY_2014_2.csv");
  records += dataParse(1,Format,"iVolatility_SPY_2015_1.csv");
  records += dataParse(1,Format,"iVolatility_SPY_2015_2.csv");
  records += dataParse(1,Format,"iVolatility_SPY_2016_1.csv");
  printf("\n%d records parsed",records);
  dataSave(1,"SPY_Options.dta");
}
 
// dataFromQuandl source code
var dataFromQuandl(int Handle,string Format,string Code,int Column)
{
  string Filename = strxc(Code,'/','-');
  if(dataFind(Handle,0) < 0) { // data array not yet loaded
    dataDownload(Code,FROM_QUANDL,12*60);
    dataParse(Handle,Format,Filename);
  }
  if(is(TRADEMODE) && !is(LOOKBACK)) {
    strcat(Filename,"1");
    int Rows = dataDownload(Code,FROM_QUANDL+1,60);
    if(Rows) dataParse(Handle,Format,Filename);	// add new record to the begin
    return dataVar(Handle,0,Column);
  } else {
    int Row = dataFind(Handle,wdate());
    return dataVar(Handle,Row,Column); 
  }
}
 
// US treasury 3-months interest rate
#include <contract.c>

var DTB3() { 
  return dataFromQuandl(801,"%Y-%m-%d,f","FRED/DTB3",1); 
}
 
// COT report for S&P500
var CFTC_SP(int Column) { 
  return dataFromQuandl(802,"%Y-%m-%d,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f","CFTC/TIFF_CME_SP_ALL",Column); 
}

// more format examples
string Format = "%Y-%m-%d,f3,f1,f2,f4,,,f6,f5"; // Quandl futures data to .t6, f.i. "CHRIS/CME_CL1"
string Format = "%Y-%m-%d,f3,f1,f2,f4,f6,f5"; // Yahoo data to unadjusted .t6, with adjusted close stored in fVal

See also:

file, strvar, price history, contract

► latest version online