diff options
Diffstat (limited to 'python')
| -rw-r--r-- | python/external_deriv_marks.py | 30 |
1 files changed, 27 insertions, 3 deletions
diff --git a/python/external_deriv_marks.py b/python/external_deriv_marks.py index f3918bec..d14bf305 100644 --- a/python/external_deriv_marks.py +++ b/python/external_deriv_marks.py @@ -3,6 +3,7 @@ import pandas as pd import re 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 @@ -155,10 +156,10 @@ def cs_navs(date: datetime.date = None): df["Trade Date"] = pd.to_datetime(df["Trade Date"]) df = df.set_index("Order No") df = df[["Trade Date", "Buy/Sell", "Notional", "Mid Price"]] + df.columns = ["trade_date", "buy/sell", "notional", "nav"] # TODO: fix this - df["ia"] = 0.0 - df.loc["61581078", "ia"] = 900_000 - df.columns = ["trade_date", "buy/sell", "notional", "nav", "ia"] + df_ia = get_ia(date) + df = df.join(df_ia) d[datetime.datetime.strptime(fname.stem.split("_")[1], "%b%d%Y").date()] = df df = pd.concat(d) # nav is from CS's point of view @@ -166,6 +167,29 @@ def cs_navs(date: datetime.date = None): return df +def get_ia(date: datetime.date = None): + glob_str = f"{date + bus_day:%m%d%Y}" + for fname in (DAILY_DIR / "CS_reports").glob( + f"CollateralCptyStatement161SerenitasCGMFRVM_{glob_str}.pdf" + ): + l = load_pdf(fname) + top, bottom = get_box_dimension(l) + trade_ids = get_col(l, top, bottom, 20, 70) + ia = get_col(l, top, bottom, 850, 1000) + df = pd.DataFrame({"trade_ids": trade_ids, "ia": ia}) + df.ia = pd.to_numeric(df.ia.str.strip().str.replace(",", "")) + return df.set_index("trade_ids") + + +def get_box_dimension(l): + for e in l: + if e.text == "**CD Swaption": + top = int(e["top"]) + if e.text == "**CD Swaption Total:": + bottom = int(e["top"]) + return (top + 1, bottom - 1) + + # def bnp_navs_old(date: datetime.date = None): # d = {} # date_str = date.strftime("%d%b%Y") if date else "" |