CDS Curve Procedure




  1 DROP PROCEDURE IF EXISTS `BMINE`.`ufincurve` $$
  2 /*!50003 SET @TEMP_SQL_MODE=@@SQL_MODE, SQL_MODE='' */ $$
  3 CREATE DEFINER=`sraaen`@`%` PROCEDURE `ufincurve`()
  4 BEGIN
  5 delete from usdfinalcurve;
  6 insert into usdfinalcurve(issuer, 1YR, 2YR, 3YR, 4YR, 5YR, 7YR, 10Y)
  7 SELECT usdrmcrconsol.issuer, ifnull(usdrmcrconsol.1YR, usdrmcrconsol.5YR * usdratsum.1YR),ifnull(usdrmcrconsol.2YR, usdrmcrconsol.5YR * usdratsum.2YR), ifnull(usdrmcrconsol.3YR, usdrmcrconsol.5YR*usdratsum.3YR),
  8 ifnull(usdrmcrconsol.4YR, usdrmcrconsol.5YR * usdratsum.4YR), usdrmcrconsol.5YR, ifnull(usdrmcrconsol.7YR, usdrmcrconsol.5YR * usdratsum.7YR),
  9 ifnull(usdrmcrconsol.10Y, usdrmcrconsol.5YR * usdratsum.10Y) from usdrmcrconsol, usdratsum;
 10 update usdfinalcurve set 6YR=(usdfinalcurve.5YR +usdfinalcurve.7YR)/2, 8YR=(usdfinalcurve.7YR*.667+usdfinalcurve.10Y*.333), 9YR=(usdfinalcurve.7YR *.333 + usdfinalcurve.10Y *.667);
 11 delete from usdfinalcurve where 5YR is null;
 12 select * from usdfinalcurve;
 13 END $$
 14 /*!50003 SET SESSION SQL_MODE=@TEMP_SQL_MODE */ $$
 15 
 16 -- -----------------------------------------------------------------------------
 17 
 18 DROP PROCEDURE IF EXISTS `BMINE`.`usdgrid` $$
 19 /*!50003 SET @TEMP_SQL_MODE=@@SQL_MODE, SQL_MODE='' */ $$
 20 CREATE DEFINER=`sraaen`@`%` PROCEDURE `usdgrid`()
 21 BEGIN
 22 delete from usdrmcr;
 23 insert into usdrmcr(issuer, 1YR) select security_des, avg(PX_BID) from
 24 usdcds where CDS_TERM='1YR' group by security_des;
 25 insert into usdrmcr(issuer, 2YR) select security_des, avg(PX_BID) from
 26 usdcds where CDS_TERM='2YR' group by security_des;
 27 insert into usdrmcr(issuer, 3YR) select security_des, avg(PX_BID) from
 28 usdcds where CDS_TERM='3YR' group by security_des;
 29 insert into usdrmcr(issuer, 4YR) select security_des, avg(PX_BID) from
 30 usdcds where CDS_TERM='4YR' group by security_des;
 31 insert into usdrmcr(issuer, 5YR) select security_des, avg(PX_BID) from
 32 usdcds where CDS_TERM='5YR' group by security_des;
 33 insert into usdrmcr(issuer, 6YR) select security_des, avg(PX_BID) from
 34 usdcds where CDS_TERM='6YR' group by security_des;
 35 insert into usdrmcr(issuer, 7YR) select security_des, avg(PX_BID) from
 36 usdcds where CDS_TERM='7YR' group by security_des;
 37 insert into usdrmcr(issuer, 8YR) select security_des, avg(PX_BID) from
 38 usdcds where CDS_TERM='8YR' group by security_des;
 39 insert into usdrmcr(issuer, 9YR) select security_des, avg(PX_BID) from
 40 usdcds where CDS_TERM='9YR' group by security_des;
 41 insert into usdrmcr(issuer, 10Y) select security_des, avg(PX_BID) from
 42 usdcds where CDS_TERM='10Y' group by security_des;
 43 delete from usdrmcrconsol;
 44 insert into usdrmcrconsol(issuer, 1YR, 2YR, 3YR, 4YR, 5YR, 6YR, 7YR, 8YR, 9YR, 10Y)
 45 select distinct issuer, sum(1YR), sum(2YR), sum(3YR), sum(4YR), sum(5YR),
 46 sum(6YR), sum(7YR), sum(8YR), sum(9YR), sum(10Y)
 47 from usdrmcr
 48 group by issuer
 49 order by issuer;
 50 delete from usdfull;
 51 insert into usdfull (select * from usdrmcrconsol where 1YR>0 and 2YR>0 and 3YR>0 and 4YR>0 and 5YR>0 and 7YR>0);
 52 delete from usdrat;
 53 insert into usdrat (1YR, 2YR, 3YR, 4YR, 6YR, 7YR, 8YR, 9YR, 10Y)select 1YR/5YR as 1YR, 2YR/5YR as 2YR, 3YR/5YR as 3YR, 4YR/5YR as 4YR, 6YR/5YR as 6YR, 7YR/5YR as 7YR, 8YR/5YR as 8YR, 9YR/5YR as 9YR, 10Y/5YR as 10Y from usdfull;
 54 delete from usdratsum;
 55 insert into usdratsum (1YR, 2YR, 3YR, 4YR, 6YR, 7YR, 8YR, 9YR, 10Y)select avg(1YR), avg(2YR), avg(3YR), avg(4YR), avg(6YR), avg(7YR), avg(8YR), avg(9YR), avg(10Y) from usdrat;
 56 select * from usdratsum;
 57 select * from usdrmcrconsol;
 58 END $$
 59 /*!50003 SET SESSION SQL_MODE=@TEMP_SQL_MODE */ $$
 60 
 61 -- -----------------------------------------------------------------------------
 62 DROP PROCEDURE IF EXISTS `BMINE`.`efincurve` $$
 63 /*!50003 SET @TEMP_SQL_MODE=@@SQL_MODE, SQL_MODE='' */ $$
 64 CREATE DEFINER=`sraaen`@`%` PROCEDURE `efincurve`()
 65 BEGIN
 66 delete from eurfinalcurve;
 67 insert into eurfinalcurve(issuer, 1YR, 2YR, 3YR, 4YR, 5YR, 7YR, 10Y)
 68 SELECT eurrmcrconsol.issuer, ifnull(eurrmcrconsol.1YR, eurrmcrconsol.5YR * eurratsum.1YR),ifnull(eurrmcrconsol.2YR, eurrmcrconsol.5YR * eurratsum.2YR), ifnull(eurrmcrconsol.3YR, eurrmcrconsol.5YR*eurratsum.3YR),
 69 ifnull(eurrmcrconsol.4YR, eurrmcrconsol.5YR * eurratsum.4YR), eurrmcrconsol.5YR, ifnull(eurrmcrconsol.7YR, eurrmcrconsol.5YR * eurratsum.7YR),
 70 ifnull(eurrmcrconsol.10Y, eurrmcrconsol.5YR * eurratsum.10Y) from eurrmcrconsol, eurratsum;
 71 update eurfinalcurve set 6YR=(eurfinalcurve.5YR +eurfinalcurve.7YR)/2, 8YR=(eurfinalcurve.7YR*.667+eurfinalcurve.10Y*.333), 9YR=(eurfinalcurve.7YR *.333 + eurfinalcurve.10Y *.667);
 72 delete from eurfinalcurve where 5YR is null;
 73 select * from eurfinalcurve;
 74 END $$
 75 /*!50003 SET SESSION SQL_MODE=@TEMP_SQL_MODE */ $$
 76 
 77 -- -----------------------------------------------------------------------------
 78 DROP PROCEDURE IF EXISTS `BMINE`.`cdsusd` $$
 79 /*!50003 SET @TEMP_SQL_MODE=@@SQL_MODE, SQL_MODE='' */ $$
 80 CREATE DEFINER=`sraaen`@`%` PROCEDURE `cdsusd`()
 81 BEGIN
 82 delete from usdrmcr;
 83 insert into usdrmcr(issuer, 1YR) select security_des, avg(PX_BID) from
 84 usdcds where CDS_TERM='1YR' group by security_des;
 85 insert into usdrmcr(issuer, 2YR) select security_des, avg(PX_BID) from
 86 usdcds where CDS_TERM='2YR' group by security_des;
 87 insert into usdrmcr(issuer, 3YR) select security_des, avg(PX_BID) from
 88 usdcds where CDS_TERM='3YR' group by security_des;
 89 insert into usdrmcr(issuer, 4YR) select security_des, avg(PX_BID) from
 90 usdcds where CDS_TERM='4YR' group by security_des;
 91 insert into usdrmcr(issuer, 5YR) select security_des, avg(PX_BID) from
 92 usdcds where CDS_TERM='5YR' group by security_des;
 93 insert into usdrmcr(issuer, 6YR) select security_des, avg(PX_BID) from
 94 usdcds where CDS_TERM='6YR' group by security_des;
 95 insert into usdrmcr(issuer, 7YR) select security_des, avg(PX_BID) from
 96 usdcds where CDS_TERM='7YR' group by security_des;
 97 insert into usdrmcr(issuer, 8YR) select security_des, avg(PX_BID) from
 98 usdcds where CDS_TERM='8YR' group by security_des;
 99 insert into usdrmcr(issuer, 9YR) select security_des, avg(PX_BID) from
100 usdcds where CDS_TERM='9YR' group by security_des;
101 insert into usdrmcr(issuer, 10Y) select security_des, avg(PX_BID) from
102 usdcds where CDS_TERM='10Y' group by security_des;
103 delete from usdrmcrconsol;
104 insert into usdrmcrconsol(issuer, 1YR, 2YR, 3YR, 4YR, 5YR, 6YR, 7YR, 8YR, 9YR, 10Y)
105 select distinct issuer, avg(1YR), avg(2YR), avg(3YR), avg(4YR), avg(5YR),
106 avg(6YR), avg(7YR), avg(8YR), avg(9YR), avg(10Y)
107 from usdrmcr
108 group by issuer
109 order by issuer;
110 delete from usdfull;
111 insert into usdfull (select * from usdrmcrconsol where 1YR>0 and 2YR>0 and 3YR>0 and 4YR>0 and 5YR>0 and 7YR>0);
112 delete from usdrat;
113 insert into usdrat (1YR, 2YR, 3YR, 4YR, 6YR, 7YR, 8YR, 9YR, 10Y)select 1YR/5YR as 1YR, 2YR/5YR as 2YR, 3YR/5YR as 3YR, 4YR/5YR as 4YR, 6YR/5YR as 6YR, 7YR/5YR as 7YR, 8YR/5YR as 8YR, 9YR/5YR as 9YR, 10Y/5YR as 10Y from usdfull;
114 delete from usdratsum;
115 insert into usdratsum (1YR, 2YR, 3YR, 4YR, 6YR, 7YR, 8YR, 9YR, 10Y)select avg(1YR), avg(2YR), avg(3YR), avg(4YR), avg(6YR), avg(7YR), avg(8YR), avg(9YR), avg(10Y) from usdrat;
116 select * from usdratsum;
117 END $$
118 /*!50003 SET SESSION SQL_MODE=@TEMP_SQL_MODE */ $$
119 
120 -- -----------------------------------------------------------------------------
121 DROP PROCEDURE IF EXISTS `BMINE`.`cdsint2` $$
122 /*!50003 SET @TEMP_SQL_MODE=@@SQL_MODE, SQL_MODE='' */ $$
123 CREATE DEFINER=`sraaen`@`%` PROCEDURE `cdsint2`()
124 BEGIN
125 delete from curveratio;
126 insert into curveratio(1y, 2y, 3y, 4y, 5y, 6y, 7y, 8y, 9y, 10y)
127 select avg(usdcds.1y), avg(usdcds.2y), avg(usdcds.3y), avg(usdcds.4y), avg(usdcds.5y),(curve.5y+curve.7y)/2 , avg(rmcrconsol.7y), (curve.7y*.667+curve.10y*.333), (curve.7y*.333+curve.10y*.667), avg(rmcrconsol.10y)
128 from rmcrconsol, curve;
129 delete from curat;
130 insert into curat(1y, 2y, 3y, 4y, 6y, 7y, 8y, 9y, 10y)
131 select 5y/1y as 1y, 5y/2y as 2y, 5y/3y as 3y, 5y/4y as 4y, 5y/6y as 6y, 5y/7y as 7y, 5y/8y as 8y, 5y/9y as 9y, 5y/10y as 10y from curveratio;
132 select * from curat;
133 END $$
134 /*!50003 SET SESSION SQL_MODE=@TEMP_SQL_MODE */ $$
135 
136 -- -----------------------------------------------------------------------------
137 DROP PROCEDURE IF EXISTS `BMINE`.`cdsint1` $$
138 /*!50003 SET @TEMP_SQL_MODE=@@SQL_MODE, SQL_MODE='' */ $$
139 CREATE DEFINER=`sraaen`@`%` PROCEDURE `cdsint1`()
140 BEGIN
141 delete from curve;
142 insert into curve(1y, 2y, 3y, 4y, 5y, 6y, 7y, 8y, 9y, 10y)
143 select avg(1Y), avg(2Y), avg(3Y), avg(4Y), avg(5Y), avg(6Y), avg(7Y), avg(8Y), avg(9Y),avg(10Y)
144 from rmcr;
145 delete from curveratio;
146 insert into curveratio(1y, 2y, 3y, 4y, 5y, 6y, 7y, 8y, 9y, 10y)
147 select avg(rmcrconsol.1y), avg(rmcrconsol.2y), avg(rmcrconsol.3y), avg(rmcrconsol.4y), avg(rmcrconsol.5y),(curve.5y+curve.7y)/2 , avg(rmcrconsol.7y), (curve.7y*.667+curve.10y*.333), (curve.7y*.333+curve.10y*.667), avg(rmcrconsol.10y)
148 from rmcrconsol, curve;
149 delete from curat;
150 insert into curat(1y, 2y, 3y, 4y, 6y, 7y, 8y, 9y, 10y)
151 select 5y/1y as 1y, 5y/2y as 2y, 5y/3y as 3y, 5y/4y as 4y, 5y/6y as 6y, 5y/7y as 7y, 5y/8y as 8y, 5y/9y as 9y, 5y/10y as 10y from curveratio;
152 select * from curat;
153 END $$
154 /*!50003 SET SESSION SQL_MODE=@TEMP_SQL_MODE */ $$
155 
156 -- -----------------------------------------------------------------------------
157 
158 DROP PROCEDURE IF EXISTS `BMINE`.`cdseur` $$
159 /*!50003 SET @TEMP_SQL_MODE=@@SQL_MODE, SQL_MODE='' */ $$
160 CREATE DEFINER=`sraaen`@`%` PROCEDURE `cdseur`()
161 BEGIN
162 delete from eurrmcr;
163 insert into eurrmcr(issuer, 1YR) select security_des, avg(PX_BID) from
164 eurcds where CDS_TERM='1YR' group by security_des;
165 insert into eurrmcr(issuer, 2YR) select security_des, avg(PX_BID) from
166 eurcds where CDS_TERM='2YR' group by security_des;
167 insert into eurrmcr(issuer, 3YR) select security_des, avg(PX_BID) from
168 eurcds where CDS_TERM='3YR' group by security_des;
169 insert into eurrmcr(issuer, 4YR) select security_des, avg(PX_BID) from
170 eurcds where CDS_TERM='4YR' group by security_des;
171 insert into eurrmcr(issuer, 5YR) select security_des, avg(PX_BID) from
172 eurcds where CDS_TERM='5YR' group by security_des;
173 insert into eurrmcr(issuer, 6YR) select security_des, avg(PX_BID) from
174 eurcds where CDS_TERM='6YR' group by security_des;
175 insert into eurrmcr(issuer, 7YR) select security_des, avg(PX_BID) from
176 eurcds where CDS_TERM='7YR' group by security_des;
177 insert into eurrmcr(issuer, 8YR) select security_des, avg(PX_BID) from
178 eurcds where CDS_TERM='8YR' group by security_des;
179 insert into eurrmcr(issuer, 9YR) select security_des, avg(PX_BID) from
180 eurcds where CDS_TERM='9YR' group by security_des;
181 insert into eurrmcr(issuer, 10Y) select security_des, avg(PX_BID) from
182 eurcds where CDS_TERM='10Y' group by security_des;
183 delete from eurrmcrconsol;
184 insert into eurrmcrconsol(issuer, 1YR, 2YR, 3YR, 4YR, 5YR, 6YR, 7YR, 8YR, 9YR, 10Y)
185 select distinct issuer, avg(1YR), avg(2YR), avg(3YR), avg(4YR), avg(5YR),
186 avg(6YR), avg(7YR), avg(8YR), avg(9YR), avg(10Y)
187 from eurrmcr
188 group by issuer
189 order by issuer;
190 delete from eurfull;
191 insert into eurfull (select * from eurrmcrconsol where 1YR>0 and 2YR>0 and 3YR>0 and 4YR>0 and 5YR>0 and 7YR>0);
192 delete from eurrat;
193 insert into eurrat (1YR, 2YR, 3YR, 4YR, 6YR, 7YR, 8YR, 9YR, 10Y)select 1YR/5YR as 1YR, 2YR/5YR as 2YR, 3YR/5YR as 3YR, 4YR/5YR as 4YR, 6YR/5YR as 6YR, 7YR/5YR as 7YR, 8YR/5YR as 8YR, 9YR/5YR as 9YR, 10Y/5YR as 10Y from eurfull;
194 delete from eurratsum;
195 insert into eurratsum (1YR, 2YR, 3YR, 4YR, 6YR, 7YR, 8YR, 9YR, 10Y)select avg(1YR), avg(2YR), avg(3YR), avg(4YR), avg(6YR), avg(7YR), avg(8YR), avg(9YR), avg(10Y) from eurrat;
196 select * from eurratsum;
197 END $$
198 /*!50003 SET SESSION SQL_MODE=@TEMP_SQL_MODE */ $$
199 
200 -- -----------------------------------------------------------------------------
201 
202 DROP PROCEDURE IF EXISTS `BMINE`.`cdseur1` $$
203 /*!50003 SET @TEMP_SQL_MODE=@@SQL_MODE, SQL_MODE='' */ $$
204 CREATE DEFINER=`sraaen`@`%` PROCEDURE `cdseur1`()
205 BEGIN
206 delete from eurcurve;
207 insert into eurcurve(1YR, 2YR, 3YR, 4YR, 5YR, 6YR, 7YR, 8YR, 9YR, 10Y)
208 select avg(1YR), avg(2YR), avg(3YR), avg(4YR), avg(5YR), avg(6YR), avg(7YR), avg(8YR), avg(9YR),avg(10Y)
209 from rmcreur;
210 delete from eurrat;
211 insert into eurrat(1YR, 2YR, 3YR, 4YR, 5YR, 6YR, 7YR, 8YR, 9YR, 10YR)
212 select avg(rmcreur.1YR), avg(rmcreur.2YR), avg(rmcreur.3YR), avg(rmcreur.4YR), avg(rmcreur.5YR),(curve.5YR+curve.7YR)/2 , avg(rmcreur.7YR), (curve.7YR*.667+curve.10Y*.333), (curve.7YR*.333+curve.10Y*.667), avg(rmcreur.10Y)
213 from rmcrconsol, eurcurve;
214 delete from eurrat;
215 insert into eurrat(1YR, 2YR, 3YR, 4YR, 6YR, 7YR, 8YR, 9YR, 10Y)
216 select 5YR/1YR as 1YR, 5YR/2YR as 2YR, 5YR/3YR as 3YR, 5YR/4YR as 4YR, 5YR/6YR as 6YR, 5YR/7YR as 7YR, 5YR/8YR as 8YR, 5YR/9YR as 9YR, 5YR/10Y as 10Y from curveratio;
217 select * from eurrat;
218 END $$
219 /*!50003 SET SESSION SQL_MODE=@TEMP_SQL_MODE */ $$
220 
221 -- -----------------------------------------------------------------------------
222 
223 DROP PROCEDURE IF EXISTS `BMINE`.`cdsgrid` $$
224 /*!50003 SET @TEMP_SQL_MODE=@@SQL_MODE, SQL_MODE='' */ $$
225 CREATE DEFINER=`sraaen`@`%` PROCEDURE `cdsgrid`()
226 BEGIN
227 delete from rmcr;
228 insert into rmcr(issuer, 1Y) select security_des, avg(PX_BID) from
229 PRIN98 where CDS_TERM='1YR' group by security_des;
230 insert into rmcr(issuer, 2Y) select security_des, avg(PX_BID) from
231 PRIN98 where CDS_TERM='2YR' group by security_des;
232 insert into rmcr(issuer, 3Y) select security_des, avg(PX_BID) from
233 PRIN98 where CDS_TERM='3YR' group by security_des;
234 insert into rmcr(issuer, 4Y) select security_des, avg(PX_BID) from
235 PRIN98 where CDS_TERM='4YR' group by security_des;
236 insert into rmcr(issuer, 5Y) select security_des, avg(PX_BID) from
237 PRIN98 where CDS_TERM='5YR' group by security_des;
238 insert into rmcr(issuer, 6Y) select security_des, avg(PX_BID) from
239 PRIN98 where CDS_TERM='6YR' group by security_des;
240 insert into rmcr(issuer, 7Y) select security_des, avg(PX_BID) from
241 PRIN98 where CDS_TERM='7YR' group by security_des;
242 insert into rmcr(issuer, 8Y) select security_des, avg(PX_BID) from
243 PRIN98 where CDS_TERM='8YR' group by security_des;
244 insert into rmcr(issuer, 9Y) select security_des, avg(PX_BID) from
245 PRIN98 where CDS_TERM='9YR' group by security_des;
246 insert into rmcr(issuer, 10Y) select security_des, avg(PX_BID) from
247 PRIN98 where CDS_TERM='10Y' group by security_des;
248 delete from rmcrconsol;
249 insert into rmcrconsol(issuer, 1Y, 2Y, 3Y, 4Y, 5Y, 6Y, 7Y, 8Y, 9Y, 10Y)
250 select distinct issuer, sum(1y), sum(2y), sum(3y), sum(4y), sum(5y),
251 sum(6y), sum(7y), sum(8y), sum(9y), sum(10y)
252 from rmcr
253 group by issuer
254 order by issuer;
255 select * from rmcrconsol;
256 END $$
257 /*!50003 SET SESSION SQL_MODE=@TEMP_SQL_MODE */ $$
258