Belen Chavez
  • Introduction
  • About
  • Teaching
  • Data Blog
  • Calligraphy

Data Blog

Using Stata to link Excel spreadsheets

10/21/2015

0 Comments

 
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") 
        

0 Comments

    Author

    My name is Belen, I like to play with data using Stata during work hours and in my free time.  I like blogging about my Fitbit, Stata, and random musings.

    If you like the Stata posts you see here, I guarantee you'll also like what's over at
    wmatsuoka.com


    Archives

    March 2018
    January 2018
    September 2016
    July 2016
    June 2016
    May 2016
    April 2016
    March 2016
    February 2016
    January 2016
    December 2015
    November 2015
    October 2015
    September 2015
    August 2015
    July 2015


    Categories

    All
    API
    Beer
    BJJ
    BreweryDB
    CURL
    Education Research
    Excel
    Fitbit
    Fitbit API
    Google
    Google Charts API
    Google Maps
    LinkedIn
    Love
    Parsing
    PPIC
    Putexcel
    Rant
    San Diego
    Stata
    Tableau
    Twitter API
    Valentine's Day


Proudly powered by Weebly
  • Introduction
  • About
  • Teaching
  • Data Blog
  • Calligraphy