diff options
| -rw-r--r-- | python/margin_estimates.py | 86 | ||||
| -rw-r--r-- | python/report_ops/utils.py | 33 |
2 files changed, 70 insertions, 49 deletions
diff --git a/python/margin_estimates.py b/python/margin_estimates.py index f32ef4d3..968350cd 100644 --- a/python/margin_estimates.py +++ b/python/margin_estimates.py @@ -1,16 +1,19 @@ 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 MarginEstimatesMonitor +from report_ops.utils import Monitor +from report_ops.misc import _cc_recipients cp_mapping = { "MS": "Morgan Stanley", "GS": "Goldman Sachs", "BOMLCM": "Baml FCM", + "BOA_FC": "Baml FCM", "BAML_ISDA": "Baml OTC", "WELLS": "Wells Fargo", "BNP": "BNP Paribas", @@ -20,17 +23,52 @@ cp_mapping = { } -def get_excess_values(date, conn): +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""" +<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[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='SERCGMAST' AND broker NOT in ('CS', 'CITI')" + "HAVING date=%s AND fund=%s AND broker NOT in ('CS', 'CITI')" ) - c.execute(sql, (args.date,)) + c.execute( + sql, + ( + args.date, + fund, + ), + ) for row in c: d = row._asdict() | {"counterparty": cp_mapping[row.broker]} @@ -39,25 +77,32 @@ def get_excess_values(date, conn): 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'" + "WHERE date=%s AND fund=%s AND currency='ZZZZZ'" ) - c.execute(sql, (args.date,)) + c.execute( + sql, + ( + args.date, + fund, + ), + ) 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 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__": @@ -71,8 +116,9 @@ if __name__ == "__main__": 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) - for d in get_excess_values(args.date, conn): - MarginEstimatesMonitor.stage(d) - - MarginEstimatesMonitor.email(args.date) + MarginEstimatesMonitor.email(args.date, fund) + MarginEstimatesMonitor.clear() diff --git a/python/report_ops/utils.py b/python/report_ops/utils.py index 6eafcc36..663ece63 100644 --- a/python/report_ops/utils.py +++ b/python/report_ops/utils.py @@ -120,6 +120,10 @@ class Monitor: ) return t + @classmethod + def clear(cls): + cls._staging_queue.clear() + class GFSMonitor( Monitor, @@ -370,35 +374,6 @@ 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=( |