diff options
Diffstat (limited to 'python/external_deriv_marks.py')
| -rw-r--r-- | python/external_deriv_marks.py | 114 |
1 files changed, 67 insertions, 47 deletions
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: |