diff options
Diffstat (limited to 'python')
| -rw-r--r-- | python/collateral/baml_isda.py | 28 | ||||
| -rw-r--r-- | python/external_deriv_marks.py | 3 |
2 files changed, 28 insertions, 3 deletions
diff --git a/python/collateral/baml_isda.py b/python/collateral/baml_isda.py index badb5f0c..aaf5768c 100644 --- a/python/collateral/baml_isda.py +++ b/python/collateral/baml_isda.py @@ -4,10 +4,13 @@ from io import BytesIO import logging import pandas as pd import pathlib -from urllib.parse import urlsplit, parse_qs, urlunsplit, urljoin import requests +import xlrd import zipfile +from urllib.parse import urlsplit, parse_qs, urlunsplit, urljoin +from xlrd import open_workbook, xldate_as_tuple + logger = logging.getLogger(__name__) @@ -80,6 +83,27 @@ def download_files(d=None, count=20): p.write_bytes(attach.content) +def baml_load_excel(fname): + wb = open_workbook(fname) + s = wb.sheet_by_index(0) + headers = s.row_values(6, 0) + i = 7 + rows = [] + while s.cell(i, 0).value != "": + 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) + for col in ["Original Notional", "Notional"]: + df[col] = pd.to_numeric(df[col].str.replace(",", "")) + return df + + def collateral(d, dawn_trades, *args): REPORTS_DIR = DAILY_DIR / "BAML_ISDA_reports" try: @@ -91,7 +115,7 @@ def collateral(d, dawn_trades, *args): collateral = float(df.Notional) fname = REPORTS_DIR / f"Interest Rates Trade Summary_{d:%d-%b-%Y}.xls" # TODO: make more robust - df = pd.read_excel(fname, skiprows=6, nrows=1) + df = baml_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") diff --git a/python/external_deriv_marks.py b/python/external_deriv_marks.py index 4f6eb3bf..30a4c3db 100644 --- a/python/external_deriv_marks.py +++ b/python/external_deriv_marks.py @@ -2,6 +2,7 @@ import datetime import pandas as pd import re from env import DAILY_DIR +from risk.baml_isda import baml_load_excel def gs_navs(date: datetime.date = None): @@ -81,7 +82,7 @@ def baml_navs(date: datetime.date = None): f"Interest Rates Trade Summary_{glob_str}.xls" ): date = datetime.datetime.strptime(fname.stem.split("_")[1], "%d-%b-%Y") - df = pd.read_excel(fname, skiprows=6, nrows=3) + df = baml_load_excel(fname) df = df.set_index("Trade ID") df = df[["Trade Date", "Flow Direction", "Notional", "MTM(USD)"]] df.columns = ["trade_date", "buy/sell", "notional", "nav"] |