1 files changed, 63 insertions, 0 deletions
diff --git a/python/collateral/barclays.py b/python/collateral/barclays.py
new file mode 100644
index 00000000..1adf3077
--- /dev/null
+++ b/python/collateral/barclays.py
@@ -0,0 +1,63 @@
+import logging
+import pandas as pd
+from . import DAILY_DIR
+
+logger = logging.getLogger(__name__)
+
+paths = {
+    "Serenitas": ["NYops", "Margin Calls Barclays"],
+    "Selene": ["SeleneOps", "Margin Barclays"],
+}
+
+
+def load_file(d, fund="Serenitas"):
+    file_mapping = {
+        "Serenitas": "Serenitas Credit Gamma Master Fund LP",
+        "Selene": "ISO Selene",  # WIll replace to the correct name once we get it
+    }
+    try:
+        fname = next(
+            (DAILY_DIR / fund / "BARCLAYS_reports").glob(
+                f"Exposure Report for {file_mapping[fund]} - Regulatory as at {d:%d %b %Y}*"
+            )
+        )
+    except StopIteration:
+        raise FileNotFoundError(f"BARCLAYS file not found for date {d}")
+    return pd.read_excel(fname, skiprows=3, skipfooter=23)
+
+
+def download_files(em, count=20, *, fund="Serenitas", **kwargs):
+    if fund not in paths:
+        return
+    emails = em.get_msgs(path=paths[fund], count=count)
+    DATA_DIR = DAILY_DIR / fund / "BARCLAYS_reports"
+    for msg in emails:
+        for attach in msg.attachments:
+            fname = attach.name
+            if fname.startswith("CreditSupport") or fname.startswith("Exposure"):
+                p = DATA_DIR / fname
+                if not p.exists():
+                    p.write_bytes(attach.content)
+
+
+def collateral(d, dawn_trades, *, fund="Serenitas", **kwargs):
+    collateral = 0
+    df = load_file(d, fund)
+    df = df[["Trade Reference", "Exposure (USD)", "Ind Amt (USD)"]]
+    df["Trade Reference"] = df["Trade Reference"].astype(str)
+    df = df.merge(
+        dawn_trades, how="left", left_on="Trade Reference", right_on="cpty_id"
+    )
+    missing_ids = df.loc[df.cpty_id.isnull(), "Trade Reference"]
+    if not missing_ids.empty:
+        raise ValueError(f"{missing_ids.tolist()} not in the database for {fund}")
+    df = df[["folder", "Exposure (USD)", "Ind Amt (USD)"]]
+    df = df.groupby("folder", dropna=False).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.loc[len(df.index)] = ["M_CSH_CASH", -collateral - df.Amount.sum(), "USD"]
+    df["date"] = d
+    return df.set_index("Strategy")