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.
In Excel the data looks like this:
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).
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.
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
|Americans For a Better Tomorrow, Tomorrow|
|Leaders for Families Super PAC|
|Make Us Great Again|
|Restoring Prosperity Fund|
|Santa Rita Super Pac|
|Sarah PAC||Sarah Palin||FEC|
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”).
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.
- Data Mining ‘fun’ coming soon
- Getting data from Internet into Excel
- How to convert Excel tables to HTML tables
- Exporting Access Data to Excel, Part 1
- Using Office on netbook computers, part 2