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

Popular posts from this blog

css - Which browser returns the correct result for getBoundingClientRect of an SVG element? -

-
this svg contains rect overflows svg element: <svg id='svg' width='10' height='10'> <rect x='-10' y='-10' width='30' height='30'/> </svg> chrome 28 , opera 12 return getboundingclientrect() svg element width , height of 10. firefox 23 reports width , height of 30. correct? jsfiddle the relevant spec cssom , delegates svg spec if svg element not "have associated css layout box". haven't found definition of "having associated css layout box", correct result seem hinge on definition, getbbox returns 30x30 rect in browsers. this firefox bug fixed now, fix in firefox nightlies , should make through firefox 33 released on 14 october 2014. see bug 530985 details.
Read more

gcc - Calling fftR4() in c from assembly -

-
i trying asm fft implementation www.embeddedsignals.com work. running make gives error: undefined reference fftr4(short*, short*, int) at top of asm have .syntax unified .thumb .global fftr4 .type fftr4, %function and there #define's associate c functions arguments registers followed macros fftr4 function uses. after that, fftr4 called .thumb_func fftr4: stmfd sp!, {r4-r11, lr} mov tmp0, #0 // bit reversed counter mov tmp1, #0 .word 0xf3a2fa92 //rbit r3,r2 //rbit r,n lsl r,#3 and c code follows: void fftr4(short *y, short *x, int n); short x[512]; short y[512]; int i; void loop() { (i=0;i<512;i++) x[i]=0; (i=0;i<512;i=i+8) { x[i+0]=16384; x[i+2]=16384; x[i+4]=-16384; x[i+6]=-16384;} fftr4(y, x, 256); serialusb.println(y[128]); serialusb.println(y[129]); serialusb.println(y[384]); serialusb.println(y[385]); // expected results: //y[128]is 8191; y[129] -8190 y[...
Read more

.htaccess - Matching full URL in RewriteCond -

-
i'm trying block post access site (and return 403), except 2 ip's , 1 url ( http://mydomain.com/about-us/contact-us/ ). here's have: rewritecond %{request_method} post rewritecond %{remote_addr} !=1.2.3.4 rewritecond %{remote_addr} !=4.3.2.1 rewritecond %{request_uri} !^http://mydomain.com/about-us/contact-us/$ rewriterule ^ / [f] these rules allowing access ip properly, contact page still not accessible. i'm not sure i'm using request_uri correctly? i've tried variations relative urls rewritecond %{request_uri} !^/about-us/contact-us$ , rewritecond %{request_uri} !^/about-us/contact-us/$ without luck. what doing wrong please? it looks me rule on file name specific. if relax bit, should work. made few other esthetic tweaks. give try: rewritecond %{request_method} post rewritecond %{remote_addr} !^1.2.3.4 rewritecond %{remote_addr} !^4.3.2.1 rewritecond %{request_uri} !about-us/contact-us rewriterule ^ - [f]
Read more