diff options
| -rw-r--r-- | python/strat_cash_realloc.py | 130 |
1 files changed, 74 insertions, 56 deletions
diff --git a/python/strat_cash_realloc.py b/python/strat_cash_realloc.py index e3319810..ae347f99 100644 --- a/python/strat_cash_realloc.py +++ b/python/strat_cash_realloc.py @@ -5,6 +5,7 @@ from process_queue import get_filepath import datetime import csv from io import StringIO +from serenitas.analytics.dates import prev_business_day def rename_keys(d, mapping): @@ -24,6 +25,66 @@ def process_upload(trades): dest = get_filepath(DAILY_DIR, "wire", "BOWDST") dest.parent.mkdir(exist_ok=True) dest.write_bytes(buf) + sftp = SftpClient.from_creds("hm_globeop") + sftp.client.chdir("incoming") + sftp.put(buf, dest.name) + + +def generate_csv(conn, date): + headers = get_headers("wire", "BOWDST") + with conn.cursor() as c, conn.cursor() as d: + c.execute( + "SELECT vr.*, accounts2.custodian, accounts2.cp_code, pfm.folder AS dirty_strat FROM (SELECT sum(endqty) AS endqty, port, strat, custacctname, invid FROM valuation_reports v WHERE periodenddate ='2022-05-19' AND fund='BOWDST' AND invid IN ('USD', 'EUR') AND port NOT IN ('GFS_HELPER_BUSINESS_UNIT', 'CASH') GROUP BY (port, strat,custacctname, invid) HAVING sum(endqty) !=0) vr LEFT JOIN accounts2 ON custacctname=cash_account LEFT JOIN portfolio_folder_mapping pfm ON vr.strat::text=pfm.clean_folder where pfm.folder is not NULL;", + (date,), + ) + trades = [] + for row in c: + if row.strat not in CASH_STRATEGY_MAPPING.keys(): + d.execute( + "INSERT INTO strat_cash_realloc (portfolio, folder, trade_date, amount, currency, fund, cash_account) VALUES (%s, %s, %s, %s, %s, %s, %s) RETURNING dealid", + ( + row.port, + row.strat, + date, + row.endqty, + row.invid, + "BOWDST", + row.custacctname, + ), + ) + (dealid,) = d.fetchone() + obj = row._asdict() + rename_keys( + obj, + { + "invid": "Currency", + "custacctname": "Cash Account", + "custodian": "Custodian", + "cp_code": "Counterparty", + "dirty_strat": "Folder", + }, + ) + obj["Deal Type"] = "CashFlowDeal" + obj["Deal Id"] = dealid + obj["Action"] = "NEW" + obj["Client"] = "HEDGEMARK" + obj["Fund"] = "BOS_PAT_BOWDOIN" + obj["State"] = "Valid" + obj["Trade Date"] = date + obj["Settlement Date"] = date + obj["Transaction Type"] = "Transfer" + obj["Instrument Type"] = "Cashflow" + obj["Amount"] = -obj["endqty"] + offset = obj.copy() + # create second leg + offset["Deal Id"] = obj["Deal Id"] + "_O" + offset["Amount"] = -obj["Amount"] + offset["Folder"] = STRATEGY_CASH_MAPPING[obj["Folder"]] + trades.append([obj.get(h, None) for h in headers]) + trades.append([offset.get(h, None) for h in headers]) + conn.commit() + if trades: + process_upload(trades) CASH_STRATEGY_MAPPING = { @@ -63,61 +124,18 @@ CASH_STRATEGY_MAPPING = { STRATEGY_CASH_MAPPING = {e: k for k, v in CASH_STRATEGY_MAPPING.items() for e in v} -wire_headers = get_headers("wire", "BOWDST") +if __name__ == "__main__": + import argparse -dawndb = dbconn("dawndb") - -with dawndb.cursor() as c, dawndb.cursor() as d: - date = datetime.date(2022, 5, 19) - c.execute( - "SELECT vr.*, accounts2.custodian, accounts2.cp_code, pfm.folder AS dirty_strat FROM (SELECT sum(endqty) AS endqty, port, strat, custacctname, invid FROM valuation_reports v WHERE periodenddate ='2022-05-19' AND fund='BOWDST' AND invid IN ('USD', 'EUR') AND port NOT IN ('GFS_HELPER_BUSINESS_UNIT', 'CASH') GROUP BY (port, strat,custacctname, invid) HAVING sum(endqty) !=0) vr LEFT JOIN accounts2 ON custacctname=cash_account LEFT JOIN portfolio_folder_mapping pfm ON vr.strat::text=pfm.clean_folder where pfm.folder is not NULL;", - (date,), + parser = argparse.ArgumentParser() + parser.add_argument( + "workdate", + nargs="?", + type=datetime.date.fromisoformat, + default=prev_business_day(datetime.date.today()), + help="working date", ) - trades = [] - for row in c: - if row.strat not in CASH_STRATEGY_MAPPING.keys(): - d.execute( - "INSERT INTO strat_cash_realloc (portfolio, folder, trade_date, amount, currency, fund, cash_account) VALUES (%s, %s, %s, %s, %s, %s, %s) RETURNING dealid", - ( - row.port, - row.strat, - date, - row.endqty, - row.invid, - "BOWDST", - row.custacctname, - ), - ) - (dealid,) = d.fetchone() - obj = row._asdict() - rename_keys( - obj, - { - "invid": "Currency", - "custacctname": "Cash Account", - "custodian": "Custodian", - "cp_code": "Counterparty", - "dirty_strat": "Folder", - }, - ) - obj["Deal Type"] = "CashFlowDeal" - obj["Deal Id"] = dealid - obj["Action"] = "NEW" - obj["Client"] = "HEDGEMARK" - obj["Fund"] = "BOS_PAT_BOWDOIN" - obj["State"] = "Valid" - obj["Trade Date"] = date - obj["Settlement Date"] = date - obj["Transaction Type"] = "Transfer" - obj["Instrument Type"] = "Cashflow" - obj["Amount"] = -obj["endqty"] - offset = obj.copy() - # create second leg - offset["Deal Id"] = obj["Deal Id"] + "_O" - offset["Amount"] = -obj["Amount"] - offset["Folder"] = STRATEGY_CASH_MAPPING[obj["Folder"]] - trades.append([obj.get(h, None) for h in wire_headers]) - trades.append([offset.get(h, None) for h in wire_headers]) -dawndb.commit() -if trades: - process_upload(trades) + args = parser.parse_args() + + conn = dbconn("dawndb") + generate_csv(args.workdate, conn) |