from serenitas.utils.db import dbconn
from exchangelib import HTMLBody
from serenitas.utils.exchange import ExchangeMessage
import datetime
import argparse
from pandas.tseries.offsets import BDay
import pandas as pd
from premailer import transform
from collateral.citi import get_total_collateral as get_collateral_citi
from collateral.cs import get_collateral as get_collateral_cs
def html_generator(df, column_name):
formatters = {
k: "{:,.2f}".format for k in ["excess", "receive", "amount to receive"]
}
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 "
"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"] = isda_cp["counterparty"].map(cp_mapping)
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"] = fcm_cp.counterparty.map(cp_mapping)
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_collateral = pd.DataFrame(
{
"account": ["VM", "IM"],
"amount to receive": get_collateral_citi(args.trade_date)[2:],
}
)
cs_collateral = get_collateral_cs(args.trade_date, "Serenitas")
cs_collateral = pd.DataFrame(
{"account": list(cs_collateral), "amount to receive": cs_collateral.values()}
)
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"),
"Citi Breakdown :
",
html_generator(citi_collateral, "amount to receive"),
"CS Breakdown :
",
html_generator(cs_collateral, "amount to receive"),
"",
]
em = ExchangeMessage()
em.send_email(
f"Collateral Estimates {args.trade_date:%Y-%m-%d}",
HTMLBody("".join(body)),
["NYOps@lmcg.com"],
["fyu@lmcg.com"],
)