Support Board

# Scid File Operations
## Imports
# Files / System
import sys
from pathlib import Path
## Data
import numpy as np
import pandas as pd
## Time / Date
import datetime as dt
from zoneinfo import ZoneInfo # needed for timezone conversion if required. Available Zones: print(zoneinfo.available_timezones()) / https://en.wikipedia.org/wiki/List_of_tz_database_time_zones

## function returns scidfile as a dataframe
def get_scid_df(scidfile, limitsize=sys.maxsize):
  f = Path(scidfile)
  assert f.exists(), "SCID file not found"
  # Check // set the size
  stat = f.stat()

  offset = 56 if stat.st_size < limitsize else stat.st_size - (
    (limitsize // 40) * 40)

  # rectype for DatFrame
  sciddtype = np.dtype([
    ('Time', '<u8'),
    ('Open', '<f4'),
    ('High', '<f4'),
    ('Low', '<f4'),
    ('Close', '<f4'),
    ('Trades','<i4'),
    ('Volume', '<i4'),
    ('BidVolume', '<i4'),
    ('AskVolume', '<i4'),
    ])

  df = pd.DataFrame(data=np.memmap(scidfile, dtype=sciddtype, offset=offset,mode="r"),copy=False)
  
  # Set the index columns and covert it to UTC time from Sierras Native Time
  df.set_index('Time', inplace=True)
  df.index = pd.to_datetime(df.index - 2209161600000000, unit='us').tz_localize(tz='utc')
  df.sort_index(inplace=True)
  return df

## function returns scidfile as a numpy array - quicker but less useful for some circumstances
def get_scid_np(scidfile, limitsize=sys.maxsize):
  # Check Path Exists
  f = Path(scidfile)
  assert f.exists(), f"SCID file not found: {scidfile}"
  # Check // set the size
  stat = f.stat()
  offset = 56 if stat.st_size < limitsize else stat.st_size - (
    (limitsize // 40) * 40)

  ### Setup the SCID dtype
  sciddtype = np.dtype([
    ('Time', '<u8'),
    ('Open', '<f4'),
    ('High', '<f4'),
    ('Low', '<f4'),
    ('Close', '<f4'),
    ('Trades','<i4'),
    ('Volume', '<i4'),
    ('BidVolume', '<i4'),
    ('AskVolume', '<i4'),
    ])

  scid = np.fromfile(scidfile, dtype=sciddtype, offset=offset)
  return scid


# takes a scid time and returns it as a dt.datetime
def time_from(sierratime):
  sierra_epoch = dt.datetime(1899, 12, 30, tzinfo=dt.timezone.utc)
  dtg = sierra_epoch + dt.timedelta(microseconds=int(sierratime))
  return dtg

# takes a dt.datetime object and returns it as a np.uint64 for working with scid files
def time_to(dtg):
  sierra_epoch = dt.datetime(1899, 12, 30, tzinfo=dt.timezone.utc)
  delta = dtg - sierra_epoch
  sierratime = np.uint64(delta.total_seconds()*1000000)
  return sierratime


## Example Usage
scidfile = 'c:/sierra/charts/data/ESH26-CME.scid'

## scicnp Opperations
scidnp = get_scid_np(scidfile)

# Get the last time from the SCID FIle
last_sierra_time = scidnp['Time'][-1]
# Convert that into a datetime object
last_dt_time = time_from(last_sierra_time)
# get the date of that object
last_date = last_dt_time.date()

# all three operations in one line:
last_date = time_from(scidnp['Time'][-1]).date()

# Now that we have a date to work on, we need to create datetime objects representing the start/end of the period we are looking
# I will use a full globex session as an example

# dt.datetime.combine takes a dt date object and a dt time object and converts it to a dattime object
# See Documentation here: https://docs.python.org/3/library/datetime.html
# as globex open is the prior day, we need to subtract 1 day from the last date, we do this using dt.imedelta(days=1) to get the last date
# as globex open is also in American Time we use a timezone native object set in US Time by specifying the timezone using tzinfo and ZoneInfo
# it is a good idea to use the correct timezone for the product you are looking at to make sure things are consistent
# e.g. if you are looking at say Bund I would use either a Berlin or London Time tzinfo=ZoneInfo('Europe/Berlin') / tzinfo=ZoneInfo('Europe/London') - London is still in Europe according to timezones.

globex_open = dt.datetime.combine(last_date-dt.timedelta(days=1),dt.time(18,0),tzinfo=ZoneInfo("America/New_York"))
globex_close = dt.datetime.combine(last_date,dt.time(17,0),tzinfo=ZoneInfo("America/New_York"))

# Now we get every entry in the numpy array where the Time is greater than Globex open and less than Globex Close
# calling the time_to function to convert the dattime objects to sierra times
window = scidnp[(scidnp['Time'] > time_to(globex_open)) & (scidnp['Time'] < time_to(globex_close))]



## Data Frame Operations
sciddf = get_scid_df(scidfile)

# As our function to get the dataframe converted the Sierra Time Object to a DateTime Index we can index quickly and efficently on the index
# The dataframe oindex in UTC time, however you can index in any timezone provided the start and end are in the same timezone
# to get the same window as on the np file we can slice the index as follows:
window = sciddf[globex_open:globex_close]


## Example of how to resample a dataframe inot a higher timeframe :
# the agg function is on the Close column instead of the Open/High/Low/Close columns due to the storage format used in scidfiles:
# Intraday Data File Format: Data Structures Used

df = sciddf.resample('1min', origin='start_day').agg({'Close': ['first','max', 'min', 'last'], 'Trades' : 'sum', 'Volume' : 'sum', 'BidVolume' : 'sum', 'AskVolume' : 'sum'}).dropna()
df.columns = ['Open', 'High', 'Low', 'Close', 'Trades','Volume','BidVolume','AskVolume']

# this is useful if you wnated to index of a specifc time as well - but that time may not have had trade at that exact milisecond.
# you could either iteratively set the window you want for every possible date... or you could just get every instance of that time in the dateframe:

# as an example:
cash_open_index = sciddf.loc[sciddf.index.tz_convert(ZoneInfo("America/New_York")).time == dt.time(9,30)].index # returns 9 instances and takes a while
cash_open_index = df.loc[df.index.tz_convert(ZoneInfo("America/New_York")).time == dt.time(9,30)].index # returns 23 instances very quickly

# we can then either call the dataframe with that index:
df.loc[cash_open_index]

# or we can iterate over that index as do more complex analysis:
for index in cash_open_index:
  time_end = dt.datetime.combine(index.date(),dt.time(9,35),tzinfo=ZoneInfo("America/New_York"))
  window = sciddf.loc[index:time_end]
  # do stuff here