1 files changed, 56 insertions, 0 deletions
diff --git a/python/margin_estimates.py b/python/margin_estimates.py
new file mode 100644
index 00000000..bd0f5647
--- /dev/null
+++ b/python/margin_estimates.py
@@ -0,0 +1,56 @@
+from serenitas.utils.db import dbconn
+from serenitas.utils.exchange import ExchangeMessage, FileAttachment
+from exchangelib import HTMLBody
+import datetime
+import argparse
+from pandas.tseries.offsets import BDay
+from io import StringIO
+
+if __name__ == "__main__":
+    parser = argparse.ArgumentParser()
+    parser.add_argument(
+        "trade_date",
+        nargs="?",
+        default=(datetime.date.today() - BDay(1)).date(),
+        type=datetime.date.fromisoformat,
+    )
+    args = parser.parse_args()
+    dawndb = dbconn("dawndb")
+
+    buf = StringIO()
+    buf.write("<html><body>\n")
+
+    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",
+        "JPM": "JP Morgan",
+    }
+
+    with dawndb.cursor() as c:
+        buf.write(
+            f"<h3> Collateral Estimates Receive/(Pay):</h3>\n\n<ol style='list-style-type:upper-roman'>"
+        )
+        c.execute(
+            "SELECT date, broker, fund, sum(amount) as amount FROM strategy_im si WHERE strategy='CSH_CASH' and date=%s and fund='SERCGMAST' GROUP BY broker, date, fund ORDER BY date DESC;",
+            (args.trade_date,),
+        )
+        for row in c:
+            amount = (
+                f"{-row.amount:,.0f}" if row.amount <= 0 else f"({row.amount:,.0f})"
+            )
+            buf.write(f"<li>{cp_mapping[row.broker]}: {amount} USD</li>")
+
+    em = ExchangeMessage()
+    buf.write("</body/></html>")
+    em.send_email(
+        f"Collateral Estimates {args.trade_date:%Y-%m-%d}",
+        HTMLBody(buf.getvalue()),
+        ["NYOps@lmcg.com"],
+        ["fyu@lmcg.com"],
+    )