import datetime import logging import subprocess from bs4 import BeautifulSoup import pandas as pd from exchangelib import HTMLBody from sqlalchemy.engine import Engine logger = logging.getLogger(__name__) CASH_STRATEGY_MAPPING = { "COCSH": ["IGREC", "IGPAYER", "HYPAYER", "HYREC", "HYOPTDEL", "IGOPTDEL"], "IRDEVCSH": ["STEEP", "FLAT"], "TCSH": [ "IGMEZ", "IGSNR", "IGEQY", "HYMEZ", "HYEQY", "BSPK", "XOMEZ", "IGINX", "HYINX", "XOINX", ], "MBSCDSCSH": ["HEDGE_MBS", "MBSCDS"], "MACCDSCSH": ["HEDGE_MAC"], "SER_ITRXCVCSH": ["SER_ITRXCURVE"], "SER_IGCVECSH": ["SER_IGCURVE"], } STRATEGY_CASH_MAPPING = {e: k for k, v in CASH_STRATEGY_MAPPING.items() for e in v} def compare_notionals(df: pd.DataFrame, positions: pd.DataFrame, fcm: str) -> None: check_notionals = ( positions.groupby(level=["security_id", "maturity"])[["notional"]] .sum() .join(df["NOTIONAL"], how="left") ) diff_notionals = check_notionals[ (check_notionals.notional != check_notionals.NOTIONAL) & (check_notionals.notional != 0.0) ] if not diff_notionals.empty: logger.error(f"Database and {fcm} FCM know different notionals") for t in diff_notionals.itertuples(): logger.error( f"{t.Index[0]}\t{t.Index[1].date()}\t{t.notional}\t{t.NOTIONAL}" ) def get_dawn_trades(d: datetime.date, engine: Engine) -> pd.DataFrame: df_cds = pd.read_sql_query( "SELECT cpty_id, folder, initial_margin_percentage * abs(notional) / 100 as IA " "FROM list_cds(%s::date) " "WHERE cpty_id IS NOT NULL", engine, params=(d,), ) df_swaptions = pd.read_sql_query( "SELECT cpty_id, folder, initial_margin_percentage * notional / 100 AS IA " "FROM swaptions " "WHERE cpty_id IS NOT NULL " "AND trade_date <= %s", engine, params=(d,), ) df_caps = pd.read_sql_query( "SELECT cpty_id, folder, initial_margin_percentage * amount / 100 AS IA " "FROM capfloors " "WHERE cpty_id IS NOT NULL " "AND trade_date <= %s", engine, params=(d,), ) df = pd.concat([df_cds, df_swaptions, df_caps]) df = df.replace({"folder": STRATEGY_CASH_MAPPING}) return df def send_email(d: datetime.date, df: pd.DataFrame) -> None: from exchange import ExchangeMessage pd.set_option("display.float_format", "{:.2f}".format) df = df.drop("date", axis=1).set_index("broker") cp_mapping = { "CITI": "Citi", "MS": "Morgan Stanley", "GS": "Goldman Sachs", "BAML_FCM": "Baml FCM", "BAML_ISDA": "Baml OTC", "WELLS": "Wells Fargo", "BNP": "BNP Paribas", "CS": "Credit Suisse", } html = "" for cp, df in df.groupby(level="broker"): name = cp_mapping[cp] html += f"

At {name}:

\n{df.loc[cp].to_html(index=False)}" em = ExchangeMessage() em.send_email( f"IAM booking {d:%Y-%m-%d}", HTMLBody(html), ["serenitas.otc@sscinc.com"], ["nyops@lmcg.com"], ) def load_pdf(file_path): proc = subprocess.run( ["pdftohtml", "-xml", "-stdout", "-i", file_path.as_posix()], capture_output=True, ) soup = BeautifulSoup(proc.stdout, features="lxml") l = soup.findAll("text") l = sorted(l, key=lambda x: (int(x["top"]), int(x["left"]))) return l def get_col(l, top, bottom, left, right): return [ c.text for c in l if int(c["left"]) >= left and int(c["left"]) < right and int(c["top"]) >= top and int(c["top"]) < bottom ]