diff options
Diffstat (limited to 'python/external_deriv_marks.py')
| -rw-r--r-- | python/external_deriv_marks.py | 38 |
1 files changed, 15 insertions, 23 deletions
diff --git a/python/external_deriv_marks.py b/python/external_deriv_marks.py index e9360e79..f3918bec 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 dates import bus_day def gs_navs(date: datetime.date = None): @@ -61,12 +62,9 @@ def ms_navs(date: datetime.date = None): def citi_navs(date: datetime.date = None): - d = {} - glob_str = date.strftime("%Y%m%d*") if date else "*" + 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"): - 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"] ) @@ -77,10 +75,10 @@ def citi_navs(date: datetime.date = None): ["Trade Date", "Party Position", "Notional", "Market Value", "BasicAmt"] ] df.columns = ["trade_date", "buy/sell", "notional", "nav", "ia"] - d[date_parsed] = df + dfs.append(df) # there can be multiple files per day, we take the latest one df = ( - pd.concat(d) + pd.concat(dfs) .sort_index() .groupby(level=["Value Date", "Operations File"]) .last() @@ -91,14 +89,13 @@ def citi_navs(date: datetime.date = None): def baml_navs(date: datetime.date = None): - d = {} - glob_str = date.strftime("%m%d%Y") if date else "*" + dfs = [] + glob_str = f"{date + bus_day:%m%d%Y}" if date else "*" for fname in (DAILY_DIR / "BAML_ISDA_reports").glob( f"301__LMCG_INVESTMENTSLP_CSA_{glob_str}_*.xls" ): - date = datetime.datetime.strptime(fname.stem.split("_")[5], "%m%d%Y") df = load_excel(fname) - df = df.set_index("Trade ID") + df = df.set_index(["Market Value Date", "Trade ID"]) df = df[ [ "Trade Date", @@ -109,8 +106,8 @@ def baml_navs(date: datetime.date = None): ] ] df.columns = ["trade_date", "buy/sell", "notional", "nav", "ia"] - d[date] = df - return pd.concat(d) + dfs.append(df) + return pd.concat(dfs) def bnp_navs(date: datetime.date = None): @@ -206,26 +203,21 @@ if __name__ == "__main__": "-d", "--debug", action="store_true", default=False, help="more verbose logging" ) args = parser.parse_args() - date = None if args.all else args.date + date = None if args.all else args.date - bus_day 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) - if date and cp != "CITI": - date_arg = (date - BDay()).date() - else: - date_arg = date - try: - df = globals()[f"{cp.lower()}_navs"](date_arg) - except ValueError: - continue + 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 DO NOTHING", + "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)), ) |