1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
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 this 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' in locals() 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)
|
