diff options
| -rw-r--r-- | sql/dawn.sql | 44 |
1 files changed, 30 insertions, 14 deletions
diff --git a/sql/dawn.sql b/sql/dawn.sql index 678db4e4..076b6fd9 100644 --- a/sql/dawn.sql +++ b/sql/dawn.sql @@ -415,13 +415,14 @@ DECLARE BEGIN query := 'WITH tmp AS (SELECT cds.security_id, cds.security_desc, cds.maturity, -cds.fixed_rate, %s SUM(cds.notional * (CASE WHEN cds.protection=''Buyer'' THEN -1 ELSE 1 END)) +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 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,', 'AND cds.attach is NOT NULL', ',tmp.attach'); + RETURN format(query, 'cds.attach,cds.detach,cds.initial_margin_percentage,', + 'AND cds.attach is NOT NULL', ',tmp.attach'); ELSIF p_type = 'cds' THEN RETURN format(query, '', 'AND cds.attach is NULL AND cds.folder!=''MBSCDS''', ''); ELSIF p_type = 'abs' THEN @@ -434,7 +435,7 @@ $$ LANGUAGE plpgsql; CREATE OR REPLACE function list_cds_positions (p_date date) RETURNS TABLE(security_id varchar(12), security_desc varchar(32), maturity date, - fixed_rate float, notional float) AS $$ + fixed_rate float, currency currency, notional float) AS $$ BEGIN RETURN QUERY EXECUTE query_positions('cds') USING p_date; END; @@ -456,7 +457,9 @@ $$ 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, attach smallint, detach smallint, notional float) AS $$ + fixed_rate float, currency currency, attach smallint, detach smallint, + initial_margin_percentage float, + notional float) AS $$ BEGIN RETURN QUERY EXECUTE query_positions('tranche') USING p_date; END; @@ -464,29 +467,37 @@ $$ LANGUAGE plpgsql; CREATE OR REPLACE function list_abscds_positions(p_date date) RETURNS TABLE(security_id varchar(12), security_desc varchar(32), maturity date, - fixed_rate float, notional float) AS $$ + fixed_rate float, currency currency, notional float) AS $$ BEGIN RETURN QUERY EXECUTE query_positions('abs') USING p_date; END; $$ LANGUAGE plpgsql; CREATE OR REPLACE function list_cds_marks(p_date date) -RETURNS TABLE(security_id varchar(12), security_desc varchar(32), maturity date, - notional float, factor float, coupon float, duration float, tenor tenor, - price float, closespread float, clean_nav float, accrued float) AS $$ +RETURNS TABLE(security_id varchar(12), security_desc varchar(32), p_index index_type, p_series smallint, + p_version smallint, tenor tenor, maturity date, notional float, factor float, + coupon float, duration float, price float, closespread float, + clean_nav float, accrued float) AS $$ DECLARE days integer; + eur_fx float; BEGIN 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.tenor, c.indexfactor/100. AS fact FROM list_cds_positions(p_date) a LEFT JOIN index_desc c ON (a.security_id=c.redindexcode AND a.maturity=c.maturity)), index_price AS (SELECT index, series, version, d.tenor, closeprice, d.duration, d.closespread FROM index_quotes d WHERE date=p_date) -SELECT temp.security_id, temp.security_desc, temp.maturity, temp.notional, temp.fact, temp.fixed_rate/100, -index_price.duration, temp.tenor, index_price.closeprice, index_price.closespread, (index_price.closeprice/100.-1)*temp.notional*temp.fact, -temp.notional*temp.fixed_rate/100.*temp.fact*days/360 +SELECT + temp.security_id, temp.security_desc, temp.index, temp.series, temp.version, temp.tenor, + temp.maturity, temp.notional, temp.fact, temp.fixed_rate/100, + index_price.duration, index_price.closeprice, index_price.closespread, + (index_price.closeprice/100.-1) * temp.notional * temp.fact * + (CASE WHEN temp.currency = 'EUR' THEN eur_fx ELSE 1 END), + temp.notional * temp.fixed_rate/100. * temp.fact * days / 360 * + (CASE WHEN temp.currency = 'EUR' THEN eur_fx ELSE 1 END) FROM temp LEFT JOIN index_price USING (index, series, version, tenor); END @@ -497,8 +508,10 @@ RETURNS TABLE(security_id varchar(12), security_desc varchar(32), maturity date, notional float, factor float, coupon integer, clean_nav float, accrued float) AS $$ DECLARE days integer; + euf_fx float; BEGIN 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, d.basketid, d.tenor, tranche_factor(a.attach, a.detach, c.indexfactor, c.cumulativeloss) AS fact @@ -507,9 +520,12 @@ WITH temp AS (SELECT a.*, c.index, c.series, c.version, d.basketid, d.tenor, LEFT JOIN index_desc d USING (redindexcode, maturity)), tranche_price AS (SELECT DISTINCT ON (basketid, tenor, attach, detach) basketid, tenor, attach, detach, upfront_mid, tranche_spread FROM markit_tranche_quotes WHERE quotedate<=p_date ORDER by basketid, tenor, attach, detach, quotedate desc) -SELECT temp.security_id, temp.security_desc, temp.maturity, temp.notional, temp.fact, tranche_spread::integer, --upfront_mid*temp.notional*temp.fact, -temp.notional*tranche_spread/10000.*temp.fact*days/360 +SELECT temp.security_id, temp.security_desc, temp.maturity, temp.notional, temp.fact, + tranche_spread::integer, + temp.notional * temp.fact * (-upfront_mid) * + (CASE WHEN temp.currency = 'EUR' THEN eur_fx ELSE 1 END), + temp.notional * temp.fact * tranche_spread/10000. * days / 360 * + (CASE WHEN temp.currency = 'EUR' THEN eur_fx ELSE 1 END) FROM temp LEFT JOIN tranche_price USING (basketid, attach, detach, tenor); END |