1 files changed, 57 insertions, 0 deletions
diff --git a/python/collateral/bnp.py b/python/collateral/bnp.py
new file mode 100644
index 00000000..edd4eb85
--- /dev/null
+++ b/python/collateral/bnp.py
@@ -0,0 +1,57 @@
+import datetime
+import pandas as pd
+from . import DAILY_DIR
+
+
+def download_files(count: int = 20):
+    from exchange import ExchangeMessage
+
+    em = ExchangeMessage()
+    emails = em.get_msgs(
+        path=["NYops", "Margin Calls BNP"],
+        count=count,
+        sender="bnppnycollateralmgmt@us.bnpparibas.com",
+    )
+    DATA_DIR = DAILY_DIR / "BNP_reports"
+    for msg in emails:
+        for attach in msg.attachments:
+            p = DATA_DIR / attach.name
+            if not p.exists():
+                p.write_bytes(attach.content)
+
+
+def load_file(d: datetime.date, report_type: str):
+    fname = (
+        f"{report_type} - BNP PARIBAS - SERENITAS CREDIT GAMMA "
+        f"MASTER FUND, LP - COB {d:%Y%m%d}.XLS"
+    )
+    return pd.read_excel(DAILY_DIR / "BNP_reports" / fname, skiprows=7)
+
+
+def collateral(d: datetime.date, dawn_trades: pd.DataFrame, *args):
+    df = load_file(d, "Collateral Positions")
+    collateral = float(df["Mkt Val (Agmt Ccy)"])
+    df = load_file(d, "Exposure Statement")
+    df = df[["Trade Ref", "Exposure Amount (Agmt Ccy)", "Lock Up (Agmt Ccy)"]]
+    df["Trade Ref"] = df["Trade Ref"].str.replace("MBO-", "")
+    df = df.merge(dawn_trades, how="left", left_on="Trade Ref", right_on="cpty_id")
+    missing_ids = df.loc[df.cpty_id.isnull(), "Trade Ref"]
+    if not missing_ids.empty:
+        raise ValueError(f"{missing_ids.tolist()} not in the database")
+    df = df[["folder", "Exposure Amount (Agmt Ccy)", "Lock Up (Agmt Ccy)"]]
+    df = df.groupby("folder").sum()
+    df = df.sum(axis=1).to_frame(name="Amount")
+    df["Currency"] = "USD"
+    df = df.reset_index()
+    df.columns = ["Strategy", "Amount", "Currency"]
+    df.Amount *= -1
+    df = df.append(
+        {
+            "Strategy": "M_CSH_CASH",
+            "Amount": -collateral - df.Amount.sum(),
+            "Currency": "USD",
+        },
+        ignore_index=True,
+    )
+    df["date"] = d
+    return df.set_index("Strategy")