diff options
| -rw-r--r-- | python/collateral_calc.py | 39 |
1 files changed, 27 insertions, 12 deletions
diff --git a/python/collateral_calc.py b/python/collateral_calc.py index 7dd218d2..1c6e013c 100644 --- a/python/collateral_calc.py +++ b/python/collateral_calc.py @@ -45,21 +45,25 @@ def download_files(d=None, ftp.retrbinary('RETR ' + f, fh.write) -def download_ms_emails(): +def download_ms_emails(count=20): emails = get_msgs(path=["NYops"], subject_filter="SERCX **Daily", - count=20) + count=count) DATA_DIR = DAILY_DIR / "MS_reports" for msg in emails: for attach in msg.attachments: if 'NETSwaps' in attach.name: - fname = attach.name.split("_")[1] - with open(DATA_DIR / fname, "wb") as fh: - fh.write(attach.content) + fname = "Trade_Detail_" + attach.name.split("_")[1] + elif 'NET_Collateral' in attach.name: + fname = "Collateral_Detail_" + attach.name.rsplit("_", 1)[1] + else: + continue + with open(DATA_DIR / fname, "wb") as fh: + fh.write(attach.content) -def download_gs_emails(): +def download_gs_emails(count=20): emails = get_msgs(path=["NYops", "Margin calls"], subject_filter="Regulatory VM Margin", - count=20) + count=count) DATA_DIR = DAILY_DIR / "GS_reports" for msg in emails: for attach in msg.attachments: @@ -123,7 +127,9 @@ def sg_collateral(d): def ms_collateral(d): - df = pd.read_excel(DAILY_DIR / "MS_reports" / f"{d:%Y%m%d}.xls" ) + 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" ) d = {'TRCDX': 'IGTCDSCSH', 'ABINT': 'MBSCDSCSH', 'ACSWN': 'IRDEVCSH', @@ -134,16 +140,22 @@ def ms_collateral(d): 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", -7_490_000 - acc, "USD"]) + r.append(["M_CSH_CASH", -collat - acc, "USD"]) return pd.DataFrame.from_records(r, columns=['Strategy', 'Amount', 'Currency']) -def gs_collateral(d): +def load_gs_file(d, pattern): try: fname = next( (DAILY_DIR / "GS_reports"). - glob(f"Trade_Detail*{d.strftime('%d_%b_%Y')}*")) + glob(f"{pattern}*{d.strftime('%d_%b_%Y')}*")) except StopIteration: - raise IOError(f"GS trade file not found for date {d}") + raise IOError(f"GS {pattern} file not found for date {d}") df = pd.read_excel(fname, skiprows=9, skipfooter=77) + return df + +def gs_collateral(d): + 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.sum(axis=1).to_frame(name='Amount') @@ -153,6 +165,9 @@ def gs_collateral(d): df.Strategy = df.Strategy.replace({'SWAP_CDINDT': 'IGTCDSCSH', 'GENERIC_IRSIRD': 'IRDEVCSH'}) 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(account, df_ms, df_sg, df_gs): |