1 files changed, 82 insertions, 0 deletions
diff --git a/python/notify_bowdst.py b/python/notify_bowdst.py
new file mode 100644
index 00000000..25b22ac0
--- /dev/null
+++ b/python/notify_bowdst.py
@@ -0,0 +1,82 @@
+from serenitas.utils.db import dbconn
+import pandas as pd
+from pandas.tseries.offsets import BDay
+import datetime
+from io import StringIO
+from exchangelib import FileAttachment
+from serenitas.utils.exchange import ExchangeMessage
+
+conn = dbconn("dawndb")
+cob = (datetime.date.today() - BDay(1)).date()
+dates = [str((cob - datetime.timedelta(days=days))) for days in range(60)]
+# dates = [str((cob - BDay(days)).date()) for days in range(60)]
+df_rates = pd.concat(
+    {
+        date: pd.read_sql_query(
+            f"""SELECT *, notional * factor as db_notional FROM list_cds_marks( %(date)s , NULL, 'BOWDST');""",
+            conn,
+            params={"date": str(date)},
+        )
+        for date in dates
+    }
+)
+df = df_rates.droplevel(level=1)
+data = df[df.index == str(cob)][
+    ["tenor", "security_desc", "security_id", "globeop_notional", "db_notional"]
+]
+inaccurate_balances = data[data["db_notional"] != data["globeop_notional"]]
+
+
+for row in inaccurate_balances.itertuples():
+    subject = (
+        f"ACTION REQUESTED: Missing Trades {row.security_desc} RED: {row.security_id}"
+    )
+
+    new_df = df[df["security_desc"] == row.security_desc]
+    try:
+        data = new_df[new_df["globeop_notional"] == new_df["db_notional"]].iloc[0, :]
+        security_desc = data.security_desc
+        date = data.name
+        globeop_notional = data.globeop_notional
+    except IndexError:
+        print("No matches")
+        continue
+
+    buf = StringIO()
+    csv = pd.read_sql_query(
+        f"""SELECT * FROM cds WHERE trade_date > %(date)s and fund = 'BOWDST' and security_desc = %(security_desc)s and orig_detach is null and orig_attach is null order by trade_date desc""",
+        conn,
+        params={"date": date, "security_desc": security_desc},
+    )
+    if len(csv[pd.to_datetime(csv["trade_date"]) >= (cob - BDay(1))].index) != 0:
+        print(len(csv[pd.to_datetime(csv["trade_date"]) >= (cob - BDay(1))].index))
+        continue
+    csv.to_csv(buf)
+    attachments = [
+        FileAttachment(name=f"{security_desc}.csv", content=buf.getvalue().encode())
+    ]
+    buf.close()
+    msg = (
+        f"Good morning!\n\n"
+        f"We notice a difference in our notional ({row.db_notional:,.2f}) versus yours ({row.globeop_notional:,.2f}) "
+        f"for security {row.security_desc} RED: {row.security_id}.\n"
+        f"Our notionals last matched on {date} for ${globeop_notional:,.2f}.\n\n"
+        f"Please see attached all the trades since we last matched. Please ensure they match your data.\n\n"
+        f"Thanks for your help!\n\n"
+        f"Flint"
+    )
+    em = ExchangeMessage()
+    em.send_email(
+        subject,
+        msg,
+        to_recipients=("fyu@lmcg.com",),
+        # to_recipients=(
+        #     "caagtradecapture@bnymellon.com",
+        #     "hm-operations@bnymellon.com",
+        #     "caagprim@bnymellon.com",
+        #     "julie.picariello@bnymellon.com",
+        #     "sa1futures.optionsprocessing@bnymellon.com"
+        # ),
+        # cc_recipients=("fyu@lmcg.com", "Bowdoin-Ops@LMCG.com",),
+        attach=attachments,
+    )