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
|
import datetime
from io import StringIO
from exchangelib import HTMLBody, FileAttachment
import pandas as pd
from serenitas.utils.db import dawn_engine
from report_ops.utils import Monitor
from report_ops.misc import _recon_recipients, _cc_recipients
class TrancheCashflowsEmails(
Monitor,
headers=(
"settle_date",
"dealid",
"counterparty_id",
"counterparty_name",
"principal",
"accrued",
"total_cashflow",
"currency",
),
num_format=[
("{0:,.2f}", 4),
("{0:,.2f}", 5),
("{0:,.2f}", 6),
],
):
@classmethod
def email(cls, date, fund, attachment):
if not cls._staging_queue:
return
cls._em.send_email(
f"Projected Tranche Cashflows {fund}: {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>Could you please confirm you see the same tranche cashflows as us for {date}? Excel file attached for your convenience<br><br>{cls.to_tabulate()}
</body>
</html>"""
),
to_recipients=_recon_recipients[fund],
cc_recipients=_cc_recipients[fund],
attach=[attachment],
)
def email_cashflow_amounts(date, fund):
sql_query = "SELECT dealid, cpty_id as counterparty_id, name as counterparty_name, coalesce(principal,0) as principal, coalesce(accrued,0) as accrued, coalesce(total_cashflow,0) as total_cashflow, currency FROM tranche_cashflow_settlements WHERE settle_date=%s AND fund=%s"
df = pd.read_sql_query(sql_query, params=(date, fund), con=dawn_engine)
buf = StringIO()
for row in df.itertuples():
d = row._asdict() | {"settle_date": date, "fund": fund}
TrancheCashflowsEmails.stage(d)
df.to_csv(buf, index=False)
attachment = FileAttachment(
name=f"tranche_cashflows_{fund}_{date}.csv", content=buf.getvalue().encode()
)
TrancheCashflowsEmails.email(date, fund, attachment)
TrancheCashflowsEmails.clear()
if __name__ == "__main__":
import argparse
"""Notify counterparties about tranche payment settlements. Must add in the argument for the settlement date as it is in the future"""
parser = argparse.ArgumentParser()
parser.add_argument(
"date",
type=datetime.date.fromisoformat,
help="settlement date",
)
args = parser.parse_args()
for fund in (
"SERCGMAST",
"BOWDST",
"ISOSEL",
):
email_cashflow_amounts(args.date, fund)
|
