90 lines
3.8 KiB
Python
90 lines
3.8 KiB
Python
import sys
|
|
import argparse
|
|
import datetime
|
|
|
|
import psycopg2
|
|
from pytz import timezone
|
|
|
|
CUT_HOUR=16
|
|
#CUT_MINUTE=0
|
|
TZ_CUT = timezone('US/Eastern')
|
|
|
|
parser = argparse.ArgumentParser(description='Generate daily returns entries in DB')
|
|
#parser.add_argument('--sloppy', action='store_true')
|
|
args = parser.parse_args()
|
|
|
|
def get_db_connection():
|
|
return psycopg2.connect(host='127.0.0.1', database='crypto_paper', user='joseph')
|
|
|
|
dbconn = get_db_connection()
|
|
|
|
# get latest daily pnl creation date and trading date
|
|
cur = dbconn.cursor()
|
|
cur.execute('SELECT created_at, trading_date FROM pnl_daily ORDER BY id DESC LIMIT 1')
|
|
res = cur.fetchone()
|
|
checkAfter = None
|
|
lastTradingDate = None
|
|
|
|
if res is not None:
|
|
# there are rows
|
|
checkAfter = res[0]
|
|
lastTradingDate = res[1]
|
|
print('checkAfter: %s, lastTradingDate: %s' % (checkAfter, lastTradingDate))
|
|
#import sys
|
|
#sys.exit(0)
|
|
|
|
if checkAfter is None:
|
|
cur.execute('SELECT * FROM acctval ORDER BY created_at')
|
|
else:
|
|
cur.execute('SELECT * FROM acctval WHERE created_at > %s ORDER BY created_at', (checkAfter,))
|
|
print("checkAfter filtered query is: SELECT * FROM acctval WHERE created_at > '%s' ORDER BY created_at" % (checkAfter,))
|
|
valRows = cur.fetchall()
|
|
#print(str(valRows))
|
|
print('got %s rows' % len(valRows))
|
|
|
|
thisDate = None
|
|
dayStart = None
|
|
dayStartTime = None
|
|
dayLast = None
|
|
for row in valRows:
|
|
#print(str(row))
|
|
rowDate = TZ_CUT.fromutc(row[1])
|
|
#print('rowDate: %s' % rowDate)
|
|
if rowDate.date() != thisDate:
|
|
print('LIVE new date: %s' % rowDate.date())
|
|
if thisDate is not None and dayStart is not None and dayLast is not None:# and thisDate.weekday() < 5:
|
|
# we can write out to DB
|
|
pnl = dayLast - dayStart
|
|
pnl_pct = pnl / dayStart
|
|
#markTime = TZ_CUT.localize(datetime.datetime.combine(rowDate.date(), datetime.datetime.min.time().replace(hour=CUT_HOUR, minute=CUT_MINUTE)))
|
|
markTime = TZ_CUT.localize(datetime.datetime.combine(thisDate, datetime.datetime.min.time())).date()
|
|
if lastTradingDate is None or markTime > lastTradingDate:
|
|
print('can mark it for %s, dayLast = %s, dayStart = %s, pnl = %s, pnl_pct = %s, markTime = %s' % (thisDate, dayLast, dayStart, pnl, pnl_pct, markTime))
|
|
cur.execute('INSERT INTO pnl_daily (created_at, trading_date, starting_value, pnl, pnl_pct) VALUES (%s, %s, %s, %s, %s)', ('NOW()', markTime, dayStart, pnl, pnl_pct))
|
|
thisDate = rowDate.date()
|
|
#print('set thisDate to %s' % thisDate)
|
|
dayStart = row[2]
|
|
dayStartTime = row[1]
|
|
dayLast = None
|
|
else:
|
|
dayLast = row[2]
|
|
|
|
# try to close "yesterday" even if we don't have records for "today" yet
|
|
if len(valRows) > 0:
|
|
now = TZ_CUT.fromutc(datetime.datetime.now())
|
|
print('now %s' % now)
|
|
print('rowDate %s' % rowDate)
|
|
if rowDate.date() != now.date() or now.hour >= CUT_HOUR:
|
|
if thisDate is not None and dayStart is not None and dayLast is not None:# and thisDate.weekday() < 5:
|
|
# we can write out to DB
|
|
pnl = dayLast - dayStart
|
|
pnl_pct = pnl / dayStart
|
|
#markTime = TZ_CUT.localize(datetime.datetime.combine(rowDate.date(), datetime.datetime.min.time().replace(hour=CUT_HOUR, minute=CUT_MINUTE)))
|
|
markTime = TZ_CUT.localize(datetime.datetime.combine(thisDate, datetime.datetime.min.time())).date()
|
|
if lastTradingDate is None or markTime > lastTradingDate:
|
|
print('can mark it for %s, dayLast = %s, dayStart = %s, pnl = %s, pnl_pct = %s, markTime = %s' % (thisDate, dayLast, dayStart, pnl, pnl_pct, markTime))
|
|
cur.execute('INSERT INTO pnl_daily (created_at, trading_date, starting_value, pnl, pnl_pct) VALUES (%s, %s, %s, %s, %s)', ('NOW()', markTime, dayStart, pnl, pnl_pct))
|
|
|
|
dbconn.commit()
|
|
cur.close()
|
|
dbconn.close() |