diff options
Diffstat (limited to 'python/margin_estimates.py')
| -rw-r--r-- | python/margin_estimates.py | 60 |
1 files changed, 41 insertions, 19 deletions
diff --git a/python/margin_estimates.py b/python/margin_estimates.py index bd0f5647..193d5914 100644 --- a/python/margin_estimates.py +++ b/python/margin_estimates.py @@ -5,6 +5,7 @@ import datetime import argparse from pandas.tseries.offsets import BDay from io import StringIO +import pandas as pd if __name__ == "__main__": parser = argparse.ArgumentParser() @@ -17,40 +18,61 @@ if __name__ == "__main__": args = parser.parse_args() dawndb = dbconn("dawndb") - buf = StringIO() - buf.write("<html><body>\n") - cp_mapping = { "CITI": "Citi", "MS": "Morgan Stanley", "GS": "Goldman Sachs", - "BAML_FCM": "Baml FCM", + "BOMLCM": "Baml FCM", "BAML_ISDA": "Baml OTC", "WELLS": "Wells Fargo", "BNP": "BNP Paribas", "CS": "Credit Suisse", "JPM": "JP Morgan", + "WELLSFCM": "Wells Fargo FCM", } + formatters = {"excess": "{:,.0f}".format, "receive": "{:,.0f}".format} - with dawndb.cursor() as c: - buf.write( - f"<h3> Collateral Estimates Receive/(Pay):</h3>\n\n<ol style='list-style-type:upper-roman'>" - ) - c.execute( - "SELECT date, broker, fund, sum(amount) as amount FROM strategy_im si WHERE strategy='CSH_CASH' and date=%s and fund='SERCGMAST' GROUP BY broker, date, fund ORDER BY date DESC;", - (args.trade_date,), - ) - for row in c: - amount = ( - f"{-row.amount:,.0f}" if row.amount <= 0 else f"({row.amount:,.0f})" - ) - buf.write(f"<li>{cp_mapping[row.broker]}: {amount} USD</li>") + 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()), + ) + body = ( + f"<h3> Collateral Estimates Receive/(Pay) at ISDA :</h3>\n\n" + + isda_cp.to_html(index=False, formatters=formatters) + + f"<h3> Collateral Estimates Receive/(Pay) at FCM :</h3>\n\n" + + fcm_cp.to_html(index=False, formatters=formatters) + + f"<h3>Payment Settlements By Date :</h3>\n\n" + + payment_settlements_agg.to_html(index=False, formatters=formatters) + + f"<h3>Payment Settlements :</h3>\n\n" + + payment_settlements.to_html(index=False, formatters=formatters) + ) em = ExchangeMessage() - buf.write("</body/></html>") em.send_email( f"Collateral Estimates {args.trade_date:%Y-%m-%d}", - HTMLBody(buf.getvalue()), + HTMLBody(body), ["NYOps@lmcg.com"], ["fyu@lmcg.com"], ) |