FEC: Electronic filing a boon for reporters
Agency website makes downloading, importing and analyzing data easier than ever.

By Aron Pilhofer
Campaign Finance Information Center

From import to analysis, working with raw federal campaign finance data used to be a frustrating chore for even the most skilled CAR reporter.

But not anymore.

The Federal Election Commission’s new electronic filing Web site makes viewing, importing and analyzing data fast enough for deadline reporting, and easy enough for anyone with basic CAR skills and a spreadsheet.

The FEC has been experimenting with electronic filing for years. But the 2002 election cycle is the first in which it is mandatory for political parties, PACs as well as candidates for president and U.S. House. For journalists, this is a boon. Electronic filing data is simply better than the raw data used in previous cycles. It is more complete because it includes both contributions and expenditures – something journalists never had access to before.

It also includes full street addresses, rather than just city, state and ZIP Code. That makes the task of positively identifying contributors, their relatives and spouses much easier.

Viewing data

The FEC electronic filing site is located here: http://herndon2.sdrdc.com/dcdev/. From the main search page, you can find filings any number of ways: by committee, name, state, party, report type, the filing date or committee ID.

Once you’ve located the committee you are interested in, you’ll see all the filings for the current cycle listed under the committee name in order of the date they were filed with the FEC. Here’s a typical example:

FEC-18381 Form F3N - period 01/01/ 2001-06/30/2001, filed 07/31/2001 -MID- YEAR

The first code (FEC-18381) you can ignore. It’s an internal code used by the FEC as a unique identifier. The rest tells you what form is being filed, whether it is a new or amended filing (amended filings will have an “A”, new filings will have an “N”), what reporting dates it covers, when it was filed and the name of the reporting period.

It’s important to note that just because a filing appears at the top of the list does not mean it is the most recent filing. Frequently, committees will amend or correct older reports, and those will appear on the list in order of the date they were filed with the FEC, not the filing periods they cover. The older reports won’t be removed but will have a small “amended by” notation added along with a link to the updated report.

The bottom line: Make sure you’re looking at the most up-to-date version of the report.

Once you’ve got the filing you want, you can view it by clicking on the “view” link just to the left of the label. This will produce an electronic version of the old paper records organized in exactly the same way with the summary pages first followed by the detailed schedules.

Download and import

To get a file onto your local computer for spreadsheet work, simply click on the “Download” link next to the one you want. The file will be saved with a “.fec” extension, but it is nothing more than a simple comma-delimited text file.

You’ll also want to save yourself some typing and download a list of column headers from the FEC site. They are found in a self-extracting file, which you can download by clicking on the link at the top of the page titled “Format documentation for downloads.” The headers file is an Excel document titled “e-filing headers.xls” and includes all the column heads for each of the FEC forms. Once you have your filing imported, you can simply copy and paste in the appropriate column head.

To begin importing, launch Excel, click File-> Open, navigate to the folder in which you saved your filings and open the filing. Unless you renamed it when you downloaded, it will be named something like “21232.fec”.

Click on your file, and click the “Open” button and the text import wizard will open. In order for the data to be imported correctly make sure of the following:

• Use the “start import at row” selector to begin at row 3. The first two lines are not important for us. They are a header row that indicates what software the committee used, and the second line is the aggregate data. (Not all versions of Excel have the “start import” feature. If yours doesn’t, you’ll have to delete the first two rows by hand later on.)

• If you are importing expenditures (which have a different layout than contributions, but are in the same file) you’ll want to open the filing in a text editor before importing into Excel and determine what line the disbursement data begins on, and use the “start import at row” selector to exclude contributions.

• By default, all the columns are formatted as the “General” datatype, which means Excel will guess what sort of format you want based on what type of data it thinks the column contains. This will cause problems for ZIP codes, which often have leading zeros. To avoid losing data, make sure the ZIP field is formatted as text.

• The date fields are unformatted and are listed as year, month then day (e.g., 20011231). To convert them to dates on import, change the column to the date format and select the YMD option in the pulldown menu. Now, your dates will be formatted correctly.

Final touches

There are a few more steps you might want to take before you start crunching and munching the data.

• Scroll down the spreadsheet until you see the “SA” codes in Column A change to “SB” (SA means schedule A, or, contributions, and SB means schedule B, or, disbursements). You don’t want to have both in the same sheet, and if you scroll over you’ll see some of the data was corrupted during the import because disbursements have a different layout than contributions even though they are in the same file. I find it easiest to simply note the row on which the disbursements begin, delete them out of the sheet and then re-import starting at the row on which the expenditure data begins.

• Wouldn’t it be nice to know what all these columns mean? Open the Excel headers file you downloaded earlier and find the row that matches the code in the first column of your data (e.g., SA, SB, etc). Find the appropriate code and paste the line into your spreadsheet at the top.

• You may have already noticed that the name field is a single column, with individuals’ first and last names delimited by the caret (^) character. There is a quick fix for this: Click on the Contributor Name column to select the entire thing, and select Insert-> Columns four times. You should have four blank columns to the right of your Contributor Name column. Now comes a nifty trick: Assuming your Contributor Name column is still selected, select Data-> Text to Columns and a wizard similar to the text import wizard will appear. In step 2, click the “other” delimiter option, and in the window type a single caret (shift-6). Not all campaigns will use four fields for names, but some do. It’s better to add too many blank fields at the be-ginning because if you are not careful with the text to columns feature, it will over write data in your spreadsheet. Don’t forget to type in the appropriate column headers, e.g. first name, last name, prefix, suffix.

Beware: Data caveats and tips

• The U.S. Senate exempted itself from the electronic filing requirement, so you will not find any Senate filings at the FEC electronic filing site. Those data are available only in the raw files available from the FEC’s FTP site.

• The FEC posts filings almost instantly on the electronic site, even before they have been reviewed by staff. Reports are frequently amended, so be aware that the data you download may be changed later on. Before you run with a story, it is a good idea to make sure the reports are the most up-to-date.

• The “official” data is still maintained on the agency’s FTP site (and from the IRE and NICAR Data Library, already processed into DBF format). If you are not working on a story that requires the absolutely most recent filings, those are the files you should use.

• Just because the data is filed electronically does not mean it is clean. Expect to find mistakes. But committees are using software that standardizes names and addresses somewhat, creating fewer data-entry errors.

• Before you dive in to the number crunching, print out a copy of the summary pages from the filing just to make sure your aggregate numbers are correct. If things don’t add up, it’s probably because the campaign has memo entries in the data with additional information about certain types of contributions. Filter those out using the Memo Code or Memo Text columns.

• It’s also best to use the codes in column A to break out the different types of contributions, and ignore the “Entity Type” column, which frequently is incorrect. The code tells you everything you need to know anyway – the schedule and line number on which the contribution will be aggregated.

Ready for stories

The electronic filing data gives you the ability to analyze in ways that would have been impractical using paper records: Calculate the number of out-of-state contributors. Look for evidence of bundling, kiddie contributors, spouses and other related individuals. There are any number of ways to look at the data.

At this point you could also import your spreadsheet into a database manager like Microsoft Access or FoxPro to compare it with previous filings or those of another candidate.

With electronic filing reporters can do more than write the standard deadline horse race story – who’s raised the most, and has the most on hand. Now, you can bring some context and depth to your reporting, and do it on deadline.

For more information about campaign finance, including data, training, tipsheets to help journalists and more, go to www.campaignfinance.org. For a more detailed tutorial about electronic filings, especially for beginners, go to the IRE Resource Center Web site at www.ire.org/resourcecenter or call 573-882-3364.

Aron Pilhofer is director of IRE's Campaign Finance Information Center. He can be reached at aron@ire.org.

Return to Tracker.