diff options
| -rw-r--r-- | sql/dawn.sql | 91 |
1 files changed, 69 insertions, 22 deletions
diff --git a/sql/dawn.sql b/sql/dawn.sql index 076b6fd9..dc24a865 100644 --- a/sql/dawn.sql +++ b/sql/dawn.sql @@ -433,20 +433,29 @@ END IF; END; $$ LANGUAGE plpgsql; -CREATE OR REPLACE function list_cds_positions (p_date date) +CREATE OR REPLACE function list_cds_positions (p_date date, strat cds_strat DEFAULT NULL::cds_strat) RETURNS TABLE(security_id varchar(12), security_desc varchar(32), maturity date, fixed_rate float, currency currency, notional float) AS $$ BEGIN -RETURN QUERY EXECUTE query_positions('cds') USING p_date; +IF strat IS NULL THEN + RETURN QUERY EXECUTE query_positions('cds') USING p_date; +ELSE + RETURN QUERY SELECT a.security_id, a.security_desc, a.maturity, a.fixed_rate, + a.currency, a.notional + FROM list_cds_positions_by_strat(p_date) a + WHERE folder=strat; +END IF; END; $$ LANGUAGE plpgsql; + CREATE OR REPLACE function list_cds_positions_by_strat(p_date date) RETURNS TABLE(security_id varchar(12), security_desc varchar(32), maturity date, - fixed_rate float, folder cds_strat, notional float) AS $$ + fixed_rate float, currency currency, folder cds_strat, notional float) AS $$ BEGIN RETURN QUERY - WITH tmp AS (SELECT cds.security_id, cds.security_desc, cds.maturity, cds.fixed_rate, cds.folder, + WITH tmp AS (SELECT cds.security_id, cds.security_desc, cds.maturity, cds.fixed_rate, + cds.currency, cds.folder, SUM(cds.notional * (CASE WHEN cds.protection='Buyer' THEN -1 ELSE 1 END)) OVER (PARTITION BY cds.security_id, cds.maturity, cds.folder) AS notional FROM cds WHERE (cds.termination_date is NULL OR cds.termination_date > p_date) AND @@ -455,6 +464,25 @@ RETURN QUERY END; $$ LANGUAGE plpgsql; +CREATE OR REPLACE FUNCTION cds_globeop_name(redcode text, fixed_rate float, maturity date, + index index_type, tenor tenor) +RETURNS text AS $$ +DECLARE + result text; + stub text; + +BEGIN +result := 'CDS_%s%s_%s.0000000000_ICE-CREDIT_SGFCM_%s'; +IF tenor != '5yr' AND index = 'IG' THEN + stub := '.' || left(tenor::text, -2); +ELSE + stub := ''; +END IF; +result := format(result, redcode, stub, fixed_rate, to_char(maturity, 'YYYYMMDD')); +RETURN result; +END; +$$ 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, @@ -473,42 +501,58 @@ RETURN QUERY EXECUTE query_positions('abs') USING p_date; END; $$ LANGUAGE plpgsql; -CREATE OR REPLACE function list_cds_marks(p_date date) +CREATE OR REPLACE function list_cds_marks(p_date date, strat cds_strat DEFAULT NULL::cds_strat) 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, + name text, coupon float, duration float, price float, closespread float, clean_nav float, accrued float) AS $$ DECLARE days integer; eur_fx float; + params text; + sqlquery text; 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 +IF strat IS NOT NULL THEN + params := '$1, $4'; +ELSE + params := '$1'; +END IF; + +sqlquery := format( +'WITH temp AS (SELECT a.*, c.index, c.series, c.version, c.tenor, c.indexfactor/100. AS fact, + cds_globeop_name(a.security_id, a.fixed_rate, a.maturity, c.index, c.tenor) + FROM list_cds_positions(%s) 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) + FROM index_quotes d WHERE date=$1) 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, + temp.maturity, temp.notional, temp.fact, temp.cds_globeop_name, 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) + (CASE WHEN temp.currency = ''EUR'' THEN $2 ELSE 1 END), + temp.notional * temp.fixed_rate/100. * temp.fact * $3 / 360 * + (CASE WHEN temp.currency = ''EUR'' THEN $2 ELSE 1 END) FROM temp -LEFT JOIN index_price USING (index, series, version, tenor); -END +LEFT JOIN index_price USING (index, series, version, tenor)', params); +IF strat IS NOT NULL THEN + RETURN QUERY EXECUTE sqlquery USING p_date, eur_fx, days, strat; +ELSE + RETURN QUERY EXECUTE sqlquery USING p_date, eur_fx, days; +END IF; +END; $$ LANGUAGE plpgsql; CREATE OR REPLACE function list_tranche_marks(p_date date) -RETURNS TABLE(security_id varchar(12), security_desc varchar(32), maturity date, - notional float, factor float, coupon integer, 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, p_tenor tenor, maturity date, + notional float, factor float, coupon integer, clean_nav float, + accrued float, initial_margin_percentage float) AS $$ DECLARE days integer; - euf_fx float; + eur_fx float; BEGIN days:=days_accrued(p_date); SELECT eurusd INTO eur_fx FROM fx WHERE date=p_date; @@ -519,13 +563,16 @@ WITH temp AS (SELECT a.*, c.index, c.series, c.version, d.basketid, d.tenor, LEFT JOIN index_version c ON a.security_id=c.redindexcode 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, + markit_tranche_quotes WHERE quotedate BETWEEN p_date - interval '1 week' AND p_date + ORDER by basketid, tenor, attach, detach, quotedate 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, 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) + (CASE WHEN temp.currency = 'EUR' THEN eur_fx ELSE 1 END), + temp.initial_margin_percentage FROM temp LEFT JOIN tranche_price USING (basketid, attach, detach, tenor); END |