import logging import pandas as pd from exchangelib import HTMLBody logger = logging.getLogger(__name__) def compare_notionals(df, positions, fcm: str): 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 ] 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, engine): df_cds = pd.read_sql_query( "SELECT cpty_id, folder, initial_margin_percentage * notional / 100 as IA " "FROM cds " "WHERE cpty_id IS NOT NULL AND trade_date <= %s", 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": { "IGREC": "COCSH", "IGPAYER": "COCSH", "HYPAYER": "COCSH", "HYREC": "COCSH", "STEEP": "IRDEVCSH", "FLAT": "IRDEVCSH", "MBSCDS": "MBSCDSCSH", "IGMEZ": "TCSH", "IGSNR": "TCSH", "IGEQY": "TCSH", "HYMEZ": "TCSH", "HYEQY": "TCSH", "BSPK": "TCSH", } } ) return df def send_email(d, df): 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", } html = "" for cp, name in cp_mapping.items(): 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"], )