def get_equity_prices_rt(date, symbology, host="10.59.3.166", port=48883):
qry = "select ic, prevpx:bpx, lastpx:bpx^tpx from (`ic xkey select ic, bpx from pr where date=%s) lj (`ic xkey select last[tpx] by ic from tr where date=%s)" % (date.strftime('%Y.%m.%d'), date.strftime('%Y.%m.%d'))
df = query_kdb(host, port, qry)
df['ic'] = df['ic'].apply(bytes.decode)
df['sym'] = df['ic'].apply(symbology)
df = df.set_index('sym')
return (df)
def get_equity_prices(date, symbology, host="10.59.2.162", port=58000):
qry = "0!select last tpx, last date by ic from tr where date = %s" % (date.strftime('%Y.%m.%d'))
df = query_kdb(host, port, qry)
df['ic'] = df['ic'].apply(bytes.decode)
df['sym'] = df['ic'].apply(symbology)
df = df.set_index('sym')
return (df)
def get_equity_snapshot(date, time, symbology, host="10.59.3.166", port=48883):
qry = "select ic, bpx^tpx from (`ic xkey select ic, bpx from pr where date=%s) lj (select last tpx by ic from (select last tpx, sum tv by ic, 30 xbar recordtm.minute from (select recordtm + 08:00:00.000, ic, tpx, tv from tr where date=%s)) where minute <= %s)" % (date.strftime('%Y.%m.%d'), date.strftime('%Y.%m.%d'), time)
#qry = "0!select last tpx by ic from (select last tpx, sum tv by ic, 30 xbar recordtm.minute from (select recordtm + 08:00:00.000, ic, tpx, tv from tr where date=%s)) where minute <= %s" % (date.strftime('%Y.%m.%d'), time)
df = query_kdb(host, port, qry)
df['ic'] = df['ic'].apply(bytes.decode)
df['sym'] = df['ic'].apply(symbology)
df = df.set_index('sym')[['tpx']]
return (df)
def get_equity_ohlc(date, symbology, host="10.59.2.162", port=58000):
#qry = "0!select op:first tpx, hi:max tpx, lo:min tpx, cl:last tpx by date,ic from tr where date = %s" % (date.strftime('%Y.%m.%d'))
qry = "select date, ic, bpx^op, bpx^hi, bpx^lo, bpx^cl from (`date`ic xkey select from pr where date = %s) lj (select op:first tpx, hi:max tpx, lo:min tpx, cl:last tpx by date,ic from tr where date = %s)" % (date.strftime('%Y.%m.%d'), date.strftime('%Y.%m.%d'))
df = query_kdb(host, port, qry)
df['ic'] = df['ic'].apply(bytes.decode)
df['sym'] = df['ic'].apply(symbology)
df = df.set_index('sym')
return (df)
def get_products(date, symbology, host="10.59.3.166", port=48883):
qry = "select from pr where date = %s" % (date.strftime('%Y.%m.%d'))
df = query_kdb(host, port, qry)
df['ic'] = df['ic'].apply(bytes.decode)
df['ty'] = df['ty'].apply(bytes.decode)
df['sym'] = df['ic'].apply(symbology)
df = df.set_index('sym')
return (df)
def get_lotsize(date, symbology, host="10.59.3.166", port=48883):
qry = "select from pr where date = %s" % (date.strftime('%Y.%m.%d'))
df = query_kdb(host, port, qry)
df = df[df['ty'] == b'5'] # filter for equities
df['ic'] = df['ic'].apply(bytes.decode)
df['sym'] = df['ic'].apply(symbology)
df = df.set_index('sym')
df = df[['tunit']]
return (df)
def init_quote_size(outputDir, minPartRate, maxPartRate, maxLotSz, timeInterval, capital):
round_lot = lambda x: math.floor(x['capital'] / (x['bpx'] / x['lotsz'])) * x['lotsz']
twap_sz = lambda x: max(x['lotsz'], round((x['qty']/timeInterval)/x['lotsz']) * x['lotsz'])
avg_quote_sz = lambda x: math.ceil(((x['bsz']+x['asz'])*.5)/x['lotsz']) * x['lotsz'] if (not np.isnan(x['bsz']) and not np.isnan(x['asz'])) else (x['bsz'] if np.isnan(x['asz']) else np.isnan(x['bsz']))
min_part_sz = lambda x: max(x['lotsz'], round((minPartRate*x['quote_sz'])/x['lotsz'])*x['lotsz'])
max_part_sz = lambda x: max(x['lotsz'], round((maxPartRate*x['quote_sz'])/x['lotsz'])*x['lotsz'])
max_lot_sz = lambda x: x['lotsz'] * maxLotSz
show_sz = lambda x: min(x['tv'], x['twap_sz'], x['max_part_sz'], x['max_lot_sz'])
show_missing_sz = lambda x: x['lotsz'] if np.isnan(x['quotesize']) or x['quotesize'] < x['lotsz'] else x['quotesize']
products = get_products(today(), local_hk_symbology, host="10.59.3.166", port=48883)
products = products.reset_index()
products = products[products['ty'] == '5']
products['Ticker'] = products['sym']
products = products[['Ticker','tunit','bpx']]
products.columns = ['ticker','lotsz','bpx']
products = products[products['bpx'] > 0.]
products = products.set_index('ticker')
# get average quote size (past 10 days, every 5 minutes)
quote_size = query_kdb("10.59.2.162", 58001, "select.avgsize[%s;10;5]" % (today().strftime("%Y.%m.%d")))
quote_size['ic'] = quote_size['ic'].apply(bytes.decode).apply(local_hk_symbology)
quote_size = quote_size.set_index('ic')
trade_size = query_kdb("10.59.2.162", 58001, "select.avgtrd[%s;10]" % (today().strftime("%Y.%m.%d")))
trade_size['ic'] = trade_size['ic'].apply(bytes.decode).apply(local_hk_symbology)
trade_size = trade_size.set_index('ic')
sizing_list = products.join(quote_size, how='left').join(trade_size, how='left')
sizing_list['capital'] = capital
sizing_list['qty'] = sizing_list.apply(round_lot, axis=1)
sizing_list['quote_sz'] = sizing_list.apply(avg_quote_sz, axis=1)
sizing_list['twap_sz'] = sizing_list.apply(twap_sz, axis=1).astype(int).fillna(0)
sizing_list['min_part_sz'] = sizing_list.apply(min_part_sz, axis=1).astype(int).fillna(0)
sizing_list['max_part_sz'] = sizing_list.apply(max_part_sz, axis=1).astype(int).fillna(0)
sizing_list['max_lot_sz'] = sizing_list.apply(max_lot_sz, axis=1).astype(int).fillna(0)
sizing_list['quotesize'] = sizing_list.apply(show_sz, axis=1)
sizing_list['quotesize'] = sizing_list.apply(show_missing_sz, axis=1).astype(int)
sizing_list = sizing_list[['quotesize']]
output_file = "%s/%s.quotesz.csv" % (outputDir, today().strftime("%Y%m%d"))
info("Writing output to %s" % (output_file))
sizing_list.to_csv(output_file)
def init_historical_ohlc(outputDir, days):
# get x days OHLC
query_ohlc = "select.histohlc[%s;%d]" % (today().strftime("%Y.%m.%d"),
days)
hist_ohlc = query_kdb("10.59.2.162", 58001, query_ohlc)
hist_ohlc['ic'] = hist_ohlc['ic'].apply(bytes.decode)
hist_ohlc['Ticker'] = hist_ohlc['ic'].apply(local_hk_symbology)
hist_ohlc = hist_ohlc.set_index('Ticker')
hist_ohlc = hist_ohlc[['op', 'hi', 'lo', 'cl']]
hist_ohlc.columns = ['hop', 'hhi', 'hlo', 'hcl']
hist_ohlc = hist_ohlc.sort_index()
output_file = "%s/%s.histohlc.csv" % (outputDir,
today().strftime("%Y%m%d"))
info("Writing output to %s" % (output_file))
hist_ohlc.to_csv(output_file)