diff options
Diffstat (limited to 'python/reallocate_cash.py')
| -rw-r--r-- | python/reallocate_cash.py | 30 |
1 files changed, 21 insertions, 9 deletions
diff --git a/python/reallocate_cash.py b/python/reallocate_cash.py index 5d76ef9b..f51a26cd 100644 --- a/python/reallocate_cash.py +++ b/python/reallocate_cash.py @@ -58,7 +58,6 @@ _brokers = { } insert_query = """INSERT INTO iam_tickets(trade_date, action, strategy, counterparty, maturity, start_money, currency, "offset") VALUES (%s, %s, %s, %s, %s, %s, %s, %s);""" -sql_query = "SELECT broker, amount, strategy FROM (SELECT *, rank() OVER(PARTITION BY broker,fund ORDER BY date desc) FROM strategy_im si WHERE fund = 'BOWDST' AND date<=%s ORDER BY date DESC) test WHERE RANK=1;" select_query = "UPDATE iam_tickets set uploaded=True where maturity is null and trade_date =%s and action='NEW' and not uploaded returning *" cancel_query = "UPDATE iam_tickets set maturity=%s where trade_date != %s and maturity is null and action='NEW' returning *" @@ -107,11 +106,16 @@ def new_iam_process(obj, action): with conn.cursor() as c: trade_date = (datetime.date.today() - bus_day.date()).date() - c.execute(sql_query, (trade_date,)) - data = [] - offsets = defaultdict(int) + strategy_allocation = ( + "SELECT broker, amount, strategy FROM (SELECT *, rank() " + "OVER(PARTITION BY broker,fund ORDER BY date desc) FROM strategy_im si " + "WHERE fund = 'BOWDST' AND date<=%s ORDER BY date DESC) test WHERE RANK=1;" + ) + c.execute(strategy_allocation, (trade_date,)) + new_allocations = [] + amount_by_broker = defaultdict(int) for row in c: - data.append( + new_allocations.append( ( trade_date, "NEW", @@ -123,15 +127,23 @@ with conn.cursor() as c: False, ) ) - offsets[_brokers[row.broker]] += row.amount - for broker, amount in offsets.items(): + amount_by_broker[_brokers[row.broker]] += row.amount + for broker, amount in amount_by_broker.items(): if broker == "GOLDNY": + # We don't need to offset the Goldman Sachs FCM because BONY doesn't book them continue - data.append((trade_date, "NEW", "CSH_CASH", broker, None, -amount, "USD", True)) + new_allocations.append( + (trade_date, "NEW", "CSH_CASH", broker, None, -amount, "USD", True) + ) csv_data = [] try: - c.executemany(insert_query, data) + insert_query = ( + """INSERT INTO iam_tickets(trade_date, action, strategy, counterparty, maturity, start_money, currency, "offset") """ + """VALUES (%s, %s, %s, %s, %s, %s, %s, %s);""" + ) + c.executemany(insert_query, new_allocations) except UniqueViolation: + # We already uploaded the IAM tickets today in that case, we need to update and cancel the old uploads conn.rollback() c.execute( "DELETE FROM iam_tickets where trade_date=%s returning *", (trade_date,) |