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
|
from serenitas.utils.db import dbconn
from serenitas.utils.exchange import ExchangeMessage, FileAttachment
from exchangelib import HTMLBody
import pandas as pd
from io import StringIO
import datetime
import argparse
if __name__ == "__main__":
parser = argparse.ArgumentParser()
parser.add_argument(
"--trade_date", type=datetime.date.fromisoformat, default=datetime.date.today()
)
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",
}
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;",
con=dawndb,
params=(args.trade_date,),
)
with dawndb.cursor() as d:
for row in df.itertuples():
d.execute(
f"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 = ("fyu@lmcg.com", "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"])
.round(2)
)
bondtrade.to_csv(buf, index=False)
body = (
f"<p>For {row.identifier} please allocate :<br><br><ul>"
+ "".join(allocations)
+ "</ul><br><br>Trade Details are attached in csv."
+ "<br><br><br>Thanks,<br>Flint<br></p><br>"
+ bondtrade.to_html(index=False)
)
em.send_email(
subject=subject,
to_recipients=row.allocation_contacts
if row.allocation_contacts
else ("fyu@lmcg.com,"),
# to_recipients=("fyu@lmcg.com",),
cc_recipients=cc_recipients,
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 = %s", (row.tradeid,))
|
