diff options
Diffstat (limited to 'python/external_deriv_marks.py')
| -rw-r--r-- | python/external_deriv_marks.py | 82 |
1 files changed, 46 insertions, 36 deletions
diff --git a/python/external_deriv_marks.py b/python/external_deriv_marks.py index 619d2700..129a3961 100644 --- a/python/external_deriv_marks.py +++ b/python/external_deriv_marks.py @@ -2,7 +2,7 @@ import datetime import pandas as pd import re from env import DAILY_DIR -from collateral.baml_isda import baml_load_excel +from collateral.baml_isda import load_excel def gs_navs(date: datetime.date = None): @@ -15,11 +15,19 @@ def gs_navs(date: datetime.date = None): continue df = df.dropna(subset=["GS Entity"]) df["Trade Date"] = pd.to_datetime(df["Trade Date"]) - df = df[["Trade Date", "Buy/Sell", "Notional (USD)", "NPV (USD)"]] - df.columns = ["trade_date", "buy/sell", "notional", "nav"] + df = df[ + [ + "Trade Date", + "Buy/Sell", + "Notional (USD)", + "NPV (USD)", + "Initial Margin Required", + ] + ] + df.columns = ["trade_date", "buy/sell", "notional", "nav", "ia"] name = fname.name.replace("9972734", "") if m := re.match(r"[^\d]*(\d{2}_.{3}_\d{4})", name): - date_string, = m.groups() + (date_string,) = m.groups() date = datetime.datetime.strptime(date_string, "%d_%b_%Y") d[date] = df df = pd.concat(d) @@ -35,11 +43,18 @@ def ms_navs(date: datetime.date = None): df = pd.read_excel(fname, index_col="trade_id") df.trade_date = pd.to_datetime(df.trade_date) df = df[ - ["trade_date", "pay_rec", "notional_in_trade_ccy", "exposure_in_rpt_ccy"] + [ + "trade_date", + "pay_rec", + "notional_in_trade_ccy", + "exposure_in_rpt_ccy", + "collat_req_in_rpt_ccy", + ] ] - df.columns = ["trade_date", "buy/sell", "notional", "nav"] + df.columns = ["trade_date", "buy/sell", "notional", "nav", "ia"] + df.ia = df.nav - df.ia if m := re.match(r"[^\d]*(\d{8})", fname.name): - date_string, = m.groups() + (date_string,) = m.groups() date = datetime.datetime.strptime(date_string, "%Y%m%d") d[date] = df return pd.concat(d) @@ -58,8 +73,10 @@ def citi_navs(date: datetime.date = None): df = df.dropna(subset=["Operations File"]).set_index( ["Value Date", "Operations File"] ) - df = df[["Trade Date", "Party Position", "Notional", "Market Value"]] - df.columns = ["trade_date", "buy/sell", "notional", "nav"] + df = df[ + ["Trade Date", "Party Position", "Notional", "Market Value", "BasicAmt"] + ] + df.columns = ["trade_date", "buy/sell", "notional", "nav", "ia"] d[date_parsed] = df # there can be multiple files per day, we take the latest one df = ( @@ -75,37 +92,29 @@ def citi_navs(date: datetime.date = None): def baml_navs(date: datetime.date = None): d = {} - glob_str = date.strftime("%d-%b-%Y") if date else "*" + glob_str = date.strftime("%m%d%Y") if date else "*" for fname in (DAILY_DIR / "BAML_ISDA_reports").glob( - f"Interest Rates Trade Summary_{glob_str}.xls" + f"301__LMCG_INVESTMENTSLP_CSA_{glob_str}_*.xls" ): - date = datetime.datetime.strptime(fname.stem.split("_")[1], "%d-%b-%Y") - df = baml_load_excel(fname) + date = datetime.datetime.strptime(fname.stem.split("_")[5], "%m%d%Y") + df = load_excel(fname) df = df.set_index("Trade ID") - df = df[["Trade Date", "Flow Direction", "Notional", "MTM(USD)"]] - df.columns = ["trade_date", "buy/sell", "notional", "nav"] + df = df[ + [ + "Trade Date", + "Buy/Sell", + "Notional 1", + "MTM(USD)", + "Cpty Independent Amount", + ] + ] + df.columns = ["trade_date", "buy/sell", "notional", "nav", "ia"] d[date] = df return pd.concat(d) -def bnp_navs(date: datetime.date = None): - d = {} - date_str = date.strftime("%Y%m%d") if date else "" - for fname in (DAILY_DIR / "BNP_reports").glob(f"Exposure*{date_str}.XLS"): - try: - df = pd.read_excel(fname, skiprows=7) - except ValueError: - continue - df["Trade Ref"] = df["Trade Ref"].str.replace("MBO-", "") - df = df.set_index("Trade Ref") - df["Trade Date"] = pd.to_datetime(df["Trade Date"], dayfirst=True) - df = df[["Trade Date", "Buy/Sell", "Notional 1", "Exposure Amount (Agmt Ccy)"]] - df.columns = ["trade_date", "buy/sell", "notional", "nav"] - 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 - return df +def cs_navs(date: datetime.date = None): + pass # def bnp_navs_old(date: datetime.date = None): @@ -124,6 +133,7 @@ def bnp_navs(date: datetime.date = None): # df = pd.concat(d) # return df + if __name__ == "__main__": import argparse import logging @@ -161,9 +171,9 @@ if __name__ == "__main__": logger.debug(df) with dbconn("dawndb") as conn: with conn.cursor() as c: - for k, v in df[["nav"]].iterrows(): + for k, v in df[["nav", "ia"]].iterrows(): c.execute( "INSERT INTO external_marks_deriv " - "VALUES(%s, %s, %s, %s) ON CONFLICT DO NOTHING", - (*k, float(v), cp), + "VALUES(%s, %s, %s, %s, %s) ON CONFLICT DO NOTHING", + (*k, float(v.nav), cp, float(v.ia)), ) |