Files: Database File Format

Ensign uses a database design to store tick data received from the live feeds from the data vendors, and from charts that are refreshed. Individual ticks and their tick volumes are stored in Tick database files, and 1-minute bars are stored in the Minute database files. The format of these files is as follows.

Tick File Format

TDataTick = class(TObject)
bSource: array[0..47] of byte; {1 for each 30-min period}
r: array of TBarTick;
end;

TBarTick = record
d: dword;     {Seconds from 1970, 136 yr range, Eastern Time Zone}
    c: single;       {price}
    v: dword;     {volume}
end;

Each tick database file contains the ticks for one day for one symbol. The file begins with a header that is 48 bytes in length, where each byte represents data for a 30-minute period. If you are writing a tick database file, set each byte to a value of 150.

Each tick follows in time sequence using a 12 byte record made up of three fields: date, price, volume. Date is a double word that is the number of seconds from January 1st, 1970, Eastern Time zone. A data time variable can be recreated using code like this:

timestamp = EncodeDate(1970,1,1) + r.d / 86400; {r.d is the tick record date field}

The tick price is a 4 byte single precision floating point value. The tick volume is a 4 byte double word integer.

Minute File Format

TDataMin = class(TObject)
bSource: array[0..47] of byte; {1 for each 30-min period}
r: array of TBarMin;
end;

TBarMin = record
d:   dword;   {Seconds from 1970, 136 yr range, Eastern Time Zone}
    o:   single; {open}
    h:   single; {high}
    l:    single; {low}
    c:   single; {close}
    vU: dword; {up volume}
    vD: dword; {down volume}
    tU: word;   {up tick count}
    tD: word;   {down tick count}
end;

Each minute database file has up to 1440 bar records for one day for each symbol. The file begins with a header that is 48 bytes in length, and each byte is an indicatation that there is data in the 30-minute period. If you are writing a minute database file, set each byte to a value of 150.

The minute records follow in time sequence. There may be minute periods when data does not exist because the symbol did not trade during that minute time period.

The date field is in seconds from January 1st, 1970, same as described for the tick database date field. The next 4 fields are the prices of open, high, low and close for the 1-minute period. When the database files are being built from a live data feed, tick by tick, the tick volume is accumulated in the vU field as up volume on an up tick or on an equal tick if the last price change was an up tick. The tick volume is accumulated in the vD filed as down volume on a down tick or on an equal tick if the last price change was a down tick. A counter for the up ticks is summed in tU, and a counter for the down ticks is summed in tD.

If you are building 1-minute database files, you may not have the detail regarding how the total volume might be split between up volume and down volume, or know about tick counts. In such a case, put the total volume in the vU field and have the vD field be a zero. Set the tick count fields to a zero when they are not known. The volume fields are 4-byte double word integers. The tick counts are 2-byte word integers.

Example Delphi Code

function dReadDatabase(var dT: TDataTick; var dM: TDataMin; b: boolean; s: string): longint;
var i,k: longint;
begin
   Result:=0;
   if b then begin dT:=TDataTick.Create; if pos('.tick',s)=0 then s:=s+'.tick'; end
   else begin dM:=TDataMin.Create; SetLength(dM.r,1441); if pos('.min',s)=0 then s:=s+'.min'; end;
   if FileExists(s) then with TFileStream.Create(s,fmOpenRead) do try
      k:=Size;
      if k<48 then i:=0
      else if b then begin
           i:=(k-48) div SizeOf(TBarTick); SetLength(dT.r,i); {number of records}
           Read(dT.bSource[0],48); Read(dT.r[0],k-48);
      end
      else begin
           i:=(k-48) div SizeOf(TBarMin); if i>1441 then SetLength(dM.r,i);
          Read(dM.bSource[0],48); Read(dM.r[0],k-48);
      end;
   finally
      Free; Result:=i;
   end
end;

procedure dWriteDatabase(dT: TDataTick; dM: TDataMin; b: boolean; i: longint; s: string);
begin
    ForceDirectories(ExtractFilePath(s));
    if b then begin if pos('.tick',s)=0 then s:=s+'.tick'; end
    else if pos('.min',s)=0 then s:=s+'.min';
    with TFileStream.Create(s,fmCreate) do try
        if b then begin
            Write(dT.bSource[0],48); Write(dT.r[0],i*SizeOf(TBarTick)); dT.Free;
        end
        else begin
            Write(dM.bSource[0],48); Write(dM.r[0],i*SizeOf(TBarMin)); dM.Free;
        end;
    finally
        Free;
    end;
end;