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
|
import logging
import pandas as pd
from exchangelib import HTMLBody
logger = logging.getLogger(__name__)
def compare_notionals(df, positions, fcm: str):
check_notionals = (
positions.groupby(level=["security_id", "maturity"])[["notional"]]
.sum()
.join(df["NOTIONAL"], how="left")
)
diff_notionals = check_notionals[
check_notionals.notional != check_notionals.NOTIONAL
]
if not diff_notionals.empty:
logger.error(f"Database and {fcm} FCM know different notionals")
for t in diff_notionals.itertuples():
logger.error(
f"{t.Index[0]}\t{t.Index[1].date()}\t{t.notional}\t{t.NOTIONAL}"
)
def get_dawn_trades(d, engine):
df_cds = pd.read_sql_query(
"SELECT cpty_id, folder, initial_margin_percentage * notional / 100 as IA "
"FROM cds "
"WHERE cpty_id IS NOT NULL AND trade_date <= %s",
engine,
params=(d,),
)
df_swaptions = pd.read_sql_query(
"SELECT cpty_id, folder, initial_margin_percentage * notional / 100 AS IA "
"FROM swaptions "
"WHERE cpty_id IS NOT NULL "
"AND trade_date <= %s",
engine,
params=(d,),
)
df_caps = pd.read_sql_query(
"SELECT cpty_id, folder, initial_margin_percentage * amount / 100 AS IA "
"FROM capfloors "
"WHERE cpty_id IS NOT NULL "
"AND trade_date <= %s",
engine,
params=(d,),
)
df = pd.concat([df_cds, df_swaptions, df_caps])
df = df.replace(
{
"folder": {
"IGREC": "COCSH",
"IGPAYER": "COCSH",
"HYPAYER": "COCSH",
"HYREC": "COCSH",
"STEEP": "IRDEVCSH",
"FLAT": "IRDEVCSH",
"MBSCDS": "MBSCDSCSH",
"IGMEZ": "TCSH",
"IGSNR": "TCSH",
"IGEQY": "TCSH",
"HYMEZ": "TCSH",
"HYEQY": "TCSH",
"BSPK": "TCSH",
}
}
)
return df
def send_email(d, df):
from exchange import ExchangeMessage
pd.set_option("display.float_format", "{:.2f}".format)
df = df.drop("date", axis=1).set_index("broker")
cp_mapping = {
"CITI": "Citi",
"MS": "Morgan Stanley",
"GS": "Goldman Sachs",
"BAML_FCM": "Baml FCM",
"BAML_ISDA": "Baml OTC",
"WELLS": "Wells Fargo",
}
html = "<html><body>"
for cp, name in cp_mapping.items():
html += f"<h3> At {name}:</h3>\n{df.loc[cp].to_html(index=False)}"
em = ExchangeMessage()
em.send_email(
f"IAM booking {d:%Y-%m-%d}",
HTMLBody(html),
["serenitas.otc@sscinc.com"],
["nyops@lmcg.com"],
)
|
