import numpy as np from pyisda.date import cds_accrued 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, funds=("SERCGMAST",), **kwargs): if by_strat: sql_string = "SELECT * FROM list_tranche_positions_by_strat(%s, %s)" else: sql_string = ( 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, *funds)) trade_ids = list(c) portf = Portfolio( [ DualCorrTranche( redcode=t.security_id, maturity=t.maturity, notional=t.notional, tranche_running=t.fixed_rate * 100, attach=t.orig_attach, detach=t.orig_detach, corr_attach=None, corr_detach=None, value_date=t.trade_date, trade_id=t.id, ) for t in trade_ids ] ) if by_strat: portf.trade_ids = [ (tid.folder, f"{t.index_type} {t.series} {t.tenor} {t.attach}-{t.detach}") for tid, t in zip(trade_ids, portf.trades) ] else: portf.trade_ids = [(t.folder, t.id) for t in trade_ids] portf.value_date = date portf.mark(**kwargs) return portf 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 cds.id, cds.upfront, cds_delta.upfront AS delta_upfront, " "cds_delta.notional * (CASE WHEN cds_delta.protection='Buy' THEN -1.0 ELSE 1.0 END) AS notional, " "cds.currency::text FROM cds " " LEFT JOIN cds AS cds_delta ON cds_delta.id=cds.delta_id " "WHERE cds.trade_date=%s", (value_date,), ) daily_trades = {rec.id: rec for rec in c} c.execute( "SELECT terminations.dealid, termination_amount, termination_fee, terminations.currency::text, " "cds.notional * delta_alloc * (CASE WHEN cds.protection='Buy' THEN -1.0 ELSE 1.0 END) AS notional, " "cds.upfront * delta_alloc AS delta_upfront " "FROM terminations LEFT JOIN cds ON cds.id=terminations.delta_id " "WHERE deal_type='CDS' AND termination_date=%s", (value_date,), ) terminations = {int(rec.dealid.removeprefix("SCCDS")): rec for rec in c} current_trades = {trade_id: trade for (strat, trade_id), trade in portf.items()} all_ids = current_trades.keys() | prev_day_risk.keys() to_insert = [] for trade_id in all_ids: pnl = 0.0 fx_pnl = 0.0 corr_pnl = 0.0 if trade_id in daily_trades: trade = daily_trades[trade_id] pnl = trade.upfront * get_fx(value_date, trade.currency) if trade_id in terminations: term = terminations[trade_id] pnl += term.termination_fee * get_fx(value_date, term.currency) fx_pnl += term.termination_fee * ( get_fx(value_date, term.currency) - get_fx(prev_day, term.currency) ) if trade_id not in current_trades: previous_risk = prev_day_risk[trade_id] pnl = pnl - (previous_risk.clean_nav + previous_risk.accrued) dirty_index_pv = ( 1 - previous_risk.index_refprice * 0.01 - cds_accrued(prev_day, previous_risk.running * 1e-4) ) if ( term.delta_upfront ): # if None means either no delta or we didn't populate delta_pnl = ( term.delta_upfront - term.notional * dirty_index_pv * previous_risk.index_factor ) else: delta_pnl = 0.0 else: trade = current_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()) * get_fx(value_date, trade.currency) - (1 - previous_risk.index_refprice * 0.01) * get_fx(prev_day, trade.currency) ) ) prev_rho = np.array( [previous_risk.corr_attach, previous_risk.corr_detach] ) rho = trade.rho corr_pnl = np.nansum((rho - prev_rho) * previous_risk.corr01_vec) else: fx_pnl = 0.0 day_trade = daily_trades[trade_id] dirty_index_pv = float(trade._index.pv() - trade._index.accrued()) if day_trade.notional: delta_pnl = ( day_trade.notional * dirty_index_pv * trade._index.factor - day_trade.delta_upfront ) else: # if None means either no delta or we didn't populate delta_pnl = 0 pnl += trade.pv * get_fx(value_date, trade.currency) unexplained = pnl - delta_pnl - fx_pnl to_insert.append( (value_date, trade_id, pnl, fx_pnl, delta_pnl, corr_pnl, unexplained) ) c.executemany( "INSERT INTO tranche_pnl_explain(date, tranche_id, pnl, fx_pnl, delta_pnl, corr_pnl, unexplained) " "VALUES (%s, %s, %s, %s, %s, %s, %s)", to_insert, ) conn.commit() def insert_tranche_risk(portf, conn): cols = [ "date", "tranche_id", "notional", "clean_nav", "accrued", "duration", "delta", "gamma", "theta", "theta_amount", "corr01_vec", "tranche_factor", "upfront", "running", "corr_attach", "corr_detach", "index_refprice", "index_refspread", "index_duration", "hy_equiv", "ir_dv01", "index_factor", ] update_str = ",".join(f"{c} = EXCLUDED.{c}" for c in cols[2:]) sql_str = ( f"INSERT INTO tranche_risk({','.join(cols)}) " f"VALUES({','.join(['%s'] * len(cols))}) " " ON CONFLICT (date, tranche_id) DO UPDATE " f"SET {update_str}" ) 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: theta = trade.theta(method="TLP") except (ValueError, RuntimeError) as e: # when there is less than one year left we computed the theta to maturity logger.info(str(e)) theta = ( trade.clean_pv / trade.notional / trade.tranche_factor / trade._index._fx + trade.tranche_running * 1e-4 * trade.duration ) c.execute( sql_str, ( trade.value_date, trade_id, trade.notional, trade.clean_pv, trade.accrued, trade.duration, trade.delta, trade.gamma, theta, -theta * trade.notional * trade.tranche_factor * trade._index._fx, trade.corr01, trade.tranche_factor, trade.upfront, trade.tranche_running, trade.rho[0], trade.rho[1], 100 * (1 - float(trade._index.pv())), trade._index._snacspread( trade._index.coupon(), trade._index.recovery, trade.maturity ) * 10000, float(trade._index.duration()), trade.hy_equiv, trade.IRDV01, trade._index.factor, ), ) conn.commit()