Using Stata to link Excel spreadsheets

One of the things that I love about Stata is its ability to simplify my life. Seriously. For example, I sometimes work on big Excel spreadsheets that are full of links to other tabs and/or other worksheets. These links to other sheets can be tedious to update as a simple "drag and drop" approach doesn't translate to correct cells being referenced. While I could use Excel Macros to update these spreadsheets with correct cell references and links, I always opt for using Stata's handy putexcel function. 

Suppose I know that I'll be referencing a group of cells from different worksheets and that I need to perform certain calculations containing those references. In this example, I'll show you how to do just that.

In the code below, I use putexcel to update a file already created called Comparison.xlsx. I know that this final worksheet needs to be updated starting at row 3, so I set my local i = 3. From there it goes through all different files within my global directory that end in ".xlsm" using a loop. Note that I told Stata to modify my excel file. I could have chosen a replace or a sheet replace option, if I needed to, too. 

It then goes through the files in that directory using the local `m' which trims the ".xlsm" out of the filename in order to reference other Excel files (.xlsx) that have the same name and contain information I need. 

Notice all the cool things I did with putexcel: I referenced different files, different tabs, performed calculations on cell references (divided and summed across), made totals, and created a percent difference. Not only that, but I also formatted to a number displaying 2 decimal points format,  number format with the 'thousands' comma separator, and percent with no decimals, for example. 

Not shown here, but I also format colors, borders, and cell alignment using putexcel to create nice tables and presentable spreadsheets that others in the company use. 

Like magic, I run this do file and it updates all numbers I need, formats it with correct number formatting, makes it look pretty and all without needing to use Excel directly. I open up the excel file I edited, Comparison.xlsx, and I replace all "=" with "=" and automatically all links become live. 

I hope you enjoyed this post. For more putexcel examples--especially if you like mata, take a look at Will's post. 

clear all 
set more off
version 14.0

global rdir "c:\users\bchavez\desktop\"
global sdir "c:\users\bchavez\wp\summaries\"

putexcel set "\${rdir}Comparisons.xlsx", sheet("All_Years") modify

local i = 3 //Beginning of Excel file inputs 

local m = ""
local s = " "
local files: dir "${rdir}" files "*.xlsm"
foreach f of local files{
        local fs = subinstr("`f'", ".xlsm","",.)
        local m  "`m'`s'`fs'"
}

foreach scenario in `m'{
        putexcel B`i' = ("='\${sdir}[`scenario'.xlsx]Results'!L288") ///
                A`i' = ("`scenario'") ///
                C`i' = ("='${sdir}[`scenario'.xlsx]Results'!L287") ///
                D`i' = ("='${rdir}[`scenario'.xlsm]Pivot Tables'!L234") ///
                T`i' = ("='${sdir}[`scenario'.xlsx]Results'!O45/1000000") ///
                V`i' = ("=SUM(D`i':S`i')") ///
                X`i' = ("=SUM('${rdir}[`scenario'.xlsm]Pivot Tables'!C3224:K3224)") ///
                AA`i' = ("=SUM(Z`i',M`i')") ///
                AH`i' = ("=(Y`i'-X`i')/X`i'") ///
                B`i':C`i' = nformat(number_d2) ///
                D`i':F`i' = nformat(number_sep) ///
                T`i':U`i' = nformat(accountcur) ///
                AH`i':AP`i' = nformat(percent) 
        local ++i
}

tokenize `m'
local name = "`1'"

putexcel A2 = ("Scenarios:") ///
        B1 = ("Summary")   ///
        B2 = ("Cost Ratio") ///
        C2 = ("Implied Payback") ///
        D2 = ("Forecasted X")