diff options
Diffstat (limited to 'sql/dawn.sql')
| -rw-r--r-- | sql/dawn.sql | 15 |
1 files changed, 8 insertions, 7 deletions
diff --git a/sql/dawn.sql b/sql/dawn.sql index 66c3058c..5ac6e602 100644 --- a/sql/dawn.sql +++ b/sql/dawn.sql @@ -1906,7 +1906,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; @@ -1921,7 +1921,7 @@ SELECT tranche_risk.date, tranche_id as trade_id, b.globeop_id, security_desc, i 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 + index_refprice, index_refspread, index_duration, initial_margin_percentage, indexfactor 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 @@ -1949,7 +1949,7 @@ SELECT tranche_risk.date, tranche_id as trade_id, security_desc, index, series, 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 + index_refprice, index_refspread, index_duration, initial_margin_percentage, indexfactor 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 @@ -1971,7 +1971,7 @@ SELECT tranche_risk.date, tranche_id as trade_id, security_desc, index, series, 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 + index_refprice, index_refspread, index_duration, initial_margin_percentage, indexfactor 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 @@ -1984,7 +1984,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) AS $$ +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 $$ DECLARE query text; fund text; @@ -2004,7 +2004,7 @@ query := 'SELECT date, security_desc, index, series, sum(admin_notional), avg(upfront), avg(index_refprice), avg(index_refspread), orig_attach, orig_detach, avg(index_duration), - avg(gamma) + avg(gamma), avg(indexfactor) FROM tranche_risk_%I GROUP BY date, index, security_desc, series, orig_attach, orig_detach, maturity HAVING date=$1 @@ -2024,7 +2024,7 @@ SELECT tranche_risk.date, tranche_id AS trade_id, fund, security_desc, index, se 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, ia as cpty_ia + index_refprice, index_refspread, index_duration, initial_margin_percentage, ia as cpty_ia, indexfactor FROM tranche_risk LEFT JOIN cds ON (tranche_id=id) LEFT JOIN index_version ON (security_id=redindexcode) @@ -2199,3 +2199,4 @@ CREATE TABLE tranche_cashflows( currency currency, PRIMARY KEY (date, tranche_id) ); + |