diff options
| -rw-r--r-- | python/collateral/__main__.py | 5 | ||||
| -rw-r--r-- | python/collateral/common.py | 2 | ||||
| -rw-r--r-- | python/collateral/jpm.py | 58 |
3 files changed, 43 insertions, 22 deletions
diff --git a/python/collateral/__main__.py b/python/collateral/__main__.py index f10ed8a2..344e9280 100644 --- a/python/collateral/__main__.py +++ b/python/collateral/__main__.py @@ -57,7 +57,10 @@ cp_dict = { "isda_cps": ("citi", "baml_isda", "ms", "gs", "bnp", "cs"), }, "Brinker": {"fcms": (), "isda_cps": ("ms", "gs")}, - "BowdSt": {"fcms": ("gs_fcm",), "isda_cps": ("ms", "bnp", "gs", "baml_isda", "cs")}, + "BowdSt": { + "fcms": ("gs_fcm",), + "isda_cps": ("ms", "bnp", "gs", "baml_isda", "cs", "jpm"), + }, } diff --git a/python/collateral/common.py b/python/collateral/common.py index d6dc958e..8e6ec4ab 100644 --- a/python/collateral/common.py +++ b/python/collateral/common.py @@ -57,7 +57,7 @@ def get_bilateral_trades(d: datetime.date, fund: str, engine: Engine) -> pd.Data df_cds = pd.read_sql_query( "SELECT cpty_id, folder, initial_margin_percentage * abs(notional) / 100 as IA " "FROM list_cds(%s::date, %s) " - "WHERE cpty_id IS NOT NULL", + "WHERE orig_attach IS NOT NULL", engine, params=(d, fund), ) diff --git a/python/collateral/jpm.py b/python/collateral/jpm.py index e9f83f47..2e991f5c 100644 --- a/python/collateral/jpm.py +++ b/python/collateral/jpm.py @@ -6,16 +6,6 @@ from . import DAILY_DIR from .common import load_pdf, get_col, parse_num -def load_file(d, fund): - try: - fname = next( - (DAILY_DIR / fund / "JPM_reports").glob(f"CSCFTCSTMT-*-{d:%y%m%d}*.pdf") - ) - except StopIteration: - raise FileNotFoundError(f"JPM file not found for date {d}") - return pd.read_excel(fname, skiprows=6, skipfooter=2) - - paths = { # "Serenitas": ["NYops", "Margin Calls JPM"], "BowdSt": ["BowdoinOps", "Margin JPM"], @@ -34,30 +24,30 @@ def load_file(d, fund): return fname -def get_collateral(d: datetime.date, fund): - pdf_file = load_file(d, fund) - collat_page = load_pdf(pdf_file, pages=True)[3] +def get_collateral(collat_page): return float(get_col(collat_page, 200, 300, 1000, 1100)[0].replace(",", "")) -def load_positions(d: datetime.date, fund): - pdf_file = load_file(d, fund) - positions_page = load_pdf(pdf_file, pages=True)[4] +def load_positions(positions_page): anchor = next(c for c in positions_page if c.text.startswith("Total Product Group")) - bottom = int(anchor["top"]) + bottom = int(anchor["top"]) - 30 + print(bottom) widths = (10, 160, 300, 350, 450, 500, 550, 600, 650, 750, 850, 950, 1000, 1200) - cols = [get_col(positions_page, 200, 289, l, r) for l, r in zip(widths, widths[1:])] + cols = [ + get_col(positions_page, 200, bottom, l, r) for l, r in zip(widths, widths[1:]) + ] def combine(l): return [f"{l[0]} {l[1]}", *l[2:]] - cols = [combine(c) if len(c) == 4 else c for c in cols] + cols = [combine(c) if len(c) == (len(cols[0]) + 1) else c for c in cols] df = pd.DataFrame({c[0]: c[1:] for c in cols}) for col in ["Pay Notional", "Rec Notional", "MTM Amount", "IM Amount"]: df[col] = df[col].apply(parse_num) for col in ["Trade Date", "Maturity Date"]: df[col] = pd.to_datetime(df[col], format="%d-%b-%y") - df["Deal ID"] = df["Deal ID"].str.extract(r"[^-]-(.*)") + df["Deal ID"] = "810RI" + df["Deal ID"].str.extract(r"([^-]*)-.*") + df[["IM Amount"]] *= -1.0 return df @@ -74,3 +64,31 @@ def download_files(em, count=20, *, fund="BowdSt", **kwargs): stream = BytesIO(attach.content) pdf = Pdf.open(stream, password="tm64EO") pdf.save(p) + + +def collateral(d, dawn_trades, *, fund="BowdSt", **kwargs): + pdf_file = load_file(d, fund) + pages = load_pdf(pdf_file, pages=True) + collat = get_collateral(pages[3]) + df = load_positions(pages[4]) + df = df.merge(dawn_trades, how="left", left_on="Deal ID", right_on="cpty_id") + missing_ids = df.loc[df.cpty_id.isnull(), "Deal ID"] + if not missing_ids.empty: + raise ValueError(f"{missing_ids.tolist()} not in the database") + df = df[["folder", "MTM Amount", "IM Amount"]] + df = df.groupby("folder").sum() + df = df.sum(axis=1).to_frame(name="Amount") + df["Currency"] = "USD" + df = df.reset_index() + df.columns = ["Strategy", "Amount", "Currency"] + df.Amount *= -1 + df = df.append( + { + "Strategy": "M_CSH_CASH", + "Amount": collat - df.Amount.sum(), + "Currency": "USD", + }, + ignore_index=True, + ) + df["date"] = d + return df.set_index("Strategy") |