Home > Programming > Export tables to Excel

## Export tables to Excel

There is a new command in Stata 13, putexcel, that allows you to easily export matrices, expressions, and stored results to an Excel file. Combining putexcel with a Stata command’s stored results allows you to create the table displayed in your Stata Results window in an Excel file.

A stored result is simply a scalar, macro, or matrix stored in memory after you run a Stata command. The two main types of stored results are e-class (for estimation commands) and r-class (for general commands). You can list a command’s stored results after it has been run by typing ereturn list (for estimation commands) and return list (for general commands). Let’s try a simple example by loading the auto dataset and running correlate on the variables foreign and mpg

```. sysuse auto
(1978 Automobile Data)

. correlate foreign mpg
(obs=74)

|  foreign      mpg
-------------+------------------
foreign |   1.0000
mpg |   0.3934   1.0000
```

Because correlate is not an estimation command, use the return list command to see its stored results.

```. return list

scalars:
r(N) =  74
r(rho) =  .3933974152205484

matrices:
r(C) :  2 x 2
```

Now we can use putexcel to export these results to Excel. The basic syntax of putexcel is

`putexcel excel_cell=(expression) … using filename [, options]`

If you are working with matrices, the syntax is

`putexcel excel_cell=matrix(expression) … using filename [, options]`

It is easy to build the above syntax in the putexcel dialog. There is a helpful video on Youtube about the dialog here. Let’s list the matrix r(C) to see what it contains.

```. matrix list r(C)

symmetric r(C)[2,2]
foreign        mpg
foreign          1
mpg  .39339742          1
```

To re-create the table in Excel, we need to export the matrix r(C) with the matrix row and column names. The command to type in your Stata Command window is

```putexcel A1=matrix(r(C), names) using corr
```

Note that to export the matrix row and column names, we used the names option after we specifed the matrix r(C). When I open the file corr.xlsx in Excel, the table below is displayed.

Next let’s try a more involved example. Load the auto dataset, and run a tabulation on the variable foreign. Because tabulate is not an estimation command, use the return list command to see its stored results.

```. sysuse auto
(1978 Automobile Data)

. tabulate foreign

Car type |      Freq.     Percent        Cum.
------------+-----------------------------------
Domestic |         52       70.27       70.27
Foreign |         22       29.73      100.00
------------+-----------------------------------
Total |         74      100.00

. return list

scalars:
r(N) =  74
r(r) =  2
```

tabulate is different from most commands in Stata in that it does not automatically save all the results we need into the stored results (we will use scalar r(N)). We need to use the matcell() and matrow() options of tabulate to save the results produced by the command into two Stata matrices.

```. tabulate foreign, matcell(freq) matrow(names)

Car type |      Freq.     Percent        Cum.
------------+-----------------------------------
Domestic |         52       70.27       70.27
Foreign |         22       29.73      100.00
------------+-----------------------------------
Total |         74      100.00

. matrix list freq

freq[2,1]
c1
r1  52
r2  22

. matrix list names

names[2,1]
c1
r1   0
r2   1
```

The putexcel commands used to create a basic tabulation table in Excel column 1 row 1 are

```putexcel A1=("Car type") B1=("Freq.") C1=("Percent") using results, replace
putexcel A2=matrix(names) B2=matrix(freq) C2=matrix(freq/r(N)) using results,
modify
```

Below is the table produced in Excel by these commands.

Again this is a basic tabulation table. You probably noticed that we did not have the Cum. column or the Total row in the export table. Also our Car type column contains the numeric values (0,1), not the value lables (Domestic, Foreign) of the variable foreign, and our Percent column is not formatted correctly. To get the exact table displayed in the Results window into an Excel file takes a little programming. With a few functions and a forvalues loop, we can easily export any table produced by running the tabulate command on a numeric variable.

There are two extended macro functions, label and display, that can help us. The label function can extract the value labels for each variable, and the display function can correctly format numbers for our numeric columns. Last, we use forvalues to loop over the rows of the returned matrices to produce our final tables. Our do-file to produce the tabulate table in Excel looks like

```sysuse auto
tabulate foreign, matcell(freq) matrow(names)

putexcel A1=("Car type") B1=("Freq.") C1=("Percent") D1=("Cum.") using results, replace

local rows = rowsof(names)
local row = 2
local cum_percent = 0

forvalues i = 1/`rows' {

local val = names[`i',1]
local val_lab : label (foreign) `val'

local freq_val = freq[`i',1]

local percent_val = `freq_val'/`r(N)'*100
local percent_val : display %9.2f `percent_val'

local cum_percent : display %9.2f (`cum_percent' + `percent_val')

putexcel A`row'=("`val_lab'") B`row'=(`freq_val') C`row'=(`percent_val') ///
D`row'=(`cum_percent') using results, modify
local row = `row' + 1
}

putexcel A`row'=("Total") B`row'=(r(N)) C`row'=(100.00) using results, modify
```

The above commands produce this table in Excel:

The solution above works well for this one table, but what if we need to export the tabulation table for 100 variables to the same Excel spreadsheet? It would be very tedious to run the same do-file 100 times, each time changing the cell and row numbers. Now we could easily change our do-file into the Stata command (ado-file) called tab2xl. The syntax for our new command could be

```tab2xl varname using filename, row(rownumber) col(colnumber) [replace sheet(name)]
```

The pseudocode of our program (file tab2xl.ado) looks like

```program tab2xl
/* parse command syntax */

/* tabulate varname */

/* get column letters based on starting column number passed in */

/* write header row to filename in starting row number passed in */

/* loop over rows of returned matrix and calculate/write values to filename */

/* write total row to filename */
end
```

If you would like to download a working version of our tab2xl command, type

```net install http://www.stata.com/users/kcrow/tab2xl
```

in Stata.

Categories: Programming Tags:
• Leonardo Goes Shibata

I wrote an alpha version of a program that can export two-way tabulations to excel.

• santhosh

i have one doubt… is it possible to transfer stata 12 result into excel spreadsheet? i tried that but i cant….

• Bryan

Great job on tab2xl2. I made a couple of corrections to use it – you have an “A” in one of the putexcel commands that keeps the column labels in column A versus column setting, and I added the sheet option. Thanks for your work on this !!!

• Leonardo Goes Shibata

Thank you! Fixed that!

• AU

Hello,
I am trying to use tab2xl to export frequency tables from stata to excel for multiple variables at once. do you have suggestions for a loop that can do this? I have tried

local variables price mpg rep78 headroom trunk weight length turn displacement gear_ratio foreign

local col=1

foreach var of varlist `variables’ {

tab2xl `var’ using “d:UsersautahDesktop1test.xls”, row(1) col(1) replace

local col=`col’+6

}
..but stata keeps replacing the last table with the next so in the end i end up with only 1 table in my Excel sheet.
Any advice on how to code this so that I have all the tables for all the variables in the same sheet?
Thanks!

• Alicia

Hi,
I’m using putexcel to export a matrix I created storing a number of results. I have given names to the columns and rows of the matrix, but the putexcel command is only saving the content of the matrix (not the column and row names). If I list the matrix, it includes the row and column names, so they are specified correctly. I tried specifying rownames and colnames options, but it returns an error message (eg option colnames not allowed). I see from your description above that I can add the row and column names manually specifying each one (eg B1=(“Population”)) Is there any way I could export those row and column names automatically?

Here is an example of the code that causes the error, using a simplified matrix:

version 14

foreach V of var ipd sero_7{
matrix ci1_`V’=J(1,5,-99)
matrix colnames ci1_`V’= Population IncidenceRate StdErr 95Lower 95Upper
matrix rownames ci1_`V’=0-4_2009
quietly ci `V’ if year==2009 & age_code_2==1, poisson exposure(population)
matrix ci1_`V'[1,1]=r(N)
matrix ci1_`V'[1,2]=r(mean)
matrix ci1_`V'[1,3]=r(se)
matrix ci1_`V'[1,4]=r(lb)
matrix ci1_`V'[1,5]=r(ub)
matrix list ci1_`V’
putexcel set ipd_results, modify sheet(“`V'”,replace)
putexcel B2 = matrix(ci1_`V’) , colnames
}

• Molly

FYI, it’s probably the fact that I’m very new to Stata, but I was getting option matcol() not allowed
r(198);
when trying to use tab2xl2

• Molly

Found my own answer further down the page, string variables don’t work, so categorical variables need to be encoded. See from below:
encode operator, generate(operator2)

• Leonardo Goes Shibata

Molly, sorry for taking long to answer! I’m glad that you could find a solution anyway. It’s always a good a idea to encode your categorical variables. Cheers!

• houda

I am Houda , I have the same problem of how to export results of the cros-correlations and the AR(1) coefficient, this is a part of my code
**********************************
clear*
set more off
log using “ALG.log”, replace
use ALG_data.dta, clear
gen time=_n
tsset time
rename var2 RO /*real GDP*/
rename var3 NO /*nominal GDP*/
rename var4 NGC /*NOMINAL GOVERNMENT CONSUMPTION EXPENDITURES*/
rename var5 NPC /*nominal private consumption*/
rename var6 RGC /*real GOVERNMENT CONSUMPTION EXPENDITURES*/
rename var7 RPC /*real private consumption*/
rename var8 M /*nominal imports*/
rename var9 X /*nominal exports*/
rename var10 TB /*net exports= X-M*/
rename var11 RX /*Real exports*/
rename var12 RM /*Real imports*/
rename var13 RTB /*Real trade balance*/
rename var14 RINV /*Real investment*/
rename var15 Eng /*energy consumption*/
rename var16 RWR /*Real workers’ remittances*/
rename var17 EXD /*external debts*/
gen tby=TB/NO
gen rtby=RTB/RO
gen lnRO=ln(RO)
gen lnRGC=ln(RGC)
gen lnRPC=ln(RPC)
gen lnRINV=ln(RINV)
gen lnEng=ln(Eng)
gen lnRWR=ln(RWR)
rename var1 Years
foreach var of varlist lnRO lnRGC lnRPC lnRINV lnEng lnRWR rtby{

twoway (line `var’ Years, sort),title (ALGERIA) subtitle (`var’) ytitle(“`var'”) xtitle(“Years”)
graph save Graph “C:UsersHOUDADesktopNew thesisTMENAALGERIAallsamplepicoriginal_`var’.gph”, replace

tsfilter hp hp`var’ = `var’,smooth(100) trend(hp`var’_tr)
*********************Volatility, peristence and correlations of the cyclical component***********
xcorr hplnRO hp`var’, lags(1) table
egen hp`var’_sd = sd(100*hp`var’)
gen hp`var’_relsd=hp`var’_sd/hplnRO_sd
corrgram hp`var’, lags(1)
egen hp`var’_tr_sd = sd(100*hp`var’_tr)
gen hp`var’_tr_relsd=hp`var’_tr_sd/hplnRO_tr_sd /*the contribution of the trend volatility to the gdp volatility*/
gen hp`var’_tr_relsdd=hplnRO_tr_sd/hp`var’_tr_sd /*the contribution of the trend volatility of GDP to the variables volatility-reverse of the ratio above*/
}
************subperiods analysis****************
gen period1=(time31)
************gross variables analysis***********************
foreach var of varlist lnRO lnRGC lnRPC lnRINV lnEng lnRWR rtby { /*Identification of the variables cyclical component obtained from the first part */
forvalues i = 1/2 { /*we keep the denomenation {`var’ for gross variables */
gen `var’`i’=`var’ if period`i’==1

}
}

foreach var of varlist lnRO lnRGC lnRPC lnRINV lnEng lnRWR rtby { /*growth rate computing for gross variables */
forvalues i = 1/2 {

************growth series of gross variables********
gen `var’`i’_ret=`var’`i'[_n-1] if period`i’==1
gen GROWTH_`var’`i’= (`var’`i’-`var’`i’_ret) if period`i’==1
egen GROWTH_`var’`i’_sd = sd(100*GROWTH_`var’`i’) if period`i’==1
gen GROWTH_`var’`i’_relsd=GROWTH_`var’`i’_sd/GROWTH_lnRO`i’_sd if period`i’==1

twoway (line `var’`i’ Years, sort),title (ALGERIA) subtitle (`var’`i’) ytitle(“`var’`i'”) xtitle(“Years”)
graph save Graph “C:UsersHOUDADesktopNew thesisTMENAALGERIAallsamplepic`i’original_`var’`i’.gph”, replace
}
}

save ALG.dta, replace
log close

************************************************

So how can I exports the results of xcorr and corrgram?, I need your help because I am working on 19 countries, and going back to the log file to copy results variable by variable take a lot of time.
thanks

• Anna Mejldal

Hi, I am trying to use tab2xl and it works for some variables (0/1) but other times I just get “to few quotes” without having changed anything but the variable name (to another numeric one, though with 12 categories and som missing). Any idea what my mistake could be? Thanks!

• Heresh Amini

Dear Statisticians, I have a problem to get the results of putexcel in columns (i.e. using colwise option). Here is the example command that I have for STATA 13:

tabstat Lectured, statistics(count sum mean min p25 p50 p75 max) save
putexcel A1=matrix(r(StatTotal), names) using test.xlsx”, sheet(“Results”) colwise replace

I don’t understand why still the results that I get in excel are in one column. Could you please help me why colwise does not work?! I need the results in one row.

• I agree with you.

• Kevin Crow

Lulu,

Don’t use the replace option. The replace option replaces the workbook with each pass of the loop.

• Kevin Crow

putexcel was introduced in Stata 13 so you can’t use tab2xl. You can use collapse and export excel to achieve similar results.

• Kevin Crow

Don’t use the replace option. The replace option replaces the workbook with each pass of the loop. The default behavior is to modify the Excel workbook.

• Kevin Crow

Try using the names option to export both the row and column names of a matrix.

putexcel B2 = matrix(ci1_`V’) , names

• Kevin Crow

Could be a bug. Can you post the exact command you are using.

• Kevin Crow

The colwise option works for multiple return results. You just have one returned result, the matrix r(StatTotal). To get the table you want, just transpose the matrix using the operator.

putexcel A1=matrix(r(StatTotal)’, names) using test.xlsx, sheet(“Results”) colwise replace

• Kevin Crow

xcorr is a graph command. To export a Stata graph to Excel use:

xcorr …

graph export gr_name.png

putexcel cell=picture(gr_name.png)

corrgram returns the autocorrelations, partial autocorrelations, and Q statistics. Which one are you trying to export?

• Sabrina Mahendra Solanki

I just tried the following command:

matrix list freq

matrix list names