diff options
Diffstat (limited to 'python/external_deriv_marks.py')
| -rw-r--r-- | python/external_deriv_marks.py | 67 |
1 files changed, 44 insertions, 23 deletions
diff --git a/python/external_deriv_marks.py b/python/external_deriv_marks.py index 68664cdb..0690d87c 100644 --- a/python/external_deriv_marks.py +++ b/python/external_deriv_marks.py @@ -6,6 +6,9 @@ from collateral.baml_isda import load_excel from collateral.citi import load_pdf, get_col from dates import bus_day +# local_nav is the nav in the trade's own currency +COLUMNS = ["trade_date", "buy/sell", "notional", "local_nav", "base_nav", "ia"] + def gs_navs(date: datetime.date = None, fund: str = "Serenitas"): d = {} @@ -24,11 +27,12 @@ def gs_navs(date: datetime.date = None, fund: str = "Serenitas"): "Trade Date", "Buy/Sell", "Notional (USD)", + "NPV (Base CCY)", "NPV (USD)", "Initial Margin Required", ] ] - df.columns = ["trade_date", "buy/sell", "notional", "nav", "ia"] + df.columns = COLUMNS name = fname.name.replace("9972734", "") if m := re.match(r"[^\d]*(\d{2}_.{3}_\d{4})", name): (date_string,) = m.groups() @@ -37,9 +41,9 @@ def gs_navs(date: datetime.date = None, fund: str = "Serenitas"): if d: df = pd.concat(d) # nav is from Goldman's point of view - df.nav *= -1.0 + df[["local_nav", "base_nav"]] *= -1.0 else: - df = pd.DataFrame(columns=["trade_date", "buy/sell", "notional", "nav", "ia"]) + df = pd.DataFrame(columns=COLUMNS) return df @@ -55,11 +59,11 @@ def ms_navs(date: datetime.date = None, fund: str = "Serenitas"): "pay_rec", "notional_in_trade_ccy", "exposure_in_rpt_ccy", - "collat_req_in_rpt_ccy", + "exposure_in_rpt_ccy", + "upfront_in_rpt_ccy", ] ] - df.columns = ["trade_date", "buy/sell", "notional", "nav", "ia"] - df.ia = df.nav - df.ia + df.columns = COLUMNS if m := re.match(r"[^\d]*(\d{8})", fname.name): (date_string,) = m.groups() date = datetime.datetime.strptime(date_string, "%Y%m%d") @@ -67,7 +71,7 @@ def ms_navs(date: datetime.date = None, fund: str = "Serenitas"): if d: df = pd.concat(d) else: - df = pd.DataFrame(columns=["trade_date", "buy/sell", "notional", "nav", "ia"]) + df = pd.DataFrame(columns=COLUMNS) return df @@ -82,9 +86,16 @@ def citi_navs(date: datetime.date = None, **kwargs): ["Value Date", "Operations File"] ) df = df[ - ["Trade Date", "Party Position", "Notional", "Market Value", "BasicAmt"] + [ + "Trade Date", + "Party Position", + "Notional", + "Market Value", + "Market Value", + "BasicAmt", + ] ] - df.columns = ["trade_date", "buy/sell", "notional", "nav", "ia"] + df.columns = COLUMNS dfs.append(df) # there can be multiple files per day, we take the latest one df = ( @@ -94,7 +105,7 @@ def citi_navs(date: datetime.date = None, **kwargs): .last() ) # nav is from Citi's point of view - df.nav *= -1.0 + df[["local_nav", "base_nav"]] *= -1.0 return df @@ -106,13 +117,15 @@ def baml_navs(date: datetime.date = None, fund: str = "Serenitas"): ): df = load_excel(fname) df = df.set_index(["Market Value Date", "Trade ID"]) - df = df[["Trade Date", "Buy/Sell", "Notional 1", "MTM(USD)", "ia",]] - df.columns = ["trade_date", "buy/sell", "notional", "nav", "ia"] + df = df[ + ["Trade Date", "Buy/Sell", "Notional 1", "local_nav", "base_nav", "ia",] + ] + df.columns = COLUMNS dfs.append(df) if dfs: df = pd.concat(dfs) else: - df = pd.DataFrame(columns=["trade_date", "buy/sell", "notional", "nav", "ia"]) + df = pd.DataFrame(columns=COLUMNS) return df @@ -132,18 +145,19 @@ def bnp_navs(date: datetime.date = None, fund: str = "Serenitas"): "Trade Date", "Buy/Sell", "Notional 1", + "Exposure Amount", "Exposure Amount (Agmt Ccy)", "Lock Up (Agmt Ccy)", ] ] - df.columns = ["trade_date", "buy/sell", "notional", "nav", "ia"] + df.columns = COLUMNS d[datetime.datetime.strptime(fname.stem[-8:], "%Y%m%d").date()] = df if d: df = pd.concat(d) # nav is from BNP's point of view - df.nav -= -1.0 + df[["local_nav", "base_nav"]] *= -1.0 else: - df = pd.DataFrame(columns=["trade_date", "buy/sell", "notional", "nav", "ia"]) + df = pd.DataFrame(columns=COLUMNS) return df @@ -163,15 +177,15 @@ def cs_navs(date: datetime.date = None, **kwargs): 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"]] - df.columns = ["trade_date", "buy/sell", "notional", "nav"] + df = df[["Trade Date", "Buy/Sell", "Notional", "Mid Price", "Mid Price"]] + df.columns = COLUMNS[:-1] # TODO: fix this df_ia = get_ia(date) df = df.join(df_ia) d[datetime.datetime.strptime(fname.stem.split("_")[1], "%b%d%Y").date()] = df df = pd.concat(d) # nav is from CS's point of view - df.nav *= -1.0 + df[["local_nav", "base_nav"]] *= -1.0 return df @@ -245,11 +259,18 @@ if __name__ == "__main__": logger.debug(df) with dbconn("dawndb") as conn: with conn.cursor() as c: - for k, v in df[["nav", "ia"]].iterrows(): + for k, v in df[["local_nav", "base_nav", "ia"]].iterrows(): c.execute( "INSERT INTO external_marks_deriv " - "VALUES(%s, %s, %s, %s, %s) " + "VALUES(%s, %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)), + "DO UPDATE SET local_nav=excluded.local_nav, " + "base_nav=excluded.base_nav, ia=excluded.ia", + ( + *k, + float(v.local_nav), + float(v.base_nav), + cp, + float(v.ia), + ), ) |