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
|
import datetime
import argparse
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 MarginEstimatesMonitor
cp_mapping = {
"MS": "Morgan Stanley",
"GS": "Goldman Sachs",
"BOMLCM": "Baml FCM",
"BAML_ISDA": "Baml OTC",
"WELLS": "Wells Fargo",
"BNP": "BNP Paribas",
"JPM": "JP Morgan",
"WELLSFCM": "Wells Fargo FCM",
"BARCLAYS": "Barclays",
}
def get_excess_values(date, 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='SERCGMAST' AND broker NOT in ('CS', 'CITI')"
)
c.execute(sql, (args.date,))
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='SERCGMAST' AND currency='ZZZZZ'"
)
c.execute(sql, (args.date,))
for row in c:
d = row._asdict() | {"counterparty": cp_mapping[row.custodian]}
yield d
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 d in get_excess_values(args.date, conn):
MarginEstimatesMonitor.stage(d)
MarginEstimatesMonitor.email(args.date)
|
