FECHell

fechell is an open-source ruby library for parsing electronically filed Federal Election Commission reports. fechell hides the complexity of dealing with the multiple revisions and confusing formats of the .FEC files, allowing an analyst to extract data in a simple and uniform way. fechell is available as gem through GitHub.

Link: fechell
Source Code: fechell @ GitHub

Installation

The fechell library is available as a ruby gem from GitHub.

Then you can install the gem directly from rubygems / gemcutter with the following command

					    sudo gem install fechell
						

fechell has an external dependency only on the FasterCSV gem.

Introduction

To begin using fechell to extract information from FEC reports you will need the following:

1. The latest FEC VendorTools distribution.
2. One or more electronically-filed .FEC reports. Individual reports can be downloaded through the FEC Query Tool and bulk access to all available reports is avaialble through the FEC ftp site.

FEC File format

A .FEC report is a line-based plain text file, with columns seperated by a version-dependent seperator character. The first column of a line describes the type of data contained in that line. The filing contains at the very least:

• a HEADER (HDR) line
• a FORM (F) line
• zero or more SCHEDULE (SCH/S) lines, depending on the type of FORM

Inside the FEC Vendor Tools distribution is an Excel file that contains a worksheet for every header, form, and schedule line, and a PDF file that defines which SCHEDULE lines are valid for which FORM. Definitions of each form type are available in both the XLS workbook and the PDF document included in the FEC Vendor Tools.

Since each FORM contains different information, you will need to know before hand what kind of data you want to extract. Knowing that, you can then work backwards to the SCHEDULE that data is contained in, and which FORMS those schedules appear on. Given this workflow we can perform the following steps to extract the data we want

1. Determine the type of information we want
2. Use the FEC Vendor Tools PDF/XLS files and FEC Form information to find out which FORM/SCHEDULE that information is contained in
3. Use the FEC Query tool to find filings
4. Use fechell to extract data

Example 1

I want to see the total amount raised and spent in the current cycle for Virgil Goode, and Thomas Perriello, candidates for Virginia's 5th congressional district.
Using the workflow above we can see we're already started.

Information we want: Financial summaries for Goode and Perriello..

Browsing the FEC Form information page under 'Forms for Candidates and Authorized Committees', we can see that a candidate must file a Form 3: Reports of receipts and disbursments. Looking at the actual PDF copy of the Form 3, available from the FEC Form information page, we see there are two columns of summary information. Column A is this current reporting period, and column B is cycle to date. Since we want to know the total amount raised and spent we'll use column B information. Opening the FEC Vendor Tools worksheet for Form 3 F3 filings gives us the column names we need to extract the summary data. Below is an example program that will download the FEC reports found via the FEC Query tool, initialize the fechell library and then output the financial summary information we want to see.

							require 'rubygems'
							require 'fechell'
							require 'fechell/forms.rb'
							require 'open-uri'

							# convenience function to download a file from a URL and save it locally

							def savefile(filename,url)
							  File.open(filename,"w") do |file|
							    file.puts open(url).read
							  end
							end

							h = FECHell.new

							# Grab the year-end fundraising reports by candidates for the 5th district of Virginia. This race was incredibly close and decided by less than 800 votes.

							# fetch Goode for Congress F3, Year End 2008
							savefile("goode.fec","http://query.nictusa.com/dcdev/posted/392684.fec")

							# fetch Perriello for Congress F3, Year end 2008
							savefile('perriello.fec',"http://query.nictusa.com/dcdev/posted/401630.fec")

							# extract the financial summary of each campaign. we want total receipts this period and cycle to date, total disbursements this period and cycle to date, 
							# and the cash position at the beginning and end of this reporting period

							# note we're using the new FECForm.form_for function that takes any version FECHell parameter hash and returns a full ruby object
							['goode.fec','perriello.fec'].each do |filename|
							  fec_version, original_form_type, form_type, values = h.header_lines(filename)
								next unless form_type == "F3" && fec_version.to_i >= 3

								f = FECForm.schedule_for(form_type, fec_version, values)
								puts "For: #{f.committee_name}"
							  puts "Total receipts this period: #{f.col_A_line_24}"
							  puts "Total receipts cycle-to-date: #{f.col_B_line_16}"
							  puts "Total disbursements this period: #{f.col_A_line_26}"
							  puts "Total disbursements cycle-to-date: #{f.col_B_line_22}"
							  puts "Cash-on-hand beginning of period: #{f.col_A_line_23}"
							  puts "Cash-on-hand end of period:  #{f.col_A_line_27}"
								puts "\n\n"
							end
						

From this output we see that Rep Goode outspent Perriello by more than a million dollars, but Rep Goode lost his seat by 727 votes.

Example 2I would like to see the names and salaries for all staff members of Mike Huckabee's political action commitee HuckPAC for the last quarter of 2008

Opening the FEC_Format worksheet again we can look at schedule descriptions and see that we want Schedule B (Disbursements), attached to F3X (forms filed by PACs). From the same worksheet we can see Scheduble B filings have several columns we're interested: PAYEE FIRST/LAST NAME, EXPENDITURE DATE, EXPENDITURE AMOUNT, and EXPENDITURE PURPOSE DESCRIPTION. Browsing the FEC report by hand we see that the software that HuckPAC uses to file reports flags payments to staff members as 'Payroll' in the "EXPENDITURE PURPOSE DESCRIPTION" column, so we'll only look for lines matching that purpose description.

							require 'rubygems'
							require 'fechell'
							require 'open-uri'

							# convenience function to download a file from a URL and save it locally

							def savefile(filename,url)
							  File.open(filename,"w") do |file|
								file.puts open(url).read
							  end
							end

							h = FECHell.new

							# fetch HuckPac F3X, Post General, 2008
							savefile('huckpac.fec','http://query.nictusa.com/dcdev/posted/407479.fec')
							# extract disbursements (schedule B) made this period by HuckPAC with the expenditure purpose(key="EXPENDITURE PURPOPOSE DESCRIPTION") of "Payroll"
							puts "TO,DATE,AMOUNT"
							h.process("huckpac.fec") do |v|
							  schedule = v[0]
							  values = v[1]

							  next if schedule != 'SB'

							  next if values['EXPENDITURE PURPOSE DESCRIP'] != 'Payroll'

							  puts "#{values['PAYEE FIRST NAME']} #{values['PAYEE LAST NAME']},#{values['EXPENDITURE DATE']},#{values['EXPENDITURE AMOUNT']}"

							end

						

Running this program we can see that HuckPAC keeps 5 full-time people on staff, including his daughter Sarah. A really interesting exercise would be to extract all staff salary payments from every PAC filing. Then you could compare salaries between different organizations, or gauge how much money of the PACs donations go to salary - just the type of information a contributor might want to know before donating.

A detailed example of using fechell to extract individual contributor data from reports filed by Obama for America in 2007 and 2008 and be found at the Offensive Politics blog entry open-source campaign finance analysis with ruby and fechell.