1 files changed, 109 insertions, 0 deletions
diff --git a/python/fcm_fx.py b/python/fcm_fx.py
new file mode 100644
index 00000000..dd53ea39
--- /dev/null
+++ b/python/fcm_fx.py
@@ -0,0 +1,109 @@
+import datetime
+from serenitas.utils.db2 import dbconn
+from tabulate import tabulate
+from report_ops.misc import _recipients, _cc_recipients
+from serenitas.utils.exchange import ExchangeMessage
+from exchangelib import HTMLBody
+
+_account_alias = {"V0NSCLMSPT": "6MZ20049"}
+
+
+def email_fcm(em, d, fund):
+    account = _account_alias.get(d["cash_account"], d["cash_account"])
+    match d["cp_code"]:
+        case "BAMSNY":
+            recipients = _recipients["BAML_FCM"]
+        case "GOLDNY":
+            recipients = _recipients["GS_FCM"]
+            additional_instructions = f"Could you please instruct moves from DCS 057575201 to Futures {account} to settle thie trade? "
+    em.send_email(
+        f"FX Details: {account} Trade Date: {d['trade_date']}",
+        HTMLBody(
+            f"""
+<html>
+  <head>
+    <style>
+      table, th, td {{ border: 1px solid black;  border-collapse: collapse;}}
+      th, td {{ padding: 5px; }}
+    </style>
+  </head>
+  <body>
+  Hello,<br><br>Please see below details for an FX Spot Trade we did with the desk today for account {account}. {additional_instructions if additional_instructions else ""}Please let me know if you need more information.<br><br>{to_tabulate(d)}
+  </body>
+</html>"""
+        ),
+        to_recipients=recipients,
+        cc_recipients=_cc_recipients[fund],
+    )
+
+
+def to_tabulate(d):
+    if d["sell_currency"] == "USD":
+        key1, key2 = "buy", "sell"
+    else:
+        key1, key2 = "sell", "buy"
+    account = _account_alias.get(d["cash_account"], d["cash_account"])
+    line = [
+        account,
+        d[f"{key1}_currency"],
+        d[f"{key1}_amount"],
+        d[f"{key2}_currency"],
+        d["spot_rate"],
+        d[f"{key2}_amount"],
+        "Buy" if d["sell_currency"] == "USD" else "Sell",
+        d["settle_date"],
+    ]
+
+    num_format = [("{0:,.2f}", 2), ("{0:.5f}", 4), ("{0:,.2f}", 5)]
+    for f, i in num_format:
+        line[i] = f.format(line[i])
+    t = tabulate(
+        [line],
+        headers=[
+            "account",
+            "curr",
+            "TotBal",
+            "HomeCurrency",
+            "fxRate",
+            "convTotBal",
+            "BuySell",
+            "Value Date",
+        ],
+        tablefmt="unsafehtml",
+    )
+    return t
+
+
+def main(trade_date, conn, fund, em):
+    with conn.cursor() as c:
+        c.execute(
+            "SELECT spots.cash_account, buy_currency, sell_currency, buy_amount, sell_amount, spot_rate, settle_date, trade_date, spots.cp_code FROM spots "
+            "LEFT JOIN accounts2 USING (cash_account) "
+            "WHERE account_type in ('Fcm', 'Future') AND spots.cp_code in ('BAMSNY', 'GOLDNY') AND spots.trade_date =%s AND spots.fund=%s",
+            (trade_date, fund),
+        )
+        for rec in c:
+            d = rec._asdict()
+            email_fcm(em, d, fund)
+
+
+if __name__ == "__main__":
+    import argparse
+
+    parser = argparse.ArgumentParser()
+    parser.add_argument(
+        "date",
+        nargs="?",
+        type=datetime.date.fromisoformat,
+        default=datetime.date.today(),
+        help="working date",
+    )
+    args = parser.parse_args()
+    conn = dbconn("dawndb")
+    em = ExchangeMessage()
+    for fund in (
+        "SERCGMAST",
+        "ISOSEL",
+        "BOWDST",
+    ):
+        main(args.date, conn, fund, em)