diff options
| -rw-r--r-- | python/collateral/__main__.py | 1 | ||||
| -rw-r--r-- | python/collateral/common.py | 1 | ||||
| -rw-r--r-- | python/collateral/cs.py | 75 |
3 files changed, 75 insertions, 2 deletions
diff --git a/python/collateral/__main__.py b/python/collateral/__main__.py index 2e33c6bc..36d424ba 100644 --- a/python/collateral/__main__.py +++ b/python/collateral/__main__.py @@ -42,7 +42,6 @@ dawn_trades = get_dawn_trades(args.workdate, dawn_engine) df = {} mapping = {"baml_fcm": "BAML", "wells": "WF"} args.workdate -= BDay() -counterparties.pop() # remove cs for cp in counterparties: cp_mod = import_module("." + cp, "collateral") if cp in ["baml_fcm", "wells"]: diff --git a/python/collateral/common.py b/python/collateral/common.py index 464f55a3..1a0d65fe 100644 --- a/python/collateral/common.py +++ b/python/collateral/common.py @@ -85,6 +85,7 @@ def send_email(d: datetime.date, df: pd.DataFrame) -> None: "BAML_ISDA": "Baml OTC", "WELLS": "Wells Fargo", "BNP": "BNP Paribas", + "CS": "Credit Suisse", } html = "<html><body>" for cp, name in cp_mapping.items(): 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") |