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