FEC Data

From MCS Wiki
Jump to: navigation, search

Federal Election Commission data from 2007-2008 is available on Medusa. This data, obtained from the FEC, contains records of all committees, candidates, and contributions during the election cycle.

Contents

Tables

committees

The committees table contains one record for each committee registered with the Federal Election Commission. This includes federal political action committees and party committees, campaign committees for presidential, house and senate candidates, as well as groups or organizations who are spending money for or against candidates for federal office.

The table contains basic information about the committees. The ID number the Commission assigned to the committee is first, along with the name of the committee, the sponsor, where appropriate, the treasurer's name and the committee's address. The file also includes information about what type of committee is being described, along with the candidate's ID number if it is a campaign committee

The committees table was created with the following SQL statement:

DROP TABLE IF EXISTS `FEC Data`.`committees`;
CREATE TABLE  `FEC Data`.`committees` (
  `ID` varchar(9) NOT NULL default ,
  `name` varchar(90) default NULL,
  `treasurer` varchar(38) default NULL,
  `addrOne` varchar(34) default NULL,
  `addrTwo` varchar(34) default NULL,
  `city` varchar(18) default NULL,
  `state` char(2) default NULL,
  `zip` varchar(5) default NULL,
  `designation` char(1) default NULL,
  `type` char(1) default NULL,
  `party` char(3) default NULL,
  `filingFreq` char(1) default NULL,
  `interestGroupCat` char(1) default NULL,
  `connectedOrgName` varchar(38) default NULL,
  `candidateID` varchar(9) default NULL,
  PRIMARY KEY  (`ID`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

candidates

The candidate table contains one record for each candidate who has either registered with the Federal Election Commission or appeared on a ballot list prepared by a state elections office.

The table contains basic information about the candidate, including name, party, whether the candidate is an incumbent, challenger, or involved in an open seat, address, state and district in which the candidate is running and the year of the election for which the candidate is registered. (Note that incumbent/challenger status is dynamic in the current election cycle and there may be delays in identifying districts that will involve open seats. The table also includes the ID number assigned to the candidate by the FEC which is used in tracking campaign finance information about the campaign, as well as the ID number of the candidate's principal campaign committee.

The candidates table was created with the following SQL statement.

DROP TABLE IF EXISTS `FEC Data`.`candidates`;
CREATE TABLE  `FEC Data`.`candidates` (
  `ID` varchar(9) NOT NULL default ,
  `Name` varchar(38) default NULL,
  `partyDesigOne` char(3) default NULL,
  `partyDesigThree` char(3) default NULL,
  `incumbChallengOpen` char(1) default NULL,
  `status` char(1) default NULL,
  `addrOne` varchar(34) default NULL,
  `addrTwo` varchar(34) default NULL,
  `city` varchar(18) default NULL,
  `state` char(2) default NULL,
  `zip` varchar(5) default NULL,
  `campaignCommID` varchar(9) default NULL,
  `electionYear` char(2) default NULL,
  `district` char(2) default NULL,
  PRIMARY KEY  (`ID`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

individualContributions

The individual contributions table contains each contribution from an individual to a federal committee if the contribution was at least $200. It includes the ID number of the committee receiving the contribution, the name, city, state, zip code, and place of business of the contributor along with the date and amount of the contribution.

The individualContributions table was created with the following SQL statement.

DROP TABLE IF EXISTS `FEC Data`.`individualContributions`;
CREATE TABLE  `FEC Data`.`individualContributions` (
  `filerID` varchar(9) default NULL,
  `amendment` char(1) default NULL,
  `reportType` char(3) default NULL,
  `primaryGeneral` char(1) default NULL,
  `microfilmLocation` varchar(11) default NULL,
  `transType` char(3) default NULL,
  `name` varchar(34) default NULL,
  `city` varchar(18) default NULL,
  `state` char(2) default NULL,
  `zip` varchar(5) default NULL,
  `occupation` varchar(35) default NULL,
  `month` char(2) default NULL,
  `day` char(2) default NULL,
  `century` char(2) default NULL,
  `year` char(2) default NULL,
  `amount` varchar(7) default NULL,
  `otherID` varchar(9) default NULL,
  `FECRecordNo` varchar(7) default NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

committeeContributions

The committee contributions table contains each contribution or independent expenditure made by a PAC, party committee, candidate committee, or other federal committee to a candidate during the two-year election cycle. It includes the ID number of the contributing committee and the ID number of the recipient.

The committeeContributions table was created with the following SQL statement:

DROP TABLE IF EXISTS `FEC Data`.`committeeContributions`;
CREATE TABLE  `FEC Data`.`committeeContributions` (
  `filerID` varchar(9) default NULL,
  `amendment` char(1) default NULL,
  `reportType` char(3) default NULL,
  `primaryGeneral` char(1) default NULL,
  `microfilmLocation` varchar(11) default NULL,
  `transType` char(3) default NULL,
  `month` char(2) default NULL,
  `day` char(2) default NULL,
  `century` char(2) default NULL,
  `year` char(2) default NULL,
  `amount` varchar(7) default NULL,
  `otherID` varchar(9) default NULL,
  `candidateID` varchar(9) default NULL,
  `FECRecordNo` varchar(7) default NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1;


otherTransactions

The other transactions table contains all transactions (contributions, transfers, etc. among federal committees). It contains all data in the itemized committee contributions file plus PAC contributions to party committees, party transfers from state committee to state committee, and party transfers from national committee to state committee.

The otherTransactions table was created with the following SQL statement:

DROP TABLE IF EXISTS `FEC Data`.`otherTransactions`;
CREATE TABLE  `FEC Data`.`otherTransactions` (
  `filerID` varchar(9) default NULL,
  `amendment` char(1) default NULL,
  `reportType` char(3) default NULL,
  `primaryGeneral` char(1) default NULL,
  `microfilmLocation` varchar(11) default NULL,
  `transType` char(3) default NULL,
  `name` varchar(34) default NULL,
  `city` varchar(18) default NULL,
  `state` char(2) default NULL,
  `zip` varchar(5) default NULL,
  `occupation` varchar(35) default NULL,
  `month` char(2) default NULL,
  `day` char(2) default NULL,
  `century` char(2) default NULL,
  `year` char(2) default NULL,
  `amount` varchar(7) default NULL,
  `otherID` varchar(9) default NULL,
  `FECRecordNo` varchar(7) default NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

COBOL formatting

The amount fields in the contribution and transaction tables are in COBOL format and may contain a special character if the amount is negative.

COBOL Formatting
COBOL Character Negative integer
] -0
j -1
k -2
l -3
m -4
n -5
o -6
p -7
q -8
r -9

To convert one of these values to a standard integer, replace the COBOL Character with the corresponding negative integer.

200p = -2007

External Links

Federal Election Commission Data

Personal tools