diff options
| -rw-r--r-- | python/margin_estimates.py | 158 | ||||
| -rw-r--r-- | python/report_ops/utils.py | 29 |
2 files changed, 88 insertions, 99 deletions
diff --git a/python/margin_estimates.py b/python/margin_estimates.py index d6ccff7c..f32ef4d3 100644 --- a/python/margin_estimates.py +++ b/python/margin_estimates.py @@ -1,118 +1,78 @@ import datetime import argparse -from pandas.tseries.offsets import BDay -import pandas as pd -from premailer import transform -from exchangelib import HTMLBody from serenitas.utils.db import dbconn -from serenitas.utils.exchange import ExchangeMessage +from serenitas.analytics.dates import prev_business_day from collateral.citi import get_total_collateral as get_collateral_citi -from collateral.cs import get_collateral as get_collateral_cs +from report_ops.utils import MarginEstimatesMonitor +cp_mapping = { + "MS": "Morgan Stanley", + "GS": "Goldman Sachs", + "BOMLCM": "Baml FCM", + "BAML_ISDA": "Baml OTC", + "WELLS": "Wells Fargo", + "BNP": "BNP Paribas", + "JPM": "JP Morgan", + "WELLSFCM": "Wells Fargo FCM", + "BARCLAYS": "Barclays", +} -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(axis="index") - .to_html() - ) + +def get_excess_values(date, 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='SERCGMAST' AND broker NOT in ('CS', 'CITI')" + ) + + c.execute(sql, (args.date,)) + + 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='SERCGMAST' AND currency='ZZZZZ'" + ) + + c.execute(sql, (args.date,)) + + for row in c: + d = row._asdict() | {"counterparty": cp_mapping[row.custodian]} + yield d + + 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( - "trade_date", + "date", nargs="?", - default=(datetime.date.today() - BDay(1)).date(), + default=prev_business_day(datetime.date.today()), 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", - } + args = parser.parse_args() + conn = dbconn("dawndb") - 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 = [ - "<html><body>", - "<h3> Collateral Estimates Receive/(Pay) at ISDA :</h3>", - html_generator(isda_cp, "excess"), - "<h3> Collateral Estimates Receive/(Pay) at FCM :</h3>", - html_generator(fcm_cp, "excess"), - "<h3>Payment Settlements By Date :</h3>", - html_generator(payment_settlements_agg, "receive"), - "<h3>Payment Settlements :</h3>", - html_generator(payment_settlements, "receive"), - "<h3>Citi Breakdown :</h3>", - html_generator(citi_collateral, "amount to receive"), - "<h3>CS Breakdown :</h3>", - html_generator(cs_collateral, "amount to receive"), - "</body></html>", - ] + for d in get_excess_values(args.date, conn): + MarginEstimatesMonitor.stage(d) - em = ExchangeMessage() - em.send_email( - f"Collateral Estimates {args.trade_date:%Y-%m-%d}", - HTMLBody("".join(body)), - ["NYOps@lmcg.com"], - ["fyu@lmcg.com"], - ) + MarginEstimatesMonitor.email(args.date) diff --git a/python/report_ops/utils.py b/python/report_ops/utils.py index d7f6cbb5..6eafcc36 100644 --- a/python/report_ops/utils.py +++ b/python/report_ops/utils.py @@ -370,6 +370,35 @@ class FxHedge( ) +class MarginEstimatesMonitor( + Monitor, + headers=("date", "counterparty", "excess", "currency"), + num_format=[("{0:,.2f}", 2)], +): + @classmethod + def email(cls, date): + if not cls._staging_queue: + return + cls._em.send_email( + f"Margin Estimates: {date}", + HTMLBody( + f""" +<html> + <head> + <style> + table, th, td {{ border: 1px solid black; border-collapse: collapse;}} + th, td {{ padding: 5px; }} + </style> + </head> + <body> + Margin Estimates Receive/(Pay):<br><br>{cls.to_tabulate()} + </body> +</html>""" + ), + to_recipients=_cc_recipients["SERCGMAST"], + ) + + class QuantifiMonitor( Monitor, headers=( |