-- -*- mode: sql; sql-product: postgres; -*- CREATE TYPE bond_strat AS ENUM('M_STR_MAV', 'M_STR_SMEZZ', 'CSO_TRANCH', 'M_CLO_BB20', 'M_CLO_AAA', 'M_CLO_BBB', 'M_MTG_IO', 'M_MTG_THRU', 'M_MTG_GOOD', 'M_MTG_B4PR', 'M_MTG_RW', 'M_MTG_FP', 'M_MTG_LMG', 'M_MTG_SD', 'M_MTG_PR'); CREATE TYPE cds_strat AS ENUM('HEDGE_CSO', 'HEDGE_CLO', 'HEDGE_MAC', 'HEDGE_MBS', 'SER_IGSNR', 'SER_IGMEZ', 'SER_IGEQY', 'SER_IGINX', 'SER_HYSNR', 'SER_HYMEZ', 'SER_HYEQY', 'SER_HYINX', 'SER_IGCURVE', 'MBSCDS', 'IGOPTDEL', 'HYOPTDEL'); CREATE TYPE swaption_strat AS ENUM('IGPAYER', 'IGREC', 'HYPAYER', 'HYREC'); CREATE TYPE repo_strat AS ENUM(''); CREATE TYPE asset_class AS ENUM('CSO', 'Subprime', 'CLO', 'Tranches', 'Futures', 'Cash', 'FX', 'Cleared'); CREATE TYPE action AS ENUM('NEW', 'UPDATE', 'CANCEL'); CREATE TYPE currency AS ENUM('USD', 'CAD', 'EUR', 'YEN'); CREATE TYPE bbg_type AS ENUM('Mtge', 'Corp'); CREATE type day_count AS ENUM('ACT/360', 'ACT/ACT', '30/360', 'ACT/365'); CREATE type bus_day_convention AS ENUM('Modified Following', 'Following', 'Modified Preceding', 'Preceding', 'Second-Day-After', 'End-of-Month'); CREATE type index_type AS ENUM('IG', 'HY', 'EU', 'LCDX', 'XO'); CREATE TYPE tenor AS ENUM('6mo', '1yr', '2yr', '3yr', '4yr', '5yr', '7yr', '10yr'); CREATE TYPE swap_type AS ENUM('CD_INDEX', 'CD_INDEX_TRANCHE', 'CD_BASKET_TRANCHE', 'ABS_CDS'); CREATE TYPE repo_type AS ENUM('REPO', 'REVERSE REPO'); CREATE TYPE swaption_type AS ENUM('PAYER', 'RECEIVER'); CREATE TYPE isda AS ENUM('ISDA2014', 'ISDA2003Cred'); CREATE TYPE protection AS ENUM('Buyer', 'Seller'); CREATE TYPE call_notice AS ENUM('24H', '48H', '3D', '4D', '5D', '6D', '1W', '8D', '9D', '10D', '2W', '1M', '2M'); CREATE TABLE counterparties(code varchar(12) primary key, name text, city text, state varchar(2), location text, dtc_number integer, sales_contact text, sales_email text, sales_phone text, valuation_contact1 text, valuation_email1 text, valuation_contact2 text, valuation_email2 text, valuation_contact3 text, valuation_email3 text, valuation_contact4 text, valuation_email4 text, notes text, instructions text); CREATE INDEX ON counterparties(name); CREATE TABLE bonds(id serial primary key, dealid varchar(28), lastupdate timestamp DEFAULT now(), action action, folder bond_strat NOT NULL, custodian varchar(12) NOT NULL, cashaccount varchar(10) NOT NULL, cp_code varchar(12) NOT NULL REFERENCES counterparties(code) ON UPDATE CASCADE, trade_date date NOT NULL, settle_date date NOT NULL, cusip varchar(9), isin varchar(12), identifier varchar(12), description varchar(32) NOT NULL, buysell bool NOT NULL, faceamount float NOT NULL, price float NOT NULL, accrued float NOT NULL, asset_class asset_class, ticket text, principal_payment float, accrued_payment float, CONSTRAINT bonds2_check CHECK (cusip IS NOT NULL OR isin IS NOT NULL)); CREATE TRIGGER dealid AFTER INSERT ON bonds FOR EACH ROW EXECUTE PROCEDURE auto_dealid(); CREATE TABLE cds(id serial primary key, dealid varchar(28), lastupdate timestamp DEFAULT now(), action action, folder cds_strat NOT NULL, custodian varchar(12) NOT NULL, cashaccount varchar(10) NOT NULL, cp_code varchar(12) NOT NULL REFERENCES counterparties(code) ON UPDATE CASCADE, trade_date date NOT NULL, effective_date date NOT NULL, maturity date NOT NULL, currency currency NOT NULL, payment_rolldate bus_day_convention NOT NULL, notional float NOT NULL, fixed_rate float NOT NULL, day_count day_count NOT NULL, frequency smallint NOT NULL, protection protection NOT NULL, security_id varchar(12) NOT NULL, security_desc varchar(32) NOT NULL, upfront float NOT NULL, upfront_settle_date date NOT NULL, swap_type swap_type NOT NULL, attach smallint, detach smallint, clearing_facility varchar(12), isda_definition isda, termination_date date DEFAULT NULL, termination_amount float DEFAULT NULL, CONSTRAINT tranche_check CHECK (swap_type != 'CD_INDEX_TRANCHE' OR (attach IS NOT NULL AND detach IS NOT NULL))); ALTER TABLE cds OWNER TO dawn_user; CREATE TRIGGER cds_dealid AFTER INSERT ON cds FOR EACH ROW EXECUTE PROCEDURE auto_dealid(); CREATE TABLE repo(id serial primary key, dealid varchar(28), lastupdate timestamp DEFAULT now(), action action, folder bond_strat NOT NULL, custodian varchar(12) NOT NULL, cashaccount varchar(10) NOT NULL, cp_code varchar(12) NOT NULL REFERENCES counterparties(code) ON UPDATE CASCADE, trade_date date NOT NULL, settle_date date NOT NULL, cusip varchar(9), isin varchar(12), identifier varchar(12), description varchar(32) NOT NULL, transaction_indicator repo_type NOT NULL, faceamount float NOT NULL, price float NOT NULL, currency currency NOT NULL, expiration_date date, weighted_amount float, haircut float, repo_rate float NOT NULL, call_notice call_notice, daycount day_count, ticket text CHECK ( (haircut is NOT NULL AND weighted_amount is NULL) OR (haircut is NULL AND weighted_amount is NOT NULL) ); CHECK (cusip is NOT NULL OR isin is NOT NULL) ); CREATE TRIGGER repo_dealid AFTER INSERT ON repo FOR EACH ROW EXECUTE PROCEDURE auto_dealid(); ALTER TABLE repo OWNER TO dawn_user; CREATE TABLE swaptions(id serial PRIMARY KEY, dealid varchar(28), lastupdate timestamp DEFAULT now(), action action, folder swaption_strat NOT NULL, custodian varchar(12) NOT NULL, cashaccount varchar(10) NOT NULL, cp_code varchar(12) NOT NULL REFERENCES counterparties(code) ON UPDATE CASCADE, trade_date date NOT NULL, settle_date date NOT NULL, buysell bool NOT NULL, notional float NOT NULL, swaption_type swaption_type NOT NULL, strike float NOT NULL, price float NOT NULL, expiration_date date NOT NULL, initial_margin_percentage float, security_id varchar(12) NOT NULL, security_desc varchar(32) NOT NULL, maturity date NOT NULL, currency currency NOT NULL, fixed_rate float NOT NULL); CREATE OR REPLACE FUNCTION auto_dealid() RETURNS TRIGGER AS $$ DECLARE stub text; sqlstr text; BEGIN sqlstr:= 'UPDATE '|| TG_TABLE_NAME ||' SET %s WHERE id = %L AND dealid is NULL'; IF (TG_TABLE_NAME = 'bonds') THEN stub := 'SC_'; sqlstr := format(sqlstr, 'dealid = $1||upper(left(asset_class::text,3))||id, identifier = COALESCE(identifier, cusip, isin)', NEW.id); ELSIF (TG_TABLE_NAME = 'cds') THEN stub := 'SCCDS'; sqlstr := format(sqlstr, 'dealid = $1||id', NEW.id); ELSIF (TG_TABLE_NAME = 'repo') THEN stub := 'SC_REP'; sqlstr := format(sqlstr, 'dealid = $1||id', NEW.id); ELSIF (TG_TABLE_NAME = 'swaptions') THEN stub := 'SWPTN'; sqlstr := format(sqlstr, 'dealid = $1||id', NEW.id); END IF; EXECUTE sqlstr USING stub; RETURN NEW; END; $$ language plpgsql; CREATE TRIGGER swaptions_dealid AFTER INSERT ON swaptions FOR EACH ROW EXECUTE PROCEDURE auto_dealid(); ALTER TABLE swaptions OWNER TO dawn_user; CREATE TABLE securities(identifier varchar(12) PRIMARY KEY, cusip varchar(9), isin varchar(12), description varchar(32), face_amount float, maturity date, floater boolean, spread float, coupon float, frequency smallint, day_count day_count, first_coupon_date date, pay_delay smallint, currency currency default 'USD', bbg_type bbg_type default 'Mtge', asset_class asset_class, paid_down date default 'Infinity', start_accrued_date date); ALTER TABLE securities OWNER TO dawn_user; CREATE TABLE marks(date date, identifier varchar(12) REFERENCES securities(identifier) ON DELETE CASCADE ON UPDATE CASCADE, price float, PRIMARY KEY(identifier, date)); CREATE TABLE cashflow_history( identifier varchar(12) REFERENCES securities ON UPDATE CASCADE, date date, principal_bal float, principal float, interest float, coupon float, PRIMARY KEY (identifier, date)); CREATE TABLE risk_numbers( identifier varchar(12) REFERENCES securities, date date, delta float, index_delta index_type, duration float, wal float, undiscounted_price float, model_price float, PRIMARY KEY (identifier, date)); CREATE TABLE fx(date date PRIMARY KEY, eurusd float, cadusd float); CREATE TABLE external_marks( identifier varchar(12) REFERENCES securities ON UPDATE CASCADE, date date, mark float, source text, PRIMARY KEY (identifier, date, source)); CREATE TABLE mark_source_mapping( globeop text, final text, PRIMARY KEY (globeop)); CREATE OR REPLACE function list_marks(p_date date, include_unsettled boolean DEFAULT False) RETURNS TABLE(identifier varchar(12), price float) AS $$ BEGIN RETURN QUERY SELECT a.identifier, b.price FROM list_positions(p_date, Null, include_unsettled) a LEFT JOIN (SELECT DISTINCT ON (identifier) date, marks.identifier, marks.price FROM marks WHERE date<= p_date ORDER BY identifier, date DESC) b USING (identifier); END; $$ LANGUAGE plpgsql; CREATE OR REPLACE function list_marks_var(p_date date, VARIADIC p_identifier varchar(12)[]) RETURNS TABLE(identifier varchar(12), price float) AS $$ BEGIN RETURN QUERY SELECT a.identifier, b.price FROM (SELECT unnest(p_identifier) AS identifier) a LEFT JOIN (SELECT DISTINCT ON (identifier) date, marks.identifier, marks.price FROM marks WHERE date<= p_date ORDER BY identifier, date DESC) b USING (identifier); END; $$ LANGUAGE plpgsql; CREATE OR REPLACE function list_risk_numbers(p_date date, assetclass asset_class, include_unsettled boolean DEFAULT False) RETURNS TABLE(identifier varchar(12), description varchar(32), mark float, delta float, index_delta index_type, duration float, wal float, undiscounted_price float, model_price float) AS $$ BEGIN RETURN QUERY SELECT a.identifier, a.description, b.price, c.delta, c.index_delta, c.duration, c.wal, c.undiscounted_price, c.model_price FROM list_positions(p_date, assetclass, include_unsettled) a LEFT JOIN (SELECT DISTINCT ON (identifier) date, marks.identifier, marks.price FROM marks WHERE date<= p_date ORDER BY identifier, date DESC) b USING (identifier) LEFT JOIN (SELECT DISTINCT ON (identifier) * FROM risk_numbers WHERE date<=p_date ORDER BY identifier, date DESC) c USING (identifier); END; $$ LANGUAGE plpgsql; CREATE OR REPLACE function list_positions(p_date date, p_class asset_class DEFAULT NULL, include_unsettled boolean DEFAULT True) RETURNS TABLE(identifier varchar(12), description varchar(32), notional float, strategy bond_strat, curr_cpn float, start_accrued_date date, last_settle_date date, principal_payment float, accrued_payment float, currency currency, daycount day_count, bbg_type bbg_type) AS $$ DECLARE sqlquery text; DECLARE asset_opt text; DECLARE unsettled_opt text; BEGIN IF p_class is not NULL THEN asset_opt := 'and securities.asset_class=$2 '; ELSE asset_opt := ''; END IF; IF include_unsettled THEN unsettled_opt = 'or settle_date>=$1'; ELSE unsettled_opt = ''; END IF; sqlquery := 'WITH temp AS (SELECT bonds.identifier, asset_class, settle_date, folder, principal_payment, accrued_payment, sum(faceamount*(2*buysell::int-1) ) OVER (PARTITION BY bonds.identifier) notional FROM bonds WHERE trade_date<=$1) SELECT DISTINCT ON (temp.identifier) temp.identifier, securities.description, notional, folder, securities.coupon, start_accrued_date, settle_date, temp.principal_payment, temp.accrued_payment, securities.currency, securities.day_count, securities.bbg_type FROM temp LEFT JOIN securities USING (identifier) WHERE (temp.notional>0 '||unsettled_opt||') AND paid_down>$1 '||asset_opt ||' ORDER BY identifier, settle_date desc'; RETURN QUERY EXECUTE sqlquery USING p_date, p_class; END; $$ LANGUAGE plpgsql; CREATE OR REPLACE function list_positions_range(start_date date, end_date date, p_class asset_class DEFAULT NULL) RETURNS TABLE(identifier varchar(12), description varchar(32), notional float, strategy bond_strat, curr_cpn float, start_accrued_date date, last_settle_date date, principal_payment float, accrued_payment float, currency currency, daycount day_count, bbg_type bbg_type) AS $$ DECLARE sqlquery text; DECLARE asset_opt text; DECLARE unsettled_opt text; BEGIN sqlquery := 'WITH temp AS (SELECT bonds.identifier, asset_class, settle_date, folder, principal_payment, accrued_payment, sum(faceamount*(2*buysell::int-1) ) OVER (PARTITION BY bonds.identifier) notional FROM bonds WHERE trade_date <=$2) SELECT DISTINCT ON (temp.identifier) temp.identifier, securities.description, notional, folder, securities.coupon, start_accrued_date, settle_date, temp.principal_payment, temp.accrued_payment, securities.currency, securities.day_count, securities.bbg_type FROM temp LEFT JOIN securities USING (identifier) WHERE (temp.notional>0 or (temp.notional==0 and settle_date>=$1)) AND paid_down>$1 '||asset_opt ||' ORDER BY identifier, settle_date desc'; RETURN QUERY EXECUTE sqlquery USING start_date, end_date, p_class; END; $$ LANGUAGE plpgsql; CREATE OR REPLACE function risk_positions(p_date date, p_assetclass asset_class) RETURNS TABLE (description varchar(32), identifier varchar(12), notional float, price float, strategy bond_strat, factor float, local_market_value float, usd_market_value float, curr_cpn float, int_acc float, last_pay_date date, principal_payment float, accrued_payment float, last_settle_date date) AS $$ BEGIN RETURN QUERY SELECT a.description, a.identifier, a.notional, c.price, a.strategy, coalesce(b.factor, 1), c.price/100. * a.notional * (CASE WHEN coalesce(b.factor,1)=0 THEN 1 ELSE coalesce(b.factor,1) END), c.price/100. * a.notional * (CASE WHEN coalesce(b.factor,1)=0 THEN 1 ELSE coalesce(b.factor,1) END) * fxrate, b.coupon, a.notional * coalesce(b.factor,1) * fxrate * yearfrac(case WHEN start_accrued_date>=p_date+1 THEN b.prev_cpn_date ELSE start_accrued_date END, p_date+1, daycount) * b.coupon/100., b.last_pay_date, a.principal_payment, a.accrued_payment, a.last_settle_date FROM list_positions(p_date, p_assetclass) a LEFT JOIN factors_history(p_date) b USING (identifier) LEFT JOIN list_marks(p_date, True) c USING (identifier) LEFT JOIN fx_rate(p_date) USING (currency) ORDER by identifier asc; END; $$ LANGUAGE plpgsql; CREATE OR REPLACE VIEW orig_cds AS SELECT DISTINCT ON (dealid) dealid, folder, index, series, version, tenor, fixed_rate, notional, upfront, protection, attach, detach, trade_date, upfront_settle_date FROM cds JOIN index_desc ON index_desc.redindexcode=cds.security_id AND index_desc.maturity=cds.maturity ORDER BY dealid, trade_date; CREATE OR REPLACE VIEW external_marks_mapped AS select date, identifier, mark, b.final as source from external_marks a left join mark_source_mapping b on a.source = b.globeop order by a.date asc; CREATE OR REPLACE function query_positions(p_type text DEFAULT NULL) RETURNS text AS $$ DECLARE query text; 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)) 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'); ELSIF p_type = 'cds' THEN RETURN format(query, '', 'AND cds.attach is NULL AND cds.folder!=''MBSCDS''', ''); ELSIF p_type = 'abs' THEN RETURN format(query, '', 'AND cds.folder=''MBSCDS''', ''); ELSE RETURN format(query, 'cds.attach, cds.detach,', '', ',tmp.attach'); END IF; END; $$ 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 $$ BEGIN RETURN QUERY EXECUTE query_positions('cds') USING p_date; 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, attach smallint, detach smallint, notional float) AS $$ BEGIN RETURN QUERY EXECUTE query_positions('tranche') USING p_date; END; $$ 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 $$ 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, clean_nav float, accrued float) AS $$ DECLARE days integer; BEGIN days:=days_accrued(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, tenor, closeprice, index_quotes.duration FROM index_quotes WHERE date=p_date) SELECT temp.security_id, temp.security_desc, temp.maturity, temp.notional, temp.fact, temp.fixed_rate/100, index_price.duration, (index_price.closeprice/100.-1)*temp.notional*temp.fact, temp.notional*temp.fixed_rate/100.*temp.fact*days/360 FROM temp LEFT JOIN index_price USING (index, series, version, tenor); 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 $$ DECLARE days integer; BEGIN days:=days_accrued(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 FROM list_tranche_positions(p_date) a 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, tranche_spread::integer, -upfront_mid*temp.notional*temp.fact, temp.notional*tranche_spread/10000.*temp.fact*days/360 FROM temp LEFT JOIN tranche_price USING (basketid, attach, detach, tenor); END $$ LANGUAGE plpgsql; CREATE OR REPLACE function list_abscds_marks(p_date date) RETURNS TABLE(security_id varchar(12), cusip varchar(9), security_desc varchar(32), maturity date, notional float, factor float, fixed_rate float, clean_nav float, accrued float) AS $$ BEGIN RETURN QUERY WITH temp AS (SELECT a.*, b.price, c.factor, d.start_accrued_date, d.cusip FROM list_abscds_positions(p_date) a LEFT JOIN (SELECT DISTINCT ON (identifier) date, marks.identifier, marks.price FROM marks WHERE date<=p_date ORDER BY identifier, date desc) b ON a.security_id=b.identifier LEFT JOIN factors_history(p_date) c ON a.security_id=c.identifier LEFT JOIN securities d ON a.security_id=d.identifier) SELECT temp.security_id, temp.cusip, temp.security_desc, temp.maturity, temp.notional, temp.factor, temp.fixed_rate, temp.notional*temp.factor*(temp.price-100)/100, -yearfrac(temp.start_accrued_date, p_date+1, 'ACT/360')*temp.fixed_rate/100*temp.notional*temp.factor FROM temp; END $$ LANGUAGE plpgsql; CREATE OR REPLACE function days_accrued(p_date date) RETURNS integer AS $$ FROM dates import days_accrued RETURN days_accrued(p_date) $$ LANGUAGE plpython2u; CREATE MATERIALIZED VIEW factors_history AS WITH temp AS ( SELECT c.date, c.identifier, c.principal, c.principal_bal, c.interest, lead(c.coupon) OVER w AS coupon, (- c.principal) - c.principal_bal + lag(c.principal_bal) OVER w AS losses FROM cashflow_history c WINDOW w AS (PARTITION BY c.identifier ORDER BY c.date) ) SELECT temp.date AS last_pay_date, temp.date-securities.pay_delay AS prev_cpn_date, temp.identifier, temp.principal_bal / securities.face_amount AS factor, temp.principal / securities.face_amount * 100::float AS principal, temp.interest / securities.face_amount * 100::float AS interest, temp.losses / securities.face_amount * 100::float AS losses, COALESCE(temp.coupon, securities.coupon) AS coupon FROM temp JOIN securities USING (identifier); CREATE UNIQUE INDEX factors_history_pkey ON factors_history(prev_cpn_date, identifier); CREATE OR REPLACE function factors_history(p_date date) RETURNS SETOF factors_history AS $$ BEGIN RETURN QUERY SELECT DISTINCT ON (identifier) * FROM factors_history WHERE prev_cpn_date<=p_date ORDER BY identifier, prev_cpn_date desc; END; $$ LANGUAGE plpgsql; CREATE OR REPLACE FUNCTION isleapyear ( D date ) RETURNS boolean AS $$ DECLARE y INTEGER; BEGIN y := extract (year from D); if (y % 4) != 0 then return false; end if; if (y % 400) = 0 then return true; end if; return (( y % 100) != 0); END; $$ LANGUAGE 'plpgsql'; CREATE OR REPLACE FUNCTION yearfrac(date1 date, date2 date, daycount day_count) RETURNS float AS $$ DECLARE factor float; y1 integer; y2 integer; m1 integer; m2 integer; d1 integer; d2 integer; BEGIN IF daycount='30/360' THEN y1 := extract(YEAR FROM date1); y2 := extract(YEAR FROM date2); m1 := extract(MONTH FROM date1); m2 := extract(MONTH FROM date2); d1 := extract(DAY FROM date1); d2 := extract(DAY FROM date2); IF d2=31 and (d1=30 or d1=31) THEN d2:=30; END IF; IF d1=31 THEN d1:=30; END IF; factor:= (360*(y2-y1) + 30*(m2-m1)+d2-d1)/360.; ELSIF daycount='ACT/365' THEN factor:=(date2-date1)/365.; ELSIF daycount='ACT/360' THEN factor:=(date2-date1)/360.; ELSIF daycount='ACT/ACT' THEN IF isleapyear(date1) THEN factor:=(date2-date1)/366.; ELSE factor:=(date2-date1)/365.; END IF; END IF; RETURN factor; END; $$ LANGUAGE plpgsql; CREATE OR REPLACE function tranche_factor(attach smallint, detach smallint, index_factor float, cumulativeloss float) RETURNS float AS $$ DECLARE newattach float; newdetach float; BEGIN newattach:=LEAST(GREATEST((attach-100*cumulativeloss)/index_factor, 0), 1); newdetach:=LEAST(GREATEST((detach-100*cumulativeloss)/index_factor, 0), 1); RETURN (newdetach-newattach)/(detach-attach)*index_factor; END; $$ LANGUAGE plpgsql; CREATE OR REPLACE function fx_rate(p_date date) RETURNS TABLE(currency currency, fxrate float) AS $$ BEGIN RETURN QUERY SELECT unnest(Array['USD', 'EUR', 'CAD'])::currency, unnest(Array[1, eurusd, cadusd]) FROM fx WHERE date<=p_date ORDER by date desc LIMIT 3; END; $$ LANGUAGE plpgsql; -- Not sure how to map enums so use text for now CREATE FOREIGN TABLE priced( cusip varchar(9), model_version smallint, normalization text, timestamp timestamp, pv float, pv_RnW float, pv_io float, pv_po float, modDur float, modDur_io float, modDur_po float, wal float, wal_width float, wal_io float, wal_po float, delta_hpi float, delta_ir float, delta_ir_io float, delta_ir_po float, delta_mult float, delta_yield float, delta_quantile float, delta_RnW float, tot_gamma_hpi float, tot_gamma_ir float, tot_gamma float) SERVER mysql_server OPTIONS (dbname 'rmbs_model'); -- We want to use INHERITS here, but will only be available on 9.5 CREATE FOREIGN TABLE priced_orig_ntl( cusip varchar(9), model_version smallint, normalization text, timestamp timestamp, pv float, pv_RnW float, pv_io float, pv_po float, modDur float, modDur_io float, modDur_po float, wal float, wal_width float, wal_io float, wal_po float, delta_hpi float, delta_ir float, delta_ir_io float, delta_ir_po float, delta_mult float, delta_yield float, delta_quantile float, delta_RnW float, tot_gamma_hpi float, tot_gamma_ir float, tot_gamma float) SERVER mysql_server OPTIONS (dbname 'rmbs_model'); CREATE FOREIGN TABLE priced_percentiles( cusip varchar(9), model_version smallint, percentile float, normalization text, timestamp timestamp, pv float, pv_io float, pv_po float, modDur float, modDur_io float, modDur_po float, wal float, wal_width float, wal_io float, wal_po float) SERVER mysql_server OPTIONS (dbname 'rmbs_model'); CREATE FOREIGN TABLE priced_percentiles_orig_ntl( cusip varchar(9), model_version smallint, percentile float, normalization text, timestamp timestamp, pv float, pv_io float, pv_po float, modDur float, modDur_io float, modDur_po float, wal float, wal_width float, wal_io float, wal_po float) SERVER mysql_server OPTIONS (dbname 'rmbs_model'); CREATE FOREIGN TABLE index_desc( basketid integer, index text, series smallint, version smallint, indexfactor float, cumulativeloss float, lastdate date, redindexcode text, tenor tenor, maturity date, coupon integer) SERVER postgresql_server; CREATE FOREIGN TABLE index_version( basketID serial, Index index_type, Series smallint, Version smallint, IndexFactor float, CumulativeLoss float, lastdate date, redindexcode text) SERVER postgresql_server; CREATE FOREIGN TABLE index_maturity( index index_type, series smallint, tenor tenor, maturity date) SERVER postgresql_server; CREATE FOREIGN TABLE index_quotes( date date, index index_type, series smallint, version smallint, tenor tenor, closeprice float, closespread float, modelprice float, modelspread float, adjcloseprice float, adjmodelprice float, duration float, theta float) SERVER postgresql_server; CREATE FOREIGN TABLE markit_tranche_quotes( quotedate date, basketid integer, tenor tenor, attach smallint, detach smallint, upfront_bid float, upfront_mid float, upfront_ask float, tranche_spread smallint, index_price float) SERVER postgresql_server; -- #bonds that get written down -- update securities set identifier='073879R75_A' where identifier='073879R75'; -- update bonds set identifier='073879R75_A' where identifier='073879R75'; -- refresh materialized view factors_history; CREATE OR REPLACE function list_subprime_data(p_date date, orig_flag bool, VARIADIC p_cusip varchar(9)[]) RETURNS TABLE(v1 float, v2 float, v3 float, duration float, percentile5 float, percentile25 float, percentile50 float, percentile75 float, percentile95 float, yield_delta float, wal float, io_pv float, po_pv float, rnw float, ir_io_delta float, ir_po_delta float, hpi_delta float, delta_rnw float, v1pv_RnW float) AS $$ DECLARE query text; opt_constraint text; BEGIN IF NOT orig_flag THEN opt_constraint := 'AND normalization =''current_notional'''; ELSE opt_constraint := ''; END IF; query:= 'WITH left_table AS ( WITH temp AS (SELECT * from %I where date(timestamp)=$1) SELECT a.cusip, a.pv as v1, b.pv as v2, c.pv AS v3, a.modDur, c.delta_yield, c.wal, c.pv_io, c.pv_po, c.pv_RnW, c.delta_ir_io, c.delta_ir_po, c.delta_hpi, c.delta_RnW, a.pv_RnW as v1pv_RnW FROM (SELECT * FROM temp WHERE model_version=1) a, (SELECT * FROM temp WHERE model_version=2) b, (SELECT * FROM temp WHERE model_version=3) c WHERE a.cusip = b.cusip AND a.cusip=c.cusip), right_table AS ( WITH temp AS( SELECT cusip, PV, percentile FROM %I WHERE timestamp BETWEEN $1 AND $1 + INTERVAL ''1 day'' AND model_version=3 AND percentile in (5, 25, 50, 75, 95)' || opt_constraint ||') SELECT a.cusip, a.PV AS pv5, b.PV AS pv25, c.PV AS pv50, d.PV AS pv75, e.PV AS pv95 FROM (SELECT cusip, PV FROM temp WHERE percentile=5) a, (SELECT cusip, PV FROM temp WHERE percentile=25) b, (SELECT cusip, PV FROM temp WHERE percentile=50) c, (SELECT cusip, PV FROM temp WHERE percentile=75) d, (SELECT cusip, PV FROM temp WHERE percentile=95) e WHERE a.cusip=b.cusip AND b.cusip=c.cusip and c.cusip=d.cusip and d.cusip=e.cusip and e.cusip=a.cusip) SELECT left_table.v1, left_table.v2, left_table.v3, left_table.moddur, right_table.pv5, right_table.pv25, right_table.pv50, right_table.pv75, right_table.pv95, left_table.delta_yield, left_table.wal, left_table.pv_io, left_table.pv_po, left_table.pv_RnW, left_table.delta_ir_io, left_table.delta_ir_po, left_table.delta_hpi, left_table.delta_RnW, left_table.v1pv_RnW FROM (SELECT unnest($2) AS cusip) l LEFT JOIN left_table ON left_table.cusip=l.cusip LEFT JOIN right_table ON left_table.cusip=right_table.cusip'; IF orig_flag THEN query := format(query, 'priced_orig_ntl', 'priced_percentiles_orig_ntl'); ELSE query := format(query, 'priced', 'priced_percentiles'); END IF; RETURN QUERY EXECUTE query USING p_date, p_cusip; END $$ LANGUAGE plpgsql;