diff options
Diffstat (limited to 'python')
| -rw-r--r-- | python/collateral_calc.py | 63 |
1 files changed, 44 insertions, 19 deletions
diff --git a/python/collateral_calc.py b/python/collateral_calc.py index de9f0960..885b29ee 100644 --- a/python/collateral_calc.py +++ b/python/collateral_calc.py @@ -264,7 +264,7 @@ def sg_collateral(d): return df -def ms_collateral(d): +def ms_collateral(d, dawn_trades): df = pd.read_excel(DAILY_DIR / "MS_reports" / f"Collateral_Detail_{d:%Y%m%d}.xls") collat = df.loc[1, 'coll_val_ccy'].replace(",", "") if "(" in collat: @@ -273,13 +273,14 @@ def ms_collateral(d): else: collat = float(collat) df = pd.read_excel(DAILY_DIR / "MS_reports" / f"Trade_Detail_{d:%Y%m%d}.xls") - d = {'TRCDX': 'IGTCDSCSH', - 'ABINT': 'MBSCDSCSH', - 'ACSWN': 'IRDEVCSH', - 'ABOPB': 'HYCDSCSH', - 'ACUSD': 'IRDEVCSH'} - df.trade_book = df.trade_book.replace(d) - df = df.groupby('trade_book')[["collat_req_in_agr_ccy"]].sum() + df = df.merge(dawn_trades, how='left', left_on='trade_id', right_on='cpty_id') + # d = {'TRCDX': 'IGTCDSCSH', + # 'ABINT': 'MBSCDSCSH', + # 'ACSWN': 'IRDEVCSH', + # 'ABOPB': 'HYCDSCSH', + # 'ACUSD': 'IRDEVCSH'} + # df.trade_book = df.trade_book.replace(d) + df = df.groupby('folder')[["collat_req_in_agr_ccy"]].sum() df['Currency'] = "USD" df = df.reset_index() col_names= ['Strategy', 'Amount', 'Currency'] @@ -300,21 +301,44 @@ def load_gs_file(d, pattern): return df -def gs_collateral(d): +def get_dawn_trades(): + df_cds = pd.read_sql_query("SELECT cpty_id, folder FROM cds " + "WHERE cpty_id IS NOT NULL", dawn_engine) + df_swaptions = pd.read_sql_query("SELECT cpty_id, folder FROM swaptions " + "WHERE cpty_id IS NOT NULL", dawn_engine) + df_caps = pd.read_sql_query("SELECT cpty_id, folder FROM capfloors " + "WHERE cpty_id IS NOT NULL", dawn_engine) + df = pd.concat([df_cds, df_swaptions, df_caps]) + df = df.replace({"folder": {'IGREC': 'IGCDSCSH', + 'IGPAYER': 'IGCDSCSH', + 'HYPAYER': 'HYCDSCSH', + 'HYREC': 'HYCDSCSH', + 'STEEP': 'IRDEVCSH', + 'FLAT': 'IRDEVCSH', + 'MBSCDS': 'MBSCDSCSH', + 'SER_IGMEZ': 'IGTCDSCSH', + 'SER_IGSNR': 'IGTCDSCSH', + 'SER_IGEQY': 'IGTCDSCSH', + 'SER_HYMEZ': 'HYTCDSCSH'}}) + return df + +def gs_collateral(d, dawn_trades): df = load_gs_file(d, "Collateral_Detail") collateral = float(df.Quantity) df = load_gs_file(d, "Trade_Detail") - df = df[['Transaction Type', 'NPV (USD)', 'Initial Margin Required']] - df = df.groupby('Transaction Type').sum() + df = df[['Trade Id', 'Transaction Type', 'NPV (USD)', 'Initial Margin Required']] + df = df.merge(dawn_trades, how='left', left_on='Trade Id', right_on='cpty_id') + df = df[['folder', 'NPV (USD)', 'Initial Margin Required']] + df = df.groupby('folder').sum() df = df.sum(axis=1).to_frame(name='Amount') df['Currency'] = 'USD' df = df.reset_index() df.columns = ['Strategy', 'Amount', 'Currency'] # TODO: need to break down SWO_CDIXOP between IG and HY - df.Strategy = df.Strategy.replace({'SWAP_CDINDT': 'IGTCDSCSH', - 'GENERIC_IRSIRD': 'IRDEVCSH', - 'SWO_CDIXOP': 'IGCDSCSH', - 'FX': 'M_CSH_CASH'}) + # df.Strategy = df.Strategy.replace({'SWAP_CDINDT': 'IGTCDSCSH', + # 'GENERIC_IRSIRD': 'IRDEVCSH', + # 'SWO_CDIXOP': 'IGCDSCSH', + # 'FX': 'M_CSH_CASH'}) df.Amount *= -1 df = df.append({'Strategy': "M_CSH_CASH", 'Amount': -collateral - df.Amount.sum(), @@ -348,16 +372,17 @@ if __name__ == "__main__": d = (pd.Timestamp.today() - BDay()).normalize() #download_sftp_files(d) download_baml_files() + dawn_trades = get_dawn_trades() try: - df_ms = ms_collateral(d) + df_ms = ms_collateral(d, dawn_trades) except FileNotFoundError as e: logging.info(e) - df_ms = ms_collateral(d - BDay()) + df_ms = ms_collateral(d - BDay(), dawn_trades) #df_sg = sg_collateral(d) df_baml = baml_collateral(d) try: - df_gs = gs_collateral(d) + df_gs = gs_collateral(d, dawn_trades) except FileNotFoundError as e: logging.info(e) - df_gs = gs_collateral(d - BDay()) + df_gs = gs_collateral(d - BDay(), dawn_trades) send_email(df_ms, df_baml, df_gs) |