diff options
Diffstat (limited to 'python')
| -rw-r--r-- | python/external_deriv_marks.py | 55 |
1 files changed, 29 insertions, 26 deletions
diff --git a/python/external_deriv_marks.py b/python/external_deriv_marks.py index dc7c12fd..20693d9d 100644 --- a/python/external_deriv_marks.py +++ b/python/external_deriv_marks.py @@ -7,7 +7,7 @@ from collateral.citi import load_pdf, get_col from dates import bus_day -def gs_navs(date: datetime.date = None): +def gs_navs(date: datetime.date = None, **kwargs): d = {} date_str = date.strftime("%d_%b_%Y") if date else "" for fname in (DAILY_DIR / "GS_reports").glob(f"Trade_Detail*{date_str}*.xls"): @@ -38,10 +38,10 @@ def gs_navs(date: datetime.date = None): return df -def ms_navs(date: datetime.date = None): +def ms_navs(date: datetime.date = None, fund: str = "Serenitas"): d = {} date_str = date.strftime("%Y%m%d") if date else "*" - for fname in (DAILY_DIR / "MS_reports").glob(f"Trade_Detail_{date_str}.xls"): + for fname in (DAILY_DIR / fund / "MS_reports").glob(f"Trade_Detail_{date_str}.xls"): df = pd.read_excel(fname, index_col="trade_id") df.trade_date = pd.to_datetime(df.trade_date) df = df[ @@ -62,7 +62,7 @@ def ms_navs(date: datetime.date = None): return pd.concat(d) -def citi_navs(date: datetime.date = None): +def citi_navs(date: datetime.date = None, **kwargs): dfs = [] glob_str = f"{date + bus_day:%Y%m%d}*" if date else "*" for fname in (DAILY_DIR / "CITI_reports").glob(f"262966_Portfolio_{glob_str}.xlsx"): @@ -89,7 +89,7 @@ def citi_navs(date: datetime.date = None): return df -def baml_navs(date: datetime.date = None): +def baml_navs(date: datetime.date = None, **kwargs): dfs = [] glob_str = f"{date + bus_day:%m%d%Y}" if date else "*" for fname in (DAILY_DIR / "BAML_ISDA_reports").glob( @@ -103,10 +103,10 @@ def baml_navs(date: datetime.date = None): return pd.concat(dfs) -def bnp_navs(date: datetime.date = None): +def bnp_navs(date: datetime.date = None, fund: str = "Serenitas"): d = {} date_str = date.strftime("%Y%m%d") if date else "" - for fname in (DAILY_DIR / "BNP_reports").glob(f"Exposure*{date_str}.XLS"): + for fname in (DAILY_DIR / fund / "BNP_reports").glob(f"Exposure*{date_str}.XLS"): try: df = pd.read_excel(fname, skiprows=7) except ValueError: @@ -125,13 +125,16 @@ def bnp_navs(date: datetime.date = None): ] df.columns = ["trade_date", "buy/sell", "notional", "nav", "ia"] d[datetime.datetime.strptime(fname.stem[-8:], "%Y%m%d").date()] = df - df = pd.concat(d) - # nav is from BNP's point of view - df.nav *= -1.0 + if d: + df = pd.concat(d) + # nav is from BNP's point of view + df.nav -= -1.0 + else: + df = pd.DataFrame(columns=["trade_date", "buy/sell", "notional", "nav", "ia"]) return df -def cs_navs(date: datetime.date = None): +def cs_navs(date: datetime.date = None, **kwargs): d = {} glob_str = f"{date:%b%d%Y}" if date else "*" for fname in (DAILY_DIR / "CS_reports").glob(f"DERV048829_{glob_str}.xlsx"): @@ -203,7 +206,6 @@ if __name__ == "__main__": import argparse import logging from utils.db import dbconn - from pandas.tseries.offsets import BDay parser = argparse.ArgumentParser() parser.add_argument( @@ -223,17 +225,18 @@ if __name__ == "__main__": logging.basicConfig() logger = logging.getLogger("external_marks") logger.setLevel(logging.DEBUG if args.debug else logging.INFO) - for cp in ["MS", "CITI", "GS", "BAML", "BNP", "CS"]: - logger.info(cp) - df = globals()[f"{cp.lower()}_navs"](date) - logger.debug(df) - with dbconn("dawndb") as conn: - with conn.cursor() as c: - for k, v in df[["nav", "ia"]].iterrows(): - c.execute( - "INSERT INTO external_marks_deriv " - "VALUES(%s, %s, %s, %s, %s) " - "ON CONFLICT (identifier, date) " - "DO UPDATE SET nav=excluded.nav, ia=excluded.ia", - (*k, float(v.nav), cp, float(v.ia)), - ) + for cp in ("MS", "CITI", "GS", "BAML", "BNP", "CS"): + for fund in ("Serenitas", "Brinker", "BowdSt"): + logger.info(f"{cp} at {fund}") + df = globals()[f"{cp.lower()}_navs"](date, fund=fund) + logger.debug(df) + with dbconn("dawndb") as conn: + with conn.cursor() as c: + for k, v in df[["nav", "ia"]].iterrows(): + c.execute( + "INSERT INTO external_marks_deriv " + "VALUES(%s, %s, %s, %s, %s) " + "ON CONFLICT (identifier, date) " + "DO UPDATE SET nav=excluded.nav, ia=excluded.ia", + (*k, float(v.nav), cp, float(v.ia)), + ) |