Issues with putexcel

[Nicolas Orgeira]

Hi,

I am attempting to export some results of a simple OLS using putexcel: I need the coefficients to be exported in specific cell and, for most of them, to be "exponentiated" first. When displaying the local from the coefficients matrix, I get both the correct coefficient and the correct exponential but for some reason, it only exports the coefficients without "exponentiating" it. However, it works when I manually copy/paste each row in Stata directly..

Does anyone know how to fix it? I would like to run OLS with significant number of variables and would like to avoid manually entering each row in Stata.

Thanks

* Example generated by -dataex-. To install: ssc install dataex
clear
input float(ln_annual_rent ln_surface_area west central)
17.296751 8.834334 0 0
15.319588 7.395433 0 0
14.994165 7.475753 0 0
15.796512 7.439742 0 0
 12.89922 5.941276 0 0
15.925724 7.904531 0 0
13.171153 6.408727 0 0
15.796512 7.507339 0 0
15.955577  7.99535 0 0
15.648092 7.211106 0 0
        0 6.651468 0 0
15.648092 7.750081 0 0
 15.47374 7.870254 0 0
 16.34124 7.908166 0 0
14.038654 6.442492 0 0
15.137266 6.960348 0 0
 16.34124 7.274479 0 0
15.931767 7.142828 0 0
15.137266 7.745756 0 0
15.201805 7.262481 0 0
 15.56471 7.953557 0 0
 15.60727 7.887017 0 0
15.725053 7.952517 0 0
15.137266 6.971838 0 0
 14.82711 7.311018 0 0
17.092655 8.065686 0 0
15.796512 6.256067 0 0
15.863203 8.453291 0 0
16.213406 7.541243 0 0
 15.47374 7.482186 0 0
13.217673 6.210289 0 0
 15.52026 7.308901 0 0
 15.52026 7.171426 0 0
14.375126 6.708914 0 0
 14.82711 6.416732 0 0
 15.47374 7.509379 0 0
14.751604 7.868457 0 0
 19.23161 7.556365 0 0
 13.71015 6.204558 0 0
 16.34124 7.718552 0 0
 14.34614 6.833204 0 0
 14.34614 7.211158 0 0
 16.34124 7.357039 0 0
 15.26243 7.069874 0 0
16.759949  8.11257 0 0
 15.47374 7.700757 0 0
 14.62644 6.200489 0 0
 15.26243 7.749219 0 0
14.375126 6.940058 0 0
 15.52026 7.096017 0 0
15.830414 7.020513 0 0
14.954945  7.03474 0 0
15.648092 7.441942 0 0
16.993565 7.030643 0 0
16.906553 8.976553 0 0
16.213406 7.398713 0 0
 15.44964 7.660025 0 0
16.759949 8.982668 0 0
14.954945 8.048974 0 0
16.951004 8.050186 0 0
 15.26243 7.305363 0 0
14.994165 7.402324 0 0
14.914123 7.245769 0 0
 14.78059 7.613814 0 0
 15.26243 7.585931 0 0
16.523561 8.262881 0 0
14.994165 5.952262 0 0
 16.55635 7.898172 0 0
14.038654 7.384406 0 0
17.129696 8.047221 0 0
14.751604  6.95225 0 0
16.118095 8.158679 0 0
16.951004 8.364448 0 0
14.375126  6.85524 0 0
15.137266 7.576891 0 0
16.213406 6.595644 0 0
  14.7318  7.28589 0 0
15.648092 7.215975 0 0
 15.52026 7.394051 0 0
16.759949 8.657619 0 0
 15.56471 7.533148 0 0
15.319588 7.486221 0 0
13.122363 5.631212 0 0
14.220976 6.400752 0 0
13.384727 6.745319 0 0
 15.47374 7.348658 0 0
 15.56471 7.839624 0 0
 15.60727 7.178858 0 0
16.677711 8.387967 0 0
17.129696 7.523616 0 0
18.064005 9.905213 0 0
16.118095 6.816966 0 0
  14.7318 7.094268 0 0
16.618872 7.447168 0 0
16.677711 6.650609 0 0
 16.34124 6.650609 0 0
 16.34124 6.862913 0 0
16.648724 8.423494 0 0
 16.34124 7.710205 0 0
 16.34124 7.478853 0 0
end

[William Lisowski]

Your code is posted below in a CODE block, as is requested for Statalist. The code I have highlighted in blue does exactly what you want it to do. I think you meant to remove the code in red, which overwrites your good work with the not expontentiated coefficient.

use "$datadir/usedata/FCC Valuation for calibration final", clear

** Globals
**********

* Section
global geo west central
    
putexcel set "$docs/Calibration/Harmonization.xlsx", modify sheet("Coefficients")

* Model 1 - Simple OLS
* Log(Value) on built area of the property, square of the area and geographical sector
regress ln_annual_rent ln_surface_area $geo

* N
putexcel (C5:D5)=(`e(N)')
* R2
putexcel (C6:D6)=(`e(r2)')
* Root MSE
putexcel (C7:D7)=(`e(rmse)')


quietly matrix a=r(table)

* Constant
local constant=a[1,4]
local const_exp=exp(`constant')
putexcel C9=(`const_exp')

if a[4, 4]<0.1 {
    putexcel D9=("+")
}
if a[4, 4]<0.05 {
    putexcel D9=("*")
}
if a[4, 4]<0.01 {
    putexcel D9=("**")
}
if a[4, 4]<0.001 {
    putexcel D9=("***")
}


forvalues i=1(1)3 {

    * i=1: Log-living space
    * i=2: West
    * i=3: Central
    
    local j=`i'+9
    
    local coeff=a[1,`i']
    display `coeff'
    local coeff_exp=exp(`coeff')
    display `coeff_exp'
    if `i'==1 {
        putexcel C`j'=(`coeff')
    }
    else {
        putexcel C`j'=(`coeff_exp')
    }
    putexcel C`j'=(a[1,`i'])
    if a[4, `i']<0.1 {
        putexcel D`j'=("+")
    }
    if a[4, `i']<0.05 {
        putexcel D`j'=("*")
    }
    if a[4, `i']<0.01 {
        putexcel D`j'=("**")
    }
    if a[4, `i']<0.001 {
        putexcel D`j'=("***")
    }
}

[Nicolas Orgeira]

Thanks William, that's what sleep deprivation does to you!