William Matsuoka
  • Introduction
  • Topics
  • Stata
  • Teaching
    • ECON 641L
    • ECON 640L >
      • Econ-Data
  • Blog
  • About

W=M/Stata

Welcome

Putexcel 14.1

11/29/2015

13 Comments

 
With the release of Stata 14.1, Stata made a good amount of changes to one of my favorite commands: putexcel – I have mixed feelings about this. 

First, I want to take the opportunity to stress version control.  Always begin your do-files with the version command.  If you don’t, you’ll likely get the error r(101) “using not allowed” when trying to implement your old putexcel commands in a do-file.  A quick note about version: you must run this command with the putexcel statement.  Just having it at the top of your do-file and calling it doesn’t work, unless you run the do-file all the way through.  Think of using version control like using a local macro. 

​What I like:

​Putexcel has been simplified…
…but it requires changing the way you think about putexcel.  Think of the command in a more modular structure.  
  1. Set your Excel file that you wish to beautify
  2. Put all your text/data into the spreadsheet
  3. Format blocks of like things

Example:
putexcel set MyBook.xlsx, sheet("MyData")
putexcel A1=("Title of Data") A2=("Subtitle") A3=formula("SUM(A4:A20)"), bold
putexcel A3:A20, nformat(number_sep) hcenter
.
The updated options simplify your code a bit:
No more bold(on), rather just bold or nobold and there’s no need for that modify command.

Update: as of the December 21st update, modify is an option again...

Advanced number formats may be used:
No more restrictions, the advanced number format can now be used and can be found under putexcel advanced – which makes sense since it was able to be used already in the Mata xl() object.
Picture
putexcel set MyBook.xlsx, sheet("MyData")
putexcel A3, nformat(#.000) nobold border(all)

What I don't like:

Using not allowed!  I liked the using statement within putexcel, especially when I needed to add something as simple as a title on an excel spreadsheet.  Now, I have to use two lines of code instead of one.  This is a minor complaint compared to the next section.

The new syntax – it’s very difficult to work with large files, which is why I’ll be sticking with my “version 14.0” command when writing putexcel statements to large excel files.  Why?  I cannot find a simple way to write different format-types in one statement (say bold font in cell A1 and a border around cell B2).  I have to do this in two separate commands.  This is a big problem when you’re working with large excel files because Stata keeps opening the file, saving it, and closing the file for EVERY putexcel statement.  We could waste minutes writing borders and adding different colors.  Granted, Excel files shouldn’t be 40MB but we have them; and management wants to keep them that way, putting the Stata programmer between a rock and a hard place.  Check out my previous post on “block” putexcel for working with very large excel files.
​

What I hope happens:

We get the best of both worlds – the putexcel set keeps the Excel file open, so that it doesn’t continuously save file after file but the new format stays.  If we have a putexcel set, why not putexcel close?  Oh, and using is added back as an optional/hidden feature of putexcel.  That, my friends, would be my ideal putexcel command: not too hot, not too cold; simple, yet powerful.
13 Comments

    Author

    Will Matsuoka is the creator of W=M/Stata - he likes creativity and simplicity, taking pictures of food, competition, and anything that can be analyzed.

    For more information about this site, check out the teaser above!

    Archives

    July 2016
    June 2016
    March 2016
    February 2016
    January 2016
    December 2015
    November 2015
    October 2015
    September 2015

    Categories

    All
    3ds Max
    Adobe
    API
    Base16
    Base2
    Base64
    Binary
    Bitmap
    Color
    Crawldir
    Email
    Encryption
    Excel
    Exif
    File
    Fileread
    Filewrite
    Fitbit
    Formulas
    Gcmap
    GIMP
    GIS
    Google
    History
    JavaScript
    Location
    Maps
    Mata
    Music
    NFL
    Numtobase26
    Parsing
    Pictures
    Plugins
    Privacy
    Putexcel
    Summary
    Taylor Swift
    Twitter
    Vbscript
    Work
    Xlsx
    XML

    RSS Feed

Proudly powered by Weebly
  • Introduction
  • Topics
  • Stata
  • Teaching
    • ECON 641L
    • ECON 640L >
      • Econ-Data
  • Blog
  • About