Dataset handling

The following functions are used to create or load a dataset:

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

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

dataCompress (int Handle, string Filename, int Fields, var Resolution): int

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

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

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

dataParseJSON (int Handle, string Format, string Filename): int

dataParseString (int Handle, string Format, string Content): int

Storing datasets

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

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

Manipulating datasets

dataMerge (int Handle1, int Handle2): int

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

dataAppendRow (int Handle, int Fields): void*

dataDelete (int Handle, int Record1, int Record2): int

dataClip (int Handle, int Records): int

dataSort (int Handle)

dataCompressSelf (int Handle, var MinTime)

Plotting datasets

dataChart (int Handle, string Filename, CONTOUR, NULL)

dataChart (int Handle, string Filename, HEATMAP, NULL)

Accessing data

dataFind (int Handle, var Date): int

dataRow (int Handle, void *Record): int

dataSize (int Handle, int *Rows, int *Columns): int

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

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

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

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

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

dataCol (int Handle, var* Data, int Column): int

dataCopy (int Handle, var* Data): int

Helper functions

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

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

Parameters:

Remarks:

• An easy way to generate the Format string is copying a line from the CSV file to the script as a format template, then editing its fields. Replace the year number with %Y, the month with %m, the day with %d, the hour with %H, the minute with %M, the second with %S. Replace prices with f, integer numbers or expiration dates with i, and text with s, ss, or sss,, dependent on the maximum string length. Delete the content of unused fields, but leave the delimiters. Add numbers to the placeholders when their fields in the target dataset are in a different order than in the CSV file (the header file trading.h contains the field numbers for often used structs). For instance, in a dataset in T6 format, the Open is the third price (f3), High is first (f1), Low is second (f2), Close is 4th (f4), and Volume is sixth (f6). Finally, append +, 0, or 2 to the begin of the resulting string dependent on date order and number of header lines. Now you have the correct Format string for parsing the file.
•  For checking the correct parsing, set Verbose to 7. The first two lines of the first CSV file are then printed to the log and message window, and their parsed versions below them. If your format string is so wrong that nothing is parsed at all, you'll get Error 058 instead. For checking the correct parsing further, the resulting dataset can be saved to a CSV file with dataSaveCSV.
• For parsing extremely large CSV files, set Verbose to 2 or above. Zorro will then print a dot for every 1,000,000 parsed lines. If the resulting dataset does not fit in 32-bit memory, use the 64-bit Zorro version and a .cpp file for parsing.
• If you cannot determine the correct format string for parsing a complex file, Zorro support can help. For a fistful of EUR you can also order a conversion script (check out the support page under "Services"). Many example scripts for data parsing and conversion can also be found under Import.
• Numbers in CSV files must not exceed the float range. Only straight decimal numbers can be imported. Hex, binary, octal, or scientific notation with appended exponent is not supported.
• If dataDownload failed due to a wrong code or for other reasons, the target file usually contains the error message from the service.
• The historical data files are simply datasets with 2, 3, 7, or 9 fields in descending timestamp order. They all can be loaded with dataLoad. The record format is defined in include\trading.h.
• Converting exotic CSV files to a specific dataset format, such as .t6 or .t8, requires often a 2-step process. In the first step, the CSV file is parsed into a temporary dataset. In the second step, the dataset is converted with a script that loops through all records and modifies fields to their final format. Examples of this can be found in the conversion scripts.
• When loading indicators from EOD data, be aware that the time stamp is usually from the begin of the day (00:00), while the data is from the end of the day (16:00). For avoiding future peeking, shift the dataFind time back by 16 hours, or use a 16*60 minutes offset with dataFromCSV.
• For loading data from Quandl (Zorro S required), register on www.quandl.com and enter your received Quandl API key in the Zorro.ini file.
• For speed reasons, full historical data arrays should be only loaded in the initial run of the system. While live trading, use FROM_QUANDL|1 or FROM_GOOGLE|1 for downloading the most recent data record only (see example).
• The size of a record in bytes is (Fields+1)*4. The size of the whole data area is therefore Records*(Fields+1)*4. Field 0 with the timestamp has a size of 8 bytes, the other fields have a size of 4 bytes. The number of records can be determined with dataFind(Handle,0).
• For storing complex data such as structs, use a sufficient record size plus 8 bytes for the timestamp, and copy the struct to dataStr(Handle,Record,1). If a separate timestamp is not needed, or if it is part of the struct as for the T1..T8 structs, the struct can be stored to dataStr(Handle,Record,0).
• For selecting a dataset not with its handle number, but with a string - for instance an asset name - use the stridx / strxid functions.
• For splitting extremely large CSV files into smaller parts, use free tools such as CSVSplitter for Windows.

Examples (see also import, scripts, and contract):

// 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); 
}

// convert some .csv formats to .t6
string Format = "%Y-%m-%d,f3,f1,f2,f4,,,f6,f5"; // Quandl futures format to .t6, f.i. "CHRIS/CME_CL1"
dataParse(1,Format,"History\\CL1.csv");
dataSave(1,"History\\CL1.t6");

string Format = "%Y-%m-%d,f3,f1,f2,f4,f6,f5"; // Yahoo data format to unadjusted .t6, with adjusted close stored in fVal
dataParse(1,Format,"History\\history.csv");
dataSave(1,"History\\AAPL.t6");

// read a time series out of field 1 of dataset H
vars MyData = series(dataVar(H,dataFind(H,wdate(0)),1));

// Coinbase Bitcoin/EUR price to .t1
void main()
{
  string Format = "+0,,,%t,f,f,s";
  int Records = dataParse(1,Format,"History\\BTCEUR.csv");
  printf("\n%d records read",Records);
// now convert it to t1 and change sign for sell quotes
  for(i=0; i<Records; i++) {
    T1* t1 = dataAppendRow(2,2);
    t1->time = dataVar(1,i,0);
    t1->fVal = dataVar(1,i,1);
    string sell = dataStr(1,i,3);
    if(sell[0] == 't') t1->fVal = -t1->fVal;
// display progress bar and check [Stop] button
    if(!progress(100*i/Records,0)) break;
  }
  if(Records) dataSave(2,"History\\BTCEUR.t1");
}

// evaluate an extra parameter stored with time stamps in a dataset
...
if(Init) dataLoad(1,"History\\MyParameters.dta",2);
int Row = dataFind(1,wdate(0));
MyExtraParameter = dataVar(1,Row,1);
...

// Download earnings data from AlphaVantage 
// and store earnings surprises in a dataset
int loadEarnings()
{
  string URL = strf("https://www.alphavantage.co/query?function=EARNINGS&symbol=%s&apikey=%s",
    Asset,report(33));  // 33 = AlphaVantage API key
  string Content = http_transfer(URL,0); // JSON format
  if(!Content) return 0; 
  string Pos = strstr(Content,"reportedDate");  
  if(!Pos) return 0;
  dataNew(1,0,0);
  while(Pos) {
    var Date = wdatef("%Y-%m-%d",Pos+12+4);
    if(Date == 0) break;
    Pos = strstr(Pos,"surprisePercentage");
    if(!Pos) break;
    int Row = dataRow(1,dataAppendRow(1,2));
    dataSet(1,Row,0,Date); // earnings date in field 0
    dataSet(1,Row,1,atof(Pos+18+4)); // surprise in field 1
    printf("\n%.4f %.4f",dataVar(1,Row,0),dataVar(1,Row,1));
    Pos = strstr(Pos,"reportedDate"); // next JSON record
  }
  return 1;
}

// plot a heatmap
void main()
{
  dataNew(1,8,10);
  int i,j;
  for(i=0; i<8; i++) 
    for(j=0; j<10; j++)
      dataSet(1,i,j,(var)(i+j));
  dataChart(1,"Test",HEATMAP,NULL);
}

// plot a contour map
void main()
{
  dataNew(1,100,3);
  int i,j;
  for(i=0; i<10; i++) 
    for(j=0; j<10; j++) {
      dataSet(1,i*10+j,2,(var)i);
      dataSet(1,i*10+j,1,(var)j);
      dataSet(1,i*10+j,0,(var)(i+j));
   }
   dataChart(1,"Test",CONTOUR|DOT,NULL);
}

// plot the 2nd field of a dataset as a red line
int Records = dataLoad(1,"MyDataset.dta");
int N;
for(N=0; N<Records; N++)
  plotBar("2nd_Field",N,N,dataVar(1,N,1),LINE,RED);

See also: