diff options
| -rw-r--r-- | sql/dawn.sql | 96 |
1 files changed, 75 insertions, 21 deletions
diff --git a/sql/dawn.sql b/sql/dawn.sql index be668225..861fc4b3 100644 --- a/sql/dawn.sql +++ b/sql/dawn.sql @@ -1874,10 +1874,10 @@ $$ LANGUAGE plpython3u; CREATE OR REPLACE VIEW tranche_risk_serenitas AS SELECT tranche_risk.date, tranche_id as trade_id, b.globeop_id, security_desc, index, series, maturity, orig_attach, orig_detach, tranche_risk.notional, - globeop_notional, + admin_notional, clean_nav * coalesce(fx, 1.) as serenitas_clean_nav, - globeop_clean_nav, - accrued * coalesce(fx, 1.) as serenitas_accrued, globeop_accrued, + admin_clean_nav, + accrued * coalesce(fx, 1.) as serenitas_accrued, admin_accrued, nav AS cpty_nav, duration, delta, gamma, theta, tranche_factor, tranche_risk.corr_attach, tranche_risk.corr_detach, @@ -1888,9 +1888,9 @@ 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 LEFT JOIN cds ON (tranche_id=id) RIGHT JOIN (SELECT invid, periodenddate, - sum(endbookunrealincome) AS globeop_accrued, - sum(endbooknav-endbookunrealincome) AS globeop_clean_nav, - sum(endqty) AS globeop_notional + sum(endbookunrealincome) AS admin_accrued, + sum(endbooknav-endbookunrealincome) AS admin_clean_nav, + sum(endqty) AS admin_notional FROM valuation_reports GROUP BY invid, periodenddate) a ON (invid=b.full_globeop_id AND periodenddate=tranche_risk.date) LEFT JOIN index_version ON (security_id=redindexcode) @@ -1898,22 +1898,76 @@ LEFT JOIN (SELECT date, 'EUR'::currency AS currency, eurusd AS fx FROM fx) fx US LEFT JOIN external_marks_deriv on cpty_id=identifier and external_marks_deriv.date=tranche_risk.date ORDER BY index, series, orig_attach; +CREATE OR REPLACE VIEW tranche_risk_bowdst AS +SELECT tranche_risk.date, tranche_id as trade_id, security_desc, index, series, maturity, orig_attach, + orig_detach, tranche_risk.notional, + admin_notional, + clean_nav * coalesce(fx, 1.) as serenitas_clean_nav, + admin_clean_nav, + accrued * coalesce(fx, 1.) as serenitas_accrued, + NULL AS admin_accrued, + nav AS cpty_nav, + duration, delta, gamma, theta, 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 +FROM tranche_risk +LEFT JOIN cds ON (tranche_id=id) +LEFT JOIN (SELECT as_of_date, link_ref, current_notional * (CASE WHEN coupon_rate=0 THEN 1. ELSE -1. END) AS admin_notional, base_market_value * (CASE WHEN coupon_rate=0 THEN 1. ELSE -1. END) AS admin_clean_nav FROM bowdst_val WHERE link_ref LIKE 'SCCDS%' aAND base_market_value!=0) b ON link_ref=format('SCCDS%s',tranche_id) AND as_of_date=tranche_risk.date +LEFT JOIN index_version ON (security_id=redindexcode) +LEFT JOIN (SELECT date, 'EUR'::currency AS currency, eurusd AS fx FROM fx) fx USING (date, currency) +LEFT JOIN external_marks_deriv on cpty_id=identifier and external_marks_deriv.date=tranche_risk.date +WHERE fund='BOWDST' +ORDER BY index, series, orig_attach; -CREATE OR REPLACE VIEW tranche_risk_serenitas_agg AS -SELECT date, security_desc, index, series, sum(globeop_notional) as globeop_notional, - sum(globeop_clean_nav) as globeop_clean_nav, maturity, sum(notional) as orig_ntl, - avg(tranche_factor) as tranche_factor, avg(running) as running, - sum(serenitas_clean_nav) as serenitas_clean_nav, - sum(serenitas_accrued) as serenitas_accrued, - sum(initial_margin_percentage * abs(notional)/100) as initial_margin, - avg(theta) as theta, avg(duration) as duration, avg(delta) as delta, - avg(upfront) as upfront, avg(index_refprice) as index_refprice, - avg(index_refspread) as index_refspread, - orig_attach, orig_detach, avg(index_duration) as index_duration, - avg(gamma) as gamma - FROM tranche_risk_serenitas - GROUP BY date, index, security_desc, series, orig_attach, orig_detach, maturity - ORDER BY index, series; +CREATE OR REPLACE VIEW tranche_risk_brinker AS +SELECT tranche_risk.date, tranche_id as trade_id, security_desc, index, series, maturity, orig_attach, + orig_detach, tranche_risk.notional, + admin_notional, + clean_nav * coalesce(fx, 1.) as serenitas_clean_nav, + admin_clean_nav, + accrued * coalesce(fx, 1.) as serenitas_accrued, + NULL AS admin_accrued, + nav AS cpty_nav, + duration, delta, gamma, theta, 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 +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 +FROM bbh_val WHERE sub_security_type_code ='CXT' AND interest_rate IS NOT NULL) b ON b.security_id=format('SCCDS%s', tranche_id) AND accounting_date=tranche_risk.date +LEFT JOIN index_version ON (cds.security_id=redindexcode) +LEFT JOIN (SELECT date, 'EUR'::currency AS currency, eurusd AS fx FROM fx) fx USING (date, currency) +LEFT JOIN external_marks_deriv on cpty_id=identifier and external_marks_deriv.date=tranche_risk.date +WHERE fund='BRINKER' +ORDER BY index, series, orig_attach; + +CREATE OR REPLACE FUNCTION tranche_risk_agg(p_date date, p_fund text) +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) AS $$ +DECLARE + query text; +BEGIN +query := 'SELECT date, security_desc, index, series, sum(admin_notional), + sum(admin_clean_nav), maturity, sum(notional), + avg(tranche_factor), avg(running), + sum(serenitas_clean_nav), + sum(serenitas_accrued), + sum(initial_margin_percentage * 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) + FROM tranche_risk_%I + GROUP BY date, index, security_desc, series, orig_attach, orig_detach, maturity + HAVING date=$1 + ORDER BY index, series'; +query := format(query, p_fund); +RETURN QUERY EXECUTE query USING p_date; +END +$$ LANGUAGE plpgsql; CREATE OR REPLACE VIEW tranche_risk_master AS |