1 files changed, 26 insertions, 2 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")