Putting SuperPAC's into Excel

Office for Mere Mortals
Your beginners guide to the secrets of Microsoft Office
Invalid email address
Tips and help for Word, Excel, PowerPoint and Outlook from Microsoft Office experts.  Give it a try. You can unsubscribe at any time.  Office for Mere Mortals has been running for over 20 years, we've never, ever revealed or sold subscriber details.  Privacy policy

How to get detailed SuperPAC donation and spending information into Excel.

Today the US Federal Election Commission (FEC) is receiving and publishing details of the controversial SuperPAC’s showing who has donated and what they spent money on.

It’s a good and timely example of how you can get information from the web and turn it into an Excel worksheet. Once in Excel you can search, sort and filter the data more easily.

Grabbing web data into Excel is something Office Watch has talked about before and we decided to use this as a good example. We’re taking no political stance on this.

SuperPAC’s can accept unlimited donations and, as satirised on The Colbert Report, spend the money in any way they like. Mr Colbert has noted that SuperPAC’s can spend money on “normal administrative expenses, including but not limited to, luxury hotel stays, private jet travel, and PAC mementos fromSaks Fifth AvenueandNeiman Marcus”

For our example we’ll use “Make us Great Again” since it has a relatively short list and is related to a candidate no longer in the Republican Primary race. We have a list of other PAC filings below.

The FEC data for the PAC starts here. Go to ‘Schedule A Filings (Itemized Receipts)’ then ‘For all Line Numbers’ ending up here. That page has a web table with a list of donors, address, employer, occupation, data and amount donated and total amount donated (where there’s multiple donations from the same source).

In Excel there are two ways to import the data:

  • Select the table in your browser, copy it then paste the results into Excel. This works best if you copy from Internet Explorer (other browsers can add additional formatting that Excel may not like).
  • Use the Web Query feature in recent versions of Excel. In Excel 2010 to Data | From Web. Paste the web link into the address box. When the page is displayed, small arrows will appear next to each web table. Click on arrow for each table you want to import (the arrow will change to a tick in a green box) then click Import.

1655 Excel 2010   Import web data query - Putting SuperPAC's into Excel

In Excel the data looks like this:

1655 Excel 2010   sample PAC donations import data - Putting SuperPAC's into Excel

Note: in some cases the list spans multiple pages. Look for the page navigation links at the bottom of the table.

Massaging the Cells

Sadly the web table isn’t formatted very well with multiple fields in a single column (most annoyingly amount and aggregate right under each other).  To sort and filter data in Excel you need each data field in a separate column with each data item (eg contribution) in a single row.

To turn the FEC table into more workable data (ie each field in a single column) you could write some clever VBA code. If you want to do it manually, you can link from the data fields to another column or worksheet then copy that relative reference (with spacing cells) down the column.

For example, the first aggregate amount is in cell D6. In another column (we’ll use Column E) make cell with a link to D6 (ie cell E5 has the formula =D6).

1655 Excel   copy cell to new column - Putting SuperPAC's into Excel

Now Aggregate is in a separate column. Copy cell E5 and the two blank cells below it down the rest of column E to put each aggregate number in a separate column. Do the same for all the second and third rows of data to put them in columns.

For a neat look put all this in a separate worksheet rather than a neighboring column. Messy and time-consuming but we have to work with the unhelpful format the FEC gives us.

Other data

Here’s a table of links to some PAC filings made at the end of January 2012 and available on the FEC site.


not co-ordinating with


9-9-9 Fund

Herman Cain


Americans For a Better Tomorrow, Tomorrow

Stephen Colbert


Endorse Liberty

Ron Paul


Leaders for Families Super PAC

Rick Santorum


Make Us Great Again

Rick Perry


Our Destiny

Jon Huntsman


Restoring Prosperity Fund

Rick Perry


Santa Rita Super Pac

Ron Paul


Sarah PACSarah PalinFEC

The main SuperPAC’s related to Messrs Gingrich, Romney and President Obama (plus many others) did not have year end filings on the FEC site at the time of our publication.

The Colbert SuperPAC has a fun list of contributors including some with obviously fake names (eg “Pat Magroin”).

More info

If you want to get really into the detail you can go here then follow the links (eg Detailed Files | 2011-2012 | Contributions by individuals).

At the time of writing these data files where not up to date compared with the FEC web tables which have filings only hours old.

Download the ZIP file. Inside the ZIP is a .dta file which is actually a text, fixed width data file that you can extract. Rename the .dta file to a .txt extension then you can Data | Get External Data | from text into Excel. There is unofficial advice for Access users on the FEC site. 

subs profile e1563205311409 - Putting SuperPAC's into Excel
Latest news & secrets of Microsoft Office

Microsoft Office experts give you tips and help for Word, Excel, PowerPoint and Outlook.

Give it a try. You can unsubscribe at any time.  Office Watch has been running for over 20 years, we've never, ever revealed or sold subscriber details.  Privacy policy
Invalid email address