Ninety Six Spreadsheets

Thank goodness for Python. My wife asked me this morning, “Honey, can you give me the history of raises for Steve Smithfield and Jeff Johnson”. I told her I’ll look into it, and thought how I might use Python to tackle the problem.

I have the entire history of payroll captured across ninety six spreadsheets, one for each pay period.

To manually click through each spreadsheet, locate the pay rate for the Steve and Jeff, write it down, and go to the next spreadsheet would take about 30 seconds per spreadsheet, or almost 50 minutes. I decided to invest 10 minutes in a Python script I could use over and over. The script basically opens every .xls file in the local directory and creates a list of employee names in the spreadsheet. If Steve or Jeff are found in the list, their salary is appended to a list. After all the spreadsheets are read, the script prints out the results. The spreadsheets are named “Timesheet_20120101.xls” for January 1, 2012, “Timesheet_20120515.xls” for May 15, 2012, etc.

Here is the completed script


A User Friendly Experience

Let’s be honest for a second, when was the last time you saw a Windows user running something from the command prompt? Well, I do it occasionally, but I can’t say I remember seeing a non-IT person using the command prompt recently. So if you’re going to offer your users a Windows program, you better give them an icon to click and let them drag stuff onto it. And if something goes wrong, you better have a decent error message. This post will take the Pivot Table generation script developed in the Extending Pivot Table Data post and turn it into a user friendly Windows program with better flexibility and improved user experience.

The scripts developed previously could be run at the command line or by double clicking on the icon for the script line this.

Command Line Script Icon

This works because the input file name, ABCDCatering.xls, is hard coded within the script. In the real world, your users have folders containing dozens of randomly named spreadsheets. If a user accidentally provides a corrupt spreadsheet, the program should keep cranking through the other files and let the user recover the damaged file later. The script developed in the last post needs some enhancements to make it more user friendly, including:

  • Provide support for multiple randomly named input spreadsheets
  • Add some simple message boxes and drag-and-drop support
  • Improve the error checking and error recovery to give the user feedback when something goes wrong

To keep things concise, this version of the script only allows the user to run the program by dragging and dropping files onto the program icon. Enhancing the script to also support command line operation is left as an exercise for the user. Let’s work through each of the usability issues below:

Multiple File Support

As I mentioned, Windows XP/Vista/7 users typically don’t interact with the command prompt. Instead, programs are run by clicking on their icons, either from the desktop, a folder, or the Start menu. A user specifies spreadsheets or document files by opening them in the application or dragging them onto the program icon on the desktop or in the Explorer window. You can also add the file names after the program name at the command prompt if needed.

To process multiple files, the program needs to process command line args, which are already conveniently available in the sys.argv list. Note that the first argument sys.argv[0] is used for the script name. The runexcel function is modified to pass sys.argv to the runexcel function, which loops through each of the input files.

if __name__ == "__main__":

for fname in args[1:]:
    # Process spreadsheet files

The for loop wraps the wb = excel.Workbooks.Open(fname) call, the wb.SaveAs() call, and everything in between so each workbook is processed within the loop. After the loop finishes, a check for errors is made. If any errors occurred a warning and message box are issued.

Primitive GUI Support

Adding message boxes and providing basic drag-and-drop support adds a level of familiarity for Windows users. Python supports a large number of GUI frameworks, see for a comprehensive list. Building a complete graphic interface for this script is beyond the scope of this article, and isn’t really necessary anyway. Instead, you can add support for simple message boxes using the MessageBoxA function built into Windows. The basic pattern for calling a message box using this technique is to import ctypes and call windll.user32.MessageBoxA:

from ctypes import *
windll.user32.MessageBoxA(None,"My Message Box","Program Name",0)

This simple code produces a message box with the text “My Message Box”, an OK button, and “Program Name” as the top banner. When Python encounters windll.user32.MessageBoxA(), program execution pauses until the user clicks the OK button.

Messsage Box

Improve Error Checking

Lots of problems can happen when reading user spreadsheet data. The user can forget to specify an input file. They could try to have the script read a Word document or other non-spreadsheet file type. The spreadsheet might be corrupted. You need to bulletproof your script and guard against potential issues, both known and unknown.

Previous versions of the script made limited use of the try/except pattern to catch errors.

    wb = excel.Workbooks.Open('ABCDCatering.xls')
    print "Failed to open spreadsheet ABCDCatering.xls"
    sys.exit(1) makes more liberal use of try/except, wrapping more of the program code in the try block. If an error occurs, it can be handled more cleanly with nice warning messages. The downside of using try/except is that you lose the traceback message telling you where the error occurred. To get this information back, use the traceback module and the traceback.print_exc()function. One usage is to call traceback.print_exc() in the except block like this:

import traceback
  a = 1/0
  # Do error recovery

Now exceptions are caught, handled, and a more detailed traceback is still available.

Running the script

Let’s test out the script. First, copy the script to the desktop and drag the ABCDCatering.xls spreadsheet onto the icon. Python starts running in the command window and begins processing the file you dragged. If everything ran successfully, you’ll see a series of messages and the “Finished” message box.


If a problem occurred, a message is displayed in the command window. At the end of the run, the message box is displayed letting you know that something bad happened and that you should review the error messages.

Error Message

The completed script is too long to reproduce here, please go here to view the complete script.


Python (refer to

Win32 Python module (refer to

Microsoft Excel (refer to

Source Files and Scripts

Source for the program and spreadsheet file ABCDCatering.xls are available at

Thanks — Dan

Extending Pivot Table Data

As shown in the last post, Automating Pivot Tables with Python, Python and Excel can help you quickly clean up a spreadsheet, organize data and build useful reports in very few lines of code. Another useful data preparation technique is to build new columns of information based on the available data. For example, you could add an industry segment column to group company names by industry, or add an item type column to group sales items by category. While Excel does have some functions to help with adding new data fields, automation with Python eliminates the tedium of clicking column names and entering formulas.

Excel does provide a function for calculating new values within a pivot table. One example is extending a pivot table containing pricing and quantity data to compute an average selling price. For example, given the table below:

Sales by Quarter

a new label called “ASP”, which is the Net Booking divided by the Quantity, can be added quickly and easily with Excel’s Calculated Field capability.

Insert Calculated Field

This feature is handy for adding labels on the fly that require a simple calculation.

In other cases, deriving the new field may not be so simple, yet needs to be performed each time the spreadsheet is updated. Python can programmatically add new data fields to the source table so that the data is ready for viewing whenever the pivot table is opened.

The script developed last time automated the data cleanup and pivot table generation tasks. Doing some further analysis based on the output spreadsheet, I created a chart of the Top 10 Customers for ABCD Catering:

Top 10 Customers Chart

Note that some of the company names are 15 characters or longer in length and occupy much of the chart space. It would be nice to have a shorter “nickname” for each company that could be used in the charts. One solution is to cut and paste the pivot table data, then modify the Company Name information by hand. Unfortunately, this would be very tedious. Another approach is to automate the process in the script and create a new column derived from a comprehensive reference table of company names and nicknames. The downside is that maintaining the list could be an issue as the business grows and the list of customers grows longer. A third method is to create an algorithm that uses the first word in the company name wherever possible, and uses a defined nickname for other special cases. “Sun Microsystems” becomes “Sun” and “Cisco Systems” becomes “Cisco”, while other company names such as “Hewlett-Packard” could be listed in a lookup with a nickname such as “HP”. The snippet below shows how this is done.

logolookup = {'Applied Materials':'AMAT', 'Electronic Arts':'EA',
              'Hewlett-Packard':'HP', 'KLA-Tencor':'KLA'}
if ("Company Name" in newdata[0]):
    cindx = newdata[0].index("Company Name")
    newdata[0][cindx+1:cindx+1] = ["Logo Name"]
    for rcnt in range(1,len(newdata)):
        if newdata[rcnt][cindx] in logolookup:
            newdata[rcnt][cindx+1:cindx+1] = [logolookup[newdata[rcnt][cindx]]]
            newname = newdata[rcnt][cindx].split()[0]
            newdata[rcnt][cindx+1:cindx+1] = [newname]
            logolookup[newdata[rcnt][cindx]] = newname

This code begins with a simple lookup for company names and can be easily extended as special case company names are added. Next, the column location of the “Company Name” field is identified and the new header “Logo Name” is inserted after “Company Name” in the list using the :file:` list[index:index]` construct. The :file:` for` loop iterates over each row in the table, checking whether the company name for that row exists in the :file:` logolookup` dictionary, then inserting the abbreviated name. If not found, then the original company name is :file:` split()` into words and the first word used as the new abbreviated name. Finally, the :file:` logolookup` dictionary is updated with the new abbreviated name.

After running the program, the new column “Logo Name” has been inserted after “Company Name” and contains the shortened company names.

Company Name and Logo Name

The new “Logo Name” column can be used in the previous pivot table and chart, replacing the “Company Name” field and producing a cleaner chart with less area used for displaying company name information.

New Top 10 Customers Chart

Another use of this technique is to add a label for “Food Category” based on the type of food purchased. For example, the food items sold by ABCD Catering are: Caesar Salad, Cheese Pizza, Cheeseburger, Chocolate Sundae, Churro, Hamburger, Hot Dog, Pepperoni Pizza, Potato Chips and Soda. Let’s say that your manager wants to track the sales of different food categories, such as Burger, Dessert, HotDog, Drink, Pizza, Salad and Snack. Using the same technique outlined above, this code will add a column for Food Category with the appropriate entry for each food item:

foodlookup = {'Caesar Salad':'Salad', 'Cheese Pizza':'Pizza',
              'Cheeseburger':'Burger', 'Chocolate Sundae':'Dessert',
              'Churro':'Snack', 'Hamburger':'Burger', 'Hot Dog':'HotDog',
              'Pepperoni Pizza':'Pizza', 'Potato Chips':'Snack',
if ("Food Name" in newdata[0]):
    cindx = newdata[0].index("Food Name")
    newdata[0][cindx+1:cindx+1] = ["Food Category"]
    for rcnt in range(1,len(newdata)):
        if newdata[rcnt][cindx] in foodlookup:
            newdata[rcnt][cindx+1:cindx+1] = [foodlookup[newdata[rcnt][cindx]]]
            newdata[rcnt][cindx+1:cindx+1] = ['UNDEFINED']

If a food item is not found in the lookup, the category is labeled UNDEFINED. This is an indication that there is a problem with the script and the lookup for food categories needs to be extended.

The section of the script which creates the pivot tables can be easily extended to build a new table based on the newly created label “Food Category”:

# What food category had the highest unit sales in Q4?
ptname = addpivot(wb,src,
         title="Unit Sales by Food Category",
         filters=("Fiscal Quarter",),
         rows=("Food Category",),
         sumvalue="Sum of Quantity",
         sortfield=("Food Category",win32c.xlDescending))
wb.Sheets("Unit Sales by Food Category").PivotTables(ptname).PivotFields("Fiscal Quarter").CurrentPage = "2009-Q4"

Based on the output spreadsheet, the best selling food category in Q4 based on quantity is “Snack”, with sales of 13700 units.

Sales by Food Category

Here is the completed script, also available on GitHub


Python (refer to

Win32 Python module (refer to

Microsoft Excel (refer to

Source Files and Scripts

Source for the program and spreadsheet file ABCDCatering.xls are available at

Thanks — Dan

Automating Pivot Tables with Python

In the last post I explained the basic concept behind Pivot Tables and provided some examples. Pivot tables are an easy-to-use tool to derive some basic business intelligence from your data. As discussed last time, there are occasions when you’ll need to do interactive data mining by changing column and row fields. But in my experience, it’s handy to have my favorite reports built automatically, with the reports ready to go as soon as I open the spreadsheet. In this post I’ll develop and explain the code to create a set of pivot tables automatically in worksheet.

The goal of this exercise is to automate the generation of pivot tables from the last post, and save them to a new Excel file.

Pivot Tables

I started with the file newABCDCatering.xls from the previous post and record the macro to create this simple pivot table showing Net Bookings by Sales Rep and Food Name for the last four quarters.

Net Bookings

Captured in Excel 2007, the recorded macro looks like this:

The post Mapping Excel VB Macros to Python covered a technique for recording a Visual Basic macro and porting it to Python. Using that approach, you could simply turn on the macro recorder and generate all the required tables, producing a long script with lots of redundancy. A better approach is to build a general purpose function that can be used over and over to generate the pivot tables.

Looking at the macro, you see lines specifying the Orientation of the field name, such as .Orientation = xlRowField and .Orientation = xlColumnField. A pivot table has four basic areas for fields:

  • Report Filter (.Orientation = xlPageField)
  • Column area (.Orientation = xlColumnField)
  • Row area (.Orientation = xlRowField)
  • Values area (PivotTables().AddDataField())

Each of these supports multiple fields (column fields for Sales Rep Name and Food Name were added in the example). The ordering of the fields changes the appearance of the table.

A general pattern should be apparent in this macro. First, the pivot table is created with the ActiveWorkbook.PivotCaches.Create() statement. Next, the columns and rows are configured with a series of ActiveSheet.PivotTables("PivotTable1").PivotFields() statements. Finally, the field used in the Values section of the table is configured using the ActiveSheet.PivotTables("PivotTable1").AddDataField statement. The general purpose function will need to contain all of these constructs. Note the parts that can’t be hard-coded: the source of the data, "Sheet2!R1C1:R791C13", and destination for the table, "Sheet3!R3C1" need to be determined based on the characteristics of the source data and can’t be hard coded in the general solution.

In Python, this pattern can be reduced to the following loop that covers fields for the Report Filter, Columns and Rows:

def addpivot(wb,sourcedata,title,filters=(),columns=(),
    """Build a pivot table using the provided source location data
    and specified fields
    for fieldlist,fieldc in ((filters,win32c.xlPageField),
        for i,val in enumerate(fieldlist):
            wb.ActiveSheet.PivotTables(tname).PivotFields(val).Orientation = fieldc
        wb.ActiveSheet.PivotTables(tname).PivotFields(val).Position = i+1

Processing the Values field is more or less copied from the Visual Basic. To keep things simple in this example, this code is limited to adding “Sum of” values only, and doesn’t handle other Summarize Value functions such as Count, Min, Max, etc.


The actual values for filters, columns and rows in the function are defined in the call to the function. The complete function creates a new sheet within the workbook, then adds an empty pivot table to the sheet and builds the table using the field information provided. For example, to answer the question: What were the total sales in each of the last four quarters?, the pivot table is built with the following call to the addpivot function:

# What were the total sales in each of the last four quarters?
         title="Sales by Quarter",
         rows=("Fiscal Quarter",),
         sumvalue="Sum of Net Booking",

which defines a pivot table using the row header “Fiscal Quarter” and data value “Sum of Net Booking”. The title “Sales by Quarter” is used to name the sheet itself.

To make the output spreadsheet more understandable, the title parameter passed into the function and used as a title in each worksheet and as the tab name.

Title Tabs

The complete script is shown below. Caveats:

  • This script has been modified to run on both Excel 2007 and Excel 2003 and has been tested on those versions.
  • Adding pivot tables increases the size of the output Excel file, which can be mitigated by disabling caching of pivot table data. Line 48 of the script contains the command newsheet.PivotTables(tname).SaveData = False, which has been commented out. Uncommenting this command will reduce the size of the output Excel file, but will require that the pivot table be refreshed before use by clicking on Refresh Data on the PivotTable toolbar.


Python (refer to

Microsoft Excel (refer to

Source Files and Scripts

Source for the program and input spreadsheet file ABCDCatering.xls are available at

Thanks — Dan

Introducing Pivot Tables

A working knowledge of Microsoft Excel is now a prerequisite for just about every working professional using a computer on a regular basis. Excel training classes can be found in junior colleges, adult education centers, computer training centers, libraries, job search centers and of course online. Microsoft Excel basics is even taught in high schools.

But in my professional life, I’ve found few people who have a solid knowledge of pivot tables and are really comfortable using them in Excel. If you aren’t aware of pivot tables or haven’t had the time to try out this function in Excel, pivot tables provide a way to cross tabulate, sort, segregate and aggregate tabular data, enabling you to quickly summarize data and extract totals, averages, and other information from the source data. I first found out about pivot tables when I was working with our business unit financial analyst more than 10 years ago. She didn’t like our corporate ERP system (SAP) any more than I did, and found it much faster to dump the raw data into Excel and get the answers to her questions by using a pivot table. I’ve been a pivot table convert ever since.

Using the spreadsheet newABCDCatering.xls developed in the last post, let’s add a pivot table and answer the questions raised previously:

  • What were the total sales in each of the last four quarters?
  • What are the sales for each food item in each quarter?
  • Who were the top 10 customers for ABCD Catering in 2009?
  • Who was the highest producing sales rep for the year?
  • What food item had the highest unit sales in Q4?

To build the pivot table, begin by selecting the entire data table in the Sheet2 worksheet by clicking cell A1, and typing the Control-* key combination (hold down Ctrl and press *). This selects the data in the table without also grabbing blank cells surrounding the table. This is effectively the same as selecting cell A1 and scrolling to the last column and row of data while holding down the left mouse key.

Next, if you’re using Excel 2007 or later, select the Insert tab then select PivotTable from the Pivot Table icon

Insert Pivot Table

Because you’ve selected the spreadsheet data, the dialog should already be populated with the range Sheet2!$A1:$M791 as shown below

Create Pivot Table

Click OK to create the empty pivot table.

Empty Pivot Table

In Excel 2003 and earlier versions, select the table data as described above, then select Data->Pivot Table and Pivot Chart Report to create the pivot table.

Pivot Table Menu

You’re presented with a three step wizard. For now, just hit Next for the first two dialogs, thenFinish at the final dialog.

Dialog 1 Dialog 2 Dialog 3

Once you’ve completed the above steps, you’ll see the following displayed in older versions of Excel.

Empty Pivot Table

Now you’re ready to do some data analysis.

What were the total sales in each of the last four quarters?

To understand the sales for the last four quarters, create a pivot table with “Fiscal Quarter” as a Row Label, and “Net Booking” as a Values field. To do this, drag the field “Fiscal Quarter” to the Row Labels section, and “Net Booking” to the Values section. (In older Excel versions, drag the “Fiscal Quarter” field directly onto the spreadsheet to the “Drop Row Fields Here” area, then drag the “Net Bookings” field onto the “Drop Data Items Here” area)

Pivot Table Field List

Your spreadsheet should now look something like this:

Pivot Table

The header for the table data should say “Sum of Net Bookings”. If it doesn’t, double click on the header text and select “Sum” in the list box “Summarize value field by”, or right mouse click over the text and select Summarize Data By->Sum.

Value Field Settings Summarize By

Based on the spreadsheet data, the total net bookings in each of the last four quarters were $83465, $77180, $79605 and $77440 respectively.

What are the sales for each food item in each quarter?

To answer this question, we need the same fields as setup previously (Fiscal Quarter as a Row Label, Sum of Net Booking as a Value field), plus a column header for “Food Name”. Remember that “Food” represents the numerical identifier for each food item, and “Food Name” contains the text description. Drag “Food Name” to the Column Labels section (in older versions of Excel, drag it to the “Drop Column Fields Here” area). The spreadsheet should now look like this:

Sales by Food Item Setup

Note that each food item is listed as a column header, each of the four quarters are listed as row headers. Using this table you can quickly scan the data and understand the sales for each food item. For example, Caesar Salad sales were $7890, $7140, $7960 and $6990 in each of the respective quarters.

Who were the top 10 customers for ABCD Catering in 2009?

Again, Sum of Net Bookings is the data value, but we no longer need the Food Name or Fiscal Quarter data fields. Remove them by selecting them in the Row Labels or Column Labels boxes and dragging them back to the top, or by clicking the small triangle and selecting “Remove Field”.

Remove Field

In Excel 2003 and earlier versions, select the column or row header and drag it back into the Field Chooser widget.

Now, add the Company Name field to the table by dragging it to the Row Labels box (or “Drop Row Fields Here” area in older Excel). The pivot table now contains the list of companies and their purchases, listed in alphabetical order. To find the top 10 customers, select the booking number for Adobe Systems, right click and select Sort->Sort Largest to Smallest

Sort Largest to Smallest

In older versions of Excel, select a booking number and click the “Sort Descending” icon in the tool bar, or select “Data->Sort” from the menu and select the descending sort.

Sort Descending

The list is now sorted, the top 10 customers for ABCD Catering are Hewlett-Packard, Intel, Oracle, Cisco Systems, Sanmina SCI, Sun Microsystems, Apple, Con-Way, eBay and Yahoo.

Top 10 Customers

Who was the highest producing sales rep for the year?

At ABCD Catering, sales reps cover multiple accounts. To find the highest producing rep, remove the Company Name field, replace it with the Sales Rep Name field and sort by Net Bookings. The top 10 sales reps are Dave Davidson, Lin Linares, Carl Carlson, Kay Kaywood and Nicole Nichols.

Top 10 Reps

What accounts are these top reps responsible for? To find out, drag the Company Name field into the Row Labels area.

Top 10 Rep Accounts

In older versions of Excel, drag Company Name directly onto the table

Top 10 Rep Accounts

Since Hewlett-Packard, Intel and Cisco Systems were 3 of the top 4 producing accounts, it’s no surprise that their sales rep Dave Davidson was the top performer.

Which food item had the highest unit sales in Q4?

To find the food item with the highest unit sales, change the data value field to Sum of Quantity by removing Sum of Net Bookings, adding Quantity and making sure the Value Field Setting is “Sum” and not some other setting. Next, remove the Sales Rep Name and Company Name row header fields and replace them with Food Name. To limit the data to the Q4 quarter, drag the Fiscal Quarter field to the Report Filter area, and select “2009-Q4”.

Fiscal Quarters

Finally, do a descending sort on the Sum of Quantity field to find the item with the highest unit sales.

Top Food Items

The number one item by unit volume was Potato Chips, followed by Soda and Churro.

Hopefully this gives you a feel for the power and flexibility of pivot tables. In the next post, we’ll automate everything with Python and generate a simple framework for quickly building pivot tables.


Microsoft Excel (refer to

Source Files and Scripts

The spreadsheet newABCDCatering.xls is available at

Thanks — Dan