1 files changed, 53 insertions, 7 deletions
diff --git a/python/baml_fcm_fx.py b/python/baml_fcm_fx.py
index 2ba8fef0..70f75aad 100644
--- a/python/baml_fcm_fx.py
+++ b/python/baml_fcm_fx.py
@@ -1,8 +1,7 @@
 import datetime
-from serenitas.utils.db import dawn_engine
 from citco_ops.utils import BamlFcmNotify
-import pandas as pd
-from exchangelib import HTMLBody
+from serenitas.utils.db2 import dbconn
+from tabulate import tabulate
 
 if __name__ == "__main__":
     import argparse
@@ -16,7 +15,54 @@ if __name__ == "__main__":
         help="working date",
     )
     args = parser.parse_args()
-    sql_str = "SELECT trade_date, near_rate, near_settle_date, near_buy_currency, near_buy_amount, near_sell_currency, near_sell_amount FROM fx_swaps fs2 WHERE cash_account ='V0NSCLMSPT' AND trade_date = %s;"
-    df = pd.read_sql_query(sql_str, con=dawn_engine, params=(args.date,))
-    if not df.empty:
-        BamlFcmNotify.email_fcm(args.date, HTMLBody(df.to_html(index=False)))
+    sql_str = (
+        "SELECT settle_date, buy_currency, buy_amount, "
+        "sell_currency, sell_amount, spot_rate FROM spots "
+        "WHERE cash_account = 'V0NSCLMSPT' AND trade_date = %s"
+    )
+    dawndb = dbconn("dawndb")
+    with dawndb.cursor() as c:
+        c.execute(sql_str, (args.date,))
+        rec = next(iter(c))
+    if rec:
+        if rec.buy_currency == "EUR":
+            data = [
+                "6MZ20049",
+                "EUR",
+                -rec.buy_amount,
+                "USD",
+                rec.spot_rate,
+                rec.sell_amount,
+                "Buy",
+                rec.settle_date,
+            ]
+        else:
+            data = [
+                "6MZ20049",
+                "EUR",
+                rec.sell_amount,
+                "USD",
+                rec.spot_rate,
+                rec.buy_amount,
+                "Sell",
+                rec.settle_date,
+            ]
+        num_format = [("{0:,.2f}", 2), ("{0:.5f}", 4), ("{0:,.2f}", 5)]
+        for f, i in num_format:
+            data[i] = f.format(data[i])
+
+        t = tabulate(
+            [data],
+            headers=[
+                "account",
+                "curr",
+                "TotBal",
+                "HomeCurrency",
+                "fxRate",
+                "convTotBal",
+                "BuySell",
+                "Value Date",
+            ],
+            tablefmt="unsafehtml",
+        )
+        BamlFcmNotify.email_fcm(args.date, t)