Office Watch

Office 2013

Office Mobile / iPad

Office 2010

Office 2007

Office 2003

Office XP

Office for Mere Mortals

Access

Email

Buying Office

Office 365

Winks

Office News Wire

Join us!

Our Ebooks

Mobile | PDA

RSS


Search

Command Finder


Microsoft Office Bookshop

About

Home




Putting SuperPAC’s into Excel

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

by Office Watch

Bookmark and Share

  | Mobile | click for more article services     


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.

Excel 2010 - Import web data query image from Putting SuperPAC’s into Excel at Office-Watch.com

In Excel the data looks like this:

Excel 2010 - sample PAC donations import data image from Putting SuperPAC’s into Excel at Office-Watch.com

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).

Excel - copy cell to new column image from Putting SuperPAC’s into Excel at Office-Watch.com

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.

Name

not co-ordinating with <g>

Link

9-9-9 Fund

Herman Cain

FEC

Americans For a Better Tomorrow, Tomorrow

Stephen Colbert

FEC

Endorse Liberty

Ron Paul

FEC

Leaders for Families Super PAC

Rick Santorum

FEC 

Make Us Great Again

Rick Perry

FEC

Our Destiny

Jon Huntsman

FEC

Restoring Prosperity Fund

Rick Perry

FEC

Santa Rita Super Pac

Ron Paul

FEC 

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”).

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. 

Article posted: Tuesday, 31 January 2012

there's more ...

If you liked this article you'll LOVE our new ebooks.

Office 2013: the real startup guide

OFFICE 2013: the real startup guide Everything you need to know about Office 2013 but Microsoft won't tell you.

How to save money, install, configure and use the new features in Office 2013.  Get it today - click here.

Windows 8 for Microsoft Office users

Windows 8 for Microsoft Office users A practical guide the new, changed and unfamiliar in Windows 8

A focused and unvarnished look at Windows 8, especially written for the many people who use Microsoft Office  Get it today - click here.

ORGANIZING OUTLOOK EMAIL - tame your Outlook 2010 Inbox

100+ pages of practical tips and help to streamline, automate and search your Inbox.  Get more than you ever thought possible from Outlook.  Read it today - click here.

More from Office Watch:



Article Services sponsored by: Office Watch Ebooks - available now to download and read today.
RSS feed for this category Subscribe

Translate | Mobile | Links
 Add to: Bookmarks | | DiggThis | Yahoo! My Web


New & Popular
» Sorting in Word
» Alphabetical order in Word
» OneNote for Windows updated
» Office for iPhone v1.2 secrecy
» Hanx Writer and Office for iPad
» Windows 8.1 update disappoints


Office Watch, Office for Mere Mortals, Access Watch and all titles used within the publications are Copyright © 1996-2014 Office Watch.
Microsoft Office, Microsoft Word, Microsoft Excel, Microsoft Outlook, Microsoft Powerpoint and doubtless many other names are registered trademarks of Microsoft Corporation.

Search  |  Sitemap |  Popular Topics | Privacy Statement |  Advertising |  Twitter |  Feedback / Contact Us
Office Watch is definitely not affiliated with Microsoft - and that's just one reason why we are so useful to Microsoft Office users around the world J (Erko).