Re: st: Export Excel and Cell references

[Phil Schumm <pschumm@uchicago.edu>]

On Dec 23, 2013, at 10:12 AM, Tal Elmatad <redacted> wrote:
> I am having a problem with Export Excel in Stata 12. I generally create a “raw data” worksheet and then create a second formatted worksheet that references cells on the “raw data” worksheet. I noticed that when I rerun my Stata script, if I use Export Excel the cell references don’t update (i.e. while the raw data is updated, the formatted worksheet is showing the old values).


Although I know very little about Excel, I can confirm that what you are trying to do works for me with Stata 13.1 (born 30 Oct 2013) and Excel 2011 (14.3.8 (130913)).  Specifically, I can do the following:

Step 1:

    sysuse auto
    export excel using foo.xlsx, sheet("mydata")

Step 2: Open the file foo.xlsx in Excel, create a new sheet, and in cell A1, enter the formula:

    =mydata!B1+mydata!C1

The resulting value should be 4,121 (e.g., price[1] + mpg[1]).  Close the file.

Step 3:

    replace price = 23 in 1
    export excel using foo.xlsx, sheet("mydata") sheetmodify

Step 4: Reopen the file foo.xlsx in Excel.  The value in Sheet1!A1 is now 45.

Thus, if this doesn't work for you, it must be due to the version(s) of Stata or Excel you are using, or as Jeph Herrin suggested, one or more settings in Excel.


On Dec 23, 2013, at 3:20 PM, Eric Booth <eric.a.booth@gmail.com> wrote:
> Using -export excel- I havent been able to get the Formulas calculate/recalculate sheet options to work, nor has changing the format of the data from ‘Text’ to ‘General’.
> 
> The only way I’ve found to do this (at least for my setup using Stata 13 and MS Excel 2011 on a Mac OSX) is to export the formulas (as string variables in Stata) into the worksheet, then: open the worksheet containing those formulas, highlight the entire sheet, Find = and replace it with a =  (there’s no difference here , just find/replace an equal sign) and in doing so it will calculate the sheet.


I don't believe this is related to the initial issue (though I could be wrong).  My impression (based on my *very* limited use of Excel) is that having a cell with a string value starting with "=" is not enough to cause Excel to automatically treat it as a formula.  Instead, there is something that happens after you hit Return/Enter whereby Excel parses what you have entered in the cell and, if it looks like a formula, causes Excel to treat it as such thereafter.  You can mimic this by selecting the cell/column and getting Data -> Text to Columns -> Finish, or, as you describe, by doing a global replace of "=" with itself.

I don't know what Stata is using to read/write Excel files, but one might guess (based on the new Java plugin functionality in Stata 13) that they are borrowing code from one of the many existing Java/Excel or Java/Office libraries.  Certainly many of these libraries permit writing formulas to a worksheet.  However, I don't believe Stata currently provides (or exposes) this functionality (at least I haven't come across it, including in Mata's new xl() class).  If I am wrong about this, hopefully someone will correct me quickly.

Finally, I can certainly imagine use-cases behind the original post.  For example, you might have a collaborator who is proficient in Excel and has agreed to assemble and format the final tables for a paper based on your Stata output.  Thus, you export your results to one or more "raw" worksheets, and your colleague rearranges these (using cell refs and formulas) on separate worksheets.  Now, if you decide to rerun your analyses, you can update your final tables simply by updating the "raw" worksheet(s) directly from Stata.  Alternatively, you could make yourself an Excel template that builds final tables based on Stata output located in a "raw" worksheet (if, say, you felt more comfortable doing this in Excel than in Stata), and then just re-use this template by updating the "raw" worksheet(s) directly from Stata.

However, I'm having trouble imagining a use case behind the second post.  Specifically, I would think that manipulating values directly in Stata and then exporting the results would be easier than constructing Excel formulas as strings in Stata.  Is there a use-case I'm missing here?


-- Phil


*
*   For searches and help try:
*   http://www.stata.com/help.cgi?search
*   http://www.stata.com/support/faqs/resources/statalist-faq/
*   http://www.ats.ucla.edu/stat/stata/