diff options
Diffstat (limited to 'sql')
| -rw-r--r-- | sql/dawn.sql | 32 |
1 files changed, 32 insertions, 0 deletions
diff --git a/sql/dawn.sql b/sql/dawn.sql index e256da29..cca1bfaa 100644 --- a/sql/dawn.sql +++ b/sql/dawn.sql @@ -1010,6 +1010,10 @@ CREATE TABLE bowdst_pnl( PRIMARY KEY (begin_date, end_date, row) ); +CREATE TABLE crt_model_versions( + begin_date date, + model_version text +); CREATE OR REPLACE function list_marks(p_date date) RETURNS TABLE(p_date date, identifier varchar(12), price float) AS $$ @@ -2201,6 +2205,12 @@ CREATE FOREIGN TABLE simulations_nonagency( SERVER mysql_server OPTIONS (dbname 'rmbs_model'); +CREATE FOREIGN TABLE bond_types( + bond text, + bond_type crt_bond_type) + SERVER mysql_server + OPTIONS(dbname 'crt'); + -- #bonds that get written down -- update securities set identifier='073879R75_A' where identifier='073879R75'; -- update bonds set identifier='073879R75_A' where identifier='073879R75'; @@ -2271,6 +2281,28 @@ SET enable_nestloop = on; END $$ LANGUAGE plpgsql; +CREATE OR REPLACE function list_crt_data(p_date date, fund fund DEFAULT 'SERCGMAST'::fund) +RETURNS TABLE(identifier varchar(9), model_time timestamp, pv float, duration float4, dm float4, delta_ir float4, delta_economy float4, + convexity_ir float4, convexity_economy float4, bondtype crt_bond_type, delta numeric) as $$ +BEGIN + RETURN QUERY + SELECT c.identifier, a.timestamp, a.pv, duration_fw, a.dm, "delta.ir", "delta.economy", + "convexity.ir", "convexity.economy", bond_type, value + from (select * from list_positions(p_date, 'CRT', true, fund)) c + left join priced_at_market a on c.identifier = a.cusip + JOIN (SELECT cusip, MAX(timestamp) timestamp + FROM priced_at_market where timestamp between p_date + AND p_date + interval '1 DAY' + and model_des = (select model_version from crt_model_versions where until_date > p_date + order by until_date asc limit 1) GROUP BY cusip) b + using (cusip, timestamp) + LEFT JOIN map_cusip using (cusip) + LEFT JOIN bond_types USING (bond) + LEFT JOIN (select bond_type, value from beta_estimates + where date = (SELECT MAX(date) as date FROM beta_estimates where date <= greatest(p_date, DATE '2021-02-01'))) be + using (bond_type); +END +$$ LANGUAGE plpgsql; CREATE TABLE swaption_marks( dealid varchar(28) REFERENCES swaptions(dealid), |