excel - Calculating pairwise rolling correlations -
please find workbook here
the first row contains names of banks , other entries daily returns of each bank. want find rolling correlation (from past 1 year of returns) of pairwise banks beginning 1/1/2008, i'll provide example of want do:
starting erste group bank, on date jan 1st 2008, want find correlation between erste group bank , raiffeisen bank intl based on past 1 year worth of returns, formula =correl(b2:b263,c2:c263), on jan 2nd 2008 formula =correl(b3:b264,c3:c264) , on until feb 28th 2013.
then want same thing on date jan 1st 2008, find pairwise correlation between erste group bank , dexia, hence formula on jan 1st 2008 =correl(b2:b263,d2:d263) , on until feb 28th 2013.
so want find pairwise correlations between erste group bank , every other bank this, 1/1/2008-28/2/2013.
then want repeat other banks, eg, take raiffeisen bank intl 'primary' bank , 1/1/2008-28/2/2013, want find pairwise correlation between raiffeisen bank intl , erste group bank, raiffeisen bank intl , dexia etc.
does have idea on how write macro this? appreciated.
edit: code have currently, works, 1 bank, have code automatically other banks too.
sub correlations() ' ' ' ' range("c1:z1").select selection.copy activewindow.smallscroll toright:=5 range("ac1").select activesheet.paste activewindow.scrollcolumn = 21 activewindow.scrollcolumn = 22 activewindow.scrollrow = 6 activewindow.scrollrow = 11 activewindow.scrollrow = 15 activewindow.scrollrow = 20 activewindow.scrollrow = 24 activewindow.scrollrow = 27 activewindow.scrollrow = 33 activewindow.scrollrow = 36 activewindow.scrollrow = 40 activewindow.scrollrow = 42 activewindow.scrollrow = 47 activewindow.scrollrow = 49 activewindow.scrollrow = 51 activewindow.scrollrow = 53 activewindow.scrollrow = 56 activewindow.scrollrow = 58 activewindow.scrollrow = 60 activewindow.scrollrow = 65 activewindow.scrollrow = 67 activewindow.scrollrow = 69 activewindow.scrollrow = 74 activewindow.scrollrow = 76 activewindow.scrollrow = 78 activewindow.scrollrow = 80 activewindow.scrollrow = 87 activewindow.scrollrow = 89 activewindow.scrollrow = 94 activewindow.scrollrow = 96 activewindow.scrollrow = 98 activewindow.scrollrow = 103 activewindow.scrollrow = 112 activewindow.scrollrow = 116 activewindow.scrollrow = 118 activewindow.scrollrow = 120 activewindow.scrollrow = 123 activewindow.scrollrow = 127 activewindow.scrollrow = 129 activewindow.scrollrow = 134 activewindow.scrollrow = 141 activewindow.scrollrow = 145 activewindow.scrollrow = 147 activewindow.scrollrow = 152 activewindow.scrollrow = 154 activewindow.scrollrow = 156 activewindow.scrollrow = 159 activewindow.scrollrow = 161 activewindow.scrollrow = 163 activewindow.scrollrow = 165 activewindow.scrollrow = 170 activewindow.scrollrow = 176 activewindow.scrollrow = 179 activewindow.scrollrow = 188 activewindow.scrollrow = 192 activewindow.scrollrow = 199 activewindow.scrollrow = 205 activewindow.scrollrow = 208 activewindow.scrollrow = 212 activewindow.scrollrow = 217 activewindow.scrollrow = 223 activewindow.scrollrow = 226 activewindow.scrollrow = 230 activewindow.scrollrow = 232 activewindow.scrollrow = 237 activewindow.scrollrow = 239 activewindow.scrollrow = 241 activewindow.scrollrow = 243 activewindow.scrollrow = 246 activewindow.scrollrow = 248 activewindow.scrollrow = 252 activewindow.scrollrow = 257 activewindow.scrollrow = 259 activewindow.scrollrow = 261 activewindow.scrollrow = 266 activewindow.scrollrow = 270 activewindow.scrollrow = 275 activewindow.scrollrow = 277 activewindow.scrollrow = 279 activewindow.scrollrow = 284 activewindow.scrollrow = 286 activewindow.scrollrow = 288 activewindow.scrollrow = 293 activewindow.scrollrow = 295 activewindow.scrollrow = 299 activewindow.scrollrow = 304 activewindow.scrollrow = 306 activewindow.scrollrow = 311 activewindow.scrollrow = 313 activewindow.scrollrow = 317 activewindow.scrollrow = 319 activewindow.scrollrow = 324 activewindow.scrollrow = 326 activewindow.scrollrow = 328 activewindow.scrollrow = 335 activewindow.scrollrow = 337 activewindow.scrollrow = 340 activewindow.scrollrow = 344 activewindow.scrollrow = 349 activewindow.scrollrow = 351 activewindow.scrollrow = 353 activewindow.scrollrow = 355 activewindow.scrollrow = 357 activewindow.scrollrow = 360 activewindow.scrollrow = 362 activewindow.scrollrow = 364 activewindow.scrollrow = 366 activewindow.scrollrow = 371 activewindow.scrollrow = 373 activewindow.scrollrow = 378 activewindow.scrollrow = 380 activewindow.scrollrow = 384 activewindow.scrollrow = 387 activewindow.scrollrow = 389 activewindow.scrollrow = 391 activewindow.scrollrow = 393 activewindow.scrollrow = 400 activewindow.scrollrow = 402 activewindow.scrollrow = 407 activewindow.scrollrow = 409 activewindow.scrollrow = 411 activewindow.scrollrow = 416 activewindow.scrollrow = 418 activewindow.scrollrow = 420 activewindow.scrollrow = 422 activewindow.scrollrow = 425 activewindow.scrollrow = 427 activewindow.scrollrow = 429 activewindow.scrollrow = 425 activewindow.scrollrow = 416 activewindow.scrollrow = 413 activewindow.scrollrow = 409 activewindow.scrollrow = 402 activewindow.scrollrow = 393 activewindow.scrollrow = 384 activewindow.scrollrow = 380 activewindow.scrollrow = 375 activewindow.scrollrow = 369 activewindow.scrollrow = 364 activewindow.scrollrow = 362 activewindow.scrollrow = 355 activewindow.scrollrow = 353 activewindow.scrollrow = 351 activewindow.scrollrow = 349 activewindow.scrollrow = 346 activewindow.scrollrow = 344 activewindow.scrollrow = 342 activewindow.scrollrow = 340 activewindow.scrollrow = 337 activewindow.scrollrow = 335 activewindow.scrollrow = 333 activewindow.scrollrow = 328 activewindow.scrollrow = 326 activewindow.scrollrow = 324 activewindow.scrollrow = 322 activewindow.scrollrow = 317 activewindow.scrollrow = 315 activewindow.scrollrow = 311 activewindow.scrollrow = 308 activewindow.scrollrow = 306 activewindow.scrollrow = 304 activewindow.scrollrow = 302 activewindow.scrollrow = 299 activewindow.scrollrow = 297 activewindow.scrollrow = 295 activewindow.scrollrow = 293 activewindow.scrollrow = 290 activewindow.scrollrow = 288 activewindow.scrollrow = 286 activewindow.scrollrow = 284 activewindow.scrollrow = 281 activewindow.scrollrow = 277 activewindow.scrollrow = 275 activewindow.scrollrow = 273 activewindow.scrollrow = 270 activewindow.scrollrow = 268 activewindow.scrollrow = 266 activewindow.scrollrow = 264 activewindow.scrollrow = 261 activewindow.scrollrow = 259 activewindow.scrollrow = 257 activewindow.scrollrow = 255 activewindow.scrollrow = 252 activewindow.scrollrow = 250 activewindow.scrollrow = 246 activewindow.scrollrow = 239 activewindow.scrollrow = 237 activewindow.scrollrow = 232 activewindow.scrollrow = 230 activewindow.scrollrow = 228 activewindow.scrollrow = 226 activewindow.scrollrow = 223 activewindow.scrollrow = 221 activewindow.scrollrow = 217 activewindow.scrollrow = 214 activewindow.scrollrow = 210 activewindow.scrollrow = 208 activewindow.scrollrow = 210 activewindow.scrollrow = 214 activewindow.scrollrow = 223 activewindow.scrollrow = 230 activewindow.scrollrow = 235 activewindow.scrollrow = 241 activewindow.scrollrow = 243 activewindow.scrollrow = 248 activewindow.scrollrow = 250 activewindow.scrollrow = 252 range("ac263").select application.cutcopymode = false activecell.formular1c1 = "=correl(r[-261]c2:rc2,r[-261]c[-26]:rc[-26])" range("ac264").select activewindow.scrollcolumn = 19 activewindow.scrollcolumn = 20 activewindow.scrollcolumn = 21 activewindow.scrollcolumn = 22 activewindow.scrollcolumn = 23 activewindow.scrollcolumn = 24 activewindow.scrollcolumn = 25 activewindow.scrollcolumn = 26 range("ac263").select selection.autofill destination:=range("ac263:az263"), type:=xlfilldefault range("ac263:az263").select selection.autofill destination:=range("ac263:az1610"), type:=xlfilldefault range("ac263:az1610").select end sub
Comments
Post a Comment