1 files changed, 90 insertions, 0 deletions
diff --git a/python/collateral/common.py b/python/collateral/common.py
new file mode 100644
index 00000000..de63f35e
--- /dev/null
+++ b/python/collateral/common.py
@@ -0,0 +1,90 @@
+import logging
+import pandas as pd
+from exchangelib import HTMLBody
+from . import ExchangeMessage
+
+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 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 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 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):
+    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 = "<html><body>"
+    for cp, name in cp_mapping.items():
+        html += f"<h3> At {name}:</h3>\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"],
+    )