Formatting Excel^® tables from within Stata

New capabilities of putexcel in Stata 14 let you format cells and fonts in Excel. This means that you can now easily export matrices, expressions, and stored results from Stata to Excel and control how they appear. You can also insert Stata graphs and add cell formulas. Now it’s easy to format the cells to create custom tables or reports. Let’s see how it’s done.

 Suppose I am estimating a fractional-response model for 401(k) participation and want to create a  table of my results that I could include in a presentation or publication. A short description of the variables is shown to the right.

. webuse 401k
. fracreg probit prate mrate sole
  (output omitted)

After I fit my model, I want to write out the estimated coefficients and standard errors to Excel. Most estimation commands in Stata store results in the r(table) matrix, giving you an easy way to access estimated coefficients, standard errors, test statistics, and the like from a single stored result. All you need to do is extract the appropriate rows and columns from the matrix.

I do this by typing

. matrix a = r(table)
. matrix b = a[1 ..2,1 ...]'
. putexcel C3=matrix(b) using 401k_report.xlsx replace

But my table is not quite publication-ready yet.

I can add bold column titles, “Estimate” and “S.E.”, and a cell border between the title and results to the worksheet by typing

. putexcel C2=("Estimate") D2=("S.E.") (C2:D2)=bold("on")
           (B2:D2)=border("bottom", "thin") using 401k_report.xlsx, modify

To add row labels and a cell border between the label and results, I type

. putexcel B3=("Matching Rate") B4=("Only Option") B5=("Constant")
           (B2:B5)=border("right", "thin") using 401k_report.xlsx, modify
. putexcel (B5:D5)=border("bottom", "double") using 401k_report.xlsx, modify

Now, I want to center the column text and results and format the numeric output to two decimals:

. putexcel (C3:D5)=nformat("number_sep_d2") (C2:D5)=halign("center")
           using 401k_report.xlsx, modify

I now have a nice-looking table.

But, putexcel can do even more. You can also write a wide range of graph file formats, including PNG, JPEG, WMF, and TIFF.

For example, I can add a bar graph of the average participation rate for companies over whether the 401(k) is the only retirement option offered by typing

. graph bar (mean) prate, over(sole) ytitle("Participation Rate")
        title("Plan Participation by Number of Options")
. graph export bar1.png
. putexcel (B7)=picture("bar1.png") using 401k_report.xlsx, modify

My worksheet now looks like this:

There are many more formats and settings that you can change in Excel by using putexcel. To view a list, see [P] putexcel.

— Kevin Crow
Senior Software Developer, StataCorp