Support Board

def deserialize(excelDateAndTime):
"""
Returns excel date time as Python datetime object
"""
date_tok = int(excelDateAndTime)
time_tok = round(86400*(excelDateAndTime - date_tok))

# account for rounding error for excelDateAndTime like 44899.99999549769
if time_tok == 86400:
time_tok = 86399

d = date(1899, 12, 30) + timedelta(date_tok)
t = time(*helper(time_tok, (24, 60, 60, 1000000)))
return datetime.combine(d, t)

ftsv = open(fileoutput, 'w', newline='')

import sys
from pathlib import Path
import numpy as np
import pandas as pd
# bar data columns
BCOLS = [
'Time', 'Open', 'High', 'Low', 'Close', 'Volume', 'Trades', 'BidVolume',
'AskVolume'
]
def get_scid_df(filename, limitsize=sys.maxsize):
f = Path(filename)
assert f.exists(), "file not found"
stat = f.stat()
offset = 56 if stat.st_size < limitsize else stat.st_size - (
(limitsize // 40) * 40)
rectype = np.dtype([
(BCOLS[0], '<u8'), (BCOLS[1], '<f4'), (BCOLS[2], '<f4'),
(BCOLS[3], '<f4'), (BCOLS[4], '<f4'), (BCOLS[6], '<i4'),
(BCOLS[5], '<i4'), (BCOLS[7], '<i4'), (BCOLS[8], '<i4')
])
df = pd.DataFrame(data=np.memmap(f, dtype=rectype, offset=offset,
mode="r"),
copy=False)
df.dropna(inplace=True)
df["Time"] = df["Time"] - 2209161600000000
df.drop(df[(df.Time < 1) | (df.Time > 1705466561000000)].index, inplace=True)
df.set_index("Time", inplace=True)
df.index = pd.to_datetime(df.index, unit='us')
df.index = df.index.tz_localize(tz="utc")
return df

df = get_scid_df('c:\SierraChart\Data\AAPL.scid')
print(df)

get_scid_df('c:\SierraChart\Data\AAPL.scid', 1024*1024)

df_1min = (
df.resample("1Min")
.agg(
{
"Open": "first",
"High": "max",
"Low": "min",
"Close": "last",
"Volume": "sum",
"Trades": "sum",
"BidVolume": "sum",
"AskVolume": "sum",
}
)
.ffill()
)
print(df_1min)

def __init__(self) -> None:

self.bcols = ['Time', 'Open', 'High', 'Low', 'Close', 'Volume', 'Trades', 'BidVolume', 'AskVolume']

def get_scid_df(self, filename, limitsize=sys.maxsize):

f = Path(filename)
assert f.exists(), "file not found"
stat = f.stat()
offset = 56 if stat.st_size < limitsize else stat.st_size - (
(limitsize // 40) * 40)
rectype = np.dtype([
(self.bcols[0], '<u8'), (self.bcols[1], '<f4'), (self.bcols[2], '<f4'),
(self.bcols[3], '<f4'), (self.bcols[4], '<f4'), (self.bcols[6], '<i4'),
(self.bcols[5], '<i4'), (self.bcols[7], '<i4'), (self.bcols[8], '<i4')
])
df = pd.DataFrame(data=np.memmap(f, dtype=rectype, offset=offset, mode="r"),copy=False)
df.dropna(inplace=True)
df["Time"] = df["Time"] - 2209161600000000
df.drop(df[(df.Time < 1) | (df.Time > 1705466561000000)].index, inplace=True)
df.set_index("Time", inplace=True)
df.index = pd.to_datetime(df.index, unit='us')
# df.index = df.index.tz_localize(tz="est")

return df

def resampling(self, df):
df_5min = (
df.resample("5Min").agg(
{
"Open": "first",
"High": "max",
"Low": "min",
"Close": "last",
"Volume": "sum",
"Trades": "sum",
"BidVolume": "sum",
"AskVolume": "sum",
}

).ffill()

)

df = df_5min[["Open", "High", "Low", "Close"]]
df.index.rename('Open time', inplace=True)

eastern = pytz.timezone('US/Eastern')
df.index = df.index.tz_localize(pytz.utc).tz_convert(eastern)
df.index = df.index.tz_localize(None)
df = df / 100 #Needed for CL prices

return df

# test.py

df = get_scid_df(scid_file_path, 1024 * 1024)
print(df[["Close"]][-5:])
df_1min = (
df.resample("1Min")
.agg(
{
"Open": "first",
"High": "max",
"Low": "min",
"Close": "last",
"Volume": "sum",
"Trades": "sum",
"BidVolume": "sum",
"AskVolume": "sum",
}
)
.ffill()
)
print(df_1min[["Close"]][-5:])

def get_scid_df(symbol, limitsize=sys.maxsize):
f = Path(rf'c:\SierraChart\Data\{symbol}')
assert f.exists(), "file not found"
stat = f.stat()
offset = 56 if stat.st_size < limitsize else stat.st_size - ((limitsize // 40) * 40)

BCOLS = ['datetime', 'open', 'high', 'low', 'close', 'volume', 'trades', 'bidVolume', 'askVolume']
rectype = np.dtype([
(BCOLS[0], '<u8'), (BCOLS[1], '<f4'), (BCOLS[2], '<f4'),
(BCOLS[3], '<f4'), (BCOLS[4], '<f4'), (BCOLS[6], '<i4'),
(BCOLS[5], '<i4'), (BCOLS[7], '<i4'), (BCOLS[8], '<i4')
])

df = pd.DataFrame(data=np.memmap(f, dtype=rectype, offset=offset, mode="r"), copy=False)

df = df.dropna()
years_microseconds = 70 * 365.25 * 24 * 60 * 60 * 10**6 # accounts for leap years supposedly
days_microseconds = 2 * 24 * 60 * 60 * 10**6

total_microseconds = years_microseconds + days_microseconds # 70 years, 2 days
df.datetime = df.datetime - total_microseconds

# Add 6 hours to get to local time
df.datetime = pd.to_datetime(df.datetime, unit='us') + pd.Timedelta(hours=6)
df.set_index('datetime', inplace=True)

# Prices need to be adjusted
if 'ES' in symbol:
div = 100
if any(item in symbol for item in ['6E', '6B']):
div = 10000
for c in ['high', 'low', 'close']:
df[c] = df[c] / div

df.open = df.close

return df

def get_scid_np(scidFile, limitsize=sys.maxsize):
  # Check Path Exists
  f = Path(scidFile)
  assert f.exists(), "SCID file not found"
  # set the offset from limitsize
  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

def write_scid(scidarray,outfile,mode = 'w'):
  ## Write header file in write mode
  if mode == 'w':
    with open(outfile, 'wb') as fscid:
      header = b'SCID8\x00\x00\x00(\x00\x00\x00\x01\x00'
      fscid.write(header)
      for i in range(21):
        fscid.write(b'\x00\x00')
      
      fscid.write(scidarray.tobytes())
      fscid.close()

  # Append the scid array else
  else:
    with open(outfile, 'ab') as fscid:
        fscid.write(scidarray.tobytes())
        fscid.close()
    
  return

BCOLS = ['datetime', 'open', 'high', 'low', 'close', 'volume', 'trades', 'bidVolume', 'askVolume']

rectype = np.dtype([

(BCOLS[0], '<u8'), (BCOLS[1], '<f4'), (BCOLS[2], '<f4'),

(BCOLS[3], '<f4'), (BCOLS[4], '<f4'), (BCOLS[6], '<i4'),

(BCOLS[5], '<i4'), (BCOLS[7], '<i4'), (BCOLS[8], '<i4')

])

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