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
110
111
112
|
import datetime
from exchangelib import HTMLBody
from serenitas.utils.db import dbconn
from serenitas.analytics.dates import bus_day
from report_ops.utils import Monitor
cp_contacts = {
"GOLDNY": (
"Patrick.Bousky@gs.com",
"CreditDerivativesSet@ny.email.gs.com",
),
"MSCSNY": (
"nafidset@morganstanley.com",
"Farzad.Taheri@morganstanley.com",
),
}
class BBHSettlementsEmail(
Monitor,
headers=(
"settle_date",
"serenitas_id",
"counterparty_id",
"counterparty",
"payment_amount",
"currency",
"asset_class",
),
num_format=[("{0:,.2f}", 4)],
):
@classmethod
def email(cls, cp_name, cp_contacts, settlement_date, net_amount, currency):
if not cls._staging_queue:
return
cls._em.send_email(
f"{cp_name} Settlements: Brinker Destinations Fund {settlement_date}",
HTMLBody(
f"""
<html>
<head>
<style>
table, th, td {{ border: 1px solid black; border-collapse: collapse;}}
th, td {{ padding: 5px; }}
</style>
</head>
<body>
Good morning Team<br><br>We're seeing the following settlements for the Brinker Destinations Fund. Please confirm you will {'receive' if net_amount < 0 else 'pay'} net {net_amount:,.2f} {currency}:<br><br>{cls.to_tabulate()}
</body>
</html>"""
),
to_recipients=cp_contacts,
cc_recipients=("nyops@lmcg.com",),
)
def check_trade_settlements(date, fund, conn):
with conn.cursor() as c, conn.cursor() as b:
c.execute(
"SELECT name, cp_code, asset_class, currency, ids FROM payment_settlements ps WHERE settle_date =%s AND fund=%s AND asset_class NOT IN ('BOND');",
(date, fund),
)
for row in c:
payment_details = {
"settle_date": date,
"counterparty": row.cp_code,
"currency": row.currency,
"asset_class": row.asset_class,
}
net_amount = 0
match row.asset_class:
case "TRANCHE_CF":
sql_query = "SELECT cds.dealid as serenitas_id, cds.cpty_id as counterparty_id, coalesce(principal,0) + coalesce(accrued,0) as payment_amount FROM tranche_cashflows LEFT JOIN cds ON tranche_id=cds.id WHERE tranche_id in %s AND date=%s"
case "TRANCHE":
sql_query = "SELECT ct.dealid as serenitas_id, c.cpty_id as counterparty_id, ct.upfront as payment_amount FROM cds_trades ct LEFT JOIN cds c USING (id) WHERE ct.id in %s AND ct.settle_date = %s"
b.execute(
sql_query,
(
tuple(row.ids),
date,
),
)
for sett in b:
d = sett._asdict() | payment_details
BBHSettlementsEmail.stage(d)
net_amount += d["payment_amount"]
BBHSettlementsEmail.email(
row.name, cp_contacts[row.cp_code], date, net_amount, row.currency
)
BBHSettlementsEmail.clear()
if __name__ == "__main__":
import argparse
parser = argparse.ArgumentParser()
parser.add_argument(
"settlement_date",
nargs="?",
type=datetime.date.fromisoformat,
default=datetime.date.today() + bus_day * 2,
help="settlement date",
)
args = parser.parse_args()
conn = dbconn("dawndb")
check_trade_settlements(args.settlement_date, "BRINKER", conn)
|
