diff options
| -rw-r--r-- | python/collateral/cs.py | 40 | ||||
| -rw-r--r-- | python/external_deriv_marks.py | 114 |
2 files changed, 93 insertions, 61 deletions
diff --git a/python/collateral/cs.py b/python/collateral/cs.py index 7f0b18bc..e6babbe4 100644 --- a/python/collateral/cs.py +++ b/python/collateral/cs.py @@ -1,3 +1,4 @@ +import datetime import pandas as pd from . import DAILY_DIR from .common import load_pdf, next_business_day @@ -41,7 +42,7 @@ def download_files(em, count=20, *, fund="Serenitas", **kwargs): p.write_bytes(attach.content) -def get_collateral(d, fund): +def get_collateral(d: datetime.date, fund): DATA_DIR = DAILY_DIR / fund / "CS_reports" collat = 0 full_name = { @@ -63,20 +64,15 @@ def get_collateral(d, fund): return collat -def collateral(d, dawn_trades, *, fund="Serenitas", **kwargs): - collateral = get_collateral(next_business_day(d), fund) +def load_cs_positions(d: datetime.date, fund: str, legacy=False): + if not legacy: + d = next_business_day(d) 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={"Notional1 CCY": "Currency"}) - elif fund == "Serenitas": + full_name = { + "Serenitas": "SerenitasCGMF", + "BowdSt": "BostonBPStLLC", + } + if legacy and fund == "Serenitas": df = pd.read_excel( DATA_DIR / f"DERV048829_{d:%b%d%Y}.xlsx", header=9, skipfooter=50, ) @@ -95,6 +91,22 @@ def collateral(d, dawn_trades, *, fund="Serenitas", **kwargs): "Order No": "Structure ID", } ) + else: + df = pd.read_excel( + DATA_DIR / f"CollateralCptyStatement161{full_name[fund]}RVM_{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={"Notional1 CCY": "Currency"}) + return df + + +def collateral(d, dawn_trades, *, fund="Serenitas", **kwargs): + collateral = get_collateral(next_business_day(d), fund) + df = load_cs_positions(d, fund) 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: diff --git a/python/external_deriv_marks.py b/python/external_deriv_marks.py index f2eda51f..29923ce7 100644 --- a/python/external_deriv_marks.py +++ b/python/external_deriv_marks.py @@ -5,7 +5,7 @@ from env import DAILY_DIR from collateral.baml_isda import load_excel from collateral.citi import load_pdf, get_col from dates import bus_day -from analytics.utils import next_business_day +from analytics.utils import next_business_day, prev_business_day # local_nav is the nav in the trade's own currency COLUMNS = ["trade_date", "buy/sell", "notional", "local_nav", "base_nav", "ia"] @@ -164,58 +164,76 @@ def bnp_navs(date: datetime.date = None, fund: str = "Serenitas"): return df -def cs_navs(date: datetime.date = None, fund: str = "Serenitas"): +def cs_navs_old(date: datetime.date = None, fund: str = "Serenitas"): d = {} 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") + glob_str = f"{date:%b%d%Y}" if date else "*" + g = DATA_DIR.glob(f"DERV048829_{glob_str}.xlsx") + for fname in g: + 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 + if d: + df = pd.concat(d) + # nav is from CS's point of view + df[["local_nav", "base_nav"]] *= -1.0 else: - g = [] + df = pd.DataFrame(columns=COLUMNS) + return df + + +def cs_navs(date: datetime.date = None, fund: str = "Serenitas"): + if date: + date = next_business_day(date) + glob_str = f"{date:%m%d%Y}" + else: + glob_str = "*" + d = {} + DATA_DIR = DAILY_DIR / fund / "CS_reports" + full_name = { + "Serenitas": "SerenitasCGMF", + "BowdSt": "BostonBPStLLC", + "Brinker": "", + } + g = DATA_DIR.glob(f"CollateralCptyStatement161{full_name[fund]}RVM_{glob_str}.xls") 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)", - ] + print("salut") + 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 + ] + df.columns = COLUMNS + date = datetime.datetime.strptime(fname.stem.split("_")[1], "%m%d%Y").date() + d[prev_business_day(date)] = df if d: df = pd.concat(d) # nav is from CS's point of view @@ -294,6 +312,8 @@ if __name__ == "__main__": for fund in ("Serenitas", "Brinker", "BowdSt"): logger.info(f"{cp} at {fund}") df = globals()[f"{cp.lower()}_navs"](date, fund=fund) + if df.empty and cp == "CS": + df = globals()[f"{cp.lower()}_navs_old"](date, fund=fund) logger.debug(df) with dbconn("dawndb") as conn: with conn.cursor() as c: |