diff options
| -rw-r--r-- | python/risk/tranches.py | 4 | ||||
| -rw-r--r-- | sql/dawn.sql | 27 |
2 files changed, 18 insertions, 13 deletions
diff --git a/python/risk/tranches.py b/python/risk/tranches.py index 0e255e18..30d7f66f 100644 --- a/python/risk/tranches.py +++ b/python/risk/tranches.py @@ -54,6 +54,7 @@ def insert_tranche_portfolio(portf, conn): "delta", "gamma", "theta", + "theta_amount", "corr01", "tranche_factor", "upfront", @@ -63,6 +64,7 @@ def insert_tranche_portfolio(portf, conn): "index_refprice", "index_refspread", "index_duration", + "hy_equiv", ] update_str = ",".join(f"{c} = EXCLUDED.{c}" for c in cols[2:]) sql_str = ( @@ -95,6 +97,7 @@ def insert_tranche_portfolio(portf, conn): trade.delta, trade.gamma, theta, + theta * trade.notional * trade.tranche_factor * trade._index._fx, trade.corr01, trade.tranche_factor, trade.upfront, @@ -107,6 +110,7 @@ def insert_tranche_portfolio(portf, conn): ) * 10000, float(trade._index.duration()), + trade.hy_equiv, ), ) conn.commit() diff --git a/sql/dawn.sql b/sql/dawn.sql index 0b1dfc92..d67edde0 100644 --- a/sql/dawn.sql +++ b/sql/dawn.sql @@ -1173,6 +1173,8 @@ CREATE TABLE tranche_risk( index_refprice float, index_refspread float, index_duration float, + hy_equiv float, + theta_amount float, PRIMARY KEY (date, tranche_id)) CREATE OR REPLACE function list_tranche_marks(p_date date, fund fund DEFAULT 'SERCGMAST'::fund) @@ -1929,7 +1931,7 @@ df.columns = df.columns.droplevel(level=0) df = df.reindex(columns=source_list).reset_index() for t in (df.itertuples(index=False)): - yield [t[0], t[1]] + [None if np.isnan(v) else v for v in t[2:]]] + yield [t[0], t[1]] + [None if np.isnan(v) else v for v in t[2:]] $$ LANGUAGE plpython3u; @@ -1941,10 +1943,10 @@ SELECT tranche_risk.date, tranche_id as trade_id, b.globeop_id, security_desc, i admin_clean_nav, accrued * coalesce(fx, 1.) as serenitas_accrued, admin_accrued, base_nav AS cpty_nav, - duration, delta, gamma, theta, tranche_factor, + duration, delta, gamma, theta, theta_amount, tranche_factor, tranche_risk.corr_attach, tranche_risk.corr_detach, tranche_risk.upfront, tranche_risk.running, - index_refprice, index_refspread, index_duration, initial_margin_percentage, indexfactor + index_refprice, index_refspread, index_duration, hy_equiv, initial_margin_percentage FROM tranche_risk LEFT JOIN LATERAL (SELECT full_globeop_id, globeop_id FROM id_mapping WHERE id_mapping.serenitas_id=tranche_id AND date <= tranche_risk.date ORDER by date desc LIMIT 1) b ON true @@ -1969,10 +1971,10 @@ SELECT tranche_risk.date, tranche_id as trade_id, security_desc, index, series, accrued * coalesce(fx, 1.) as serenitas_accrued, NULL AS admin_accrued, base_nav AS cpty_nav, - duration, delta, gamma, theta, tranche_factor, + duration, delta, gamma, theta, theta_amount, tranche_factor, tranche_risk.corr_attach, tranche_risk.corr_detach, tranche_risk.upfront, tranche_risk.running, - index_refprice, index_refspread, index_duration, initial_margin_percentage, indexfactor + index_refprice, index_refspread, index_duration, hy_equiv, initial_margin_percentage FROM tranche_risk LEFT JOIN cds ON (tranche_id=id) LEFT JOIN (SELECT as_of_date, link_ref, (CASE WHEN coupon_rate=0 THEN current_notional ELSE -current_notional END) AS admin_notional, base_market_value AS admin_clean_nav FROM bowdst_val WHERE security_description_1 LIKE 'TRNCH%' AND base_market_value!=0) b ON link_ref=format('SCCDS%s',tranche_id) AND as_of_date=tranche_risk.date @@ -1991,10 +1993,10 @@ SELECT tranche_risk.date, tranche_id as trade_id, security_desc, index, series, accrued * coalesce(fx, 1.) as serenitas_accrued, NULL AS admin_accrued, base_nav AS cpty_nav, - duration, delta, gamma, theta, tranche_factor, + duration, delta, gamma, theta, theta_amount, tranche_factor, tranche_risk.corr_attach, tranche_risk.corr_detach, tranche_risk.upfront, tranche_risk.running, - index_refprice, index_refspread, index_duration, initial_margin_percentage, indexfactor + index_refprice, index_refspread, index_duration, hy_equiv, initial_margin_percentage FROM tranche_risk LEFT JOIN cds ON (tranche_id=id) LEFT JOIN (SELECT accounting_date, security_id, quantity * (CASE WHEN long_short_indicator = 'S' THEN 1. ELSE -1. END) AS admin_notional, (local_market_value - quantity) * fx_rate AS admin_clean_nav @@ -2007,7 +2009,7 @@ ORDER BY index, series, orig_attach; CREATE OR REPLACE FUNCTION tranche_risk_agg(p_date date, p_fund fund DEFAULT 'SERCGMAST'::fund) RETURNS TABLE(date date, security_desc varchar(32), index index_type, series smallint, admin_notional float, admin_clean_nav float, -maturity date, serenitas_notional float, tranche_factor float, running float, serenitas_clean_nav float, serenitas_accrued float, initial_margin float, theta float, duration float, delta float, upfront float, index_refprice float, index_refspread float, orig_attach smallint, orig_detach smallint, index_duration float, gamma float, indexfactor float) AS $$ +maturity date, serenitas_notional float, tranche_factor float, running float, serenitas_clean_nav float, serenitas_accrued float, initial_margin float, theta float, theta_amount float, duration float, delta float, gamma float, hy_equiv float, upfront float, index_refprice float, index_refspread float, index_duration float, orig_attach smallint, orig_detach smallint) AS $$ DECLARE query text; fund text; @@ -2023,11 +2025,10 @@ query := 'SELECT date, security_desc, index, series, sum(admin_notional), sum(serenitas_clean_nav), sum(serenitas_accrued), sum(initial_margin_percentage * tranche_factor * abs(notional)/100), - avg(theta), avg(duration), avg(delta), - avg(upfront), avg(index_refprice), - avg(index_refspread), - orig_attach, orig_detach, avg(index_duration), - avg(gamma), avg(indexfactor) + avg(theta), sum(theta_amount), avg(duration), avg(delta), + avg(gamma), sum(hy_equiv), avg(upfront), avg(index_refprice), + avg(index_refspread), avg(index_duration), + orig_attach, orig_detach FROM tranche_risk_%I GROUP BY date, index, security_desc, series, orig_attach, orig_detach, maturity HAVING date=$1 |