def init_signals(outputDir, statsDir, timerSecs):
quote_sz_df = None
hist_ohlc_df = None
quote_sz_file = "%s/%s.quotesz.csv" % (statsDir, today().strftime("%Y%m%d"))
if os.path.exists(quote_sz_file):
quote_sz_df = pd.read_csv(quote_sz_file, index_col=0)
else:
fatal("Missing Quote Size file %s" % (quote_sz_file))
hist_ohlc_file = "%s/%s.histohlc.csv" % (statsDir, today().strftime("%Y%m%d"))
if os.path.exists(hist_ohlc_file):
hist_ohlc_df = pd.read_csv(hist_ohlc_file, index_col=0)
else:
fatal("Missing Historical OHLC file %s" % (hist_ohlc_file))
output_file = "%s/%s.signals.csv" % (outputDir, today().strftime("%Y%m%d"))
finish_time = format_time()
info("Finish Time = %s" % (finish_time))
while datetime.now() <= finish_time:
try:
init_loop(output_file, hist_ohlc_df, quote_sz_df)
except:
error("Unexpected error: %s" % (str(sys.exc_info()[0])))
finally:
sleep(timerSecs)
def stockloan_payments(iDate, iPortfolio, dbConn, exchCode, inputDir, dryRun):
inputFile = "%s/SUB_%s_20953289.CSV" % (inputDir,
iDate.strftime("%Y-%m-%d"))
if os.path.exists(inputFile):
info("Reading file %s" % (inputFile))
stockloan_df = pd.read_csv(inputFile,
skiprows=1,
parse_dates=['From Date', 'To Date'])
stockloan_df = stockloan_df[stockloan_df['Account ID'] ==
__ACCOUNTID__] # filter by account
stockload_grp_df = stockloan_df.groupby('To Date').sum()
stockload_grp_df = pd.DataFrame(
stockload_grp_df['Amount Due in Accrual Currency'])
stockload_grp_df = stockload_grp_df.reset_index()
stockload_grp_df.columns = ['date', 'amount']
stockload_grp_df['type'] = 'Borrow Fee'
stockload_grp_df['indicator'] = 'Short'
stockload_grp_df['portfolio'] = iPortfolio
# prepare insert to portfolios/charges database
set_charges(stockload_grp_df, dbConn, dryrun=dryRun)
else:
warn("File %s not found." % (inputFile))
def main(argv):
argDBConn = ""
argExchCode = ""
argDryRun = True
try:
opts, args = getopt.getopt(argv, "hrd:c:",
["dryrun", "database=", "exchcode="])
except getopt.GetoptError:
print_usage()
sys.exit(2)
for opt, arg in opts:
if opt == '-h':
print_usage()
sys.exit()
elif opt in ("-d", "--database"):
argDBConn = arg
elif opt in ("-c", "--exchcode"):
argExchCode = arg
elif opt in ("-r", "--dryrun"):
argDryRun = False
if len(argDBConn) == 0 or len(argExchCode) == 0:
print_usage()
exit(0)
if len(business_days(today(), today(), argExchCode)) == 1:
info("Dry Run is turned %s" % ("ON" if argDryRun else "OFF"))
init_eod(argDBConn, argExchCode, argDryRun)
else:
fatal("Skipping...holiday")
def main(argv):
argExchCode = ""
argDays = 21
argOutput = "/home/sqtprod/bca/stats"
try:
opts, args = getopt.getopt(argv, "hc:d:o:",
["exchcode=", "days=", "output="])
except getopt.GetoptError:
print_usage()
sys.exit(2)
for opt, arg in opts:
if opt == '-h':
print_usage()
sys.exit()
elif opt in ("-c", "--exchcode"):
argExchCode = arg
elif opt in ("-d", "--days"):
argDays = int(arg)
elif opt in ("-o", "--output"):
argOutput = arg
if len(argExchCode) == 0 or argDays <= 0:
print_usage()
exit(0)
if len(business_days(today(), today(), argExchCode)) == 1:
info("Historical Days = %d" % (argDays))
info("Output Directory = %s" % (argOutput))
init_historical_ohlc(argOutput, argDays)
else:
fatal("Skipping...holiday")
def get_borrows_nomura_hk(date):
# check borrows
borrow_file = "/home/sqtdata/dfs/raw/live/nomu.day/%s/%s.hkd.csv" % (date.strftime('%Y%m%d'), date.strftime('%Y%m%d'))
info ("Reading borrow file " + borrow_file)
borrow_df = pd.read_csv(borrow_file)
borrow_df.columns = [x.strip() for x in borrow_df.columns]
borrow_df = borrow_df[['Ric','Quantity','Fee']]
borrow_df.columns = ['Ticker','BorrowQty','BorrowFee']
borrow_df = borrow_df.set_index('Ticker')
return (borrow_df)
def get_restricted_list(date):
restricted_symbology = lambda x: str(x).zfill(4)
restricted_pattern = "/home/sqtdata/dfs/raw/live/rest.day/%s/%s.*.restricted.csv" % (date.strftime('%Y%m%d'), date.strftime('%Y%m%d'))
restricted_files = sorted(glob.glob(restricted_pattern), reverse=True)
info("Reading Restricted List %s" % (restricted_files[0]))
restricted_df = pd.read_csv(restricted_files[0])
restricted_df['Ticker'] = restricted_df['code'].apply(restricted_symbology) + "." + restricted_df['exchange']
restricted_df['Restricted'] = True
restricted_df = restricted_df[['Ticker','Restricted']]
restricted_df = restricted_df.set_index('Ticker')
return (restricted_df)
def set_totals(dbconn, dryrun=True):
if not dryrun:
db = create_engine(dbconn, echo=False)
db.connect()
sql = "REPLACE INTO totals SELECT portfolio, CURRENT_DATE() as date, ticker, sum(comms) as totcomms, sum(divs) as totdivs, sum(netpnl) as totpnl FROM report WHERE date <= CURRENT_DATE() GROUP BY portfolio, ticker"
try:
info("Executing query [%s]" % (sql))
if not dryrun:
db.execute(sql)
except exc.IntegrityError as e:
error("DB Integrity Error: %s, sql=%s" % (e, sql))
except exc.SQLAlchemyError as e:
error("DB SQLAlchemy Error: %s, sql=%s" % (e, sql))
def main(argv):
argDBConn = ""
argExchCode = ""
argPortfolio = ""
argStart = ""
argEnd = ""
argDryRun = True
argInput = "/home/sqtdata/dfs/raw/live/bcar.day/reports"
try:
opts, args = getopt.getopt(argv, "hrc:d:s:e:p:i:", [
"dryrun", "database=", "exchcode=", "start=", "end=", "portfolio=",
"input="
])
except getopt.GetoptError:
print_usage()
sys.exit(2)
for opt, arg in opts:
if opt == '-h':
print_usage()
sys.exit()
elif opt in ("-d", "--database"):
argDBConn = arg
elif opt in ("-c", "--exchcode"):
argExchCode = arg
elif opt in ("-p", "--portfolio"):
argPortfolio = arg
elif opt in ("-s", "--start"):
argStart = datetime.strptime(arg, '%Y%m%d')
elif opt in ("-e", "--end"):
argEnd = datetime.strptime(arg, '%Y%m%d')
elif opt in ("-i", "--input"):
argInput = arg
elif opt in ("-r", "--dryrun"):
argDryRun = False
if len(argDBConn) == 0 or len(argExchCode) == 0 or len(
argPortfolio) == 0 or len(argInput) == 0:
print_usage()
exit(0)
if argStart > argEnd:
error("Start date must be less than End date")
print_usage()
exit(0)
dates = business_days(argStart, argEnd, argExchCode)
for date in dates:
info("Running payments for %s" % (date.strftime('%Y-%m-%d')))
stockloan_payments(date, argPortfolio, argDBConn, argExchCode,
argInput, argDryRun)
def interest_payments(iDate, iPortfolio, dbConn, exchCode, inputDir, dryRun):
inputFile = "%s/SUB_%s_20953280.CSV" % (inputDir, iDate.strftime("%Y-%m-%d"))
if os.path.exists(inputFile):
info("Reading file %s" % (inputFile))
interest_df = pd.read_csv(inputFile, skiprows=1, parse_dates=['From Date','To Date'])
interest_df = interest_df[interest_df['Account ID'] == __ACCOUNTID__] # filter by account
interest_df = interest_df[['Long/Short Indicator','Balance Type','To Date','Interest Amount']]
interest_df.columns = ['indicator','type','date','amount']
interest_df['type'] = interest_df['type'] + ' Interest'
interest_df['portfolio'] = iPortfolio
# prepare insert to portfolios/charges database
set_charges(interest_df, dbConn, dryrun=dryRun)
else:
warn("File %s not found." % (inputFile))
def set_orders(orders, dbconn, dryrun=True):
if not dryrun:
db = create_engine(dbconn, echo=False)
db.connect()
for index, row in orders.iterrows():
sql = "REPLACE INTO orders VALUES ('%s','%s','%s','%s',%d, CURRENT_TIMESTAMP())" % (row['title'], row['date'].strftime('%Y-%m-%d'), str(index), row['side'], row['ordqty'])
try:
info("Executing query [%s]" % (sql))
if not dryrun:
db.execute(sql)
except exc.IntegrityError as e:
error("DB Integrity Error: %s, sql=%s" % (e, sql))
except exc.SQLAlchemyError as e:
error("DB SQLAlchemy Error: %s, sql=%s" % (e, sql))
def set_reports(portfolios, dbconn, dryrun=True):
if not dryrun:
db = create_engine(dbconn, echo=False)
db.connect()
for index, row in portfolios.iterrows():
sql = "REPLACE INTO report VALUES ('%s','%s','%s',%d,%f,%d,%f,%d,%f,%d,%f,%f,%f,%f,%f)" % (row['portfolio'], row['date'], str(index), row['sodqty'], row['sodnot'], row['buyqty'], row['buynot'], row['sellqty'], row['sellnot'], row['eodqty'], row['eodnot'], row['comms'], row['divs'], row['grosspnl'], row['netpnl'])
try:
info("Executing query [%s]" % (sql))
if not dryrun:
db.execute(sql)
except exc.IntegrityError as e:
error("DB Integrity Error: %s, sql=%s" % (e, sql))
except exc.SQLAlchemyError as e:
error("DB SQLAlchemy Error: %s, sql=%s" % (e, sql))
def set_prices(portfolios, dbconn, dryrun=True):
if not dryrun:
db = create_engine(dbconn, echo=False)
db.connect()
for index, row in portfolios.iterrows():
sql = "REPLACE INTO prices VALUES ('%s','%s',%f,%f)" % (row['date'], str(index), row['prevpx'], row['lastpx'])
try:
info("Executing query [%s]" % (sql))
if not dryrun:
db.execute(sql)
except exc.IntegrityError as e:
error("DB Integrity Error: %s, sql=%s" % (e, sql))
except exc.SQLAlchemyError as e:
error("DB SQLAlchemy Error: %s, sql=%s" % (e, sql))
def set_trades(execs, dbconn, dryrun=True):
if not dryrun:
db = create_engine(dbconn, echo=False)
db.connect()
for index, row in execs.iterrows():
sql = "REPLACE INTO trades VALUES ('%s','%s','%s','%s',%d,%f,%f,%d)" % (row['portfolio'], row['date'], str(index), row['side'], row['execqty'], row['avgpx'], row['comms'], int(row['mult']))
try:
info("Executing query [%s]" % (sql))
if not dryrun:
db.execute(sql)
except exc.IntegrityError as e:
error("DB Integrity Error: %s, sql=%s" % (e, sql))
except exc.SQLAlchemyError as e:
error("DB SQLAlchemy Error: %s, sql=%s" % (e, sql))
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)
def set_corporate_actions(corporate_actions, dbconn, dryrun=True):
if not dryrun:
db = create_engine(dbconn, echo=False)
db.connect()
for index, row in corporate_actions.iterrows():
sql = "REPLACE INTO corpactions VALUES ('%s','%s','%s',%f,%f,%d,%d,%f)" % (row['portfolio'], row['date'], str(index), row['dividend'], row['split'], row['oldqty'], row['newqty'], row['cashadj'])
try:
info("Executing query [%s]" % (sql))
if not dryrun:
db.execute(sql)
except exc.IntegrityError as e:
error("DB Integrity Error: %s, sql=%s" % (e, sql))
except exc.SQLAlchemyError as e:
error("DB SQLAlchemy Error: %s, sql=%s" % (e, sql))
def init_sod(sDate, sPortfolio, dbConn, exchCode, dryRun):
days = business_days(sDate - timedelta(days=10), sDate - timedelta(days=1),
exchCode)
positions = get_portfolio(sPortfolio, days[-1], dbConn)
# positions = positions[positions['eodqty'] != 0] # remove empty positions
# set today's trade date
positions['date'] = sDate.strftime('%Y-%m-%d')
# carry over eod to sod
positions['sodqty'] = positions['eodqty']
positions['sodnot'] = positions['eodnot']
# zero out entries
positions[['buyqty', 'sellqty', 'eodqty']] = 0
positions[[
'buynot', 'sellnot', 'eodnot', 'grosspnl', 'netpnl', 'comms', 'divs'
]] = 0.
# prepare to submit entries
if positions.shape[0] > 0:
dvd = get_dividends(sDate, sDate)
dvd = dvd[['dividend', 'split']]
posdvd = positions.join(dvd, how='inner').copy(deep=True)
if posdvd.shape[0] > 0:
# adjustment required
posdvd['dividend'] = posdvd['dividend'].fillna(0.)
posdvd['split'] = posdvd['split'].fillna(1.)
posdvd['oldqty'] = posdvd['sodqty']
for index, row in posdvd.iterrows():
info("Adjusting div/split %s, div=%f, split=%f" %
(index, row['dividend'], row['split']))
positions.ix[index,
'divs'] = positions.ix[index,
'sodqty'] * row['dividend']
positions.ix[index,
'sodqty'] = positions.ix[index, 'sodqty'] * (
1. / row['split'])
# record adjustment for records
posdvd.ix[index,
'cashadj'] = positions.ix[index,
'sodqty'] * row['dividend']
posdvd.ix[index, 'newqty'] = positions.ix[index, 'sodqty'] * (
1. / row['split'])
# prepare insert to portfolios/corpactions database
set_corporate_actions(posdvd, dbConn, dryrun=dryRun)
else:
info("No adjustments found")
# prepare insert to portfolios/reports database
set_reports(positions, dbConn, dryrun=dryRun)
def get_eligible_shorts_hk(date):
hkex_symbology = lambda x: str(x).zfill(4) + '.HK'
elig_shorts = sorted(glob.glob("/dfs/raw/live/hkex/*/*.shorts.csv"), reverse=True)
elig_shorts_df = None
for elig_short in elig_shorts:
match = re.search("(20[0-2][0-9]{5})", elig_short)
if match and (datetime.strptime(match.group(), '%Y%m%d') <= date):
info("Reading Eligible Shorts %s" % (elig_short))
elig_shorts_df = pd.read_csv(elig_short, skiprows=4)
break
elig_shorts_df['Ticker'] = elig_shorts_df['No.'].apply(hkex_symbology)
elig_shorts_df['Eligible to Short'] = True
elig_shorts_df = elig_shorts_df[['Ticker', 'Eligible to Short']]
elig_shorts_df.columns = ['Ticker', 'EligibleToShort']
elig_shorts_df = elig_shorts_df.set_index('Ticker')
return (elig_shorts_df)
def extract_file(filename, outdir):
outfile = "%s/%s" % (outdir, os.path.basename(filename).replace(
".pgp", ""))
if not os.path.exists(outfile):
if not os.path.getsize(filename) == 0:
outcmd = "echo SQT001 | /usr/bin/gpg --passphrase-fd 0 --batch --yes --output %s %s" % (
outfile, filename)
os.system(outcmd)
if os.path.exists(outfile):
info("Extract successful for %s" % (outfile))
else:
warn("Extract unsuccessful for %s [%s]" % (outfile, outcmd))
else:
warn("File %s is size 0. Skipping..." % (filename))
else:
info("File %s already exists" % (outfile))
def query_mysql(dbConn, dbQuery, verbose=False):
dfSQL = None
try:
if verbose: info("Creating MariaDB Engine...")
db = create_engine(dbConn, echo=False)
conn = db.connect()
if verbose: info("Executing query [%s]" % (dbQuery))
dfSQL = pd.read_sql_query(dbQuery, conn)
conn.close()
except exc.SQLAlchemyError as e:
error("Database Error: %s, conn=%s" % (e, dbConn))
except:
error("Unknown Error: %s, conn=%s" % (sys.exc_info()[0], dbConn))
error(str(sys.exc_info()))
return (dfSQL)
def set_charges(payments, dbconn, dryrun=True):
if not dryrun:
db = create_engine(dbconn, echo=False)
db.connect()
for index, row in payments.iterrows():
sql = "REPLACE INTO charges VALUES ('%s','%s','%s','%s',%f)" % (
row['portfolio'], row['date'].strftime('%Y-%m-%d'), row['type'],
row['indicator'], row['amount'])
try:
info("Executing query [%s]" % (sql))
if not dryrun:
db.execute(sql)
except exc.IntegrityError as e:
error("DB Integrity Error: %s, sql=%s" % (e, sql))
except exc.SQLAlchemyError as e:
error("DB SQLAlchemy Error: %s, sql=%s" % (e, sql))
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_loop(outputFile, histOHLC, quoteSize):
# get portfolios
portfolios = get_portfolios(today(), 'mysql+mysqlconnector://sqtprod:[email protected]:3306/portfolios')
# get ohlc
today_ohlc = get_equity_ohlc(today(), local_hk_symbology, host="10.59.3.166", port=48883)
today_ohlc = today_ohlc.reset_index()
today_ohlc = today_ohlc[['sym','op','hi','lo','cl']]
today_ohlc.columns = ['Ticker','top','thi','tlo','tcl']
today_ohlc = today_ohlc.set_index('Ticker')
today_range = lambda x: 'SELL' if x['tcl']>=x['thi'] else ('BUY' if x['tcl']<=x['tlo'] else '')
signals = portfolios.join(today_ohlc, how='left').join(histOHLC, how='left').join(quoteSize, how='left')
signals['breakout'] = signals.apply(breakout, axis=1)
signals['range'] = signals.apply(today_range, axis=1)
signals = signals[['sodqty','eodqty','top','thi','tlo','tcl','hop','hhi','hlo','hcl','quotesize','breakout','range']]
info("Writing output to %s" % (outputFile))
signals.to_csv(outputFile)
def main(argv):
argDBConn = ""
argExchCode = ""
argFinish = "17:00:00"
argSleep = 1
argDryRun = True
try:
opts, args = getopt.getopt(
argv, "hrd:c:f:s:",
["dryrun", "database=", "exchcode=", "finish=", "sleep="])
except getopt.GetoptError:
print_usage()
sys.exit(2)
for opt, arg in opts:
if opt == '-h':
print_usage()
sys.exit()
elif opt in ("-d", "--database"):
argDBConn = arg
elif opt in ("-c", "--exchcode"):
argExchCode = arg
elif opt in ("-f", "--finish"):
argFinish = arg
elif opt in ("-s", "--sleep"):
argSleep = arg
elif opt in ("-r", "--dryrun"):
argDryRun = False
if len(argDBConn) == 0 or len(argExchCode) == 0:
print_usage()
exit(0)
finishTime = format_time(argFinish)
info("Dry Run is turned %s" % ("ON" if argDryRun else "OFF"))
while datetime.now() <= finishTime:
try:
init_intraday(argDBConn, argExchCode, argDryRun)
except:
error("Unexpected error: %s" % (str(sys.exc_info()[0])))
finally:
sleep(argSleep)
def main(argv):
argExchCode = ""
argStats = "/home/sqtprod/bca/stats"
argOutput = "/home/sqtprod/bca/signals"
argTimer = 5
try:
opts, args = getopt.getopt(argv,"hc:s:o:",["exchcode=","stats=","output=","timer="])
except getopt.GetoptError:
print_usage()
sys.exit(2)
for opt, arg in opts:
if opt == '-h':
print_usage()
sys.exit()
elif opt in ("-c", "--exchcode"):
argExchCode = arg
elif opt in ("-s", "--stats"):
argStats = arg
elif opt in ("-o", "--output"):
argOutput = arg
elif opt in ("-t", "--timer"):
argTimer = int(arg)
if len(argExchCode) == 0 or len(argStats) == 0 or len(argOutput) == 0:
print_usage()
exit(0)
if len(business_days(today(), today(), argExchCode)) == 1:
info("Stats Directory = %s" % (argStats))
info("Output Directory = %s" % (argOutput))
info("Timer = %d" % (argTimer))
init_signals(argOutput, argStats, argTimer)
else:
fatal("Skipping...holiday")
def init_intraday(dbConn, exchCode, dryRun):
func_buy_qty = lambda x: x['execqty'] if x['side'] in ['B', 'C'] else 0.
func_sell_qty = lambda x: x['execqty'] if x['side'] in ['S', 'H'] else 0.
reports_query = "select * from report where date = '%s'" % (
today().strftime('%Y-%m-%d'))
trades_query = "Select * from trades where date = '%s'" % (
today().strftime('%Y-%m-%d'))
reports = query_mysql(dbConn, reports_query, verbose=True)
info("%d report record(s) found" % (reports.shape[0]))
reports = reports.set_index(['portfolio', 'ticker'])
trades = query_mysql(dbConn, trades_query, verbose=True)
info("%d trades record(s) found" % (trades.shape[0]))
if trades.shape[0] > 0:
trades['tbuyqty'] = trades.apply(func_buy_qty, axis=1)
trades[
'tbuynot'] = trades['tbuyqty'] * trades['avgpx'] * trades['mult']
trades['tsellqty'] = trades.apply(func_sell_qty, axis=1)
trades[
'tsellnot'] = trades['tsellqty'] * trades['avgpx'] * trades['mult']
trades['tcomms'] = trades['comms']
trades_totals = trades.groupby(['portfolio',
'ticker'])['tbuyqty', 'tbuynot',
'tsellqty', 'tsellnot',
'tcomms'].sum()
else:
trades_totals = pd.DataFrame(columns=[
'portfolio', 'ticker', 'tbuyqty', 'tbuynot', 'tsellqty',
'tsellnot', 'tcomms'
])
trades_totals = trades_totals.set_index(['portfolio', 'ticker'])
portfolio = reports.join(trades_totals, how='outer')
# copy over trade notional
portfolio[['buyqty', 'buynot', 'sellqty', 'sellnot',
'comms']] = portfolio[[
'tbuyqty', 'tbuynot', 'tsellqty', 'tsellnot', 'tcomms'
]]
# fill zeros for missing data
portfolio = portfolio.fillna(0.)
portfolio = portfolio.reset_index().set_index('ticker')
# products
products = get_products(today(), local_hk_symbology)
products = products[['tmult']] # take only multiplier column
# price portfolio
prices = get_equity_prices_rt(today(), local_hk_symbology)
portfolio = portfolio.join(products, how='left').join(prices, how='left')
# compute eodqty
portfolio['eodqty'] = portfolio['sodqty'] + (portfolio['buyqty'] -
portfolio['sellqty'])
portfolio['eodnot'] = portfolio['eodqty'] * portfolio[
'lastpx'] * portfolio['tmult']
portfolio['grosspnl'] = (portfolio['eodnot'] -
(portfolio['buynot'] - portfolio['sellnot']) -
portfolio['sodnot']) + portfolio['divs']
portfolio['netpnl'] = portfolio['grosspnl'] - portfolio['comms']
portfolio['date'] = today().strftime('%Y-%m-%d')
# prepare insert to database
set_reports(portfolio, dbConn, dryrun=dryRun)
# submit prices
set_prices(portfolio, dbConn, dryrun=dryRun)
# run totals
set_totals(dbConn, dryrun=dryRun)
def main(argv):
argExchCode = ""
argMinPartRate = 0.01
argMaxPartRate = 0.05
argMaxLotSz = 5
argTimeInterval = 10
argCapital = 2e6
argOutput = "/home/sqtprod/bca/stats"
try:
opts, args = getopt.getopt(argv,"hc:n:x:l:t:m:o:",["exchcode=","minpartrate=","maxpartrate=","maxlotsize=","timeinterval=","capital=","output="])
except getopt.GetoptError:
print_usage()
sys.exit(2)
for opt, arg in opts:
if opt == '-h':
print_usage()
sys.exit()
elif opt in ("-c", "--exchcode"):
argExchCode = arg
elif opt in ("-n", "--minpartrate"):
argMinPartRate = float(arg)
elif opt in ("-x", "--maxpartrate"):
argMaxPartRate = float(arg)
elif opt in ("-l", "--maxlotsize"):
argMaxLotSz = int(arg)
elif opt in ("-t", "--timeinterval"):
argTimeInterval = int(arg)
elif opt in ("-m", "--capital"):
argCapital = float(arg)
elif opt in ("-o", "--output"):
argOutput = arg
if len(argExchCode) == 0:
print_usage()
exit(0)
if len(business_days(today(), today(), argExchCode)) == 1:
info("Minimum Participation Rate = %f" % (argMinPartRate))
info("Maximum Participation Rate = %f" % (argMaxPartRate))
info("Maximum Lot Size = %d" % (argMaxLotSz))
info("Time Interval Slices = %d" % (argTimeInterval))
info("Capital = %d" % (argCapital))
info("Output Directory = %s" % (argOutput))
init_quote_size(argOutput, argMinPartRate, argMaxPartRate, argMaxLotSz, argTimeInterval, argCapital)
else:
fatal("Skipping...holiday")
