Average of time-interval correlation coefficients

[Benedikt Dieker]

Hello Statalist community,

I would like to compute the correlation between my variables on a quarterly basis, and then take the averages of those correlation coefficients (from 84 quarters) and present these averages in a matrix. Is there an efficient way of doing this? Ideally I end up with a correlation matrix, where I have Spearman rank correlation coefficients above the diagonal, and Pearson correlation coefficients below the diagonal. However, if there is no efficient way of doing this, I don't mind combining the two by hand. The main issue is finding an efficient way of finding the average of the correlation coefficients from the 84 quarters.

An example of my data:

input double permno float(num_QUARTER EXRET ln_MV ln_MB) double(INCVOL RETVOL) float ln_AGE double TURN float INSOWN
10011 43 7.620689 -.12948246 -2.9178214 . 247.42563409266216 -1.94591 .00970882269757567 5.335223
10011 44 3.627314 -.02595909 -3.685693 . 264.1430764290126 -2.0794415 .03400871273258637 12.897807
10011 46 6.99464 .1716081 -3.536731 . 228.98534437131354 -2.0794415 .028441274854084177 20.15268
10011 47 -7.960851 .002572919 -3.756951 2.717672115669003 194.11344543235032 -2.0794415 .021733686793595552 17.707294
10011 48 .23876584 -.1999276 -3.994967 2.67519623302301 303.8931356431741 -2.1972246 .04065265415313964 25.705116
10011 49 -3.2104526 -.4408333 -4.257204 2.532721266515545 306.11591451900983 -2.1972246 .018068486351459738 34.033783
10011 50 -5.325699 -.14146712 -3.983347 2.4463803968178697 158.8931413965622 -2.1972246 .17539847263445457 19.195
10011 51 -3.56842 -.15825415 -4.0266595 2.334583952508747 121.60774712072502 -2.1972246 .017153829995853204 14.916425
10011 52 -.11500657 -.11414901 -4.0190063 2.25453858594281 112.88942093829684 -2.3025851 .01234552709502168 39.19349
10015 1 .2713797 -1.629819 -3.8523865 . 480.52632675489315 -.6931472 .014994475239936652 8.153241
10015 2 -.029075697 -1.3508308 -3.768261 . 292.51310846599114 -.6931472 .006539496836545556 3.864492
10015 3 -13.90464 -1.1431913 -3.6667576 . 394.96986195956003 -.6931472 .009006608085473999 15.327478
10015 4 -2.116692 -.9068727 -3.4728985 . 139.82119983310892 -1.0986123 .026447932400192404 7.276555
10016 5 -.8200542 1.2196877 -4.124325 . 248.033473556382 . .011132207795162686 0
10016 6 3.152819 1.19607 -4.4086137 . 128.3786360656205 . .025953122664941475 0
10016 7 6.870953 1.2435 -4.3892055 . 155.7964236298974 0 .01645664480359604 .
10016 8 -.6537237 1.340664 -4.33871 . 141.1249312210978 0 .04161635882143552 0
10016 10 3.6085796 1.6337105 -4.108911 . 108.91969334035373 0 .009270969637630818 0
10016 11 -2.0152378 1.63778 -4.1308627 . 141.52395258650856 -.6931472 .007975471643505679 0
10016 12 -2.138123 1.289078 -4.48736 . 285.4929426668194 -.6931472 .01378725740360096 0
10016 13 4.791698 1.4207436 -4.3233166 . 206.78123350647815 -.6931472 .015366282956286644 0
10016 14 -.3678725 1.5638124 -4.3088436 . 132.2993098431583 -.6931472 .024335319903912023 0
10016 15 5.258823 1.5520096 -4.35008 . 143.79052032145998 -1.0986123 .02500570530925567 .
10016 16 -3.352444 1.7044204 -4.197584 . 188.5019190099017 -1.0986123 .005414249435928249 0
10016 17 .4465417 1.7507187 -4.1779604 . 142.185368876796 -1.0986123 .0093223560277238 0
10016 18 -6.536911 1.7653347 -4.1859303 . 179.18183233563886 -1.0986123 .0062506626645699725 0
10016 19 1.9284724 1.6026098 -4.0964417 . 151.95245786545706 -1.3862944 .031724607437706345 1.3777038
10016 20 .9492266 1.4102423 -4.3470936 . 222.2715181349232 -1.3862944 .01268149937192599 0
10016 21 .5641308 1.5005156 -4.2921352 . 168.46580859342922 -1.3862944 .019486638961097924 0
10016 22 2.715161 1.5347958 -4.282747 . 271.31572366219575 -1.3862944 .018034091372101102 0
10016 23 7.286383 1.0643004 . . 531.674809604646 -1.609438 .014007795978614013 67.54179
10016 24 -16.9453 .9444991 . . 454.2604649453411 -1.609438 .01794318180376043 0
10016 25 8.111321 .958392 . . 378.0751127145016 -1.609438 .018063096911646426 0
10016 26 2.3040948 1.1223191 . . 350.5660588430002 -1.609438 .021603378485451685 0
10016 27 .8245112 1.0282434 . . 316.62459076120484 -1.7917595 .011039680651118678 68.56397
10016 28 10.352745 .9034932 . .8673182866282676 326.8249837054665 -1.7917595 .02028837803237397 61.10061
10016 29 -.07700622 1.3427196 . .8129080140783217 216.12791285642354 -1.7917595 .024367767075697582 0
10016 30 -13.59457 1.2188176 . .7665897111991958 269.4078366231146 -1.7917595 .00655783359494914 0
10016 31 .3153377 1.0214072 . .7665897111991958 299.93774839936447 -1.94591 .015043272002648952 0
10016 32 2.0904431 1.1322953 . .836974770270605 201.46739454951734 -1.94591 .013948722335044295 0
10016 33 .6409574 1.221085 . .7992138311994667 237.24611682892353 -1.94591 .013566704064942314 70.53608
10016 34 -3.5840454 1.1476834 . .7651898483983927 240.07009699992244 -2.0794415 .008782547320151934 0
10016 35 -9.149167 1.1659228 . .7651898483983927 186.27984147352197 -2.0794415 .006947739239436605 0
10016 36 6.431603 .9373431 -4.377015 3.5851541593250156 482.19801359464606 -2.0794415 .0577281655316862 .4175623
10016 37 -.322807 .5872893 . 3.4605721486564005 390.6781721588001 -2.0794415 .04871882127287487 0
10016 38 -9.567686 .6373979 -4.515998 3.343341188878521 210.95490045793116 -2.1972246 .013064499583936507 69.99777
10016 39 -12.797285 .4753642 -4.5784645 3.343341188878521 215.12529154390413 -2.1972246 .02089026223802648 0
10016 40 12.408442 .12010264 -4.0576944 4.249474619486368 427.89651675411216 -2.1972246 .022183166748587172 74.02661
10016 41 -.8127576 .50293654 -3.5476825 4.140813296672247 177.04877607868843 -2.1972246 .01740940980198502 0
10016 42 5.496794 .45836115 -3.4909384 4.150593166610387 281.4240995931218 -2.3025851 .018607186175358947 72.574684
10016 43 -5.719497 .8895463 -3.088957 4.1449059607530545 151.84726740831067 -2.3025851 .030523568120164175 0
end

When I run the following code, I end up with the correlation coefficients for each quarter:
byso num_QUARTER: pwcorr EXRET ln_MV ln_MB INCVOL RETVOL ln_AGE TURN INSOWN

But how do I take the averages of all the coefficients?

Many thanks in advance,
Benedikt

[George Ford]

clear
set obs 100
g id = _n
expand 10
egen qtr = seq() , from(1) to(10) by(id)
xtset id qtr
matrix C = (1 , 0.25 , 0.5 , -0.3 \ 0.25 , 1 , 0.33 , -0.1 \ 0.5,  0.33 , 1 , -0.6 \ -0.3 , -0.1 , -0.6 , 1)
corr2data x1 x2 x3 x4, corr(C)
correl x1 x2 x3 x4

matrix R = J(4,4,0)
levelsof qtr, local(levels)
foreach qtr of local levels {
    qui correl x1 x2 x3 x4 if qtr==`qtr'
    matrix R = R + r(C)
}
matrix R = R/10
matrix list R