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 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 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_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_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 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_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 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 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 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)