diff options
Diffstat (limited to 'python')
| -rw-r--r-- | python/external_deriv_marks.py | 60 |
1 files changed, 47 insertions, 13 deletions
diff --git a/python/external_deriv_marks.py b/python/external_deriv_marks.py index 9d0eef3a..c6eb7ccb 100644 --- a/python/external_deriv_marks.py +++ b/python/external_deriv_marks.py @@ -2,12 +2,12 @@ import datetime import pandas as pd import re from env import DAILY_DIR -from utils.db import dbconn -def gs_navs(): +def gs_navs(date: datetime.date = None): d = {} - for fname in (DAILY_DIR / "GS_reports").glob("Trade_Detail*.xls"): + date_str = date.strftime("%d_%b_%Y") if date else "" + for fname in (DAILY_DIR / "GS_reports").glob(f"Trade_Detail*{date_str}*.xls"): try: df = pd.read_excel(fname, skiprows=9, skipfooter=77, index_col="Trade Id") except ValueError: @@ -27,9 +27,10 @@ def gs_navs(): return df -def ms_navs(): +def ms_navs(date: datetime.date = None): d = {} - for fname in (DAILY_DIR / "MS_reports").glob("Trade_Detail*.xls"): + date_str = date.strftime("%Y%m%d") if date else "*" + for fname in (DAILY_DIR / "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[ @@ -44,9 +45,13 @@ def ms_navs(): return pd.concat(d) -def citi_navs(): - l = [] - for fname in (DAILY_DIR / "CITI_reports").glob("262966_Portfolio_*.xlsx"): +def citi_navs(date: datetime.date = None): + d = {} + glob_str = date.strftime("%Y%m%d*") if date else "*" + for fname in (DAILY_DIR / "CITI_reports").glob(f"262966_Portfolio_{glob_str}.xlsx"): + date_parsed = datetime.datetime.strptime( + fname.stem.rsplit("_", 1)[1][:-3], "%Y%m%d%H%M%S%f" + ) df = pd.read_excel( fname, skiprows=6, skipfooter=2, parse_dates=["Trade Date", "Value Date"] ) @@ -55,17 +60,24 @@ def citi_navs(): ) df = df[["Trade Date", "Party Position", "Notional", "Market Value"]] df.columns = ["trade_date", "buy/sell", "notional", "nav"] - l.append(df) - df = pd.concat(l) + d[date_parsed] = df + # there can be multiple files per day, we take the latest one + df = ( + pd.concat(d) + .sort_index() + .groupby(level=["Value Date", "Operations File"]) + .last() + ) # nav is from Citi's point of view df.nav *= -1.0 return df -def baml_navs(): +def baml_navs(date: datetime.date = None): d = {} + glob_str = date.strftime("%d-%b-%Y") if date else "*" for fname in (DAILY_DIR / "BAML_ISDA_reports").glob( - "Interest Rates Trade Summary_*.xls" + f"Interest Rates Trade Summary_{glob_str}.xls" ): date = datetime.datetime.strptime(fname.stem.split("_")[1], "%d-%b-%Y") df = pd.read_excel(fname, skiprows=6, nrows=1) @@ -77,8 +89,30 @@ def baml_navs(): if __name__ == "__main__": + import argparse + from utils.db import dbconn + from pandas.tseries.offsets import BDay + + parser = argparse.ArgumentParser() + parser.add_argument( + "date", + type=datetime.datetime.fromisoformat, + nargs="?", + default=datetime.date.today(), + ) + parser.add_argument( + "-a", "--all", action="store_true", default=False, help="download everything" + ) + args = parser.parse_args() + date = None if args.all else args.date + for cp in ["MS", "CITI", "GS", "BAML"]: - df = globals()[f"{cp.lower()}_navs"]() + if cp != "CITI": + date_arg = (date - BDay()).date() + else: + date_arg = date + df = globals()[f"{cp.lower()}_navs"](date_arg) + print(df) with dbconn("dawndb") as conn: with conn.cursor() as c: for k, v in df[["nav"]].iterrows(): |