1 files changed, 74 insertions, 1 deletions
diff --git a/python/collateral/cs.py b/python/collateral/cs.py
index bfee4d17..ae25d0db 100644
--- a/python/collateral/cs.py
+++ b/python/collateral/cs.py
@@ -1,15 +1,19 @@
+import datetime
 import pandas as pd
 from . import DAILY_DIR
+from operator import itemgetter
+from pandas.tseries.offsets import BDay
+from xlrd import open_workbook
 
 
 def download_files(count=20):
     from exchange import ExchangeMessage
 
+    DATA_DIR = DAILY_DIR / "CS_reports"
     em = ExchangeMessage()
     emails = em.get_msgs(
         path=["NYops", "Margin Calls CS"], count=count, subject__contains="DERV048829"
     )
-    DATA_DIR = DAILY_DIR / "CS_reports"
     for msg in emails:
         for attach in msg.attachments:
             fname = attach.name
@@ -17,3 +21,72 @@ def download_files(count=20):
                 p = DATA_DIR / fname
                 if not p.exists():
                     p.write_bytes(attach.content)
+
+    emails = em.get_msgs(
+        path=["NYops", "Margin Calls CS"],
+        count=count,
+        sender="americas.collateralmgt@credit-suisse.com",
+    )
+
+    for msg in emails:
+        for attach in msg.attachments:
+            fname = attach.name
+            if "Serenitas CGMF" in fname:
+                p = DATA_DIR / fname
+                p = p.parent / f"{msg.datetime_sent:%Y-%m-%d} {p.stem}{p.suffix}"
+                if not p.exists():
+                    p.write_bytes(attach.content)
+
+
+def get_collateral(d):
+    DATA_DIR = DAILY_DIR / "CS_reports"
+    # get most recent file before current date
+    def get_date(p):
+        return datetime.date.fromisoformat(p.stem.split(" ", 1)[0])
+
+    files = ((f, get_date(f)) for f in DATA_DIR.glob("*.xls"))
+    files = sorted(filter(lambda t: t[1] <= d, files), key=itemgetter(1), reverse=True)
+    wb = open_workbook(files[0][0])
+    s = wb.sheet_by_index(0)
+    i = 0
+    for i, v in enumerate(s.col_values(0)):
+        if v.startswith("Total Value of Collateral"):
+            return s.cell_value(i, 1) + 900_000
+        if v.startswith("No Positions to Report"):
+            return 0.0
+
+
+def collateral(d, dawn_trades, *args):
+    collateral = get_collateral(d + BDay())
+    df = pd.read_excel(
+        f"/home/serenitas/Daily/CS_reports/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"]
+    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 *= -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")