diff options
Diffstat (limited to 'python/collateral')
| -rw-r--r-- | python/collateral/baml_isda.py | 39 |
1 files changed, 33 insertions, 6 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: |