1 files changed, 120 insertions, 0 deletions
diff --git a/python/strat_cash_realloc.py b/python/strat_cash_realloc.py
new file mode 100644
index 00000000..12f48ff4
--- /dev/null
+++ b/python/strat_cash_realloc.py
@@ -0,0 +1,120 @@
+from serenitas.utils.db import dbconn
+from serenitas.utils.env import DAILY_DIR
+from headers import get_headers
+from process_queue import get_filepath
+import datetime
+import csv
+from io import StringIO
+
+
+def rename_keys(d, mapping):
+    """rename keys in dictionary according to mapping dict inplace"""
+    for k, v in mapping.items():
+        if k in d:
+            d[v] = d.pop(k)
+
+
+def process_upload(trades):
+    buf = StringIO()
+    csvwriter = csv.writer(buf)
+    headers = get_headers("wire", "BOWDST")
+    csvwriter.writerow(headers)
+    csvwriter.writerows(trades)
+    buf = buf.getvalue().encode()
+    dest = get_filepath(DAILY_DIR, "wire", "BOWDST")
+    dest.parent.mkdir(exist_ok=True)
+    dest.write_bytes(buf)
+
+
+CASH_STRATEGY_MAPPING = {
+    "COCSH": ["IGREC", "IGPAYER", "HYPAYER", "HYREC", "HYOPTDEL", "IGOPTDEL"],
+    "IRDEVCSH": ["DV01", "STEEP", "FLAT"],
+    "TCSH": [
+        "IGMEZ",
+        "IGSNR",
+        "IGEQY",
+        "HYMEZ",
+        "HYEQY",
+        "HYSNR",
+        "BSPK",
+        "XOMEZ",
+        "XOEQY",
+        "IGINX",
+        "HYINX",
+        "XOINX",
+        "EUMEZ",
+        "EUINX",
+    ],
+    "MBSCDSCSH": [
+        "HEDGE_MBS",
+        "MBSCDS",
+        "CRT_LD",
+        "CRT_LD_JNR",
+        "CRT_SD",
+        "MTG_FP",
+        "MTG_LMG",
+    ],
+    "MACCDSCSH": ["HEDGE_MAC", "CASH_BASIS"],
+    "CVECSH": ["ITRXCURVE", "IGCURVE", "HYCURVE", "XCURVE"],
+    "CLOCDSCSH": ["HEDGE_CLO", "CLO_BB20"],
+}
+
+STRATEGY_CASH_MAPPING = {e: k for k, v in CASH_STRATEGY_MAPPING.items() for e in v}
+
+wire_headers = get_headers("wire", "BOWDST")
+
+dawndb = dbconn("dawndb")
+
+with dawndb.cursor() as c, dawndb.cursor() as d:
+    date = datetime.date(2022, 5, 18)
+    c.execute(
+        "SELECT endqty, port, strat, custacctname, invid, custodian, cp_code FROM valuation_reports vr LEFT JOIN accounts2 ON custacctname=cash_account WHERE periodenddate =%s AND vr.fund='BOWDST' AND invid IN ('USD','EUR') AND port NOT IN ('CASH', 'GFS_HELPER_BUSINESS_UNIT') and endqty >0;",
+        (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",
+                    "port": "Portfolio",
+                    "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["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()
+process_upload(trades)