1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
|
CREATE TABLE tranche_data (
QuoteDate date,
TrancheId integer,
BasketId integer,
Maturity date,
Tenor varchar(4),
RefBasketPrice float,
BPS varchar(1),
BasketDuration float,
TQC varchar(1),
TrancheDuration float,
TrancheDelta float,
CorrAtDetachment float,
Basis float,
QuoteSource varchar(2),
Index varchar(4),
Series smallint,
Upfront float,
Running float,
Attach smallint,
Detach smallint,
IndexFactor float,
CumulativeLoss float
);
GRANT ALL ON tranche_data TO mlpdb_user;
CREATE TABLE index_desc(
BasketID integer,
Index varchar(4),
Series smallint,
Maturity date,
Tenor varchar(4),
IndexFactor float,
CumulativeLoss float,
PRIMARY KEY(BasketID, Tenor)
);
GRANT ALL ON index_desc TO mlpdb_user;
CREATE TABLE tranche_desc(
TrancheID integer,
BasketID integer,
Tenor varchar(4),
Attach smallint,
Detach smallint,
Foreign Key(BasketID, Tenor) REFERENCES index_desc(BasketID, Tenor),
Primary Key(TrancheID, Tenor)
);
GRANT ALL ON tranche_desc TO mlpdb_user;
CREATE TABLE quotes (
QuoteDate date,
TrancheID integer,
BasketID integer,
RefBasketPrice float,
BasketDuration float,
TrancheDuration float,
TrancheDelta float,
CorrAtDetachment float,
Basis float,
QuoteSource varchar(4),
Upfront float,
Running float,
Tenor varchar(4),
FOREIGN KEY(BasketID, Tenor) REFERENCES index_desc(BasketID, Tenor),
FOREIGN KEY(TrancheID, Tenor) REFERENCES tranche_desc(TrancheID, Tenor)
);
GRANT ALL ON quotes TO mlpdb_user;
-- Idiosyncracies:
-- for Itraxx 9 index:
-- tr.id coupon type
-- 3-6 98155 all running
-- 98154 U+500
-- 6-9 98160 all running
-- 166395 U+300
-- 98159 U+500
-- For IG 9 index:
-- tr.id coupon type
-- 3-7 162290 all running
-- 162291 U+500
-- 7-10 162298 all running
-- 162300 U+500
-- 10-15 162301 all running
-- 162304 U+500
-- 15-30 162305 all running
-- 162306 U+500
-- 30-100 162307 all running
-- 162308 U+500
-- All other tranches should have a unique trancheid for a given basketid, attach, detach combination
CREATE TYPE curr AS ENUM('USD', 'EUR', 'JPY', 'GBP', 'CAD');
CREATE TYPE sen AS ENUM('Senior', 'Subordinated');
CREATE TYPE bbgSource AS ENUM('MSG1', 'CBIN', 'CBGN');
CREATE TYPE DocClause AS ENUM('No Restructuring', 'Modified Modified Restructurin', 'Full Restructuring');
CREATE TYPE tenor AS ENUM('6mo', '1yr', '2yr', '3yr', '4yr', '5yr', '7yr', '10yr');
CREATE TABLE CDS_Issuers(
Name text,
company_id integer,
ticker text,
currency curr,
seniority sen,
doc_clause DocClause,
cds_curve text[8],
index_list integer[],
PRIMARY KEY(company_id));
GRANT ALL ON CDS_Issuers to mlpdb_user;
CREATE TABLE cds_quotes(
Date Date,
curve_ticker text,
UpfrontBid float,
UpfrontAsk float,
RunningBid float,
RunningAsk float,
Source bbgSource,
Recovery float,
PRIMARY KEY(curve_ticker, Date));
GRANT ALL ON cds_quotes to mlpdb_user;
CREATE OR REPLACE FUNCTION nameToBasketID(index_name varchar(4)) RETURNS integer AS $$
DECLARE
p_index varchar(2);
p_series smallint;
p_basketid integer;
BEGIN
p_index := left(index_name, 2);
p_series := right(index_name, -2);
SELECT MAX(basketid) INTO p_basketid FROM index_desc WHERE Index=p_index and Series=p_series;
RETURN p_basketid;
END;
$$ LANGUAGE plpgsql;
CREATE OR REPLACE FUNCTION index_members(index_name varchar(4))
RETURNS SETOF CDS_Issuers AS $$
DECLARE
basketid integer;
BEGIN
SELECT nameToBasketID(index_name) INTO basketid;
RETURN QUERY SELECT * FROM CDS_Issuers WHERE index_list @> Array[basketid];
END;
$$ LANGUAGE plpgsql;
CREATE OR REPLACE FUNCTION historical_cds_quotes(date) RETURNS SETOF cds_quotes AS $$
BEGIN
RETURN QUERY SELECT b.*
FROM (SELECT max(c.Date) AS latestdate, c.curve_ticker FROM cds_quotes c
WHERE c.Date<=$1 GROUP BY c.curve_ticker) a
JOIN cds_quotes b ON a.curve_ticker = b.curve_ticker AND a.latestdate=b.Date;
END;
$$ LANGUAGE plpgsql;
CREATE OR REPLACE FUNCTION curve_quotes(varchar(4), date) RETURNS
TABLE(cds_ticker text, spread_curve float[], recovery_curve float[]) AS $$
BEGIN
RETURN QUERY SELECT max(ticker) AS t, array_agg((a.runningbid+a.runningask)/2 ORDER BY tenor),
array_agg(a.Recovery ORDER BY tenor) FROM historical_cds_quotes($2) a JOIN
(SELECT generate_series(1, 8) AS tenor, unnest(cds_curve) AS curve_ticker, ticker, company_id FROM index_members($1)) b
ON b.curve_ticker = a.curve_ticker GROUP by company_id ORDER BY t;
END;
$$ LANGUAGE plpgsql;
|
