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
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
|
import datetime
import argparse
from exchangelib import HTMLBody
from serenitas.utils.db import dbconn
from serenitas.analytics.dates import prev_business_day
from collateral.citi import get_total_collateral as get_collateral_citi
from report_ops.utils import Monitor
from report_ops.misc import _cc_recipients
cp_mapping = {
"MS": "Morgan Stanley",
"GS": "Goldman Sachs",
"GS_FCM": "Goldman Sachs FCM",
"BOMLCM": "Baml FCM",
"BOA_FC": "Baml FCM",
"BAML_ISDA": "Baml OTC",
"WELLS": "Wells Fargo",
"BNP": "BNP Paribas",
"JPM": "JP Morgan",
"WELLSFCM": "Wells Fargo FCM",
"BARCLAYS": "Barclays",
}
class MarginEstimatesMonitor(
Monitor,
headers=("date", "counterparty", "excess", "currency"),
num_format=[("{0:,.2f}", 2)],
):
@classmethod
def email(cls, date, fund):
if not cls._staging_queue:
return
cls._em.send_email(
f"Margin Estimates {fund}: {date}",
HTMLBody(
f"""
<html>
<head>
<style>
table, th, td {{ border: 1px solid black; border-collapse: collapse;}}
th, td {{ padding: 5px; }}
</style>
</head>
<body>
Margin Estimates Receive/(Pay):<br><br>{cls.to_tabulate()}
</body>
</html>"""
),
to_recipients=_cc_recipients[fund],
)
def get_excess_values(date, fund, conn):
with conn.cursor() as c:
sql = (
"SELECT date, broker, -sum(amount) as excess, currency "
"FROM strategy_im "
"WHERE strategy='CSH_CASH' "
"GROUP BY broker, date, fund, currency "
"HAVING date=%s AND fund=%s AND broker NOT in ('CS', 'CITI')"
)
c.execute(
sql,
(
args.date,
fund,
),
)
for row in c:
d = row._asdict() | {"counterparty": cp_mapping[row.broker]}
yield d
sql = (
"SELECT date, custodian, current_excess_deficit AS excess, 'USD' as currency "
"FROM fcm_moneyline fm LEFT JOIN accounts ON account=cash_account "
"WHERE date=%s AND fund=%s AND currency=%s"
)
c.execute(
sql,
(
args.date,
fund,
"USD" if fund == "BOWDST" else "ZZZZZ",
),
)
for row in c:
custodian = row.custodian if row.custodian != "GS" else "GS_FCM"
d = row._asdict() | {"counterparty": cp_mapping[custodian]}
yield d
if fund in ("SERCGMAST",):
for counterparty, excess in zip(
["CITI Variation Margin", "CITI Initial Margin"],
get_collateral_citi(args.date)[2:],
):
yield {
"date": args.date,
"counterparty": counterparty,
"currency": "USD",
"excess": excess,
}
if __name__ == "__main__":
parser = argparse.ArgumentParser()
parser.add_argument(
"date",
nargs="?",
default=prev_business_day(datetime.date.today()),
type=datetime.date.fromisoformat,
)
args = parser.parse_args()
conn = dbconn("dawndb")
for fund in ("SERCGMAST", "BOWDST", "ISOSEL"):
for d in get_excess_values(args.date, fund, conn):
MarginEstimatesMonitor.stage(d)
MarginEstimatesMonitor.email(args.date, fund)
MarginEstimatesMonitor.clear()
|
