diff options
| -rw-r--r-- | python/collateral/cs.py | 55 | ||||
| -rw-r--r-- | python/external_deriv_marks.py | 70 |
2 files changed, 86 insertions, 39 deletions
diff --git a/python/collateral/cs.py b/python/collateral/cs.py index 479ec4ec..f151a9c2 100644 --- a/python/collateral/cs.py +++ b/python/collateral/cs.py @@ -48,7 +48,6 @@ def get_collateral(d, fund): "Serenitas": "SerenitasCGMF", "BowdSt": "BostonBPStLLC", } - for collat_type in ("RVM", "IM"): pdf_file = ( DATA_DIR @@ -66,27 +65,45 @@ def get_collateral(d, fund): def collateral(d, dawn_trades, *, fund="Serenitas", **kwargs): collateral = get_collateral(next_business_day(d), fund) - df = pd.read_excel( - DAILY_DIR / fund / "CS_reports" / f"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"] + 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={"Notional1CCY": "Currency"}) + elif fund == "Serenitas": + df = pd.read_excel( + DATA_DIR / f"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.rename( + columns={ + "Mid Price": "PV (USD)", + "Notional Currency": "Currency", + "Order No": "Structure ID", + } + ) + 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: 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"] = df.ia + df["PV (USD)"] + df = df[["folder", "Amount", "Currency"]] + df = df.groupby(["folder", "Currency"], as_index=False).sum() + df = df.rename(columns={"folder": "Strategy"}) df.Amount *= -1 df = df.append( { 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 |