Working with electronic filings using Excel

From Reporting Cookbook: www.forjournalists.com/cookbook

This is a step-by-step guide to working with electronic filings from the FEC using Microsoft Excel. Many of the steps are illustrated with screenshots.

1. From the FEC home page, go to the View/Download Electronic Filings site.

2. Enter the candidate last name or the ID of the committee into the search form and click "Send Query".

3. If you haven't downloaded the file formats, now's your chance - click on the "Format documentation for downloads" link (or get it from here) and save the format.exe file to your computer. Note: it's a Windows-only file.

4. Extract the files from format.exe by double-clicking on it and choosing a folder to extract the files inside into. I recommend using the latest version of the E-Filing Headers.xls file, which currently is 5.2.0.1.

5. Back to the search results - click the Download link next to the filing you want to use. Save that to your computer. Now find it and rename the file's extension from .fec to .txt.

6. Open Excel and then open the filing. You'll be greeted with an import wizard asking you to choose the file type for the filing. Stick with "Delimited" and click "Next."

7. The filing is delimited by commas, not tabs, so on the next screen change that by un-checking the box next to "Tab" and checking the box next to "Comma". Make sure the "Text qualifier:" is set to a double quote mark and click "Next".

8. On the next screen, highlight the ninth column and change the data format to "Text". This prevents Excel from dropping leading zeros in zip codes. After that, click "Finish".

9. Excel will load the filing into rows and columns. The first row designates what version of the FEC filing software is being used; you can ignore that in most cases. The second row contains summary information that matches what appears on the summary pages of the filing. Unless you're saving that information, you can remove those two rows.

10. At this point you can insert the headers from the E-Filing Headers spreadsheet for whichever type of transaction you want to examine. Use SchA for contributions, SchB for expenditures. Don't have contributions and expenditures on the same sheet, even though that's how they are arranged after import. Copy the expenditures and other transactions to their own respective sheets.

11. Make sure you save your file as an Excel workbook!