diff options
Diffstat (limited to 'python/collateral/cs.py')
| -rw-r--r-- | python/collateral/cs.py | 29 |
1 files changed, 28 insertions, 1 deletions
diff --git a/python/collateral/cs.py b/python/collateral/cs.py index ae25d0db..5850aaa8 100644 --- a/python/collateral/cs.py +++ b/python/collateral/cs.py @@ -1,6 +1,9 @@ import datetime import pandas as pd +import pathlib +import subprocess from . import DAILY_DIR +from bs4 import BeautifulSoup from operator import itemgetter from pandas.tseries.offsets import BDay from xlrd import open_workbook @@ -38,6 +41,22 @@ def download_files(count=20): p.write_bytes(attach.content) +def load_pdf(file_path: pathlib.Path): + proc = subprocess.run( + ["pdftohtml", "-xml", "-l", "1", "-stdout", "-i", file_path.as_posix()], + capture_output=True, + ) + soup = BeautifulSoup(proc.stdout, features="lxml") + l = soup.findAll("text") + for e in l: + if e.text.startswith( + "Market Value of Collateral required pursuant to this notice" + ): + sib = e.next_siblings + next(sib) + return float(next(sib).text.replace(",", "")) + + def get_collateral(d): DATA_DIR = DAILY_DIR / "CS_reports" # get most recent file before current date @@ -46,12 +65,20 @@ def get_collateral(d): files = ((f, get_date(f)) for f in DATA_DIR.glob("*.xls")) files = sorted(filter(lambda t: t[1] <= d, files), key=itemgetter(1), reverse=True) + excel_file, date = files[0] + pdf_file = DATA_DIR / f"{date} Margin_Notice161 Serenitas CGMF RVM.pdf" + last_margin_call = load_pdf(pdf_file) + if date == d: # margin call is current do not include + last_margin_call = 0 wb = open_workbook(files[0][0]) s = wb.sheet_by_index(0) i = 0 + im = 0.0 for i, v in enumerate(s.col_values(0)): + if s.cell_value(i, 4) == "Total IM (USD):": + im = s.cell_value(i, 5) if v.startswith("Total Value of Collateral"): - return s.cell_value(i, 1) + 900_000 + return s.cell_value(i, 1) + last_margin_call + im if v.startswith("No Positions to Report"): return 0.0 |