output R2 of regression equations using a loop through columns in Excel starting from AA, AB, etc..

[Antonina Popova]

Dear All,

I wonder if anyone has an idea how to output R2 of the regression equations to an Excel file using loops (since I have to run lots of regressions automatically) especially to those columns that start from AA, AB etc.?

The idea is to output cross-variable R2 to the following table (that is in Excel file):

	var 1	var2	var 3
var 1	1	R2 (between var 1 and var 2)	R2
var 2	R2	1	R2
var 3	R2	R2	1

Now I have this part of the code:

local row = 3
local col = 3

foreach dep_var of varlist * {
foreach indep_var of varlist * {
regress `dep_var' `indep_var'
local Cell = char(64 + `col') + string(`row')
putexcel `Cell' = (e(r2))
local col = `col' + 1
}
local row = `row' + 1
local col = 3
}


However it works corectly only over Excel columns from A to Z and not more.

Does anyone know how to proceed with this output futher from AA, AB, etc...? Thank you for any advice!!!

Regards,
Antonina

[Phil Bromiley]

I'm not sure exactly what you're trying to do, but I might try instead of doing a ton of putexcel statements, write the parameter you want into Stata variables:

g v1=" "
g v2=" "
g rsq=.
local rownum=1
foreach dep_var of varlist * {
foreach indep_var of varlist * {
regress `dep_var' `indep_var'
replace v1="`dep var'" in `rownum'/`rownum'
replace v2="`indep var'" in `rownum'/`rownum'
replace rsq=e(r2) in `rownum'/`rownum'
local rownum=`rownum' + 1
}
}

After, you can drop everything you don't want and move it into excel if you really want excel. I may not have the quotes right in the replace statements but you get the idea.
You can transpose in in excel.