Login Page - Create Account

Support Board


Date/Time: Fri, 29 Mar 2024 10:08:00 +0000



Post From: Python for Sierra Chart

[2021-06-18 18:10:52]
biconoid - Posts: 5
Trying to read Sierra Chart SCID file using Python and export as CSV/TSV

Using following approach:

With reference to s_IntradayRecord Structure Member Descriptions at
Intraday Data File Format

- The Intraday data record structure, s_IntradayRecord, is 40 bytes in size.
- The Intraday data file header, s_IntradayHeader, is 56 bytes in size.
- The DateTime member variable is a SCDateTimeMS variable. This is a 64-bit integer.
It represents the number of microseconds since the SCDateTime epoch of December 30, 1899.

s_IntradayRecord()// Constructor
struct s_IntradayRecord
{
static const float SINGLE_TRADE_WITH_BID_ASK;
static const float FIRST_SUB_TRADE_OF_UNBUNDLED_TRADE;
static const float LAST_SUB_TRADE_OF_UNBUNDLED_TRADE;

s_IntradayRecord()// Constructor PYTHON PART
Sierra Chart | { Type | https://docs.python.org/3/library/struct.html
----------------------- |---------------------------------- | -----------------------------------------------
SCDateTimeMS DateTime; | DateTime; d |
float Open; | Open = 0.0; f | Format C Python type Standard
float High; | High = 0.0; f | Type Type Size(bytes)
float Low; | Low = 0.0; f | ------------------------------------------
float Close; | Close = 0.0; f | f float float 4
u_int32 NumTrades; | NumTrades = 0; I | I unsigned int integer 4
u_int32 TotalVolume; | TotalVolume = 0; I | d double float 8
u_int32 BidVolume; | BidVolume = 0; I |
u_int32 AskVolume; | AskVolume = 0; I |
| }
};



Procedure:
Data unpacking as:
size_of_header = 0x36 (hex) # 56 bytes # Discarded
size_of_record = 0x28 (hex) # 40 bytes (<d 4f 4I) # Refer to above table
unpacked_data = struct.unpack('<d 4f 4I', data) # Further exported and saved to files like below

# Following TSV (CSV) file is obtainted after getting the data.scid (check attached)

$ cat data.tsv
Date Open High Low Close Volume NumberOfTrades BidVolume AskVolume
1.8752101406881474e-308 2623.0 0.0 2623.0 2623.0 1 1 1 0
1.8754475234208735e-308 2717.0 0.0 2717.0 2717.0 1 1 1 0
1.875547350372747e-308 2750.0 0.0 2750.0 2750.0 1 1 1 0
1.8757877222026304e-308 2742.5 2757.75 2742.5 2742.5 1 1 1 0
1.876050741013588e-308 2830.0 2830.0 2805.75 2830.0 5 0 0 5
1.876054622887367e-308 2845.0 2845.0 2814.75 2845.0 3 0 0 3
1.876054699961608e-308 2845.0 2845.0 2814.75 2845.0 2 0 0 2
1.8761342153807153e-308 2900.0 2900.0 2870.75 2900.0 3 0 0 3
1.876134249471245e-308 2900.0 2900.0 2870.75 2900.0 2 0 0 2
1.876686289274035e-308 2890.0 2918.25 2890.0 2890.0 1 1 1 0

# Looks similar in data_frame
$ python3
Python 3.8.5 (default, May 27 2021, 13:30:53)
[GCC 9.3.0] on linux
Type "help", "copyright", "credits" or "license" for more information.
>>> import pandas as pd
>>> pd.read_csv('data.tsv', sep='\t')
Date Open High Low Close Volume NumberOfTrades BidVolume AskVolume
0 1.875210e-308 2623.0 0.00 2623.00 2623.0 1 1 1 0
1 1.875448e-308 2717.0 0.00 2717.00 2717.0 1 1 1 0
2 1.875547e-308 2750.0 0.00 2750.00 2750.0 1 1 1 0
3 1.875788e-308 2742.5 2757.75 2742.50 2742.5 1 1 1 0
4 1.876051e-308 2830.0 2830.00 2805.75 2830.0 5 0 0 5
5 1.876055e-308 2845.0 2845.00 2814.75 2845.0 3 0 0 3
6 1.876055e-308 2845.0 2845.00 2814.75 2845.0 2 0 0 2
7 1.876134e-308 2900.0 2900.00 2870.75 2900.0 3 0 0 3
8 1.876134e-308 2900.0 2900.00 2870.75 2900.0 2 0 0 2
9 1.876686e-308 2890.0 2918.25 2890.00 2890.0 1 1 1 0
>>>
Note: Above dataframe output is almost same EXCEPT DATETIME as if i export SCID to a text file and import into a dataframe.

Manual conversion of SCID file to TXT via SierraChart
#ESM21-CME-Sample.txt
Date, Time, Open, High, Low, Last, Volume, NumberOfTrades, BidVolume, AskVolume
2020/4/8, 00:32:59, 2623.00, 0.00, 2623.00, 2623.00, 1, 1, 1, 0
2020/4/13, 14:00:47, 2717.00, 0.00, 2717.00, 2717.00, 1, 1, 1, 0
2020/4/15, 22:08:19, 2750.00, 0.00, 2750.00, 2750.00, 1, 1, 1, 0
2020/4/21, 13:16:57, 2742.50, 2757.75, 2742.50, 2742.50, 1, 1, 1, 0
2020/4/27, 17:09:33, 2830.00, 2830.00, 2805.75, 2830.00, 5, 1, 0, 5
2020/4/27, 19:20:30, 2845.00, 2845.00, 2814.75, 2845.00, 3, 1, 0, 3
2020/4/27, 19:23:06, 2845.00, 2845.00, 2814.75, 2845.00, 2, 1, 0, 2
2020/4/29, 16:05:27, 2900.00, 2900.00, 2870.75, 2900.00, 3, 1, 0, 3

----------------------------------------------------------------------------------------------------------------
Issue:
----------------------------------------------------------------------------------------------------------------
Unable to extract/convert the datetime part to a proper value. Anyone has any pointers. Tried via
- numpy
- datetime module

Sample Python code
-----------------

$ python3
Python 3.8.5 (default, May 27 2021, 13:30:53)
[GCC 9.3.0] on linux
Type "help", "copyright", "credits" or "license" for more information.
>>> from datetime import datetime
>>> datetime.fromtimestamp(1.8752101406881474e-308)
datetime.datetime(1969, 12, 31, 19, 0)
>>>
FAIL: That's an error


Sample Python code
-----------------
import numpy as np
from time import time

"""
From: Intraday Data File Format

The DateTime member variable is a SCDateTimeMS variable.
This is a 64-bit integer. It represents the number of microseconds since the SCDateTime epoch of December 30, 1899.
"""

# Single parameters
ep = np.datetime64('1899-12-30') # epoch date
tz = np.timedelta64(5, 'h')/np.timedelta64(1, 'D') # time-zone adjustment UTC +5
dv = 1.8752101406881474e-308 # d[0] value from struct.unpack('<d 4f 4I') refer to TSV file or DataFrame above
dt = dv + tz # adjusted time

# Try to get full date
ms = (np.timedelta64(int(round((dt-int(dt))*86400)),'ms')) # Delta in millisecond for current day
my_time = ep + np.timedelta64(int(dt)) + ms # Epoch Time + Delta + millisecond spent

print(my_time)

#Output
-------------------------
1899-12-30T00:00:18.000
FAIL: That's an error

# Attachment
# data.scid - is a subset of 'ESM21-CME.scid' 2.5Gb file
i've only extracted
456 bytes for simplifying data extraction
(56 + 400 bytes)
56 bytes = header
400 bytes = 40 x 10 intraday records
attachmentdata.scid - Attached On 2021-06-18 18:09:39 UTC - Size: 457 B - 362 views