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
|
import datetime
import argparse
import pandas as pd
from pandas.tseries.offsets import BDay
from io import StringIO
from exchangelib import HTMLBody
from serenitas.utils.db import dawn_engine, dbconn
from serenitas.utils.exchange import ExchangeMessage, FileAttachment
if __name__ == "__main__":
parser = argparse.ArgumentParser()
parser.add_argument(
"--trade_date",
type=datetime.date.fromisoformat,
default=(datetime.date.today() - BDay(1)).date(),
)
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",
"ISOSEL": "ISO Selene (ALERT acronym: LMUNDER, Account: SER004)",
}
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 as counterparty, allocation_contacts FROM bonds LEFT JOIN counterparties on cp_code=code WHERE trade_date >= %s and not EMAILED and allocated AND ctm_code is NULL;",
con=dawn_engine,
params=(args.trade_date,),
)
with dawndb.cursor() as d:
formatters = {
"net_amount": "{:.2f}".format,
"price": "{:.4f}".format,
"accrued": "{:.8f}".format,
"notional": "{:.0f}".format,
}
for row in df.itertuples():
d.execute(
"SELECT tradeid, notional, fund FROM bond_allocation "
"LEFT JOIN accounts a USING (code) WHERE account_type='Cash' "
"AND active AND tradeid=%s;",
(row.tradeid,),
)
em = ExchangeMessage()
subject = (
f"LMCG - Trade Allocations - TD {row.trade_date} - {row.counterparty}"
)
cc_recipients = ("NYOps@lmcg.com",)
allocations = [
"<li>" + f" {row.notional:,.0f} to {alert[row.fund]}" + "</li>"
for row in d
]
buf = StringIO()
bondtrade = df[df["tradeid"] == row.tradeid].drop(
columns=["allocation_contacts", "tradeid"]
)
bondtrade.to_csv(buf, index=False)
body = (
f"<p>For {row.identifier} please allocate :<br><br><ul>"
+ "".join(allocations)
+ f"</ul><br><i>Please confirm net amount of {row.net_amount:.2f}.</i>"
+ "<br><br><br>Thanks,<br>Flint<br></p><br>"
+ bondtrade.to_html(index=False, formatters=formatters)
)
if not row.allocation_contacts:
raise Exception(
f"There are no contacts for {row.trade_date} - {row.counterparty}"
)
em.send_email(
subject=subject,
to_recipients=row.allocation_contacts,
cc_recipients=cc_recipients,
reply_to=("NYOps@lmcg.com",),
body=HTMLBody(body),
attach=[
FileAttachment(
name=f"{row.trade_date}-{row.counterparty}-{row.identifier}.csv",
content=buf.getvalue().encode(),
)
],
)
d.execute(
"UPDATE bonds SET emailed = True WHERE id = %(tradeid)s",
{"tradeid": row.tradeid},
)
dawndb.commit()
|
