1 files changed, 36 insertions, 19 deletions
diff --git a/python/collateral/cs.py b/python/collateral/cs.py
index 479ec4ec..f151a9c2 100644
--- a/python/collateral/cs.py
+++ b/python/collateral/cs.py
@@ -48,7 +48,6 @@ def get_collateral(d, fund):
         "Serenitas": "SerenitasCGMF",
         "BowdSt": "BostonBPStLLC",
     }
-
     for collat_type in ("RVM", "IM"):
         pdf_file = (
             DATA_DIR
@@ -66,27 +65,45 @@ def get_collateral(d, fund):
 
 def collateral(d, dawn_trades, *, fund="Serenitas", **kwargs):
     collateral = get_collateral(next_business_day(d), fund)
-    df = pd.read_excel(
-        DAILY_DIR / fund / "CS_reports" / f"DERV048829_{d:%b%d%Y}.xlsx",
-        header=9,
-        skipfooter=50,
-    )
-    df = df[["Order No", "Mid Price", "Notional Currency"]]
-    df["Mid Price"] = (
-        df["Mid Price"]
-        .str.replace(",", "")
-        .apply(lambda s: -float(s[1:-1]) if s.startswith("(") else float(s))
-    )
-    df["Order No"] = df["Order No"].astype("str")
-    df = df.merge(dawn_trades, how="left", left_on="Order No", right_on="cpty_id")
-    missing_ids = df.loc[df.cpty_id.isnull(), "Order No"]
+    DATA_DIR = DAILY_DIR / fund / "CS_reports"
+    if fund == "BowdSt":
+        df = pd.read_excel(
+            DATA_DIR / f"CollateralCptyStatement161BostonBPStLLCRVM_{d:%m%d%Y}.xls",
+            header=5,
+            skipfooter=29,
+        )
+        df.columns = [c.replace("\n", " ").strip() for c in df.columns]
+        df = df[1:]
+        df["Trade ID"] = df["Trade ID"].astype("int").astype("str")
+        df = df.rename(columns={"Notional1CCY": "Currency"})
+    elif fund == "Serenitas":
+        df = pd.read_excel(
+            DATA_DIR / f"DERV048829_{d:%b%d%Y}.xlsx", header=9, skipfooter=50,
+        )
+
+        df = df[["Order No", "Mid Price", "Notional Currency"]]
+        df["Mid Price"] = (
+            df["Mid Price"]
+            .str.replace(",", "")
+            .apply(lambda s: -float(s[1:-1]) if s.startswith("(") else float(s))
+        )
+        df["Order No"] = df["Order No"].astype("str")
+        df = df.rename(
+            columns={
+                "Mid Price": "PV (USD)",
+                "Notional Currency": "Currency",
+                "Order No": "Structure ID",
+            }
+        )
+    df = df.merge(dawn_trades, how="left", left_on="Structure ID", right_on="cpty_id")
+    missing_ids = df.loc[df.cpty_id.isnull(), "Structure ID"]
     if not missing_ids.empty:
         raise ValueError(f"{missing_ids.tolist()} not in the database")
     df.ia = df.ia.fillna(0.0)
-    df["Amount"] = df.ia + df["Mid Price"]
-    df = df[["folder", "Amount", "Notional Currency"]]
-    df = df.groupby(["folder", "Notional Currency"], as_index=False).sum()
-    df = df.rename(columns={"folder": "Strategy", "Notional Currency": "Currency"})
+    df["Amount"] = df.ia + df["PV (USD)"]
+    df = df[["folder", "Amount", "Currency"]]
+    df = df.groupby(["folder", "Currency"], as_index=False).sum()
+    df = df.rename(columns={"folder": "Strategy"})
     df.Amount *= -1
     df = df.append(
         {