1 files changed, 57 insertions, 0 deletions
diff --git a/python/bondallocation_email.py b/python/bondallocation_email.py
new file mode 100644
index 00000000..b05964ea
--- /dev/null
+++ b/python/bondallocation_email.py
@@ -0,0 +1,57 @@
+from serenitas.utils.db import dbconn
+from serenitas.utils.exchange import ExchangeMessage, FileAttachment
+import pandas as pd
+from io import StringIO
+import datetime
+import argparse
+
+if __name__ == "__main__":
+    parser = argparse.ArgumentParser()
+    parser.add_argument(
+        "trade_date", type=datetime.date.fromisoformat, default=datetime.date.today()
+    )
+    args = parser.parse_args()
+    dawndb = dbconn("dawndb")
+
+    alert = {
+        "SERCGMAST": "Serenitas Credit Gamma Master Fund",
+        "BOWDST": "Bowdoin St. (ALERT acronym: LMUNDER, Account: SER003)",
+        "BRINKER": "Brinker Destinations Multi-Strategy Alternatives - LMCG",
+    }
+
+    df = pd.read_sql_query(
+        "SELECT bonds.id as tradeid, trade_date, settle_date, CASE WHEN buysell THEN 'Buy' ELSE 'Sell' end as buysell, identifier, description , faceamount AS notional, price, accrued, principal_payment + accrued_payment as net_amount, name FROM bonds LEFT JOIN counterparties on cp_code=code where id=2014 and trade_date = %s",
+        con=dawndb,
+        params=(args.trade_date,),
+    )
+    for row in df.itertuples():
+        with dawndb.cursor() as d:
+            d.execute(
+                f"SELECT tradeid, notional, fund FROM bond_allocation LEFT JOIN accounts a  USING (code) WHERE account_type='Cash' AND active AND tradeid={row.tradeid};"
+            )
+            em = ExchangeMessage()
+            subject = f"LMCG - Trade Allocations - TD {row.trade_date} - {row.name}"
+            recipients = ("fyu@lmcg.com",)
+            cc_recipients = ("fyu@lmcg.com",)
+            allocations = "\n\t".join(
+                [f"- {row.notional:,.0f} to {alert[row.fund]}" for row in d]
+            )
+            body = (
+                f"For {row.identifier} please allocate :\n\n\t"
+                + allocations
+                + "\n\n\nThanks,\nFlint"
+            )
+            buf = StringIO()
+            df[df["tradeid"] == row.tradeid].to_csv(buf)
+            em.send_email(
+                subject=subject,
+                to_recipients=recipients,
+                cc_recipients=cc_recipients,
+                body=body,
+                attach=[
+                    FileAttachment(
+                        name=f"{row.trade_date}-{row.name}-{row.identifier}.csv",
+                        content=buf.getvalue().encode(),
+                    )
+                ],
+            )