import datetime
import argparse
from exchangelib import HTMLBody
from serenitas.utils.db import dbconn
from serenitas.analytics.dates import prev_business_day
from collateral.citi import get_total_collateral as get_collateral_citi
from report_ops.utils import Monitor
from report_ops.misc import _cc_recipients
cp_mapping = {
"MS": "Morgan Stanley",
"GS": "Goldman Sachs",
"GS_FCM": "Goldman Sachs FCM",
"BOMLCM": "Baml FCM",
"BOA_FC": "Baml FCM",
"BAML_ISDA": "Baml OTC",
"WELLS": "Wells Fargo",
"BNP": "BNP Paribas",
"JPM": "JP Morgan",
"WELLSFCM": "Wells Fargo FCM",
"BARCLAYS": "Barclays",
}
class MarginEstimatesMonitor(
Monitor,
headers=("date", "counterparty", "excess", "currency"),
num_format=[("{0:,.2f}", 2)],
):
@classmethod
def email(cls, date, fund):
if not cls._staging_queue:
return
cls._em.send_email(
f"Margin Estimates {fund}: {date}",
HTMLBody(
f"""
Margin Estimates Receive/(Pay):
{cls.to_tabulate()}
"""
),
to_recipients=_cc_recipients[fund],
)
def get_excess_values(date, fund, conn):
with conn.cursor() as c:
sql = (
"SELECT date, broker, -sum(amount) as excess, currency "
"FROM strategy_im "
"WHERE strategy='CSH_CASH' "
"GROUP BY broker, date, fund, currency "
"HAVING date=%s AND fund=%s AND broker NOT in ('CS', 'CITI')"
)
c.execute(
sql,
(
args.date,
fund,
),
)
for row in c:
d = row._asdict() | {"counterparty": cp_mapping[row.broker]}
yield d
sql = (
"SELECT date, custodian, current_excess_deficit AS excess, 'USD' as currency "
"FROM fcm_moneyline fm LEFT JOIN accounts ON account=cash_account "
"WHERE date=%s AND fund=%s AND currency=%s"
)
c.execute(
sql,
(
args.date,
fund,
"USD" if fund == "BOWDST" else "ZZZZZ",
),
)
for row in c:
custodian = row.custodian if row.custodian != "GS" else "GS_FCM"
d = row._asdict() | {"counterparty": cp_mapping[custodian]}
yield d
if fund in ("SERCGMAST",):
for counterparty, excess in zip(
["CITI Variation Margin", "CITI Initial Margin"],
get_collateral_citi(args.date)[2:],
):
yield {
"date": args.date,
"counterparty": counterparty,
"currency": "USD",
"excess": excess,
}
if __name__ == "__main__":
parser = argparse.ArgumentParser()
parser.add_argument(
"date",
nargs="?",
default=prev_business_day(datetime.date.today()),
type=datetime.date.fromisoformat,
)
args = parser.parse_args()
conn = dbconn("dawndb")
for fund in ("SERCGMAST", "BOWDST", "ISOSEL"):
for d in get_excess_values(args.date, fund, conn):
MarginEstimatesMonitor.stage(d)
MarginEstimatesMonitor.email(args.date, fund)
MarginEstimatesMonitor.clear()