diff options
Diffstat (limited to 'python')
| -rw-r--r-- | python/collateral_calc.py | 34 |
1 files changed, 27 insertions, 7 deletions
diff --git a/python/collateral_calc.py b/python/collateral_calc.py index 47a9bda8..ab52db5c 100644 --- a/python/collateral_calc.py +++ b/python/collateral_calc.py @@ -297,8 +297,16 @@ def load_gs_file(d, pattern): glob(f"{pattern}*{d.strftime('%d_%b_%Y')}*")) except StopIteration: raise FileNotFoundError(f"GS {pattern} file not found for date {d}") - df = pd.read_excel(fname, skiprows=9, skipfooter=77) - return df + return pd.read_excel(fname, skiprows=9, skipfooter=77) + + +def load_citi_file(d): + try: + fname = next((DAILY_DIR / "CITI_reports"). + glob(f"262966_Portfolio_{d.strftime('%Y%m%d')}*")) + except StopIteration: + raise FileNotFoundError(f"CITI file not found for date {d}") + return pd.read_excel(fname, skiprows=6, skipfooter=2) def get_dawn_trades(): @@ -335,11 +343,6 @@ def gs_collateral(d, dawn_trades): 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.Amount *= -1 df = df.append({'Strategy': "M_CSH_CASH", 'Amount': -collateral - df.Amount.sum(), @@ -348,6 +351,22 @@ def gs_collateral(d, dawn_trades): return df +def citi_collateral(d, dawn_trades): + df = load_citi_file(d) + df = df[['Trade ID', 'Market Value', 'BasicAmt']] + df = df.merge(dawn_trades, how='left', left_on='Trade ID', right_on='cpty_id') + df = df.groupby('folder').sum() + df = df.sum(axis=1).to_frame(name='Amount') + df['Currency'] = 'USD' + df = df.reset_idnex() + df.columns = ['Strategy', 'Amount', 'Currency'] + df.amount *=1 + df = df.append({"Strategy": "M_CSH_CASH", + "Amount": - collateral - df.Amount.sum(), + "Currency": "USD"}, ignore_index=True) + return df + + def send_email(df_ms, df_baml, df_gs): pd.set_option('display.float_format', '{:.2f}'.format) content = HTMLBody('<html><body>' @@ -374,6 +393,7 @@ if __name__ == "__main__": #download_sftp_files(d) download_baml_files() dawn_trades = get_dawn_trades() + df_citi = citi_collateral(d, dawn_trades) try: df_ms = ms_collateral(d, dawn_trades) except FileNotFoundError as e: |