diff options
| -rw-r--r-- | python/collateral/baml_isda.py | 39 | ||||
| -rw-r--r-- | python/external_deriv_marks.py | 82 | ||||
| -rw-r--r-- | sql/dawn.sql | 9 |
3 files changed, 84 insertions, 46 deletions
diff --git a/python/collateral/baml_isda.py b/python/collateral/baml_isda.py index c5918a88..4df80c68 100644 --- a/python/collateral/baml_isda.py +++ b/python/collateral/baml_isda.py @@ -87,7 +87,7 @@ def download_files(d=None, count=20): p.write_bytes(attach.content) -def baml_load_excel(fname): +def load_excel_old(fname): wb = open_workbook(fname) s = wb.sheet_by_index(0) headers = s.row_values(6, 0) @@ -108,6 +108,36 @@ def baml_load_excel(fname): return df +def load_excel(fname): + wb = open_workbook(fname) + s = wb.sheet_by_index(0) + headers = s.row_values(13, 0) + i = 19 + rows = [] + while s.cell(i, 0).ctype != xlrd.XL_CELL_DATE: + if s.cell(i, 0).value == "" or s.cell(i, 0).value.startswith("Credit"): + i += 1 + continue + r = [] + for e in s.row_slice(i, 0): + if e.ctype == xlrd.XL_CELL_DATE: + r.append(pd.Timestamp(*xldate_as_tuple(e.value, wb.datemode))) + else: + r.append(e.value) + rows.append(r) + i += 1 + df = pd.DataFrame.from_records(rows, columns=headers) + df = df.rename( + columns={ + "Contract ID ": "Trade ID", + "Market Value Amount": "MTM(USD)", + "Trade Date ": "Trade Date", + } + ) + df["MTM(USD)"] *= -1.0 + return df + + def collateral(d, dawn_trades, *args): REPORTS_DIR = DAILY_DIR / "BAML_ISDA_reports" try: @@ -120,12 +150,9 @@ def collateral(d, dawn_trades, *args): collateral = float(df.Notional) except TypeError: collateral = df.Notional.sum() - d -= BDay() - fname = REPORTS_DIR / f"Interest Rates Trade Summary_{d:%d-%b-%Y}.xls" - # TODO: make more robust - df = baml_load_excel(fname) + fname = next(REPORTS_DIR.glob(f"301__LMCG_INVESTMENTSLP_CSA_{d:%m%d%Y}_*")) + df = load_excel(fname) df = df[["Trade ID", "MTM(USD)"]] - df["Trade ID"] = df["Trade ID"].astype("str") df = df.merge(dawn_trades, how="left", left_on="Trade ID", right_on="cpty_id") missing_ids = df.loc[df.cpty_id.isnull(), "Trade ID"] if not missing_ids.empty: 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)), ) diff --git a/sql/dawn.sql b/sql/dawn.sql index 19c2d2fa..f0e09836 100644 --- a/sql/dawn.sql +++ b/sql/dawn.sql @@ -1019,7 +1019,7 @@ CREATE OR REPLACE FUNCTION list_swaption_positions_and_risks(IN p_date date) maturity date, notional double precision, option_type option_type, strike double precision, expiration_date date, serenitas_nav double precision, globeop_nav double precision, - initial_margin_percentage double precision, latest_model_date date, + initial_margin double precision, latest_model_date date, delta double precision, gamma double precision, vega double precision, theta double precision, index index_type, series smallint, tenor tenor, duration double precision) AS $$ @@ -1030,7 +1030,7 @@ SELECT DISTINCT ON (a.dealid) a.dealid, a.security_id, c.maturity, (CASE WHEN buysell = 't' THEN 1 ELSE -1 END) * (a.notional - coalesce(terminated_amount, 0.)), a.option_type, a.strike, a.expiration_date, b.market_value, endbooknav, - a.initial_margin_percentage, b.date, b.delta, b.gamma, b.vega, + f.ia, b.date, b.delta, b.gamma, b.vega, b.theta, c.index, c.series, c.tenor, d.duration FROM swaptions a LEFT JOIN (SELECT * FROM swaption_marks where date <= p_date) b USING (dealid) @@ -1039,11 +1039,12 @@ LEFT JOIN (SELECT * FROM index_quotes WHERE date=p_date) d USING (index, series, LEFT JOIN valuation_reports ON invid = a.globeop_id LEFT JOIN (SELECT dealid, SUM(termination_amount) AS terminated_amount FROM terminations WHERE termination_date <= p_date GROUP BY dealid) e USING (dealid) +LEFT JOIN external_deriv_marks f ON a.cpty_id = f.identifier WHERE a.notional IS DISTINCT FROM terminated_amount AND a.expiration_date > p_date AND trade_date <= p_date AND swap_type = 'CD_INDEX_OPTION' -ORDER BY dealid, date DESC, periodenddate DESC -END; +ORDER BY dealid, date DESC, periodenddate DESC; +END $$ LANGUAGE plpgsql; CREATE OR REPLACE FUNCTION public.list_ir_capfloor_positions(p_date date, p_fund fund DEFAULT 'SERCGMAST'::fund) |