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_total_collateral as get_collateral_citi
from collateral.cs import get_collateral as get_collateral_cs
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()),
)
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"],
)