Panel data: Time series average over cross-sectional correlation matrix

[Carlos Teigimiz]

Hi Guys,

I am desperately trying to find an approach to the following problem:

• I have panel data sorted by company and month.
• In order to extend an existing paper, I have to generate a time-series average over monthly cross-sectional correlations between variables used in a pricing regression

This is related to the time-series average of cross-sectional regression coeff., as proposed by Fama/MacBeth (1973), but unfortunately the -xtfmb-command is not useful in this context, as I am only looking for the correlation coefficients, not the regression coefficients.


For a set of variables (e.g. return, size, book-to-market) I need to generate a correlation matrix, which averages each month's correlation coefficient over a longer time-series.

	var1	var2	var3
var1	1
var2	avg. var1/2 correlation coeff.	1
var3	avg. var1/3 correlation coeff.	avg. var2/3 correlation coeff.	1

It looks so easy, but I have spend two days thinking of and researching for possible approaches and I have failed, hence, I would really appreciate any help.

Thank you,

Carlos

[William Lisowski]

Welcome to Statalist!

Is something like the following what you have in mind?

. * get two variables to correlate
. webuse nlswork, clear
(National Longitudinal Survey.  Young Women 14-26 years of age in 1968)

. keep idcode year ln_wage ttl_exp

. keep if inlist(year,83,85,87)
(22,298 observations deleted)

. * for each year 
. foreach y in 83 85 87 {
  2.     * calculate correlation matrix
.     correlate ln_wage ttl_exp if year==`y'
  3.         * calculate running total of the correlation matrices
.         if `y'==83 {
  4.             matrix sum = r(C)
  5.                 }
  6.         else {
  7.             matrix sum = sum + r(C)
  8.                 }
  9.         }
(obs=1,987)

             |  ln_wage  ttl_exp
-------------+------------------
     ln_wage |   1.0000
     ttl_exp |   0.3890   1.0000

(obs=2,085)

             |  ln_wage  ttl_exp
-------------+------------------
     ln_wage |   1.0000
     ttl_exp |   0.4149   1.0000

(obs=2,164)

             |  ln_wage  ttl_exp
-------------+------------------
     ln_wage |   1.0000
     ttl_exp |   0.4318   1.0000


. * what have we got?
. matrix list sum

symmetric sum[2,2]
           ln_wage    ttl_exp
ln_wage          3
ttl_exp  1.2357678          3

. * divide by the number of years
. matrix avg = sum / 3

. matrix list avg

symmetric avg[2,2]
          ln_wage   ttl_exp
ln_wage         1
ttl_exp  .4119226         1

.

[Carole J. Wilson]

William answered before I refreshed my browser, but I have a similar solution:

webuse grunfeld, clear
mat drop _all

levelsof company, local(C)      //get list of companies to loop thru
local i= `:word count `C''          //total number of companies
mat A=J(3, 3, 0)                //make an empty matrix--Change if more variables
foreach c of local C {
    corr invest mvalue kstock if company==`c'  //corr if company==c
    mat C=r(C)                    //get the corr matrix
    mat A= A + C                //add it to A (running sum)
    }
mat MC= A / `i'                    //divide the sum by # of companies to get average
mat list MC
    
****turn the matrix into Stata data****
clear
svmat MC, names(col)

**add a string variable vars with name of variables
gen vars=""
order vars, first
local v: rownames MC
local i= `:word count `v''
forvalues k= 1/`i' {
    replace vars= `"`:word `k' of `v''"' in `k'
    }
list

[Carlos Teigimiz]

Hi William & Carole,

Thank you so much for your answers, I am up for trying to understand them as soon as I get up tomorrow. A lot of Stata for today Already looking forward to try your options!

[Carlos Teigimiz]

Just built it into my file. Works awesome!

[Mengqi Liu]

William answered before I refreshed my browser, but I have a similar solution:

webuse grunfeld, clear
mat drop _all

levelsof company, local(C) //get list of companies to loop thru
local i= `:word count `C'' //total number of companies
mat A=J(3, 3, 0) //make an empty matrix--Change if more variables
foreach c of local C {
corr invest mvalue kstock if company==`c' //corr if company==c
mat C=r(C) //get the corr matrix
mat A= A + C //add it to A (running sum)
}
mat MC= A / `i' //divide the sum by # of companies to get average
mat list MC

****turn the matrix into Stata data****
clear
svmat MC, names(col)

**add a string variable vars with name of variables
gen vars=""
order vars, first
local v: rownames MC
local i= `:word count `v''
forvalues k= 1/`i' {
replace vars= `"`:word `k' of `v''"' in `k'
}
list

Hey Carole,
your code works great! However, in some years there are missing value in my panel dataset, and the code just stop working.
I try to change corr to pwcorr and in the end end up with an empty matrix. Do you have any ideas how I can deal with this problem
Thanks!
Mengqi