1 files changed, 79 insertions, 0 deletions
diff --git a/python/insert_fx_id.py b/python/insert_fx_id.py
new file mode 100644
index 00000000..cc4b798c
--- /dev/null
+++ b/python/insert_fx_id.py
@@ -0,0 +1,79 @@
+import datetime
+import pandas as pd
+from serenitas.utils.env import DAILY_DIR
+from serenitas.analytics.dates import prev_business_day
+from collateral.baml_isda import load_excel
+
+FX_REPORT_COLUMNS = [
+    "cpty_id",
+    "trade_date",
+    "settle_date",
+    "buy_currency",
+    "buy_amount",
+    "sell_currency",
+    "sell_amount",
+]
+
+
+def get_tag(fund):
+    if fund == "Serenitas":
+        return "TSLP"
+    elif fund == "BowdSt":
+        return "TLLC"
+    elif fund == "Selene":
+        return "INC"
+    else:
+        return ""
+
+
+def read_BAMSNY(fund, cob):
+    REPORTS_DIR = DAILY_DIR / fund / "BoA_reports"
+    fname = next(
+        REPORTS_DIR.glob(f"301__LMCG_INVESTMEN{get_tag(fund)}_CSA_{cob:%m%d%Y}_*")
+    )
+    df = load_excel(fname)
+    df[df["ProductID"] == "FX_Fwd"]
+    df = df[
+        [
+            "Back Office Number",
+            "Trade Date",
+            "Maturity Date",
+            "Ccy1",
+            "Notional 1",
+            "Ccy2",
+            "Notional 2",
+        ]
+    ]
+    df.columns = FX_REPORT_COLUMNS
+    return df
+
+
+def read_MSCSNY(fund, cob):
+    df = pd.read_excel(
+        DAILY_DIR / fund / "MS_reports" / f"Trade_Detail_FX_{cob:%Y%m%d}.xls"
+    )
+    df = df[
+        [
+            "trade_id",
+            "trade_date",
+            "settlement_date",
+            "buy_ccy",
+            "amt_buy_ccy",
+            "sell_ccy",
+            "amt_sell_ccy",
+        ]
+    ]
+    df.columns = FX_REPORT_COLUMNS
+    return df
+
+
+def main(date):
+    for fund in ("Serenitas", "BowdSt", "Selene"):
+        for counterparty in ("MSCSNY", "BAMSNY"):
+            read_fun = globals()[f"read_{counterparty}"]
+            print(read_fun(fund, cob))
+
+
+if __name__ == "__main__":
+    cob = prev_business_day(datetime.date.today())
+    main(cob)