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
|
from serenitas.utils.db import dbconn
from serenitas.utils.exchange import ExchangeMessage, FileAttachment
from exchangelib import HTMLBody
import datetime
import argparse
from pandas.tseries.offsets import BDay
from io import StringIO
import pandas as pd
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",
}
formatters = {"excess": "{:,.0f}".format, "receive": "{:,.0f}".format}
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()),
)
body = (
f"<h3> Collateral Estimates Receive/(Pay) at ISDA :</h3>\n\n"
+ isda_cp.to_html(index=False, formatters=formatters)
+ f"<h3> Collateral Estimates Receive/(Pay) at FCM :</h3>\n\n"
+ fcm_cp.to_html(index=False, formatters=formatters)
+ f"<h3>Payment Settlements By Date :</h3>\n\n"
+ payment_settlements_agg.to_html(index=False, formatters=formatters)
+ f"<h3>Payment Settlements :</h3>\n\n"
+ payment_settlements.to_html(index=False, formatters=formatters)
)
em = ExchangeMessage()
em.send_email(
f"Collateral Estimates {args.trade_date:%Y-%m-%d}",
HTMLBody(body),
["NYOps@lmcg.com"],
["fyu@lmcg.com"],
)
|
