def test_7_next_expiry(self):
res1 = stxcal.next_expiry(self.dt1)
res2 = stxcal.next_expiry(self.dt1, 10)
res3 = stxcal.next_expiry(self.dt1, 50)
res4 = stxcal.next_expiry(self.dt1, 100)
self.assertTrue((res1 == '2016-12-16') and (res2 == '2017-01-20') and
(res3 == '2017-03-17') and (res4 == '2017-05-19'))
def eod_analysis(self, ana_date):
# special case when the date is an option expiry date:
# 1. wait until eoddata is downloaded.
# 2. calculate liquidity leaders
# 3. download options for all liquidity leaders
# 4. calculate option spread leaders
# 5. populate leaders table
# 6. MON vs. FRI
next_exp = stxcal.next_expiry(ana_date, 0)
if next_exp == ana_date:
res = stxdb.db_read_cmd('select max(date) from eods where '
'open_interest>=0')
last_eod_date = str(res[0][0])
while last_eod_date < ana_date:
print('Could not find eod data for {0:s}, sleeping one hour'.
format(ana_date))
time.sleep(3600)
res = stxdb.db_read_cmd('select max(date) from eods where '
'open_interest>=0')
last_eod_date = str(res[0][0])
self.get_liq_leaders(ana_date)
self.get_data(ana_date, get_eod=False, get_for_all=True)
self.get_opt_spread_leaders(ana_date)
else:
self.get_data(ana_date, get_for_all=False, get_eod=True)
setup_df = self.calc_setups(ana_date)
self.mail_analysis(setup_df)
def get_opt_spreads(self, crt_date, eod):
exp_date = stxcal.next_expiry(crt_date, min_days=(1 if eod else 0))
q = sql.Composed([
sql.SQL('select stk, opt_spread from leaders '
'where expiry='),
sql.Literal(exp_date)
])
cnx = stxdb.db_get_cnx()
with cnx.cursor() as crs:
crs.execute(q.as_string(cnx))
spread_dict = {x[0]: x[1] for x in crs}
return spread_dict
def eow_job(self):
print('247 end of week job')
print('247 end of week job')
ana_date = '2002-04-19'
crt_date = '2002-07-19'
max_dt_q = stxdb.db_read_cmd('select max(dt) from leaders')
if max_dt_q[0][0] is not None:
ana_date = str(max_dt_q[0][0])
crt_date = str(datetime.datetime.now().date())
self.end_date = stxcal.move_busdays(
str(datetime.datetime.now().date()), 1)
while ana_date <= crt_date:
self.get_liq_leaders(ana_date)
self.get_opt_spread_leaders(ana_date)
ana_date = stxcal.move_busdays(stxcal.next_expiry(ana_date), 0)
def get_liq_leaders(self, ana_date, min_act=80000, min_rcr=0.015):
stk_list = stxdb.db_read_cmd(
"select distinct stk from eods where "
"date='{0:s}' order by stk".format(ana_date))
all_stocks = [
s[0] for s in stk_list if re.match(r'^[A-Za-z]', str(s[0]))
]
print('Found {0:d} stocks for {1:s}'.format(len(all_stocks), ana_date))
next_exp = stxcal.next_expiry(ana_date)
next_exp_busday = stxcal.move_busdays(next_exp, 0)
num_stx = 0
num = 0
liq_leaders = []
for s in all_stocks:
num += 1
ts = self.ts_dct.get(s)
if ts is None:
ts = StxTS(s, self.start_date, self.end_date)
ts.set_day(str(ts.df.index[-1].date()))
ts.df['activity'] = ts.df['volume'] * ts.df['c']
ts.df['avg_act'] = ts.df['activity'].rolling(50).mean()
ts.df['rg'] = ts.df['hi'] - ts.df['lo']
ts.df['avg_rg'] = ts.df['rg'].rolling(50).mean()
ts.df['rg_c_ratio'] = ts.df['avg_rg'] / ts.df['c']
self.ts_dct[s] = ts
num_stx += 1
stk_act = [s]
if self.is_liq_leader(ts, ana_date, min_act, min_rcr, stk_act):
liq_leaders.append(stk_act)
if num % 1000 == 0 or num == len(all_stocks):
print('Processed {0:d} stocks, found {1:d} liquidity leaders'.
format(num, len(liq_leaders)))
print('Found {0:d} liquidity leaders for {1:s}'.format(
len(liq_leaders), ana_date))
print('Loaded {0:d} stocks for {1:s}'.format(num_stx, ana_date))
cnx = stxdb.db_get_cnx()
with cnx.cursor() as crs:
for ldr in liq_leaders:
crs.execute(
'insert into leaders(exp,stk,activity,opt_spread) values '
+ crs.mogrify('(%s,%s,%s,%s)',
[next_exp, ldr[0],
int(ldr[1]), -1000]) +
'on conflict do nothing')
def get_leaders(self, ldr_date, get_for_all=True):
ldr_expiry = stxcal.next_expiry(ldr_date)
cnx = stxdb.db_get_cnx()
if get_for_all:
q = sql.Composed([
sql.SQL('select stk from leaders where exp='),
sql.Literal(ldr_expiry)
])
else:
q = sql.Composed([
sql.SQL('select stk from leaders where exp='),
sql.Literal(ldr_expiry),
sql.SQL(' and opt_spread >= 0 and atm_price is not null '
'and atm_price<='),
sql.Literal(self.max_atm_price),
sql.SQL('and stk not in (select * from exclusions) '
'order by opt_spread asc limit '),
sql.Literal(self.num_stx)
])
with cnx.cursor() as crs:
crs.execute(q.as_string(cnx))
ldrs = [x[0] for x in crs]
return ldrs
def load_opts_daily(self, opt_fname):
# print('{0:s} -load_opts_daily'.format(stxcal.print_current_time()))
invalid_days = [
'2002-02-01', '2002-02-04', '2002-02-05', '2002-02-06',
'2002-02-07', '2002-05-30', '2002-05-31', '2002-06-14',
'2002-06-17', '2002-12-02', '2002-12-03', '2002-12-04',
'2002-12-05', '2002-12-06', '2002-12-09', '2002-12-10'
]
dt = '{0:s}-{1:s}-{2:s}'.format(opt_fname[-12:-8], opt_fname[-8:-6],
opt_fname[-6:-4])
if dt in invalid_days:
return 0, 0
# print('dt = {0:s}'.format(dt))
exp_0 = stxcal.next_expiry(dt, min_days=0)
# print('exp_0 = {0:s}'.format(exp_0))
exp_1 = stxcal.next_expiry(exp_0)
# print('exp_1 = {0:s}'.format(exp_1))
exps = [str(exp_0), str(exp_1)]
spot_dct, opt_dct, spots, opts = {}, {}, [], []
stx = {}
sep = ' '
# print('opt_fname = {0:s}'.format(opt_fname))
with open(opt_fname) as csvfile:
frdr = csv.reader(csvfile)
for row in frdr:
stk = row[0]
# print('dt = {0:s}, stk = {1:s}, exps = {2:s}'.
# format(dt, stk, str(exps)))
# print('row = {0:s}'.format(str(row)))
try:
spot = int(100 * float(row[1]))
cp = row[5][:1]
exp = str(datetime.strptime(row[6], '%m/%d/%Y').date())
strike = int(100 * float(row[8]))
bid = int(100 * float(row[10]))
ask = int(100 * float(row[11]))
volume = int(row[12])
# print('exp = {0:s}'.format(exp))
except:
# print(traceback.print_exc())
continue
# print('1')
if exp not in exps or ask == 0 or spot >= 2147483647 or \
strike >= 2147483647 or bid >= 2147483647 or \
ask >= 2147483647 or volume >= 2147483647:
continue
# print('2')
if stk not in spot_dct:
spot_dct[stk] = spot
spots.append([stk, dt, spot])
opt_key = ':'.join([exp, stk, cp, str(strike)])
if opt_key not in opt_dct:
opt_dct[opt_key] = [bid, ask, volume]
opts.append([exp, stk, cp, strike, dt, bid, ask, volume])
# print('len(spots) {0:d}, len(opts) = {1:d}'.format(
# len(spots), len(opts)))
spots_upload_file = '{0:s}/spots.txt'.format(self.upload_dir)
opts_upload_file = '{0:s}/opts.txt'.format(self.upload_dir)
with open(spots_upload_file, 'w') as spots_file:
for s in spots:
spots_file.write('{0:s}\t{1:s}\t{2:d}\n'.format(
s[0], s[1], s[2]))
with open(opts_upload_file, 'w') as opts_file:
for o in opts:
opts_file.write('{0:s}\t{1:s}\t{2:s}\t{3:d}\t{4:s}\t{5:d}\t'
'{6:d}\t{7:d}\t0\n'.format(
o[0], o[1], o[2], o[3], o[4], o[5], o[6],
o[7]))
if self.upload_spots:
stxdb.db_upload_file(spots_upload_file, self.spot_tbl, '\t')
if self.upload_options:
stxdb.db_upload_file(opts_upload_file, self.opt_tbl, '\t')
d_spots, d_opts = len(spots), len(opts)
print('{0:s}\t{1:s}\t{2:5d}\t{3:6d}'.format(
stxcal.print_current_time(), dt, d_spots, d_opts))
return d_spots, d_opts
def get_opt_spread_leaders(self, ldr_date):
next_exp = stxcal.next_expiry(ldr_date)
calc_exp = stxcal.next_expiry(ldr_date, 9)
crt_date = stxcal.current_busdate()
cnx = stxdb.db_get_cnx()
stx = self.get_leaders(ldr_date)
print('Calculating option spread for {0:d} stocks'.format(len(stx)))
num = 0
if ldr_date <= self.last_opt_date:
opt_tbl_name = 'options'
spot_tbl_name = 'opt_spots'
spot_column = 'spot'
opt_date_column = 'date'
else:
opt_tbl_name = 'opt_cache'
spot_tbl_name = 'eods'
spot_column = 'c'
opt_date_column = 'dt'
for stk in stx:
print('stk = {0:s}'.format(stk))
spot_q = sql.Composed([
sql.SQL('select {} from {} where stk=').format(
sql.Identifier(spot_column),
sql.Identifier(spot_tbl_name)),
sql.Literal(stk),
sql.SQL(' and date='),
sql.Literal(crt_date)
])
with cnx.cursor() as crs:
crs.execute(spot_q.as_string(cnx))
spot_res = crs.fetchone()
if spot_res is None:
continue
spot = float(spot_res[0])
tokens = stk.split('.')
und = '.'.join(tokens[:-1]) if tokens[-1].isdigit() else stk
opt_q = sql.Composed([
sql.SQL('select * from {} where expiry=').format(
sql.Identifier(opt_tbl_name)),
sql.Literal(calc_exp),
sql.SQL(' and und='),
sql.Literal(und),
sql.SQL(' and {}=').format(sql.Identifier(opt_date_column)),
sql.Literal(crt_date)
])
opt_df = pd.read_sql(opt_q.as_string(cnx), cnx)
if len(opt_df) < 6:
continue
opt_df['strike_spot'] = abs(opt_df['strike'] - spot)
opt_df['spread'] = 100 * (1 - opt_df['bid'] / opt_df['ask'])
opt_df.sort_values(by=['strike_spot'], inplace=True)
opt_df['avg_spread'] = opt_df['spread'].rolling(6).mean()
try:
avg_spread = int(opt_df.iloc[5].avg_spread * 100)
avg_atm_price = round(
(opt_df.iloc[0].ask + opt_df.iloc[1].ask) / 2, 2)
with cnx.cursor() as crs:
crs.execute(
'update leaders set opt_spread=%s, '
'atm_price=%s where stk=%s and exp=%s',
(avg_spread, avg_atm_price, stk, next_exp))
except:
print('Failed to calc avg_spread for {0:s}'.format(stk))
num += 1
if num % 100 == 0 or num == len(stx):
print('Calculated option spread for {0:d} stocks'.format(num))