While I was preparing a series of campaign finance panels for the National Institute for Computer-Assisted Reporting conference in Baltimore this weekend, Chris Keller sent an email to the listserv asking for advice on how to view a campaign finance report submitted to the FEC in Excel.
At USA TODAY, I’ve got a massive FEC database parked on a SQL Server box and fed by a small Army of Python scripts I’ve developed over the last two years. It’s a labor of love that has netted me a ton of co-bylines with my colleague Fredreka Schouten. But as Chris’ post pointed out, not everybody has the time, resources or even the desire, for that matter, to build such a complex system. He just wanted a way to get the data for a single campaign finance report into Excel so he could analyze it.
Anyone who has dealt with electronic filings knows how difficult it can be to work with that data. There are two header rows at the top of each report, followed by data for up to 16 schedules. Each schedule has its own record layout, none of which is included in the data files. And the FEC periodically changes the data headers. (Presently, they’re using v8.1: the fourteenth iteration.) It’s a hassle to work with these reports when you know what you’re doing and next to impossible when you don’t.
I realized in preparing for my panels that I not only had to show other journalists how to find data on the FEC website; I also had to show them how to easily view and analyze that data with the tools they have. After thinking about it, I came up with the idea to build an Excel template. Here’s what you do.
Build your template: (or download v8.1 here)
- Download the FEC’s .zip file containing all header information, decompress it and open “vX.X e-filing headers.xlsx,” where X.X is the header version you want. (You can use these instructions to build additional templates for other header versions as well.)
- Make sure you’re on the first tab., click cell B1, then press Ctrl-Shift-End to highlight all of your data from B1 to the bottom-right corner.
- Copy this data (Ctrl-C).
- Create a new worksheet (Ctrl-N).
- Click on cell A2 of your new worksheet (we’re leaving a blank row for headers) and Paste the data (Ctrl-V).
- We don’t need Column B, so let’s delete it. Click the B heading to highlight that column, right-click the column and select Delete.
- Go to A1 and create a generic header, such as Col1 or just 1. Then go to B1 and type Col2, 2 or something similar. Highlight these two cells, grab the fill handle (the little square in the bottom-right corner of the frame around the two highlighted cells) and drag it all the way to the right to make sure every column has a header. As of version 8.1, this requires you to go to cell GX1, generating 206 column headings.
- Save your file as an Excel template. You should give it a name that identifies the header version you’re using, such as: Form3Template_v81.xltx
Use your template:
- Go to the FEC website, find the report you want and download it in CSV format.
- Open your template, then immediately save the file as a regular Excel workbook to make sure you don’t overwrite your template later. Give it a descriptive name that reflects the report you are about to view.
- Click on the first blank cell. If you are using the v8.1 headers, this should be A50.
- Go to Data, Get External Data, From Text and browse to your .csv file.
- Step 1: Select Delimited data, and make sure the “My data has headers” box is unchecked. Click Next.
- Step 2: Check the Comma delimiter and make sure all other delimiters are unchecked. Text qualifier should be quotation marks (“). Click Next.
- Step 3: Leave the defaults and click Finish. If an Import Data window pops up, Make sure “Existing worksheet” is selected and the cell address is the first blank cell (A50, if you’re using v8.1). To break the data connection between your Excel file and the CSV file (which I recommend), click Properties, uncheck “Save query definition” and click OK twice to close both dialog boxes.
- Here comes the fun part: Click a non-empty cell in your data and turn on AutoFilter (Data, Filter). You’ll see our generic columns in Row 1 have dropdown arrows.
- Now you can filter your data to see only what you want. To see all receipts (from Schedule A), for example, click the dropdown arrow in A1 and select Text Filters, then Begins With. In the dialog that opens, enter “SA” (without the quotes), then click OK. You are now looking at just your Schedule A data with a nice row of headers in the second row.
- If you want to make sure your headers remain visible at all times, click the first cell in Column A containing data (rather than headers), then go to View, Freeze Panes.
I recommend you don’t sort your data while using AutoFilter. If you really want to do that, copy all of your data to a separate worksheet, skipping the generic column headings in Row 1. This will put the headers you really care about in the first row and will make sorting much easier and less likely to scramble your data.