diff options
| -rw-r--r-- | python/margin_estimates.py | 34 |
1 files changed, 22 insertions, 12 deletions
diff --git a/python/margin_estimates.py b/python/margin_estimates.py index d4c16b94..02979f0b 100644 --- a/python/margin_estimates.py +++ b/python/margin_estimates.py @@ -5,15 +5,17 @@ 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} + 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"') @@ -22,6 +24,7 @@ def html_generator(df, column_name): .render() ) + if __name__ == "__main__": parser = argparse.ArgumentParser() parser.add_argument( @@ -47,28 +50,35 @@ if __name__ == "__main__": } 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;", + "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"] = [ - cp_mapping[counterparty] for counterparty in isda_cp["counterparty"] - ] + 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';", + "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"] - ] + 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;", + "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;", + "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()), ) |