1 files changed, 50 insertions, 20 deletions
diff --git a/python/external_deriv_marks.py b/python/external_deriv_marks.py
index 67d0d8f3..f2eda51f 100644
--- a/python/external_deriv_marks.py
+++ b/python/external_deriv_marks.py
@@ -166,26 +166,56 @@ def bnp_navs(date: datetime.date = None, fund: str = "Serenitas"):
 
 def cs_navs(date: datetime.date = None, fund: str = "Serenitas"):
     d = {}
-    glob_str = f"{date:%b%d%Y}" if date else "*"
-    for fname in (DAILY_DIR / fund / "CS_reports").glob(f"DERV048829_{glob_str}.xlsx"):
-        try:
-            df = pd.read_excel(fname, skiprows=9, skipfooter=50, thousands=",")
-        except ValueError:
-            continue
-        df["Mid Price"] = df["Mid Price"].apply(
-            lambda s: -float(s[1:-1].replace(",", ""))
-            if s.startswith("(") and s.endswith(")")
-            else float(s)
-        )
-        df["Order No"] = df["Order No"].astype("str")
-        df["Trade Date"] = pd.to_datetime(df["Trade Date"])
-        df = df.set_index("Order No")
-        df = df[["Trade Date", "Buy/Sell", "Notional", "Mid Price", "Mid Price"]]
-        df.columns = COLUMNS[:-1]
-        # TODO: fix this
-        df_ia = get_ia(date, fund)
-        df = df.join(df_ia)
-        d[datetime.datetime.strptime(fname.stem.split("_")[1], "%b%d%Y").date()] = df
+    DATA_DIR = DAILY_DIR / fund / "CS_reports"
+    date_fmt = "%b%d%Y" if fund == "Serenitas" else "%m%d%Y"
+    glob_str = f"{date:{date_fmt}}" if date else "*"
+    if fund == "Serenitas":
+        g = DATA_DIR.glob(f"DERV048829_{glob_str}.xlsx")
+    elif fund == "BowdSt":
+        g = DATA_DIR.glob(f"CollateralCptyStatement161BostonBPStLLCRVM_{glob_str}.xls")
+    else:
+        g = []
+    for fname in g:
+        if fund == "Serenitas":
+            try:
+                df = pd.read_excel(fname, skiprows=9, skipfooter=50, thousands=",")
+            except ValueError:
+                continue
+            df["Mid Price"] = df["Mid Price"].apply(
+                lambda s: -float(s[1:-1].replace(",", ""))
+                if s.startswith("(") and s.endswith(")")
+                else float(s)
+            )
+            df["Order No"] = df["Order No"].astype("str")
+            df["Trade Date"] = pd.to_datetime(df["Trade Date"])
+            df = df.set_index("Order No")
+            df = df[["Trade Date", "Buy/Sell", "Notional", "Mid Price", "Mid Price"]]
+            df.columns = COLUMNS[:-1]
+            # TODO: fix this
+            df_ia = get_ia(date, fund)
+            df = df.join(df_ia)
+
+        elif fund == "BowdSt":
+            print("pomme")
+            try:
+                df = pd.read_excel(fname, header=5, skipfooter=29)
+            except ValueError:
+                continue
+            df.columns = [c.replace("\n", " ").strip() for c in df.columns]
+            df = df[1:]
+            df = df.set_index("Structure ID")
+            df = df[
+                [
+                    "Trade Date",
+                    "Buy/Sell",
+                    "Notional1",
+                    "PV (USD)",
+                    "PV (USD)",
+                    "Initial Margin (USD)",
+                ]
+            ]
+            df.columns = COLUMNS
+        d[datetime.datetime.strptime(fname.stem.split("_")[1], date_fmt).date()] = df
     if d:
         df = pd.concat(d)
         # nav is from CS's point of view