diff options
| -rw-r--r-- | python/collateral_calc.py | 37 |
1 files changed, 21 insertions, 16 deletions
diff --git a/python/collateral_calc.py b/python/collateral_calc.py index 9bed262d..4f836641 100644 --- a/python/collateral_calc.py +++ b/python/collateral_calc.py @@ -153,33 +153,36 @@ def sg_collateral(d): apply(f)) df = df.groupby(['folder']).agg({'Collateral': 'sum', 'currency': 'first'}) df = df.reset_index('folder') - df = df.rename(columns= - {'folder': 'Strategy', - 'currency': 'Currency', - 'Collateral': 'Amount'}) + df = df.rename(columns={'folder': 'Strategy', + 'currency': 'Currency', + 'Collateral': 'Amount'}) df.Strategy = df.Strategy.map({'HEDGE_MBS': 'MBSCDSCSH', 'SER_ITRXCURVE': 'SER_ITRXCVCSH', 'SER_IGCURVE': 'SER_IGCVECSH', - 'HYOPTDEL': 'HYCDSCSH'}) + 'HYOPTDEL': 'HYCDSCSH', + 'IGOPTDEL': 'IGCDSCSH'}) return df def ms_collateral(d): df = pd.read_excel(DAILY_DIR / "MS_reports" / f"Collateral_Detail_{d:%Y%m%d}.xls") collat = float(df.loc[1, 'coll_val_ccy'].replace(",", "")) - df = pd.read_excel(DAILY_DIR / "MS_reports" / f"Trade_Detail_{d:%Y%m%d}.xls" ) + 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'} - r = [] - acc = 0 - for k, v in d.items(): - amount = df.loc[df.trade_book == k, "collat_req_in_agr_ccy"].sum() - r.append((v, amount, "USD")) - acc += amount - r.append(["M_CSH_CASH", -collat - acc, "USD"]) - return pd.DataFrame.from_records(r, columns=['Strategy', 'Amount', 'Currency']) + 'ABOPB': 'HYCDSCSH', + 'ACUSD': 'IRDEVCSH'} + df.trade_book = df.trade_book.replace(d) + df = df.groupby('trade_book')[["collat_req_in_agr_ccy"]].sum() + df['Currency'] = "USD" + df = df.reset_index() + col_names= ['Strategy', 'Amount', 'Currency'] + df.columns = col_names + return pd.concat([df, + pd.DataFrame. + from_records([('M_CASH_CASH', -collat - df.Amount.sum(), "USD")], + columns=col_names)]) def load_gs_file(d, pattern): @@ -202,8 +205,10 @@ def gs_collateral(d): 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'}) + 'GENERIC_IRSIRD': 'IRDEVCSH', + 'SWO_CDIXOP': 'IGCDSCSH'}) df.Amount *= -1 df = df.append({'Strategy': "M_CSH_CASH", 'Amount': -collateral - df.Amount.sum(), |