1 files changed, 53 insertions, 0 deletions
diff --git a/python/collateral/ms.py b/python/collateral/ms.py
new file mode 100644
index 00000000..f71356d6
--- /dev/null
+++ b/python/collateral/ms.py
@@ -0,0 +1,53 @@
+import pandas as pd
+from . import ExchangeMessage, DAILY_DIR
+
+
+def download_files(count=20):
+    em = ExchangeMessage()
+    emails = em.get_msgs(
+        path=["NYops", "Margin calls MS"],
+        count=count,
+        subject__contains="SERCX **Daily",
+    )
+    DATA_DIR = DAILY_DIR / "MS_reports"
+    for msg in emails:
+        for attach in msg.attachments:
+            if "NETSwaps" in attach.name:
+                fname = "Trade_Detail_" + attach.name.split("_")[1]
+            elif "NET_Collateral" in attach.name:
+                fname = "Collateral_Detail_" + attach.name.rsplit("_", 1)[1]
+            else:
+                continue
+            p = DATA_DIR / fname
+            if not p.exists():
+                p.write_bytes(attach.content)
+
+
+def collateral(d, dawn_trades, *args):
+    df = pd.read_excel(DAILY_DIR / "MS_reports" / f"Collateral_Detail_{d:%Y%m%d}.xls")
+    collat = df.loc[1, "coll_val_ccy"].replace(",", "")
+    if "(" in collat:
+        collat = collat[1:-1]
+        collat = -float(collat)
+    else:
+        collat = float(collat)
+    df = pd.read_excel(DAILY_DIR / "MS_reports" / f"Trade_Detail_{d:%Y%m%d}.xls")
+    df = df.dropna(subset=["trade_ccy"])
+    df = df.merge(dawn_trades, how="left", left_on="trade_id", right_on="cpty_id")
+    missing_ids = df.loc[df.cpty_id.isnull(), "trade_id"]
+    if not missing_ids.empty:
+        raise ValueError(f"{missing_ids.tolist()} not in the database")
+    df = df.groupby("folder")[["collat_req_in_agr_ccy"]].sum()
+    df["Currency"] = "USD"
+    df = df.reset_index()
+    df.columns = ["Strategy", "Amount", "Currency"]
+    df = df.append(
+        {
+            "Strategy": "M_CSH_CASH",
+            "Amount": -collat - df.Amount.sum(),
+            "Currency": "USD",
+        },
+        ignore_index=True,
+    )
+    df["date"] = d
+    return df.set_index("Strategy")