"Perhaps you've noticed the new Microsoft Office formats" is something you'd say back in 2007.  Nevertheless, I'd bet that a good portion of people haven't looked into what that new "x" is at the end of all default files (.docx, .xlsx, .pptx) and an equal number of people probably don't care.  However, I'd like to try and convince these doubters just how great this is!  This "x" stands for XML (extensible markup language) and makes the file formats seem hipper than their x-less counterparts.  Rather than documents and spreadsheets being stored as binary files, which are difficult to read, all information is stored in easily read zipped-xml files.  That's right - all .abcx files are just zip files.

Let's look at an example Excel file (Ex-Book.xlsx):

It has a little bit of everything: values, text, color formats, comments, and formulas.  If we wish to look at how the data are stored, we can easily change the extension of the file from .xlsx to .zip.

Naturally, we already have a way to take care of zip files in Stata with the command unzipfile.  So from this point on, we're going to navigate this file strictly using Stata.  My Excel workbook is located on my desktop, so let's unzip it.

cap mkdir unzipbook
cd unzipbook
unzipfile ../Ex-Book.xlsx, replace

dir xl/

Note that we didn't have to change the extension, and we now have a list of files and directories under the "xl" folder.  Files and properties of interest:

1. workbook.xml - contains sheet name information
2. styles.xml - color and font information referenced by the worksheet file
3. sharedStrings.xml - contains all the words or strings referenced by the worksheet file
4. comments*.xml - all comments within the workbook

These are good reference files, but our main interest is located within the "worksheets" directory under our "xl" folder.  Here, we can find each sheet named sheet1, sheet2, sheet3, etc.  The sheets are indexed according to the workbook.xml files (ex: "rId1" corresponds to sheet1).

I've always had trouble getting formulas out of Excel.  Instead of resorting to VBA, we can now use Stata to catalog this information (I've found this very useful for auditing purpose).  Here's a snippet of our worksheets/sheet1.xml file:

Interpreting this section, we can see that it contains rows 2-4 which corresponds to our strings (A, B, C), our styles (Red, Orange, Yellow), and our formulas.  Going from top to bottom, by deduction we can see that our first <c> or cell class contains a value of 3 and references cell D2 with the style of 7.  Here, since the type is a string ("s"), we need to reference our sharedStrings.xml file to see what the fourth string is.  We look for the fourth string, since the count initializes at zero rather than looking at the third string.  Our fourth observation is a "D" and our fifth observation is an "E" in the shareStrings.xml file.

The third row contains our colored cells without any data.  Therefore, they have no values, but contain styles corresponding to the fill colors.  A quick note: the order here goes (3, 4, 2, 5, 6) because I accidentally chose yellow first.

Finally, the most valuable part of this exercise (for me at least) are the formulas.  Cell A4 contains the formula "=A1&A2" and displays the evaluated value of the formula.  When linking to other workbooks, the workbook path will likely be another reference such as [1], [2], [3], etc.  A quick use of some nice Stata commands below yields a simple code for creating an index of your workbook's information.

tempfile f1 f2
filefilter xl/worksheets/sheet1.xml `f1', from("<c") to("\r\n<c")
filefilter `f1' `f2', from("</c>") to("</c>\r\n")
import delimited `f2', delimit("@@@", asstring) clear

keep if regexm(v1, "([^<>]+)")
gen value   = regexs(1) if regexm(v1, "v>([^<>]+)")
replace formula = subinstr(formula, "&amp;", "&", .)

keep cell formula value

Thanks to that little x, we are able to easily obtain meta-information about our Excel files, presentations, or even Word documents.  Think about having to look through thousands of documents to find a specific comment or reference - seems like a simple loop would suffice.  Maybe that x is pretty cool after all.

Coercion.  It’s the topic of the day, and a short topic at that.  Coercion is the leading driver for Excel usage for most Stata users.  Workbook linkage is usually a pain – the practice of linking one cell from a workbook to a different cell in another workbook – and can turn a relatively simple task into a nightmare usually ending the job with a headache and a stiff drink.
​
However, with a few simple Mata commands, the crisscross of equal signs, dollar signs, and formulas can turn a repetitive task into a few simple copy-and-paste commands. There is also less room for error.  For fun, we'll do this entire thing in Mata.  First, we need to decide how many observations/variables we need.  Let’s look at our current workbook and target workbook.

​So we need 5 columns and 7 rows, with columns from D to H and rows from 3 to 9 in our BookB set.  Write it:

mata:
st_addobs(7)
st_addvar("str255", "v" :+ strofreal(1..5)) 

​
​Starting with a blank dataset, add seven observations.  From there, we create 5 variables (v1 v2 v3 v4 v5) that are all of type str255.  This is a placeholder value for long equations.  The second part of this command “v” :+ strofreal(1..5) is my favorite part about Mata.  The colon operator (:+) is an element-wise operator that is super helpful.  No more Matrix conformability errors (well maybe a few).  It creates the matrix:

("v1", "v2", "v3", "v4", "v5")

​without much work and allows for any number of variables!  Sidenote – changing (1..5) to (1..500000) took about 3 seconds to create 500,000 v#’s.  Next, let’s set up our workbook name:

bookname = "'C:\Users\wmmatsuo\Desktop\[BookB.xlsx]Linked'!"

Specify the location of the workbook with the file name in square brackets and enclose the sheet name in apostrophes.  End that expression with an exclamation point!  Since we know we want D through H, and cells 3-9, we can write the following:

cols = numtobase26(4..8)
rows = strofreal(3::9)

numtobase26() turns 4 through 8 into D through H.  For more information see my previous post on its inverse.  The rows variable is a column vector containing values 3 through 9.  Now it’s time to build our expression:

expr = "=" :+ bookname :+ "$" :+ J(7, 1, cols) :+ "$" :+ rows

We put an equals sign in front of everything so that Excel will know we’re calling an equation.  Follow that with the workbook name.  If you didn’t know, dollar signs in excel will lock your formulas so they don’t move when you copy formulas from one cell to another.  We’ll put those in as well.

The next part, J(7, 1, cols), takes our column letters and essentially repeats it seven rows down.  Thanks to the colon operator, we just have to add another dollar sign to all elements of the matrix and our row numbers.  Since the new cols matrix contains seven rows, and our row vector contains seven rows, it knows to repeat the row values for all columns.
​​
Let’s just put that matrix back into Stata and compress our variables.

st_sstore(., ("v" :+ strofreal(1..5)), expr)
stata("compress")
end

And voilà, we have a set of equations ready to copy into Excel.  One quick tip: set your copy preferences so that when you copy the formulas, you won’t also copy variable names by going to edit->preferences->data editor-> uncheck “Include variable names on copy to clipboard”​.  Now just copy the equations directly from your Stata browse window into Excel, and enjoy those sweet results!

In other news, this post built upon the putexcel linking discoveries found over at belenchavez.com - who recently had her Fitbit stolen!  Follow her journey to get it back on her website or on Twitter - hopefully she'll be able to coerce the thief into returning the device.