FEC: Electronic filing a boon for reporters
Agency website makes downloading, importing and analyzing data easier
By Aron Pilhofer
Campaign Finance Information Center
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.
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
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
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
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
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.
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
• 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,
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 firstname.lastname@example.org.
Return to Tracker.