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
128
129
130
|
from serenitas.utils.db import dbconn
from serenitas.utils.exchange import ExchangeMessage, FileAttachment
from serenitas.utils.env import DAILY_DIR
from exchangelib import HTMLBody
import datetime
import argparse
from pandas.tseries.offsets import BDay
from io import StringIO
import pandas as pd
from premailer import transform
from collateral.common import load_pdf, get_col
from collateral.citi import get_df
def html_generator(df, column_name):
formatters = {"excess": "{:,.0f}".format, "receive": "{:,.0f}".format}
return transform(
df.style.format(formatter=formatters, thousands=",")
.set_table_attributes('border="1"')
.applymap(lambda x: "text-align: right;", subset=[column_name])
.hide_index()
.render()
)
def get_total_collateral_citi(d):
try:
fname = next(
(DAILY_DIR / "CITI_reports").glob(
f"262966_MarginNotice_{d.strftime('%Y%m%d')}_*.pdf"
)
)
except StopIteration:
raise FileNotFoundError(f"CITI file not found for date {d.date()}")
l = load_pdf(fname)
col1 = (370, 500, 70, 250)
col2 = (370, 500, 300, 530)
col3 = (370, 500, 530, 600)
variation_margin = get_df(l, col1, col2, col3)
anchor = next(c for c in l if c.text == "Non Regulatory Initial Margin")
top = int(anchor["top"]) + 10
bottom = top + 160
col1 = (top, bottom, 70, 320)
col2 = (top, bottom, 320, 530)
col3 = (top, bottom, 530, 600)
initial_margin = get_df(l, col1, col2, col3)
margins = {
"vm": [-variation_margin.loc["Regulatory VM Requirement", "amount"]],
"im": [-initial_margin.loc["Non Reg IM Requirement Due Customer", "amount"]],
}
return (
pd.DataFrame.from_dict(margins, orient="index", columns=["amount to receive"])
.rename_axis("account")
.reset_index()
)
if __name__ == "__main__":
parser = argparse.ArgumentParser()
parser.add_argument(
"trade_date",
nargs="?",
default=(datetime.date.today() - BDay(1)).date(),
type=datetime.date.fromisoformat,
)
args = parser.parse_args()
dawndb = dbconn("dawndb")
cp_mapping = {
"CITI": "Citi",
"MS": "Morgan Stanley",
"GS": "Goldman Sachs",
"BOMLCM": "Baml FCM",
"BAML_ISDA": "Baml OTC",
"WELLS": "Wells Fargo",
"BNP": "BNP Paribas",
"CS": "Credit Suisse",
"JPM": "JP Morgan",
"WELLSFCM": "Wells Fargo FCM",
}
isda_cp = pd.read_sql_query(
"SELECT date, broker as counterparty, -sum(amount) as excess FROM strategy_im si WHERE strategy='CSH_CASH' GROUP BY broker, date, fund having date=%s and fund='SERCGMAST' ORDER BY date DESC;",
con=dawndb,
params=(args.trade_date,),
)
isda_cp["counterparty"] = [
cp_mapping[counterparty] for counterparty in isda_cp["counterparty"]
]
fcm_cp = pd.read_sql_query(
"SELECT date, custodian AS counterparty, current_excess_deficit as excess FROM fcm_moneyline fm LEFT JOIN accounts ON account=cash_account WHERE date=%s AND fund='SERCGMAST' AND currency='ZZZZZ';",
con=dawndb,
params=(args.trade_date,),
)
fcm_cp["counterparty"] = [
cp_mapping[counterparty] for counterparty in fcm_cp["counterparty"]
]
payment_settlements = pd.read_sql_query(
"SELECT settle_date, name as counterparty, asset_class, currency, payment_amount as receive FROM payment_settlements ps WHERE settle_date between %s and %s and fund='SERCGMAST' order by settle_date asc;",
con=dawndb,
params=((args.trade_date + BDay(1)).date(), (args.trade_date + BDay(3)).date()),
)
payment_settlements_agg = pd.read_sql_query(
"SELECT settle_date , currency, sum(payment_amount) as receive FROM payment_settlements WHERE fund= 'SERCGMAST' AND settle_date BETWEEN %s AND %s GROUP BY settle_date, currency order by settle_date asc;",
con=dawndb,
params=((args.trade_date + BDay(1)).date(), (args.trade_date + BDay(3)).date()),
)
citi = get_total_collateral_citi(args.trade_date)
body = [
"<html><body>",
"<h3> Collateral Estimates Receive/(Pay) at ISDA :</h3>",
html_generator(isda_cp, "excess"),
"<h3> Collateral Estimates Receive/(Pay) at FCM :</h3>",
html_generator(fcm_cp, "excess"),
"<h3>Payment Settlements By Date :</h3>",
html_generator(payment_settlements_agg, "receive"),
"<h3>Payment Settlements :</h3>",
html_generator(payment_settlements, "receive"),
"<h3>Citi Breakdown :</h3>",
html_generator(citi, "amount to receive"),
"</body></html>",
]
em = ExchangeMessage()
em.send_email(
f"Collateral Estimates {args.trade_date:%Y-%m-%d}",
HTMLBody("".join(body)),
["NYOps@lmcg.com"],
["fyu@lmcg.com"],
)
|
