diff options
| -rw-r--r-- | python/Dawn/templates/cds_blotter.html | 4 | ||||
| -rw-r--r-- | sql/dawn.sql | 37 |
2 files changed, 23 insertions, 18 deletions
diff --git a/python/Dawn/templates/cds_blotter.html b/python/Dawn/templates/cds_blotter.html index e4cdf5ce..a1734f0f 100644 --- a/python/Dawn/templates/cds_blotter.html +++ b/python/Dawn/templates/cds_blotter.html @@ -25,8 +25,8 @@ <td>{{trade.security_id}}</td> <td>{{"{0:,.2f}".format(trade.notional)}}</td> <td>{{"{0:,.2f}".format(trade.upfront)}}</td> - <td>{{trade.attach if trade.attach is not none}}</td> - <td>{{trade.detach if trade.detach is not none}}</td> + <td>{{trade.orig_attach if trade.attach is not none}}</td> + <td>{{trade.orig_detach if trade.detach is not none}}</td> <td><a href="{{url_for('edit_counterparty', cpcode=trade.counterparty.code)}}">{{trade.counterparty.name}}</a></td> <td>{{trade.folder}}</td> diff --git a/sql/dawn.sql b/sql/dawn.sql index 161c734a..5894a684 100644 --- a/sql/dawn.sql +++ b/sql/dawn.sql @@ -315,10 +315,8 @@ END $$ LANGUAGE plpgsql; -create - trigger dealid after insert - on - capfloors for each row execute procedure auto_dealid() ; +CREATE TRIGGER dealid AFTER INSERT ON capfloors +FOR EACH ROW EXECUTE PROCEDURE auto_dealid() ; CREATE TRIGGER cds_attach AFTER INSERT OR UPDATE OF orig_attach, orig_detach ON cds FOR EACH ROW EXECUTE PROCEDURE update_attach(); @@ -544,13 +542,14 @@ DECLARE BEGIN query := 'WITH tmp AS (SELECT cds.security_id, cds.security_desc, cds.maturity, cds.fixed_rate, cds.currency, %s SUM(cds.notional * (CASE WHEN cds.protection=''Buyer'' THEN 1 ELSE -1 END)) -OVER (PARTITION BY cds.security_id, cds.maturity, cds.attach, cds.detach) AS notional +OVER (PARTITION BY cds.security_id, cds.maturity, cds.orig_attach, cds.orig_detach) AS notional FROM cds WHERE (cds.termination_date is NULL OR cds.termination_date> $1) and cds.trade_date <=$1 %s) SELECT DISTINCT ON (tmp.security_id, tmp.maturity %s) * FROM tmp WHERE tmp.notional!=0'; IF p_type = 'tranche' THEN - RETURN format(query, 'cds.attach,cds.detach,cds.initial_margin_percentage,', - 'AND cds.attach is NOT NULL', ',tmp.attach'); + RETURN format(query, 'cds.orig_attach,cds.orig_detach,cds.attach,cds.detach,' + 'cds.initial_margin_percentage,', + 'AND cds.orig_attach is NOT NULL', ',tmp.orig_attach'); ELSIF p_type = 'cds' THEN RETURN format(query, '', 'AND cds.attach is NULL AND cds.folder!=''MBSCDS''', ''); ELSIF p_type = 'abs' THEN @@ -613,7 +612,8 @@ $$ LANGUAGE plpgsql; CREATE OR REPLACE function list_tranche_positions(p_date date) RETURNS TABLE(security_id varchar(12), security_desc varchar(32), maturity date, - fixed_rate float, currency currency, attach smallint, detach smallint, + fixed_rate float, currency currency, orig_attach smallint, + orig_detach smallint, attach float, detach float, initial_margin_percentage float, notional float) AS $$ BEGIN @@ -724,16 +724,17 @@ days:=days_accrued(p_date); SELECT eurusd INTO eur_fx FROM fx WHERE date=p_date; RETURN QUERY WITH temp AS (SELECT a.*, c.index, c.series, c.version, c.basketid, d.tenor, - tranche_factor(a.attach, a.detach, c.indexfactor, c.cumulativeloss) AS fact + (a.detach-a.attach)/(a.orig_detach-a.orig_attach) * c.indexfactor / 100 AS fact FROM list_tranche_positions(p_date) a LEFT JOIN index_version c ON a.security_id=c.redindexcode LEFT JOIN index_maturity d USING (index, series, maturity)), -tranche_price AS (SELECT DISTINCT ON (basketid, tenor, attach, detach) basketid, tenor, e.attach, e.detach, upfront_mid, tranche_spread FROM +tranche_price AS (SELECT DISTINCT ON (basketid, tenor, orig_attach, orig_detach) basketid, tenor, + e.attach AS orig_attach, e.detach AS orig_detach, upfront_mid, tranche_spread FROM markit_tranche_quotes e WHERE quotedate BETWEEN p_date - interval '1 week' AND p_date - ORDER by basketid, tenor, attach, detach, quotedate desc), -risk_num AS (SELECT DISTINCT ON (series, attach, detach, tenor) * from risk_num_per_quote + ORDER by basketid, tenor, e.attach, e.detach, quotedate desc), +risk_num AS (SELECT DISTINCT ON (series, b.attach, b.detach, tenor) * from risk_num_per_quote b WHERE date BETWEEN p_date - interval '1 week' AND p_date - ORDER by series, attach, detach, tenor, date desc) + ORDER by series, b.attach, b.detach, tenor, date desc) SELECT temp.security_id, temp.security_desc, temp.index, temp.series, temp.version, temp.tenor, temp.maturity, temp.notional, temp.fact, tranche_spread::integer, @@ -743,10 +744,14 @@ SELECT temp.security_id, temp.security_desc, temp.index, temp.series, temp.versi (CASE WHEN temp.currency = 'EUR' THEN eur_fx ELSE 1 END), temp.initial_margin_percentage, risk_num.theta, risk_num.duration, risk_num.tranchedelta, risk_num.trancheupfrontmid, risk_num.indexrefprice, risk_num.indexrefspread, - temp.attach, temp.detach, risk_num.index_duration + temp.orig_attach, temp.orig_detach, risk_num.index_duration FROM temp -LEFT JOIN tranche_price USING (basketid, attach, detach, tenor) -LEFT JOIN risk_num USING (series, attach, detach, tenor); +LEFT JOIN tranche_price USING (basketid, orig_attach, orig_detach, tenor) +LEFT JOIN risk_num ON + temp.series=risk_num.series + AND temp.orig_attach = risk_num.attach + AND temp.orig_detach = risk_num.detach + AND temp.tenor = risk_num.tenor; END $$ LANGUAGE plpgsql; |