diff options
Diffstat (limited to 'python/risk/tranches.py')
| -rw-r--r-- | python/risk/tranches.py | 89 |
1 files changed, 82 insertions, 7 deletions
diff --git a/python/risk/tranches.py b/python/risk/tranches.py index 41a5da5f..134baa24 100644 --- a/python/risk/tranches.py +++ b/python/risk/tranches.py @@ -1,21 +1,23 @@ from serenitas.analytics.api import Portfolio, DualCorrTranche +from serenitas.analytics.dates import prev_business_day +from serenitas.analytics.utils import get_fx import logging logger = logging.getLogger(__name__) -def get_tranche_portfolio(date, conn, by_strat=False, fund="SERCGMAST", **kwargs): +def get_tranche_portfolio(date, conn, by_strat=False, funds=("SERCGMAST",), **kwargs): if by_strat: sql_string = "SELECT * FROM list_tranche_positions_by_strat(%s, %s)" else: sql_string = ( - "SELECT * FROM list_cds(%s, %s) " + f"SELECT * FROM list_cds(%s, {','.join(['%s'] * len(funds))}) " "WHERE orig_attach IS NOT NULL " "ORDER BY security_desc, attach" ) with conn.cursor() as c: - c.execute(sql_string, (date, fund)) + c.execute(sql_string, (date, *funds)) trade_ids = list(c) portf = Portfolio( @@ -47,7 +49,77 @@ def get_tranche_portfolio(date, conn, by_strat=False, fund="SERCGMAST", **kwargs return portf -def insert_tranche_portfolio(portf, conn): +def insert_tranche_pnl_explain(portf, conn): + value_date = portf.value_date + prev_day = prev_business_day(value_date) + with conn.cursor(binary=True) as c: + c.execute("SELECT * FROM tranche_risk WHERE date=%s", (prev_day,)) + prev_day_risk = {rec.tranche_id: rec for rec in c} + c.execute("SELECT id, upfront FROM cds WHERE trade_date=%s", (value_date,)) + daily_trades = {rec.id: rec for rec in c} + c.execute( + "SELECT dealid, termination_amount, termination_fee " + "FROM terminations WHERE deal_type='CDS' AND termination_date=%s", + (value_date,), + ) + terminations = {int(rec.dealid.removeprefix("SCCDS")): rec for rec in c} + + today_trades = {trade_id: trade for (strat, trade_id), trade in portf.items()} + all_ids = today_trades.keys() | prev_day_risk.keys() + + to_insert = [] + for trade_id in all_ids: + pnl = 0 + if trade_id in daily_trades: + pnl += daily_trades[trade_id].upfront * get_fx( + value_date, trade.currency + ) + if trade_id in terminations: + pnl += terminations[trade_id].termination_fee * get_fx( + value_date, trade.currency + ) + if trade_id not in today_trades: + previous_risk = prev_day_risk[trade_id] + pnl = pnl - (previous_risk.clean_nav + previous_risk.accrued) + else: + trade = today_trades[trade_id] + if trade_id in prev_day_risk: + previous_risk = prev_day_risk[trade_id] + pnl = trade.pv * get_fx(value_date, trade.currency) - ( + previous_risk.clean_nav + previous_risk.accrued + ) + + fx_pnl = trade.pv * ( + get_fx(value_date, trade.currency) + - get_fx(prev_day, trade.currency) + ) + delta_pnl = ( + previous_risk.delta + * previous_risk.index_factor + * previous_risk.notional + * ( + float(trade._index.pv()) + - (1 - previous_risk.index_refprice * 0.01) + ) + * get_fx(prev_day, trade.currency) + ) + else: + fx_pnl = trade.pv * ( + get_fx(value_date, trade.currency) + - get_fx(prev_day, trade.currency) + ) + delta_pnl = 0.0 + to_insert.append((value_date, trade_id, pnl, fx_pnl, delta_pnl)) + + c.executemany( + "INSERT INTO tranche_pnl_explain(date, tranche_id, pnl, fx_pnl, delta_pnl) " + "VALUES (%s, %s, %s, %s, %s)", + to_insert, + ) + conn.commit() + + +def insert_tranche_risk(portf, conn): cols = [ "date", "tranche_id", @@ -79,8 +151,9 @@ def insert_tranche_portfolio(portf, conn): " ON CONFLICT (date, tranche_id) DO UPDATE " f"SET {update_str}" ) - - with conn.cursor() as c: + value_date = portf.value_date + prev_day = prev_business_day(value_date) + with conn.cursor(binary=True) as c: for (strat, trade_id), trade in portf.items(): logger.info(f"marking tranche {trade_id} in {strat}") try: @@ -95,6 +168,7 @@ def insert_tranche_portfolio(portf, conn): / trade._index._fx + trade.tranche_running * 1e-4 * trade.duration ) + c.execute( sql_str, ( @@ -114,7 +188,7 @@ def insert_tranche_portfolio(portf, conn): trade.tranche_running, trade.rho[0], trade.rho[1], - 100 - float(trade._index.pv()) * 100, + 100 * (1 - float(trade._index.pv())), trade._index._snacspread( trade._index.coupon(), trade._index.recovery, trade.maturity ) @@ -125,4 +199,5 @@ def insert_tranche_portfolio(portf, conn): trade._index.factor, ), ) + conn.commit() |