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
import pandas as pd
from premailer import transform
from collateral.common import load_pdf, get_col
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"],
)