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
from premailer import transform
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()
)
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()),
)
body = [
"",
" Collateral Estimates Receive/(Pay) at ISDA :
",
html_generator(isda_cp, "excess"),
" Collateral Estimates Receive/(Pay) at FCM :
",
html_generator(fcm_cp, "excess"),
"Payment Settlements By Date :
",
html_generator(payment_settlements_agg, "receive"),
"Payment Settlements :
",
html_generator(payment_settlements, "receive"),
"",
]
em = ExchangeMessage()
em.send_email(
f"Collateral Estimates {args.trade_date:%Y-%m-%d}",
HTMLBody("".join(body)),
["NYOps@lmcg.com"],
["fyu@lmcg.com"],
)