OpenSecrets User Guide

User Manual:

Open the PDF directly: View PDF .
Page Count: 62

Download
Open PDF In Browser	View PDF

OpenSecrets OpenData User’s Guide
Last updated: 6/12/2015
INTRODUCTION ............................................................................................................................................................................... 2
CHANGE LOG .................................................................................................................................................................................. 3
GENERAL INFORMATION .............................................................................................................................................................. 5
WHAT IS ATTRIBUTION?................................................................................................................................................................ 6
CAMPAIGN FINANCE DATA........................................................................................................................................................... 8


Tables ................................................................................................................................................................................................................. 8



More Campaign Finance Information ....................................................................................................................................................... 11

LOBBYING DATA ........................................................................................................................................................................... 13
527 DATA ........................................................................................................................................................................................ 14
PERSONAL FINANCES DATA ...................................................................................................................................................... 15
DATA DICTIONARIES FOR OPENSECRETS TABLES .............................................................................................................. 18


Data Dictionary – Campaign Finance tables ............................................................................................................................................. 18



Data Dictionary – Lobby tables ................................................................................................................................................................... 29



Data Dictionary – 527 tables ........................................................................................................................................................................ 33



Data Dictionary – Personal Finances tables .............................................................................................................................................. 37

SCRIPTS TO GENERATE DATA TABLES FOR IMPORT .......................................................................................................... 49

INTRODUCTION
This guide provides information on how to correctly use the relational data tables that CRP creates, standardizes and
codes and that serve as the foundation of its web site, OpenSecrets.org . We are excited to be able to share this
information in a form that allows others to use it and we look forward to new mashups. Using this data requires a
fundamental understanding of relational systems and many of the tables require a database program capable of
handling millions of records. Be forewarned that downloading large files may take considerable time and using the
millions of individual records to calculate what you need can be an arduous undertaking. Please check out our APIs as
these are easy to use and we've already done the heavy lifting for you -- calculating the most common requested
information. Data in the APIs is as current as we have on OpenSecrets.org while the bulk data tables lag many months
behind data used for the APIs. If you are not deterred by these conditions, read on!
Every data table that we have available has a data definition document that explains the fields found in the data file. In
order to download the files and/or documentation, you must have a MyOpenSecrets account. Many of you already do
(if you’ve forgotten your password, you can ask to have it emailed to you on the login screen). We never share your
information, as explained in our Privacy Policy. All MyOpenSecrets users agree to our Terms of Service.
MyOpenSecrets allows you to customize our site so you can see the information most valuable to you and many
improvements are on the drawing board now!
This OpenData User’s Guide is designed to provide information on how to correctly use the OpenData data tables we
offer. Because our name must appear on every web or printed page on which our data appears, it is important to us
that the data be used and interpreted correctly. (Please see the section on “What is Attribution?”). This guide provides
the information necessary for folks using the data to understand the relations, criteria and groupings most suitable to
their needs. We expect to update this document regularly. We would love to hear your comments, questions and
ideas -- please post on the Google Group: OpenSecrets OpenData
NOTE: This data is released under the Creative Commons license Attribute Non-Commercial Share Alike. This data
may NOT be used for any commercial purpose under this license. To request use of data for commercial purposes,
contact us via apis@crp.org. All for-profit organizations must obtain a data license to use the OpenData.

6/14/2015

2 of 62

Change Log
11/30/2009 – PFD data released with the new pipe (ascii 134) delimiter.
1/5/2010 – Change Log added to document
4/19/2010 – Campaign Finance data information (pages 7 – 11) refined
10/12/2010 improved Lobbying data field IncludeNSFS explanation
8/22/11 - modified several PFD table structures:




The AssetValue field in the PFDAsset table changed from 1 character to 2 characters.
The AssetIncomeAmtRange in PFDAsset changed from 1 character to 4.
The Asset4TransAmt field in the PFDTransactions table changed from 1 character to 2.

10/3/11 -- Updated introduction
8/23/12 - Campaign finance tables structure changes (Note: these changes only apply to 2012 and later) Other cycles are in the previous format
FECTransID (Indivs) from 7 to 19 characters
FECRecNo (Pacs and Pac_Other tables) from 7 to 19 characters
Contrib (Indivs) from 34 to 50 characters
Orgname (Indivs), Ultorg (Indivs, Cmtes), DonorCmte (Pac_Other), ContribLendTrans (Pac_Other),
Pacshort (Pacs), Affiliate (Pacs), FirstLastP (Cands) from 40 to 50 characters
City (Indivs, Pac_Other) from 18 to 30 characters
FECOccEmp field removed from Indivs (replaced by separate Occupation and Employer fields)
FECOccEmp field in Pac_Other from 35 to 38 characters
Occ_EF field in Indivs table renamed to Occupation
Employer field in Indivs table renamed to Employer
Amount field in PACs table from integer to float
9/21/12 - Lobbying table structure changes.
Registrant_raw (Lobbying) from 95 to 110 characters
Client_raw (Lobbying) from 95 to 110 characters
Client {Lobbying and Lob Industry) from 40 to 50 characters
Ultorg (Lobbying) from 40 to 50 characters
Sub (Lob Industry) from 40 to 50 characters
Orgid (Lobbying) - unused field removed
2/13/14 - improved recipcode descriptions
2/16/14 -- adding Date and DateText fields to documentation of PFD_Asset table (p.54)

6/14/2015

3 of 62

Change Log continued:
3/7/14 - modified structure of Receipts527 table:
Orgname from 40 to 50 characters
Ultorg from 40 to 50 characters
Recipient from 40 to 50 characters
City from 18 to 50 characters
1/16/15 - modified structure of Expenditures table
Pacshort from 40 to 50 characters
6/5/15 - removed all reference to the old FEC formats
6/12/15 - PFD_Agreements -- orgname, ultorg, orgname2, ultorg2 all from 40 to 50 characters
PFD_Assets -- AssetDividend from 1 to 2 characters and AssetIncomeAmountText from 10 to 50 characters
Expenditures - city from 18 to 30 characters

6/14/2015

4 of 62

GENERAL INFORMATION
The files are provided in zipped text files. The major Open Data tables are provided in a non-standard format that allows dirty data to be
imported as we are provided some raw data fields that can contain formatting and other unprintable characters that choke many data
systems. This format requires a more advanced level of skill to import than a conventional CSV file. In this bulk data, text fields are
surrounded by the pipe character (ascii 124). Date and numeric fields are not. Commas separate all fields. We have provided some
conventional CSV format files for smaller sets of data for those seeking a subset of data in an easy to import format. The OpenSecrets
OpenData Google Group has some wonderful suggestions for solving import problems - check it out!
We expect to update data for the current cycle a couple of times a year. Timing will depend on the interest in the data and our staff
demands. We will be collecting data about the files being downloaded, and this information will be used only to help us evaluate and
improve our OpenData service.
While we are giving away data that we had been selling – and it continues to cost us real money to create and provide this data – we ask
that you consider a donation to the Center to support this work and help ensure the continued availability of the data.
Our OpenData initiative encompasses several different data sets that we gather from different sources. These data sets have different
characteristics, histories, peculiarities and limitations. CRP takes the original data and adds value by providing coding, standardizing
names and applying IDs. We expect to continue to grow this list of data sets as well as improve our documentation. At this time, we have
data available for:
Campaign Finance Data, including Expenditures (from the FEC)
Lobbying Data (from SOPR)
527 Data (from IRS)
Personal Finances Data (from Senate Office of Public Records, Office of the Clerk of the House and the US Office of Government Ethics)
This OpenData User’s Guide covers each data set independently and includes information to explain criteria and restrictions commonly
used when linking them together. Each data section provides information about the data tables, information on criteria and processes for
grouping and summarizing data and even scripts to generate the tables into which the CSV data files can be loaded.
This data is provided under a Creative Commons license, Attribute, Non-commercial, Share Alike. More information can be found in our
Terms of Service. If you would like to use the data for a commercial purpose -- to create a product for sale, include it in a for-profit web
site or any other commercial use, we’d love to talk to you about that project. Contact us at apis@crp.org

6/14/2015

5 of 62

What is Attribution?
Keeping our name attached to this data when it appears in places other than on OpenSecrets.org helps you, the user of it, by sourcing the
information to a trusted nonpartisan organization. And it helps us, the producer of this data, by spreading the name of our organization and
Web site far and wide and illustrating the need to support our research so it can continue to "make change" and power projects like yours.
Our Creative Commons license (legal code) spells out the Terms of Service in legalese. In short, you can remix, tweak and build upon our
work non-commercially, as long as we are credited and your new creation is licensed under the identical terms. This guide shows you ho w
CRP prefers to be credited—and what we consider to be insufficient credit. Should you prefer to use insufficient attribution in your
design, please contact us at apis@crp.org to purchase a data license that allows that.
Preferred Citation
If you're building a site or online feature that essentially runs off of CRP's data, or if your project draws on a large portion of our data set,
we ask you to display CRP's logo (you can get it here in various sizes) and link the logo to OpenSecrets.org. We prefer that you create a
link to the specific type of data that you are displaying, when possible. For example, if you are displaying lobbying data, you can link to
the OpenSecrets Lobbying section at http://www.OpenSecrets.org/lobby/ -- and better yet, you can often link to the precise page of
interest, such as the lobbyist or firm. Here's one way to do that:

Sufficient Citation
If you're using only a small set of CRP data—say for a single chart or graphic—or we're just one of
many information sources you're employing, an easily readable text-only credit line containing the name
of our organization or Web site, and linked to OpenSecrets.org, preferably to the specific page of
interest, is sufficient. (No link is necessary if this use is on paper, of course.) Please place the credit line
as close to the data as possible, as in this example. Should your design prohibit sufficient citation,
please contact us to negotiate a paid license agreement.

Insufficient Citation
As indicated above, at a minimum we expect you to credit CRP using the group's full name and the name of our site. Here are some
examples of insufficient citation that would violate our Creative Commons license:


6/14/2015

"Source: CRP"
6 of 62








"Source: Open Secrets"
"Thanks to CRP for the data."
"I got the data on OpenSecrets.org"
The Center's full name and/or OpenSecrets.org but no link back to us
No sourcing statement—no mention at all of the Center for Responsive Politics or OpenSecrets.org, and no link to
our site
Any citation that implies that the Center for Responsive Politics endorses your project, the way you use the data or
any conclusions you draw from it

If you have questions, or you want to run your method of crediting CRP by someone at our organization, e-mail info@crp.org with the
subject line: "Crediting CRP for data."
One more thing: We always like to see examples of our research in action, so let us know what cool things you do with it -- academic
research, mashups, apps or op-eds. We'd love to spread the word.

6/14/2015

7 of 62

CAMPAIGN FINANCE DATA
 Tables
1. Candidates table (CandsCRP table) – FEC table = foiacn
Common criteria:
Currcand = Y all current candidates
Cyclecand = Y includes all candidates active this cycle, including those who have dropped out or lost primaries or special elections
Distidrunfor like __S_ = Senate seats only
2. Committees data (Cmtes table) – FEC table = foaicm:
3. Individual Contributions (Indivs table) – FEC table = itcont:
Common criteria applied to indivs data:
Realcode not like Z9* (these are noncontributions)
Limit types to 10, 11, 15, 15E, 15J, 22Y
Remember that Type 10 is soft money before 2004 and Levin Funds or outside spending 2004+
RecipID like N* limits to candidates
Source <> P/PAC excludes contributions to PACs other than leadership PACs
Link to committees on cmteid to cmteid and set primcode to Not like z4* to exclude contributions to joint fund raising committees
To limit to individuals: contribid not blank
To exclude generic orgnames: source <> "GEN"
4. PACs to Candidates data (PAC table) – FEC table = itpas2 :
PAC data is easily coded using the Cmtes table. This data is not generally used to calculate geography-based info because the location of
the PAC is more often than not in DC or a few other major areas, like NYC, often unrelated to where the money is actually raised or for
which influence is sought..
In all cases, be sure to set realcode to Not like z9* and Not like z4* to eliminate transfers and joint fund raising committees.
6/14/2015

8 of 62

To calculate direct contributions to candidates, you limit to DI = D. You will almost always want to do this.
To calculate totals to Democrats:
link PACs to Cands on CID where Cands.Party = D and DI = D
To calculate totals to Repubs:
link PACs to Cands on CID where Cands.Party = R and DI = D
To calculate indirect expenditures made for/against cands, you limit to DI = I
Types 24A is an Indendent Expenditure against
Types 24N are Comm Costs against
Type 24C is Coordinate Party Exp for
Type 24E is an Independent Exp for
Type 24F are Comm Costs for
Ex: To calculate indirect expenditures/comm costs made against democrats:
Link PACs to Cands on CID where Cands.Party = D and PACs.type in (24A, 24N) and DI = I
Note that the FEC every now and then puts PAC to candidate data in the PAC to PAC table – to be thorough, check there as well
5. PACs to PACs data (Pac_Other table) – FEC table = itoth:
Common criteria:
Industries: Types 24K, 22Z, 24R, 24Z
Recipprimcode like Z5* for contributions to party committees
Recipprimcode like like Z1* or RecipID like N* to limit contributions to candidates
Recipprimcode like j2* to limit contributions to leadership PACs
Realcode not like z9* and not like z4* to exclude transfers and contributions from joint fund raising committees

6. Expenditures – FEC table electronic filing:
Data is only available for part of the 2000 cycle and in complete/ongoing sets for 2002-2014. At least at this time, there is no electronic
filing for senators or Senate candidates, but the FEC started providing Senate members and candidates expenditure data in 2010 in their
data catalog. We have incorporated this data into the electronically filed expenditure data.
6/14/2015

9 of 62

We have never had sufficient staff to properly work with the expenditure data, so in general it is not up to the standards of most of our
other data. We started coding with a two-digit code and switched to a three character code several years ago. The earlier the cycle, the
less standardization and coding that has been done. During the 2000-2010 period of this data, the FEC changed the reporting formats and
requirements a number of times, which adds to the quality variance between cycles.

6/14/2015

10 of 62

 More Campaign Finance Information
RecipCodes – just two characters, and so much information
Recipcodes are found in many campaign finance tables. They provide a quick way to select specific large groups. Here’s how to “decode”
the recipcode field:
For Candidates =  + 
For Cmtes:
a)
Party Cmtes =  + P
b)
Outside spending Cmtes = O + 
c)
All other Cmtes = P+ 





“D” for Democratic, “R” for Republican, “3” for Independent, Libertarian or third party, “U” for Unknown

“W” for Winner, “L” for Loser, “I” for incumbent, “C” for Challenger, “O” for “Open Seat”, and “N” for Non-incumbent. “N” is
reserved for candidates that are neither in office nor running during the cycle in question.

“B” for Business, “L” for Labor”, “I” for Ideological, “O” for “Other” and “U” for unknown.

Party Cmtes = recipcode like _P
Non-party Cmtes = recipcode like p*
Problems when combining Indivs and PACs data – don’t double count!
You do not want to include the money listed in the Indivs table going to PACs because it will show up again as contributions from the
PAC. So, limit the money counted from Indivs:
Join Indivs to Cmtes on Indivs.CmteID = Cmtes.CmteID with
Indivs.RecipCode not like P*
Note that this will exclude contributions to leadership PACs. If you want to include individual contributions to leadership PACs, do
NOT exclude based on Recipcode. Instead, limit to where Indivs.Party is not null and Indivs.Party<>”” (does not equal blank.)
Additionally, restrictions are required for the PAC to PAC data because if those PACs getting money from other PACs then turn around
and give that money to candidates, that will also result in double counting.

6/14/2015

11 of 62

We do not fill in or standardize orgname for contributions where recipient is a non-leadership PAC (in which case source is generally
P/PAC). Those transactions are presumed to be representative of the recipient, and we want to count the money coming FROM tho se
PACs, not the money going to those PACs (and we have to choose or we'll double count).
Calculate Top Contributors
Data to include: We calculate top contributors for House members using 2-year cycle data and for Senate members/candidates we
calculate for a 6-year or 3 cycle period. So, for a Senate member/candidate who was also a senator and/or was raising money to run for
that Senate seat during either of the two previous cycles, that data is counted, as well as the data for the cycle being calculated. For
example, to calculate the top contributors in the 2008 cycle to Steve Kagen (WI08), use just the 2008 data (Indivs, PACs, Pac_other). But
to calculate the 2008 top contributors to Ted Kennedy (MAS1), I would include his 2004, 2006 and 2008 cycle data. For someon e who
served in the House in 2004 and was elected to the Senate in 2006, calculating his 2008 top contributors, we’d only use his 2006 and 2008
data. Note that we now typically exclude money to presidential campaign committees for congressional candidate and member profiles. To
exclude money to presidential campaign committees, link Indivs.Cmteid to Cmtes.Cmteid and limit Cmtes.Feccandid to “Not like p*”. For
most members and candidates this will not be necessary.
How to pick orgname or ultorg: When calculating top contributors, it’s important to select the display/calculating unit properly: the
orgname or the ultorg. In general, here’s the rule: if the grouping unit (candidate, state, race, etc) has more than one di stinct orgname for
any given ultorg, you list the ultorg with the total of the orgnames. If an ultorg has but a single orgname for a given group, you list the
orgname.
Information about FEC data field values such as transaction types, report codes and the like can be found in .txt files on ftp.fec.gov/FEC/

6/14/2015

12 of 62

LOBBYING DATA
There are 7 Lobbying tables:
Lobbying
Lobbyists
Agencies
Industries
Issues
Bills
Report Types
The Ins and Outs of Calculating Lobbying Totals by Industry
We use the individual expenditures in the lobbying table to calculate the total in the lobbying industries table. In most cases it is a straight
forward scenario where you just take in account the ind=y. It is more complicated for cases where registrants include their non self filer
subsidiaries' activities (IncludeNSFS=y). For those, we examine the catcode of the parent and the catcode of the subsidiary (self = c and
self =b). If they are from different industries then we subtract the total of the subsidiary from the total of the parent and count it toward the
other industry. For example look a General Electric in 2007.
IncludeNSFS signifies whether a filer includes expenditures from its own self filing. A value of "n' means that the parent company does
include the lobbying expenditures of its subsidiaries in its disclosure form and thus, the expenditures reported by subsidiaries should not be
included in the total sum. Conversely, a value of "y" would mean the parent company's disclosure report does not capture the lobbying
expenditures of its subsidiaries and any expenditures by the subsidiaries should be added in addition to the parent companies lobbying
expenditures. The field is also used to indicate if the filer is a subcontractor (making business on behalf of) with the "s" value.

6/14/2015

13 of 62

527 DATA
There are 3 527 tables:
527 Committees
Receipts
Expenditures

6/14/2015

14 of 62

PERSONAL FINANCES DATA
There are 10 Personal Finances tables provided.
Agreements
Assets
Compensation
Gifts
Honoraria
Income
Liabilities
Positions
Transactions
Travel
You will also need the excel spreadsheet of the various ranges for the forms: CRP_PFD_RangeData.xls in order to interpret and calculate
the data in many of the tables.
Personal financial disclosure forms (PFDs) are filed by May 15 each year, covering the prior calendar year, and are released to the public
30 days later. The Center for Responsive Politics obtained reports covering 2004 through 2009 for members of Congress from the Senate
Office of Public Records and the Office of the Clerk of the House. CRP did not collect personal financial data for non-incumbent
candidates for federal office; there are simply too many. For the executive branch, the U.S. Office of Government Ethics provided reports
for the president, vice president, presidential Cabinet and other select officials. Supreme Court filings are provided by the Administrative
Office of the US Courts. After electronically scanning the paper reports into digital images, the information was captured via data entry
and verified. Further description of what can be found in PFD reports is available on the Disclosure Rules page.
Each PFD table includes a “Dupe” field. All records where Dupe = D, should be excluded from calculations because it is replaced or
removed by an amended report or double counts information in the same filing.
Filers are required to include much of the same information about their spouses that they must disclose about their own finances and
activities. Though lower levels of specificity are often allowed, assets, liabilities, income, transactions, gifts, and travel reimbursements of
spouses and dependent children are included in these disclosures and, therefore, are used in our industry and net worth calculations. This
information is found in fields such as “AssetSpouseJointDep” or “Asset4SJD.” Some filers attach account statements or other additional
material as a supplement or replacement for the standard forms. When new or more detailed information was available in these
attachments, it was entered as well. When a figure is present in “AssetExactValue” (Assets) or “Asset4ExactAmt” (Transactions) it should
be used in calculations as both the minimum and maximum value, in place of the ranges represented in “AssetValue” or
“Asset4TransAmt” respectively.
6/14/2015

15 of 62

Once the database was populated, the names of assets, liabilities and transactions, as well as sources of income, gifts, travel
reimbursements, outside positions and agreements, were standardized in the “Orgname” field and, when appropriate, categorized according
to industry in the “RealCode” field. The Assets, Trans, and Agreements tables also have an Orgname2 field that should be used when
present, otherwise use Orgname. When both are filled, Orgname will usually give information about what account the assets are held in.
For Agreements, the two fields disclose the two parties to the agreement. The second party is assumed to be the filer and is thus typically
left empty.
Assets were also coded according to type of investment, allowing them to be identified, where possible, as stocks, bonds, mutual funds,
etc. When an industry code was not appropriate, as with diversified mutual funds and items such as cash accounts, those figures are
excluded from industry profiles. When calculating totals by industry, use AssetTypeCRP to limit to public and private compani es (S,P),
corporate bonds (C), sector specific mutual funds (FI), and real estate (R). The other types, including bonds, accounts, and diversified
mutual funds are not categorized and counted as part of an industry. P is used as a placeholder when a company’s status as public or
private has not yet been determined.
Calculate the value of assets and transactions will require reference to CRP_PFD_RangeData.xls. The code found in “AssetValue”,
“LiabilityAmt”, “Asset4TransAmt” corresponds to the “Code” fields in the spreadsheet’s Ranges tables and will provide a minimum and
maximum value. Remember, when “AssetExactValue” or “Asset4ExactAmt” are not null, that value is more accurate and should be u sed
in place of the range. On her 2007 report, Sen. Elizabeth Dole reported the full value of private ventures in which her husband was part
owner while also indicating what percentage was attributable to him. After consulting with her office, CRP calculated as best as possible
the true value of those interests. The “MinValue” and “MaxValue” columns in the “DoleAssetFactors” and “DoleTransFactors” tabs
figures must be used to replace the standard ranges for her assets whenever working with data that includes 2007 Senate
Net worth was calculated by summing the filer's assets and then subtracting any listed liabilities. Filers report the amount of each of their
assets, transactions and liabilities as falling within one of several ranges. The minimum possible values for each asset are added together as
are the maximum possible values. Likewise, minimum and maximum liability amounts are summed. The maximum debt figure was then
subtracted from the minimum asset figure and the minimum debt figure was subtracted from the maximum asset figure. The resulting
range represents the extremes of how much a filer could be worth and their actual net worth should fall somewhere within that range. The
midpoint or average of the two limits was also calculated and used for purposes of ranking the filers by wealth. Using the av erage for these
rankings avoids much of the distortion caused when a filer is highly leveraged. Due to the various ranges reported on the forms, filer s with
high liability totals as well as high asset totals could find themselves deep in the red and ranked accordingly low if the minimum possible
net worth is used despite the fact that they are widely regarded as one of the wealthiest members of Congress. All three figures are
displayed for reference, but the use of the average paints a picture that much more accurately reflects reality. The top range of "Over $50
million" limits valuation of very large assets. Additionally, Senate forms allow spousal assets to be categorized as “Over $1 million.”
When further disclosure or research definitively reveals a more accurate figure, it is used in place of the range.
Top Assets were determined by adding the minimum and maximum value ranges for a given asset for all filers that held it. For example, if
three filers each reported holding Microsoft stock worth between $1,001 and $5,000, the total holding of Microsoft would be listed as
$3,003 to $15,000.
6/14/2015

16 of 62

Note that the ethics law does not require filers to report property or liabilities, including personal residences and their r elated mortgages,
that are not held as investments and do not produce income. Regardless, some filers did list this information, and when they did, it is
included in our totals and detailed listings. On occasion, filers will provide, as required, detailed listings of the assets that underlie a given
account for which they also report their combined value. In these cases, the summary figure has been omitted from calculations to avoid
double counting.

6/14/2015

17 of 62

DATA DICTIONARIES FOR OPENSECRETS TABLES
 Data Dictionary – Campaign Finance tables
OpenSecrets Data Definition: Candidates – FROM FEC’s foiacn
Field
Cycle
FECCandID
CID

FirstLastP
Party

DistIDRunFor

DistIDCurr

6/14/2015

Definition
Last year (even year) of the federal two year
election cycle
Assigned by FEC and selected by CRP as the
active, should multiples exist.
Unique identifier for each candidate. Every
candidate should have one and only one CID
throughout all cycles. Recipid for candidates is
based on CID.
Candidate name in format of firstname lastname
and party in parens, like Steve Kagen (D)
The party of the candidate. “D” for Democratic,
“R” for Republican”, “I” for Independent, “L” for
Libertarian”, “3” for other third party and “U” for
Unknown.
Four character identifier of the office sought by
the candidate. For congressional races, the first
two characters are the state and the next two are
the district for House candidates and “S1” or “S2”
for Senate candidates. “PRES” indicates a
presidential candidate.
Four character identifier of the office currently
held (if any) by the candidate. For House
members, the first two characters are the state and
the next two are the district. For Senators the first
two characters are the state and the last two
characters are “S1” or “S2”. “PRES” indicates a
presidential candidate. For non-incumbents, this
field is blank. If a member of Congress dies or
leaves office, this field should become blank. This

Type (Length)
Text (4)

Source
FEC

Text (9)

FEC

Text (9)

CRP

Text (50)

CRP

Text (1)

CRP

Text (4)

CRP

Text (4)

CRP

18 of 62

CurrCand

CycleCand

CRPICO

RecipCode

6/14/2015

field is frozen on election day. For cycles prior to
the current cycle, DistidCurr reflects office held
on Election Day of the Cycle.
This field indicates whether the candidate is
currently running for federal office – “Y” means
yes, otherwise this field is blank. If a candidate
loses a primary or drops out of the race, this field
becomes blank. This field is frozen on Election
Day, and thus for previous cycles can be used to
show the candidate who ran in the general
election.
This field indicates whether the candidate ever ran
for federal office during the cycle in question.
Like CurrCand, “Y” means yes and blank means
no. This field should be “Y” for any candidate
who filed to run for office or otherwise formally
declared intention to run. This does NOT change
if the candidate drops out or loses a primary. Be
aware that we’ve tightened the definition in recent
cycles – for older data, CycleCand is likely to cast
a broader net. Also note that incumbents are
usually assumed to be running for re-election and
get a “Y” in CycleCand unless there is evidence to
the contrary.
Identifies type of candidate – “I” is incumbent,
“C” is challenger, “O” is open seat. This may be
blank if the candidate is neither a member of
Congress nor running this cycle. Note this is based
on the office sought. A House incumbent running
for the Senate would have a CRPICO of “C” or
“O”, not “I.”
A two-character code defining the type of
candidate. The first character is party (“D” for
Democratic, “R” for Republican, “3” for
Independent, Libertarian or third party, “U” for
Unknown.) The second character is “W” for
Winner, “L” for Loser, “I” for incumbent, “C” for
Challenger, “O” for “Open Seat”, and “N” for

Text (1)

CRP

Text (1)

CRP

Text (1)

CRP

Text (2)

CRP

19 of 62

NoPacs

6/14/2015

Non-incumbent. Incumbent, Challenger and Open
Seat are based on CRPICO. “N” is reserved for
candidates that are neither in office nor running
during the cycle in question. This lives in
dbo_CandsCRP.
Indicates whether candidate has publicly
committed to forego contributions from PACs

Text (1)

20 of 62

CRP

OpenSecrets Table Definition: FEC Committee table
Field
Cycle
CmteID
PACShort
Affiliate
Ultorg

RecipID
RecipCode

FECCandID
Party
PrimCode
Source
Sensitive

Foreign

Active
6/14/2015

Definition
Last year (even year) of the federal 2-year election cycle
Unique ID given by FEC to each committee.
Standardized committee name based on PAC’s sponsor.
Usually blank. For leadpacs, shows the sponsoring member.
The standardized parent organization for the organization listed
in the PACShort field. If there is no parent identified, this field
will be equal to PACShort.
For candidate committees this will be the candidate’s CID.
Otherwise, it will be the same as CmteID.
A two-character code defining the type of recipient. For
candidates, the first character is party (“D” for Democratic, “R”
for Republican, “3” for Independent, Libertarian or third party,
“U” for Unknown.) The second character is “W” for Winner,
“L” for Loser, “I” for incumbent, “C” for Challenger, “O” for
“Open Seat”, and “N” for Non-incumbent. “N” is reserved for
candidates that are neither in office nor running during the cycle
in question. For party committees, the first character is party and
the second character is “P.” For PACs, the first character is “P”
and for outside spending groups, "O". For both, the second
character is “B” for Business, “L” for Labor”, “I” for
Ideological, “O” for “Other” and “U” for unknown.
Unique ID given to candidates by FEC.
(D,R,3,I,L) Will be null or empty if committee is not a party,
joint fundraising, leadership or candidate committee.
The standard five character code identifying the committee’s
industry or ideology.
Indicates how the PrimCode was determined.
If "Y", the committee has significant business in multiple
industries, some of which fall under the jurisdiction of specific
congressional committees.
Off/False indicate that the company is not owned by a foreign
entity. Those that are owned by a foreign entity are on/True,
sometimes -1
Determines if cmte is active in the cycle – 0 is no and 1 is yes
21 of 62

Type
Text
Text
Text
Text
Text

Length
4
9
50
50
50

Source
FEC
FEC
CRP
CRP
CRP

Text

9

CRP

Text

2

CRP

Text
Text

9
1

FEC
CRP/FEC

Text

5

CRP

Text
Text

5
1

CRP
CRP

Bit

CRP

Integer

CRP

NOTE: This table can be linked to the FEC’s raw data file foiacm on cmteid to obtain additional information about the cmte
OpenSecrets Data Definition: Individual Contribution Data – FEC table itcont
Field
Cycle
FECTransID
ContribID
Contrib
RecipID

Orgname

UltOrg

RealCode

Date
Amount
Street

City
State
Zip
6/14/2015

Definition
Last year (even year) of the federal 2-year election cycle
A unique record identifier within a given cycle.
A unique identifier for individual donors. Family groups
match on first 11 chars
The name of the contributor, usually in the format Last
name, First Name.
The recipient’s id number. If the contribution is to a
candidate this will be the candidate’s unique candidate id
number. Otherwise, it will be the FEC committee id
number.
The standardized organization name for the contributor.
This is usually based on the donor’s employer. The donor
may not have an income producing occupation (e.g.
homemaker)
The standardized parent organization for the organization
listed in the Orgname field. If there is no parent identified,
this field will be blank or null.
The standard five character code identifying the donor’s
industry or ideology. Usually based on Orgname (e.g., the
orgname “Microsoft Corp” would normally get realcode
C5120 for computer software.)
The reported date of the contribution.

Type
Text
Text
Text

Length
4
19
12

Source
FEC
FEC
CRP

Text

50

FEC

Text

9

CRP

Text

50

CRP

Text

50

CRP

Text

5

CRP

MM/DD/
YYYY
The amount contributed. This will be negative for refunds.
Integer
The donor’s street address. Federal law PROHIBITS use of Text
this information for solicitation, fundraising or other
commercial purposes.
The donor’s city. This could be based on a home address or Text
an employer’s address.
The donor’s state. This could be based on a home address
Text
or an employer’s address.
The donor’s zip code. This could be based on a home
Text
address or an employer’s address.
22 of 62

FEC

40

FEC
FEC

30

FEC

2

FEC

5

FEC

Field
RecipCode

Type

CmteID

OtherID
Gender
Microfilm
Occupation
Employer
Source

6/14/2015

Definition
A two-character code defining the type of recipient. For
candidates, the first character is party (“D” for Democratic,
“R” for Republican, “3” for Independent, Libertarian or
third party, “U” for Unknown.) The second character is
“W” for Winner, “L” for Loser, “I” for incumbent, “C” for
Challenger, “O” for “Open Seat”, and “N” for Nonincumbent. “N” is reserved for candidates that are neither in
office nor running during the cycle in question. For party
committees, the first character is party and the second
character is “P.” For PACs, the first character is “P” and for
outside spending groups, "O". For bothm the second
character is “B” for Business, “L” for Labor”, “I” for
Ideological, “O” for “Other” and “U” for unknown.
The transaction type code for the contribution. 15 is a
contribution, 15e is an earmarked contribution (made
through a group such as Club for Growth or Emily’s List),
15j is a contribution through a joint fund raising committee
and 22y is a refund. “10” indicates “soft” or nonfederal
money for the 2002 cycle and earlier. For the 2004 cycle
and later type “10” indicates Levin funds or outside
spending.
The committee id number for the recipient. Note that a
candidate can have more than one committee – this field
indicates the exact committee receiving the contribution.
The committee id number for the intermediary party to
earmarked contributions.
The donor’s gender. Can also be “U” if unknown or “N” if
the name is ambiguous.
Refers to specific page of FEC report images on which this
transaction appears.
The donor’s disclosed occupation from electronic filing.
The donor’s disclosed employer from electronic filing.
Indicates how the RealCode was determined

23 of 62

Type
Text

Length
2

Source
CRP

Text

3

FEC

Text

9

FEC

Text

9

FEC

Text

1

CRP

Text

11

FEC

Text
Text
Text

50
50
5

FEC
FEC

OpenSecrets Data Definition: PAC table (PACs to Candidates) – FROM FEC’s itpas2
Field
Cycle
FECRecNo
PACID
CID
Amount
Date
RealCode

Type

DI

FECCandID

6/14/2015

Definition
Last year (even year) of the federal 2-year election cycle
A unique record identifier within a given cycle.
The committee id number for the PAC making the
contribution.
A unique identifier for candidates that is constant
throughout cycles.
The amount contributed. This will be negative for
refunds.
The reported date of the contribution.
The standard five character code identifying the donor’s
industry or ideology. Usually based on Primcode.
Sometimes a PAC sponsor will have secondary interests
which may replace the main realcode depending on
recipient. For example, Boeing is primarily Air
Transport but has Air Defense interests. Thus Boeing
contributions to members of the Armed Services
committee would have a realcode of Air Defense.
The transaction type code for the contribution. 24A is an
Independent Expenditure against the candidate, 24C is a
coordinated expenditure, 24E is an independent
expenditure for the candidate, 24F is a communication
cost for the candidate, 24K is a direct contribution, 24N
is a communication cost against the candidate and 24Z
is an in kind contribution
Whether the contribution is direct (“D”) or indirect
(“I.”). Indirect contributions include independent
expenditures and communications costs, are not subject
to contribution limits and must be made completely
independently of the candidate. Indirect contributions
can also be against the candidate.
FECCandid of candidate

Type
Text
Text
Text

Length
Source
4
FEC
19
FEC
9
FEC

Text

9

CRP

Float

FEC

Date
Text

5

FEC
CRP

Text

3

FEC

Text

1

CRP

Text

9

FEC

24 of 62

OpenSecrets Data Definition for PAC to PAC Data – FEC table itoth
Field
Cycle
FECRecNo
Filerid

Definition
Last year (even year) of the federal 2-year election cycle
A unique record identifier within a given cycle.
The committee id number for the PAC making the
filing. Refers to donor if Type 2* or recipient if
Type=1*.
DonorCmte
The standardized name for the donor based on the name
of the PAC’s sponsor.
ContribLendTrans Reported name of the donor if Type=1* or the recipient
if Type=2*.
City
The donor’s city. This could be based on a home address
or an employer’s address.
State
The donor’s state. This could be based on a home
address or an employer’s address.
Zip
The donor’s zip code. This could be based on a home
address or an employer’s address.
FECOccEmp
The donor’s disclosed employer and/or occupation.
Primcode
The primary industry/ideological code for the donor
PAC’s sponsor.
Date
The reported date of the contribution.
Amount
The amount contributed. This will be negative for
refunds.
RecipID
The recipient’s id number. If the contribution is to a
candidate this will be the candidate’s unique candidate
id number. Otherwise, it will be the FEC committee id
number.
Party
The party (if any) of the recipient. “D” for Democratic,
“R” for Republican”, “I” for Independent, “L” for
Libertarian”, “3” for other third party and “U” for
Unknown. This field will be blank or null for PACs
other than leadership PACs.
Otherid
Committee id for donor if Type=1* or recipient if
Type=2*.
RecipCode
A two character code defining the type of recipient. For
candidates, the first character is party (“D” for
6/14/2015

25 of 62

Type
Text
Text
Text

Length
4
19
9

Source
FEC
FEC
FEC

Text

50

CRP

Text

50

FEC

Text

30

FEC

Text

2

FEC

Text

5

FEC

Text
Text

38
5

FEC
CRP

Date
Float

FEC
FEC

Text

9

CRP

Text

1

CRP

Text

9

FEC

Text

2

CRP

RecipPrimcode

Amend
Report
PG
Microfilm
Type

RealCode

Source

6/14/2015

Democratic, “R” for Republican, “3” for Independent,
Libertarian or third party, “U” for Unknown.) The
second character is “W” for Winner, “L” for Loser, “I”
for incumbent, “C” for Challenger, “O” for “Open
Seat”, and “N” for Non-incumbent. “N” is reserved for
candidates that are neither in office nor running during
the cycle in question. For party committees, the first
character is party and the second character is “P.” For
PACs, the first character is “P” and for outside spending
groups, "O". For both, the second character is “B” for
Business, “L” for Labor”, “I” for Ideological, “O” for
“Other” and “U” for unknown.
The industry/ideological code for the recipient – codes
beginning with Z1 are candidate committees, codes
beginning with Z5 are party committees and codes
beginning with J2 are leadership PACs.
Whether the record comes from an amended report
The type of report – 1st quarter, year end, etc.
Whether the contribution is for a Primary (“P”) or
General (“G”) election.
The FEC microfilm record for the contribution
The transaction type code for the contribution. 11 is a
tribal contribution, 22Z is a contribution refund to a
candidate or committee, 24G is a Transfer to an
affiliated committee, 24K is a direct contribution, 24R is
a election recount disbursement and 24Z is an in kind
contribution
The standard five character code identifying the donor’s
industry or ideology. Usually based on Primcode.
Sometimes a PAC sponsor will have secondary interests
which may replace the main realcode depending on
recipient. For example, Boeing is primarily Air
Transport but has Air Defense interests. Thus Boeing
contributions to members of the Armed Services
committee would have a realcode of Air Defense.
Indicates how the Realcode was determined.

26 of 62

Text

5

CRP

Text
Text
Text

1
3
1

FEC
FEC
FEC

Text
Text

11
3

FEC
FEC

Text

5

CRP

Text

5

CRP

OpenSecrets Data Dictionary for Expenditure Data – from FEC electronic filings
Field
Cycle
ID

Definition
Last year (even) of a federal 2-yr election cycle
An auto ID added when dbo_Expenditures is
made- acts as a unique identifier. This field
cannot be used to match records from one
download to the next.
TransID
A unique record identifier within a given cycle.
CRPFilerid
ID of the filing committee, same as Filerid
unless it is a candidate committee, in which case
it will be the candidate’s unique id. (Note that a
candidate can have more than one committee –
this field indicates the exact committee receiving
the contribution)
Recipcode
A two character code defining the type of
recipient. For candidates, the first character is
party (“D” for Democratic, “R” for Republican,
“3” for Independent, Libertarian or third party,
“U” for Unknown.) The second character is “W”
for Winner, “L” for Loser, “I” for incumbent,
“C” for Challenger, “O” for “Open Seat”, and
“N” for Non-incumbent. “N” is reserved for
candidates that are neither in office nor running
during the cycle in question. For party
committees, the first character is party and the
second character is “P.” For PACs, the first
character is “P” and for outside spending groups,
"O". For both, the second character is “B” for
Business, “L” for Labor”, “I” for Ideological,
“O” for “Other” and “U” for unknown.
Pacshort
The standardized name of the filing committee
CRPRecipname The recipient of the disbursement. This field is
standardized when possible
Expcode
The expenditure code assigned by the
researcher, based on the old codes.
Amount
The amount spent. This will be negative for
refunds.
6/14/2015

Type/Len
Text (4)
Integer

Source
CRP
CRP

Text (20)
Text (9)

FEC
CRP

Text (2)

CRP

Text (50)
Text (90)

CRP
CRP

Text (3)

CRP

Float

FEC

27 of 62

Date
City
State

The reported date of the expenditure
City of the recipient
State of the recipient

mm/dd/yyyy FEC
Text (30)
FEC
Text (2)
FEC

Zip
CmteID_EF
Candid
Type

Zip of the recipient
Alternate committee ID
candidate id
The transaction type code for the contribution.
15 is a contribution, 15e is an earmarked
contribution (made through a group such as Club
for Growth or Emily’s List), 15j is a contribution
through a joint fund raising committee and 22y
is a refund. “10” indicates “soft” or nonfederal
money for the 2002 cycle and earlier. For the
2004 cycle and later type “10” indicates Levin
funds. Mostly empty
Description of the expenditure
Indicates whether expenditure is for the primary
or general election, plus the year. Can also be
‘S’ for special elections, ‘R’ for runoff or ‘O’ for
other. Not always filled in
Explanation if there is anything other than ‘P’ or
‘G’ in previous field
A filer-provided code for classifying the
recipient. CCM = Candidate Cmte, PTY =
Party Cmte, ORG & IND. Since these are
applied differently across filing committees,
these codes can be helpful in coding but are not
reliable
The source researcher used to assign a certain
code to a disbursement. A list can be found in
the Expenditures methodology

Text (5)
Text (9)
Text (9)
Text (3)

FEC
FEC
FEC
FEC

Text (100)
Text (5)

FEC
FEC

Text (20)

FEC

Text (3)

FEC

Text (5)

CRP

Descrip
PG

ElecOther
EntType

Source

6/14/2015

28 of 62

 Data Dictionary – Lobby tables
OpenSecrets Data Definitions for Lobbying
Field
Uniqid
Registrant_raw
Registrant
Isfirm
Client_raw
Client
Ultorg
Amount
Catcode
Source
Self

6/14/2015

Definition
Corresponds to a particular report from SOPR
Raw registrant
Standardized registrant
Notes whether or not the firm is a lobbying firm.
Raw client
Standardized client
Parent company to the client
Lobbying income/expenses
The standard five character code identifying the donor’s
industry or ideology.
Souce of catcode
Indicate type of filing:
n means a non self filer parent.
m means a non self filer subsidiary for a non self filer
parent.
x means self filer subsidiary for a non self filer parent
p means a self filer parent
i means a non self filer for a self filer parent that has
same catorder as the parent
s means a self filer subsidiary for a self filer parent
e means a non self filer subsidiary for a self file
subsidiary. Don't count this unless the e is bigger than
the s
c means a non self filer subsidiary for a self filer parent
with same catorder. Count it in both total and industry
when IncludeNSFS is null. Don't count it in total or
industry when IncludeNSFS=y
b means a non self filer subsidiary for a self filer parent
that has different catorder. Count it in both total and
industry when IncludeNSFS is null. Exclude from total
and include in indus but mines it from the total of the
parent when IncludeNSFS=y
29 of 62

Type
Varchar
Varchar
Varchar
Char
Varchar
Varchar
Varchar
Float
Char

Length
36
110
50
1
110
50
50
5

Source
SOPR
SOPR
CRP
CRP
SOPR
CRP
CRP
SOPR
CRP

char
Char

5
1

CRP
CRP

IncludeNSFS
Use
Ind
Year
Type
Typelong
Affiliate

6/14/2015

To indicate if the filer included its non self filers
affiliates activities
To indicate if this report should be used or ignored. The
general method is to use the latest report.
To indicate if the amount on this report should be
included to calculate industry totals.
The year.
A CRP short version of reports’ types. Look at the
reference table “ReportTypes” for possible values
The long version of reports’ types. Look at the reference
table “ReportTypes” for possible values
To indicate if the major political contributor is an
affiliate or not

30 of 62

Char

1

CRP

Char

1

CRP

Char

1

CRP

Char
Char

4
4

CRP
CRP

Varchar

80

SOPR

Char

1

CRP

OpenSecrets Data Definition for Lobbyists
Field
UniqID
Lobbyist_raw
Lobbyist
Lobbyist_id
Year
OfficialPosition
CID

Formercongmem

Definition
Corresponds to a particular report from SOPR
Raw value of lobbyist’s name.
Standardized lobbyist.
An ID assigned to each unique lobbyist.
The year.
Any previous government official position the
individual lobbyist had
An ID assigned to members of congress and federal
candidates. It will have a value if the lobbyist is a
former member of congress
Notes whether the lobbyist is a former member.

Type
Varchar
Varchar
Varchar
Varchar
Char
Varchar

Length
36
50
50
12
4
100

Source
SOPR
SOPR
CRP
CRP
SOPR
SOPR

Varchar

15

CRP

CRP

OpenSecrets Data Definitions for Lobbying Data: Lobby Issues
Field
SI_ID
Uniqid
IssueID
Issue
SpecificIssue
Year

Definition
Unique identifier for this table
Corresponds to a particular report from SOPR
A three-letter code corresponding to the general issue
area.
The long version of the three letter general issue.
The specific issue.
The Year.

Type
int
Varchar
Char

Length

Source

36
3

SOPR
CRP

Varchar
Varchar
Char

50
Max
4

SOPR
SOPR
SOPR

Type
Varchar
Varchar
Float
Char
Char

Length
50
50

Source
CRP
CRP
CRP
SOPR
CRP

OpenSecrets Data Definitions for Lobbying Industries
Field
Client
Sub
Total
Year
Catcode

6/14/2015

Definition
Standardized client company.
Standardized subsidiary
Total amount by catcode
The year.
The standard five character code identifying the donor’s
industry or ideology.
31 of 62

4
5

OpenSecrets Data Definitions for Lobbying Agency
Field
Uniqid
AgencyID
Agency

Definition
Corresponds to a particular report from SOPR
An agency unique identifier
The government agency lobbied

Type
Varchar
Char
Varchar

Length
36
3
80

Source
SOPR
CRP
SOPR

Type
int
int

Length

Source
CRP
CRP

OpenSecrets Data Definition for Lobbying Bills
Field
B_ID
SI_ID
CongNo
Bill_Name

Definition
A bill unique identifier
It is the specific issue unique identifier. It is a foreign
key on this table
Congress number
The bill name

char
Char

3
15

CRP
CRP

Type
Text
Text

Length
50
4

Source
SOPR
CRP

OpenSecrets Data Definition for Report Types
Field
Type Long
Type code

6/14/2015

Definition

32 of 62

 Data Dictionary – 527 tables
OpenSecrets Data Definition: 527 Committees
Description
Last year (even year) of the federal 2-year election cycle
Indicates reporting period. First two characters indicate the quarter (e.g., Q3) and last two characters
Rpt
indicate year (e.g., 03)
ID assigned to each 527 committee by IRS
EIN
CRP527Name Standardized committee name based on 527’s sponsor.
Usually blank. For leadpacs, shows the sponsoring member.
Affiliate
The standardized parent organization for the organization listed in the CRP527Name field. If there is
UltOrg
no parent identified, this field will be equal to CRP527Name.
A two-character code defining the type of recipient. For candidates, the first character is party (“D” for
Democratic, “R” for Republican, “3” for Independent, Libertarian or third party, “U” for Unknown.) The
second character is “W” for Winner, “L” for Loser, “I” for incumbent, “C” for Challenger, “O” for “Open
Seat”, and “N” for Non-incumbent. “N” is reserved for candidates that are neither in office nor running
during the cycle in question. For party committees, the first character is party and the second
character is “P.” For PACs, the first character is “P” and for outside spending groups, "O". For both,
the second character is “B” for Business, “L” for Labor”, “I” for Ideological, “O” for “Other” and “U” for
RecipCode
unknown.
FEC id for PAC if the same organization also has a PAC.
CmteID
CID
FEC id for electioneering communications PAC if the same organization also has one.
ECCmteID
(D,R,3,I,L, U) Will be null or empty if committee is not a party, joint fundraising, leadership or
candidate committee.
Party
Field
Cycle

PrimCode
Source
FFreq
Ctype
CSource
ViewPt
Comments
State

6/14/2015

The standard five character code identifying the committee’s industry or ideology.
Indicates how the PrimCode was determined.
‘Q’ if the 527 files quarterly, ‘M’ if it files monthly.
Committee type indicates whether the 527 is focused on state level politics (‘S’) or federal level (‘F’).
‘U’ is unknown.
Source used to determine Ctype.
‘L’ for liberal, ‘C’ for conservative, ‘N’ for none, ‘U’ for unknown
Additional information about the 527, including web addresses, sources of information, and other
notes about the organization.
State where organization is based.

33 of 62

Type
Text

Length
4

Text
Text
Text
Text

4
9
40
40

Text

40

Source
CRP
CRP
IRS
CRP
CRP
CRP
CRP

Text
Text
Text
Text

2
9
9
10

Text
Text
Text
Text

1
5
10
1

Text
Text
Text

10
5
1

Text
Text

250
2

CRP
CRP
CRP
CRP
CRP
CRP
IRS
CRP
CRP
CRP
CRP
IRS

OpenSecrets Data Dictionary 527 Contribution Data – from IRS Form 8872A
Field

Definition

ID

A unique record identifier. This is added by CRP and is NOT
Integer
consistent from download to download.
The quarter and year in which the contribution was reported. The Text (4)
first two characters indicate the quarter, the last two the year.
IRS field
Text (38)
An id field supplied by the IRS.
Text (38)
A unique identifier for individual donors and their immediate
Text (12)
families
The name of the contributor, usually in the format Last name,
Text (50)
First Name.

CRP

Amount

The amount contributed. This will be negative for refunds.

IRS

Date
Orgname

The reported date of the contribution.
mm/dd/yyyy IRS
The standardized organization name for the contributor. This is Text (50) CRP
usually based on the donor’s employer if the donor is an
individual. Otherwise it is the donor organization itself. The
donor may not have an income producing occupation (e.g.
homemaker)
The standardized parent organization for the organization listed Text (50) CRP
in the Orgname field. If there is no parent identified, this field
will be blank or null.
The standard five character code identifying the donor’s industry Text (5)
CRP
or ideology. Usually based on Orgname (e.g., the orgname
“Microsoft Corp” would normally get realcode C5120 for
computer software.)
The recipient’s id number. This is the IRS employer
Text (9)
IRS
identification number.

Rpt
FormID
SchAID
ContribID
Contrib

UltOrg

RealCode

RecipID

6/14/2015

Type/Len

Float

34 of 62

Source

CRP
IRS
IRS
CRP
IRS

RecipCode A two-character code defining the type of recipient. For
candidates, the first character is party (“D” for Democratic, “R”
for Republican, “3” for Independent, Libertarian or third party,
“U” for Unknown.) The second character is “W” for Winner,
“L” for Loser, “I” for incumbent, “C” for Challenger, “O” for
“Open Seat”, and “N” for Non-incumbent. “N” is reserved for
candidates that are neither in office nor running during the cycle
in question. For party committees, the first character is party and
the second character is “P.” For PACs, the first character is “P"
and for outside spending groups, "O". For both, the second
character is “B” for Business, “L” for Labor”, “I” for
Ideological, “O” for “Other” and “U” for unknown.
Party
The party (if any) of the recipient. “D” for Democratic, “R” for
Republican”, “I” for Independent, “L” for Libertarian”, “3” for
other third party and “U” for Unknown. In most cases, 527
committees are not formally affiliated with a political party.
Recipient The standardized name for the recipient.
City
The donor’s city. This could be based on a home address or an
employer’s address.
State
The donor’s state. This could be based on a home address or an
employer’s address.
Zip
The donor’s zip code. This could be based on a home address or
an employer’s address.

Text (2)

CRP

Text (1)

CRP

Text (50)
Text (50)

CRP
IRS

Text (2)

IRS

Text (5)

IRS

Zip4

Donor’s zip4

Text (4)

IRS

PMSA

Metro area code

Text (4)

CRP

Employer

The donor’s disclosed employer.

Text (70)

IRS

Occupation The donor’s disclosed occupation.

Text (70)

IRS

YTD

Supplied by filer

Text (17)

IRS

Gender

M/F/U/N for male, female, unknown or ambiguous name

Text (1)

CRP

Source

The code for the source used for coding Orgname, Ultorg and
Realcode.

Text (5)

CRP

6/14/2015

35 of 62

OpenSecrets Data Dictionary 527 Expenditure Data – from IRS Form 8872B
Description
Field
Rpt
FormID
SchBID
Orgname
EIN
Recipient
RecipientCRP
Amount
Date
ExpCode
Source
Purpose
Addr1
Addr2
City
State
Zip
Employer
Occupation

6/14/2015

Type
text
text
text
text
text
text
text
Integer
Date
text
text
text
text
text
text
text
text
text
text

Source

Length
4
38
38
70
9
50
50

3
5
512
50
50
50
2
5
70
70

The quarter and year in which the contribution was reported. The first two characters indicate
the quarter, the last two the year.
IRS field
An id field supplied by the IRS.
Standardized organization name for the 527 making the distribution.
ID assigned to each 527 committee by IRS
The recipient of the disbursement.
The recipient of the disbursement. This field is standardized when possible
Amount of expenditure
Date of expenditure
Indicates category of expenditure.
Source used to determine ExpCode or ExpCode_Old.
Description of the expenditure
Street address of recipient
Further address information for recipient
City of recipient
State of recipient
Zip of recipient
Employer of recipient
Occupation of recipient

36 of 62

CRP
IRS
IRS
CRP
IRS
IRS
CRP
IRS
IRS
CRP
CRP
IRS
IRS
IRS
IRS
IRS
IRS
IRS
IRS

 Data Dictionary – Personal Finances tables
OpenSecrets Data Definition:
Personal Finances Agreements
Field
ID
Chamber
CID
CalendarYear
ReportType
AgreementDate1
AgreementDate1Text
AgreementDate2
AgreementDate2Text
AgreementParty1
Orgname
Ultorg
Realcode
Source
AgreementParty1Loc
AgreementParty2
Orgname2
Ultorg2
Realcode2
Source2
AgreementTerms
Dupe

6/14/2015

Description
Type
Unique id within a year.
Text
Refers to chamber the report was filed with. "H" for House, "S" for Senate, "E" for executive branch
Text
Unique id for each filer.
Text
Calendar year that is covered by report and that this record applies to.
Text
Y=Annual filing, A=Amendment, T=Termination
text
Date when agreement was made or began.
Date
Date when agreement was made or began.
Text
Date when agreement ended. Agreement was not concluded if null or empty.
Date
Date when agreement ended. Agreement was not concluded if null or empty.
Text
First party to the agreement.
Text
Standardized version of first party to the agreement. Based on AgreementParty1.
Text
Standardized parent organization of first party to the agreement. If none, this field will be null or empty.
Text
Industry code for AgreementParty1
Text
Source used to determine Realcode
Text
Location information for AgreementParty1. City and state for example.
Text
Second party to the agreement. Based on AgreementParty2. Understood to be the filer if null or empty.
Text
Standardized version of second party to the agreement. Based on AgreementParty2.
Text
Standardized parent organization of second party to the agreement. If none, this field will be null or empty. Text
Industry code for AgreementParty2
Text
Source used to determine Realcode2
Text
Description of purpose and terms of the agreeement
Memo
"D" if record is duplicated in same or subsequent reports and should not be calculated or displayed.
Text

37 of 62

Length Source
15 CRP
1 Report
9 CRP
2 Report
1 Report
Report
50 Report
Report
50 Report
100 Report
50 CRP
50 CRP
5 CRP
5 CRP
50 Report
100 Report
50 CRP
50 CRP
5 CRP
5 CRP
Report
1 CRP

OpenSecrets Data Definition:
Personal Finances Assets
Field Name
ID

Chamber
CID
CalendarYear
ReportType
SenAB
AssetSpouseJointDep

AssetSource

Orgname
Ultorg
RealCode
Source
AssetDescrip
Orgname2
Ultorg2
RealCode2
Source2
AssetSourceLocation
AssetValue

6/14/2015

Description
In combination with CalendarYear, the primary key.
Starting with a letter, followed by two digits indicating
the year, and then a 7 digit ID number. With the
addition of the year in the ID, these should be unique
by themselves but as of now cannot be assumed to
be so.
Refers to chamber the report was filed with. "H" for
House, "S" for Senate, "E" for executive branch
Unique CRP identifier for each candidate or
executive branch member
Year covered by report
Indicates if the report is an Annual Filing (Y), an
Amendment (A), or a Termination (T)
Senate forms separate publicly traded assets (A)
and non-publicly traded assets (B)
Indicates whether an asset belongs to the filers
spouse (S), a dependent child (D), or is held jointly
(J). If null we assume it is held by the filer.

Type
Text

Name of asset or account as reported by filer. If
assets are held within an account, the name of the
account appears here. If not, the asset itself will
appear here.
Standardized version of AssetSource
Standardized parent organization of AssetSource. If
none, this field will be null or empty.
Industry code for AssetSource
Source used to determine Realcode
Name of asset as reported by filer. Actual assets,
when they are held in an account.
Standardized version of AssetDescrip
Standardized parent organization of AssetDescrip. If
none, this field will be null or empty.
Industry code for AssetDescrip
Source used to determine Realcode2
Combines City, State info into one field
Value range of asset as reported by filer

Length Source Reference
10 CRP

Text

2

Report

Text

10

CRP

Text
Text

2
1

Report
Report

Text

1

Report

Text

1

Report

Text

100

Report

Text
Text

40
40

CRP
CRP

Text
Text
Text

5
5
100

CRP
CRP
Report

Text
Text

40
40

CRP
CRP

Text
Text
Text
Text

5
5
50
2

CRP
CRP
Report
Report

38 of 62

RangesAssets.Code

AssetExactValue

Exact value of asset when provided.

AssetDividends
AssetRent
AssetInterest
AssetCapitalGains
AssetExemptedFund

Asset produced Dividend income
Asset produced Rental income
Asset produced Interest income
Asset produced Capital Gains income
Income from an exempted fund. (“mutual fund,
common trust fund of a bank, pension or deferred
compensation plan, or any other investment fund,
which is: 1)widely held…2)publicly traded…or widely
diversified; and 3)held under circumstances where
you neither exercise control over nor have the ability
to exercise control over the financial interests held
by the fund.”) Applies to Senate & Exec forms only
AssetExemptedTrust
Income from an exempted trust
AssetQualifiedBlindTrust Income from a qualified blind trust
AssetTypeCRP
Type of investment. Stock, real estate, etc. Used to
determine which to count in industry totals.
OtherTypeIncome
Other than Div, Rent, Interest, CG
AssetIncomeAmtRange Indicates how much income was produced by asset.

AssetIncomeAmountText Description of income amount or raw version of
precise amount in AssetIncomeAmt
AssetIncomeAmt
Precise numbers when available
AssetPurchased
Asset was purchased during reporting period
AssetSold
Asset was sold during reporting period
AssetExchanged
Asset was exchanged during reporting period
Date
Date provided by filer
DateText
Alternate date value
AssetNotes
Comments or further information
Dupe
"D" if record is duplicated in same or subsequent
reports and should not be calculated or displayed.

6/14/2015

Number
(Decimal)
Text
Text
Text
Text
Text

2
1
1
1
1

Report
Report
Report
Report
Report

Text
Text
Text

1
1
2

Report
Report
CRP

Text
Text

100
4

Report
Report

Text

50

Report

Currency
Text
Text
Text
Date
Text
Text
Text

39 of 62

Report

1
1
1
25
100
1

Report
Report
Report
Report
Report
Report
CRP
CRP

AssetTypeCodes.AssetTypeCode

RangesAssetIncome.AssetIncomeAmtRange
(Also see DoleAssetFactors)

OpenSecrets Data Definition:
Personal Finances Compensation
Field
ID
Chamber
CID
CalendarYear
ReportType
CompSource
Orgname
Ultorg
Realcode
Source
CompSourceLocation
CompDuties
dupe

6/14/2015

Description
Type Length Source
unique id within a year
Text
15 CRP
Refers to chamber the report was filed with. "H" for House, "S" for Senate, "E" for executive branch Text
1 Report
Unique id for each filer.
Text
9 CRP
Year covered by report
Text
2 Report
"Y" for annual report, "A" for amendment, "N" for nominee or candidate, "T" for termination
Text
1 Report
Source of compensation as reported by filer
Text 100 Report
Standardized source of compensation
Text
40 CRP
Standardized parent organization of Orgname
Text
40 CRP
Industry code for CompSource
Text
5 CRP
Source used to determine Realcode
Text
5 CRP
City, State of CompSource
Text
50 Report
Description of duties performed in return for compensation
Text 100 Report
"D" if record is duplicated in same or subsequent reports and should not be calculated or displayed. Text
1 CRP

40 of 62

OpenSecrets Data Definition:
Personal Finances Gifts
Field

Description
unique id within a year
Refers to chamber the report was filed with. "H" for House, "S" for Senate, "E"
Chamber
for executive branch
CID
Unique id for each filer.
Year covered by report
CalendarYear
"Y" for annual report, "A" for amendment, "N" for nominee or candidate, "T" for
ReportType
termination
Gift recipient is filer jointly with spouse, spouse alone, or dependent child. Null
GiftSpouseJointDep or empty presumes filer was recipient.
GiftSource
Organization or individual who made the gift as reported by filer
Orgname
Standardized GiftSource
Ultorg
Standardized parent organization of GiftSource
Realcode
Industry code for GiftSource
Source
Source used to determine Realcode
GiftLocation
City, state of GiftSource
GiftDate
Date the gift was given
GiftDateText
Date the gift was given
GiftDescrip
Description of gift
GiftInfo
Additional information about gift
GiftValue
Value of gift
GiftValueText
Value of gift
"D" if record is duplicated in same or subsequent reports and should not be
Dupe
calculated or displayed.
ID

6/14/2015

41 of 62

Type
Text

Length Source
15
CRP

Text
Text
Text

1
9
2

Report
CRP
Report

Text

1

Report

Text
Text
Text
Text
Text
Text
Text
Date
Text
Text
Text
Currency
Text

1
200
40
40
5
5
50

50

Report
Report
CRP
CRP
CRP
CRP
Report
Report
Report
Report
Report
Report
Report

Text

1

CRP

20
200
100

OpenSecrets Data Definition:
Personal Finances Honoraria
Field

Description
unique id within a year
Refers to chamber the report was filed with. "H" for House, "S" for Senate,
Chamber
"E" for executive branch
CID
Unique id for each filer.
Year covered by report
CalendarYear
"Y" for annual report, "A" for amendment, "N" for nominee or candidate, "T"
ReportType
for termination
HonorariaSource
Organization that paid honoraria, as reported by filer
Orgname
Standardized version of HonorariaSource
Ultorg
Standardized parent organization of HonorariaSource
Realcode
Industry code of HonorariaSource
Source
Source used to determine Realcode
HonorariaSourceLoc City, state of Honoraria Source
HonorariaActivity
Description of activities that earned the honoraria
HonorariaDate
Date activity was performed
HonorariaDateText Date activity was performed
HonorariaAmt
Value of honoraria paid
HonorariaAmtText Value of honoraria paid
"D" if record is duplicated in same or subsequent reports and should not be
Dupe
calculated or displayed.
ID

6/14/2015

42 of 62

Type
text

Length Source
15 CRP

text
text
text

1
9
2

Report
CRP
Report

text
text
text
text
text
text
text
text
date
text
currency
text

1
100
40
40
5
5
50
255

25

Report
Report
CRP
CRP
CRP
CRP
Report
Report
Report
Report
Report
Report

text

1

CRP

20

OpenSecrets Data Definition:
Personal Finances Income
Field

Description
unique id within a year
Refers to chamber the report was filed with. "H" for House, "S"
Chamber
for Senate, "E" for executive branch
CID
Unique id for each filer.
Year covered by report
CalendarYear
"Y" for annual report, "A" for amendment, "N" for nominee or
ReportType
candidate, "T" for termination
IncomeSource
Organization that paid income, as reported by filer
Orgname
Standardized version of IncomeSource
Ultorg
Standardized parent organization of IncomeSource
Realcode
Industry code of IncomeSource
Source
Source used to determine Realcode
IncomeLocation
City, state of IncomeSource
Income recipient is filer jointly with spouse, spouse alone, or
IncomeSpouseDep dependent child. Null or empty presumes filer was recipient.
IncomeType
Description of income type. e.g., "salary"
Value of income. Non-filer items can be reported as "Over
IncomeAmt
$1,000"
Value of income. Non-filer items can be reported as "Over
IncomeAmtText
$1,000"
"D" if record is duplicated in same or subsequent reports and
Dupe
should not be calculated or displayed.
ID

6/14/2015

43 of 62

Type
text

Length Source
15
CRP

text
text
text

1
9
2

Report
CRP
Report

text
text
text
text
text
text
text

1
100
40
40
5
5
50

Report
Report
CRP
CRP
CRP
CRP
Report

text
text

1
50

Report
Report

currency

Report

text

50

Report

text

1

CRP

OpenSecrets Data Definition:
Personal Finances Liabilities
Field

Description
unique id within a year
Refers to chamber the report was filed with. "H" for House, "S" for Senate, "E"
Chamber
for executive branch
CID
Unique id for each filer.
Year covered by report
CalendarYear
"Y" for annual report, "A" for amendment, "N" for nominee or candidate, "T" for
ReportType
termination
Person liable is filer jointly with spouse, spouse alone, or dependent child. Null
LiabilitySpouseJointDep or empty presumes filer is liable alone.
Creditor
Organization or individual who made the loan as reported by filer
Orgname
Standardized Creditor
Ultorg
Standardized parent organization of Creditor
Realcode
Industry code for Creditor
Source
Source used to determine Realcode
TypeofLiability
Description of loan type. e.g., "mortgage"
LiabilityLoc
City, state of Creditor
LiabilityDate
Date the liability originated
LiabilityDateText
Date the liability originated
LiabilityTerm
Length of loan term
LiabilityInterestRate
Interest rate paid on loan
LiabilityAmt
Value of liability
"D" if record is duplicated in same or subsequent reports and should not be
Dupe
calculated or displayed.
ID

6/14/2015

44 of 62

Type
text

Length Source
15
CRP

text
text
text

1
9
2

Report
CRP
Report

text

1

Report

text
text
text
text
text
text
text
text
date
text
text
text
text

1
100
40
40
5
5
100
50
25
50
20
2

Report
Report
CRP
CRP
CRP
CRP
Report
Report
Report
Report
Report
Report
Report

text

1

CRP

Reference

RangesLiability.Code

OpenSecrets Data Definition:
Personal Finances Positions

Field
ID
Chamber
CID
CalendarYear
ReportType
PositionHeld
PositionOrg
Orgname
Ultorg
Realcode
Source
PositionOrgLoc
PositionOrgType
PositionFromDate
PositionFromDateText
PositionToDate
PositionToDateText
Dupe

6/14/2015

Description
unique id within a year
Refers to chamber the report was filed with. "H" for House, "S" for Senate,
"E" for executive branch
Unique id for each filer.
Year covered by report
"Y" for annual report, "A" for amendment, "N" for nominee or candidate,
"T" for termination
Position held with organization listed in PositionOrg
Organization with which position is held
Standardized version of PositionOrg
Standardized parent of PositionOrg
Industry code of PositionOrg
Source used to determine Realcode
City, state of PositionOrg
Description of organization
Date filer began holding this position
Date filer began holding this position
Date filer stopped holding this position
Date filer stopped holding this position
"D" if record is duplicated in same or subsequent reports and should not
be calculated or displayed.

45 of 62

Type
text

Length
15

text
text
text

1
9
2

text
text
text
text
text
text
text
text
text
date
text
date
text

1
100
100
40
40
5
5
50
50

text

1

50
50

Source

OpenSecrets Data Definition:
Personal Finances Transactions

Field

Description

Type Length Source
text
15 CRP

ID
Chamber
CID
CalendarYear
ReportType
Asset4SJD
Asset4Transacted

Orgname
Ultorg
RealCode
Source
Asset4Descrip

Orgname2
Ultorg2
RealCode2
Source2
Asset4Purchased
Asset4Sold
Asset4Exchanged
Asset4Date
Asset4DateText

Asset4TransAmt
Asset4ExactAmt
CofD

6/14/2015

Refers to chamber the report was filed with. "H" for
House, "S" for Senate, "E" for executive branch
Unique code for filer
Indicates Year coverd by report
Y=Annual filing, A=Amendment, T=Termination
S=Spouse, J=Joint, D=Dependent Child, Null=Filer
Raw asset name. If the asset is held within an account,
the raw account name.
Standardized version of Asset4Transacted. (More
reliable than Asset4Transacted. If errors are found in
Asset4Descrip, they are corrected here but not in the
raw field.)
Standardized parent organization of Orgname

If the asset is held within an account, the raw asset
name.
Standardized version of Asset4Descrip. (More reliable
than Asset4Descrip. If errors are found in
Asset4Descrip, they are corrected here but not in the
raw field.)
Standardized parent organization of Orgname2

Asset was purchased
Asset was sold
Asset was exchanged
Date of Transaction
Date of Transaction

Code indicating amount of transaction.
Exact amount of transaction, if given.
Certificate of Divestiture

text
text
text
text
Text

1
9
2
1
1

Report
CRP
Report
Report
Report

Text

100

Report

Text
Text
Text
Text

40
40
5
5

CRP
CRP
CRP
CRP

Text

100

Report

Text
Text
Text
Text
Text
Text
Text
Date
Text

40
40
5
5
1
1
1

CRP
CRP
CRP
CRP
Report
Report
Report
Report
Report

Text
Number
Text

46 of 62

50

4
1

Report
Report
Report

Reference

If Chamber='H', then
RangesTransHouseOnly.Code.
Otherwise, RangesAssets.Code (Also
see DoleTransFactors

TransNotes
Dupe

6/14/2015

Notes including other types than sold, purchased,
exchanged.
"D" if record is duplicated in same or subsequent
reports and should not be calculated or displayed.

47 of 62

Text

100

CRP

Text

1

CRP

OpenSecrets Data Definition:
Personal Finances Travel

Field

Description
unique
id
within
a
year
ID
Refers to chamber the report was filed with. "H" for House, "S" for Senate, "E"
Chamber
for executive branch
CID
Unique id for each filer.
Year covered by report
CalendarYear
"Y" for annual report, "A" for amendment, "N" for nominee or candidate, "T" for
ReportType
termination
TravelSource
Travel sponsor as reported by filer
Orgname
standardized version of TravelSource
Ultorg
Standardized parent organization of TravelSource
Realcode
Industry code for TravelSource
Source
Source used to determine Realcode
SourceCity
City where TravelSource is located
SourceState
State where TravelSource is located
BeginDate
Date that trip began
BeginDateText
Date that trip began
EndDate
Date that trip ended
EndDateText
Date that trip ended
DepartCity
City from which filer traveled to start trip
DepartState
State from which filer traveled to start trip
DestCity
Destination city where trip took place
DestState
Destination state where trip took place
PofRCity
City that filer returned to at end of trip (point of return)
PofRState
City that filer returned to at end of trip (point of return)
Descrip
Description of trip and purpose
Lodging
Was lodging provided by TravelSource during the trip
Food
Was food provided by TravelSource during the trip
FamilyIncl
Was travel for family members also provided by TravelSource
TimeAtOwnExpense Description of portions of trip that were paid for at filer's expense
"D" if record is duplicated in same or subsequent reports and should not be
Dupe
calculated or displayed.

6/14/2015

48 of 62

Type
text

Length Source
15 CRP

text
text
text

1
9
2

Report
CRP
Report

text
text
text
text
text
text
text
text
date
text
date
text
text
text
text
text
text
text
text
text
text
text
text

1
100
40
40
5
5
50
2

Report
Report
CRP
CRP
CRP
CRP
Report
Report
Report
Report
Report
Report
Report
Report
Report
Report
Report
Report
Report
Report
Report
Report
Report

text

1

25
25
50
2
50
2
50
2
255
1
1
1
25

CRP

Scripts to Generate Data Tables for Import
CAMPAIGN FINANCE DATA TABLE SCRIPTS
CREATE TABLE CandsCRP14(
[Cycle] [char](4) NOT NULL,
[FECCandID] [char](9) NOT NULL,
[CID] [char](9) NULL,
[FirstLastP] [varchar](50) NULL,
[Party] [char](1) NULL,
[DistIDRunFor] [char](4) NULL,
[DistIDCurr] [char](4) NULL,
[CurrCand] [char](1) NULL,
[CycleCand] [char](1) NULL,
[CRPICO] [char](1) NULL,
[RecipCode] [char](2) NULL,
[NoPacs] [char](1) NULL
) ON [PRIMARY]
CREATE TABLE Cmtes14 (
[Cycle] [char](4) NOT NULL,
[CmteID] [char](9) NOT NULL,
[PACShort] [varchar](50) NULL,
[Affiliate] [varchar](50) NULL,
[UltOrg] [varchar](50) NULL,
[RecipID] [char](9) NULL,
[RecipCode] [char](2) NULL,
[FECCandID] [char](9) NULL,
[Party] [char](1) NULL,
[PrimCode] [char](5) NULL,
[Source] [char](10) NULL,
[Sensitive] [char](1) NULL,
[Foreign] [bit] NOT NULL,
[Active] [int] NULL
) ON [PRIMARY]
CREATE TABLE PACs14 (
[Cycle] [char](4) NOT NULL,
[FECRecNo] [char](19) NOT NULL,
[PACID] [char](9) NOT NULL,
[CID] [char](9) NULL,

6/14/2015

49 of 62

[Amount] [int] DEFAULT (0),
[Date] [smalldatetime] NULL,
[RealCode] [char](5) NULL,
[Type] [char](3) NULL,
[DI] [char](1) NOT NULL,
[FECCandID] [char](9) NULL
) ON [PRIMARY]
CREATE TABLE Pac_Other14 (
[Cycle] [char](4) NOT NULL,
[FECRecNo] [char](19) NOT NULL,
[FilerID] [char](9) NOT NULL,
[DonorCmte] [varchar](50) NULL,
[ContribLendTrans] [varchar](50)
[City] [varchar](30) NULL,
[State] [char](2) NULL,
[Zip] [char](5) NULL,
[FECOccEmp] [varchar](38) NULL,
[PrimCode] [char](5) NULL,
[Date] [smalldatetime] NULL,
[Amount] [float] NULL,
[RecipID] [char](9) NULL,
[Party] [char](1) NULL,
[OtherID] [char](9) NULL,
[RecipCode] [char](2) NULL,
[RecipPrimcode] [char](5) NULL,
[Amend] [char](1) NULL,
[Report] [char](3) NULL,
[PG] [char](1) NULL,
[Microfilm] [char](11) NULL,
[Type] [char](3) NULL,
[Realcode] [char](5) NULL,
[Source] [char](5) NULL
) ON [PRIMARY]

6/14/2015

NULL,

50 of 62

CREATE TABLE [dbo].[Indivs14](
[Cycle] [char](4) NOT NULL,
[FECTransID] [char](19) NOT NULL,
[ContribID] [char](12) NULL,
[Contrib] [varchar](50) NULL,
[RecipID] [char](9) NULL,
[Orgname] [varchar](50) NULL,
[UltOrg] [varchar](50) NULL,
[RealCode] [char](5) NULL,
[Date] [datetime] NULL,
[Amount] [int] NULL,
[City] [varchar] (30) NULL,
[State] [char] (2) NULL,
[Zip] [char] (5) NULL,
[Recipcode] [char] (2) NULL,
[Type] [char](3) NULL,
[CmteID] [char](9) NULL,
[OtherID] [char](9) NULL,
[Gender] [char](1) NULL,
[Microfilm] [varchar](11) NULL,
[Occupation] [varchar](38) NULL,
[Employer] [varchar](38) NULL,
[Source] [char](5) NULL
) ON [PRIMARY]

6/14/2015

51 of 62

CREATE TABLE [dbo].[Expend14](
[Cycle] [char](4) NOT NULL,
[TransID] [char](20) ,
[CRPFilerid] [char](9) ,
[recipcode] [char](2) ,
[pacshort] [varchar](50) ,
[CRPRecipName] [varchar](90) ,
[ExpCode] [char](3) ,
[Amount] [decimal](12, 0) NOT NULL,
[Date] [smalldatetime] NULL,
[City] [varchar](30) ,
[State] [char](2) ,
[Zip] [char](9) ,
[CmteID_EF] [char](9) ,
[CandID] [char](9) ,
[Type] [char](3) ,
[Descrip] [varchar](100) ,
[PG] [char](5) ,
[ElecOther] [varchar](20) ,
[EntType] [char](3) ,
[Source] [char](5) )
) ON [PRIMARY]

6/14/2015

52 of 62

SCRIPTS TO GENERATE LOBBYING TABLES:
CREATE TABLE [dbo].[lobbying](
[uniqid] [varchar](36) NOT NULL,
[registrant_raw] [varchar](110) NULL,
[registrant] [varchar](50) NULL,
[isfirm] [char](1) NULL,
[client_raw] [varchar](110) NULL,
[client] [varchar](50) NULL,
[ultorg] [varchar](50) NULL,
[amount] [float] NULL,
[catcode] [char](5) NULL,
[source] [char] (5) NULL,
[self] [char](1) NULL,
[IncludeNSFS] [char](1) NULL,
[use] [char](1) NULL,
[ind] [char](1) NULL,
[year] [char](4) NULL,
[type] [char](4) NULL,
[typelong] [varchar](50) NULL,
[affiliate] [char](1) NULL,
) ON [PRIMARY]

CREATE TABLE [dbo].[Lobbyists](
[uniqID] [varchar](36) NOT NULL,
[lobbyist] [varchar](50) NULL,
[lobbyist_raw] [varchar](50) NULL,
[lobbyist_id] [char](12) NULL,
[year] [char](4) NULL,
[Official Position] [varchar](100) NULL,
[cid] [char] (9) NULL,
[formercongmem] [char](1) NULL
) ON [PRIMARY]

6/14/2015

53 of 62

CREATE TABLE [dbo].[LobbyIndus](
[client] [varchar](50) NULL,
[sub] [varchar](50) NULL,
[total] [float] NULL,
[year] [char](4) NULL,
[catcode] [char](5) NULL
) ON [PRIMARY]
CREATE TABLE [dbo].[lobbyagency](
[uniqID] [varchar](36) NOT NULL,
[agencyID] [char](3) NOT NULL,
[Agency] [varchar](80) NULL
) ON [PRIMARY]
CREATE TABLE [dbo].[lobbyissue](
[SI_ID] [int] NOT NULL,
[uniqID] [varchar](36) NOT NULL,
[issueID] [char](3) NOT NULL,
[issue] [varchar](50) NULL,
[SpecificIssue] [varchar](max) NULL,
[year] [char] (4) NULL
) ON [PRIMARY]
CREATE TABLE [dbo].[lob_bills](
[B_ID] [int] NULL,
[si_id] [int] NULL,
[CongNo] [char](3) NULL,
[Bill_Name] [varchar](15) NOT NULL
) ON [PRIMARY]

CREATE TABLE [dbo].[lob_rpt](
[TypeLong] [varchar] (50) NOT NULL,
[Typecode] [char](4) NOT NULL
) ON [PRIMARY]

6/14/2015

54 of 62

SCRIPTS TO CREATE PERSONAL FINANCES TABLES
CREATE TABLE [dbo].[Agreement](
[ID] [varchar](15) NOT NULL,
[Chamber] [char](1) NULL,
[CID] [char](9) NULL,
[CalendarYear] [char](2) NOT NULL,
[ReportType] [char](1) NULL,
[AgreementDate1] [smalldatetime] NULL,
[AgreementDate1Text] [char](50) NULL,
[AgreementDate2] [smalldatetime] NULL,
[AgreementDate2Text] [char](50) NULL,
[AgreementParty1] [varchar](100) NULL,
[Orgname] [varchar](40) NULL,
[Ultorg] [varchar](40) NULL,
[Realcode] [char](5) NULL,
[Source] [char](5) NULL,
[AgreementParty1Loc] [varchar](50) NULL,
[AgreementParty2] [varchar](100) NULL,
[Orgname2] [varchar](40) NULL,
[Ultorg2] [varchar](40) NULL,
[Realcode2] [char](5) NULL,
[Source2] [char](5) NULL,
[AgreementTerms] [varchar](1500) NULL,
[Dupe] [char](1) NULL
) ON [PRIMARY]
CREATE TABLE [dbo].[PFD_Asset](
[ID] [varchar](15) NOT NULL,
[Chamber] [char](1) NULL,
[CID] [char](9) NULL,
[CalendarYear] [char](2) NOT NULL,
[ReportType] [char](1) NULL,
[SenAB] [char](1) NULL,
[AssetSpouseJointDep] [char](1) NULL,
[AssetSource] [varchar](100) NULL,
[Orgname] [varchar](40) NULL,
[Ultorg] [varchar](40) NULL,
[Realcode] [char](5) NULL,
[Source] [char](5) NULL,
[AssetDescrip] [varchar](100) NULL,
[Orgname2] [varchar](40) NULL,
[Ultorg2] [varchar](40) NULL,
[Realcode2] [char](5) NULL,

6/14/2015

55 of 62

[Source2] [char](5) NULL,
[AssetSourceLocation] [varchar](50) NULL,
[AssetValue] [char](2) NULL,
[AssetExactValue] [decimal](18, 0) NULL,
[AssetDividends] [char](1) NULL,
[AssetRent] [char](1) NULL,
[AssetInterest] [char](1) NULL,
[AssetCapitalGains] [char](1) NULL,
[AssetExemptedFund] [char](1) NULL,
[AssetExemptedTrust] [char](1) NULL,
[AssetQualifiedBlindTrust] [char](1) NULL,
[AssetTypeCRP] [char](2) NULL,
[OtherTypeIncome] [varchar](100) NULL,
[AssetIncomeAmtRange] [varchar](4) NULL,
[AssetIncomeAmountText] [varchar](10) NULL,
[AssetIncomeAmt] [money] NULL,
[AssetPurchased] [char](1) NULL,
[AssetSold] [char](1) NULL,
[AssetExchanged] [char](1) NULL,
[AssetDate] [smalldatetime] NULL,
[AssetDateText] [varchar](25) NULL,
[AssetNotes] [varchar](100) NULL,
[Dupe] [char](1) NULL
) ON [PRIMARY]
CREATE TABLE [dbo].[Compensation](
[ID] [varchar](15) NOT NULL,
[Chamber] [char](1) NULL,
[CID] [char](9) NULL,
[CalendarYear] [char](2) NULL,
[ReportType] [char](1) NULL,
[CompSource] [varchar](100) NULL,
[Orgname] [varchar](40) NULL,
[Ultorg] [varchar](40) NULL,
[Realcode] [char](5) NULL,
[Source] [char](5) NULL,
[CompSourceLocation] [varchar](50) NULL,
[CompDuties] [varchar](100) NULL,
[dupe] [char](1) NULL
) ON [PRIMARY]
CREATE TABLE [dbo].[Gift](
[ID] [varchar](15) NOT NULL,
[Chamber] [char](1) NULL,
[CID] [char](9) NULL,
[CalendarYear] [char](2) NULL,

6/14/2015

56 of 62

[ReportType] [char](1) NULL,
[GiftSpouseJointDep] [char](1) NULL,
[GiftSource] [varchar](200) NULL,
[Orgname] [varchar](40) NULL,
[Ultorg] [varchar](40) NULL,
[Realcode] [char](5) NULL,
[Source] [char](5) NULL,
[GiftLocation] [varchar](50) NULL,
[GiftDate] [smalldatetime] NULL,
[GiftDateText] [varchar](20) NULL,
[GiftDescrip] [varchar](200) NULL,
[GiftInfo] [varchar](100) NULL,
[GiftValue] [money] NULL,
[GiftValueText] [varchar](50) NULL,
[Dupe] [char](1) NULL
) ON [PRIMARY]
CREATE TABLE [dbo].[Honoraria](
[ID] [varchar](15) NOT NULL,
[Chamber] [char](1) NULL,
[CID] [char](9) NULL,
[CalendarYear] [char](2) NULL,
[ReportType] [char](1) NULL,
[HonorariaSource] [varchar](100) NULL,
[Orgname] [varchar](40) NULL,
[Ultorg] [varchar](40) NULL,
[Realcode] [char](5) NULL,
[Source] [char](5) NULL,
[HonorariaSourceLoc] [varchar](50) NULL,
[HonorariaActivity] [varchar](255) NULL,
[HonorariaDate] [smalldatetime] NULL,
[HonorariaDateText] [varchar](20) NULL,
[HonorariaAmt] [money] NULL,
[HonorariaAmtText] [varchar](25) NULL,
[Dupe] [char](1) NULL
) ON [PRIMARY]
CREATE TABLE [dbo].[Income](
[ID] [varchar](15) NOT NULL,
[Chamber] [char](1) NULL,
[CID] [char](9) NULL,
[CalendarYear] [char](2) NULL,
[ReportType] [char](1) NULL,
[IncomeSource] [nvarchar](100) NULL,
[Orgname] [varchar](40) NULL,
[Ultorg] [varchar](40) NULL,

6/14/2015

57 of 62

[Realcode] [char](5) NULL,
[Source] [char](5) NULL,
[IncomeLocation] [varchar](50) NULL,
[IncomeSpouseDep] [char](1) NULL,
[IncomeType] [varchar](50) NULL,
[IncomeAmt] [money] NULL,
[IncomeAmtText] [varchar](50) NULL,
[Dupe] [char](1) NULL
) ON [PRIMARY]
CREATE TABLE [dbo].[Liability](
[ID] [varchar](15) NOT NULL,
[Chamber] [char](1) NULL,
[CID] [char](9) NULL,
[CalendarYear] [char](2) NULL,
[ReportType] [char](1) NULL,
[LiabilitySpouseJointDep] [char](1) NULL,
[Creditor] [varchar](100) NULL,
[Orgname] [varchar](40) NULL,
[Ultorg] [varchar](40) NULL,
[Realcode] [char](5) NULL,
[Source] [char](5) NULL,
[TypeofLiability] [varchar](100) NULL,
[LiabilityLoc] [varchar](50) NULL,
[LiabilityDate] [smalldatetime] NULL,
[LiabilityDateText] [varchar](25) NULL,
[LiabilityTerm] [varchar](50) NULL,
[LiabilityInterestRate] [varchar](20) NULL,
[LiabilityAmt] [char](2) NULL,
[Dupe] [char](1) NULL
) ON [PRIMARY]
CREATE TABLE [dbo].[Position](
[ID] [varchar](15) NOT NULL,
[Chamber] [char](1) NULL,
[CID] [char](9) NULL,
[CalendarYear] [char](2) NULL,
[ReportType] [char](1) NULL,
[PreviousPositions] [varchar](255) NULL,
[PositionHeld] [varchar](100) NULL,
[PositionOrg] [varchar](100) NULL,
[Orgname] [varchar](40) NULL,
[Ultorg] [varchar](40) NULL,
[Realcode] [char](5) NULL,
[Source] [char](5) NULL,
[PositionOrgLoc] [varchar](50) NULL,

6/14/2015

58 of 62

[PositionOrgType] [varchar](50) NULL,
[PositionFromDate] [smalldatetime] NULL,
[PositionFromDateText] [varchar](50) NULL,
[PositionToDate] [smalldatetime] NULL,
[PositionToDateText] [varchar](50) NULL,
[Dupe] [char](1) NULL
) ON [PRIMARY]
CREATE TABLE [dbo].[Transactions](
[ID] [varchar](15) NOT NULL,
[Chamber] [char](1) NULL,
[CID] [char](9) NULL,
[CalendarYear] [char](2) NOT NULL,
[ReportType] [char](1) NULL,
[Asset4SJD] [char](1) NULL,
[Asset4Transacted] [varchar](100) NULL,
[Orgname] [varchar](40) NULL,
[Ultorg] [varchar](40) NULL,
[Realcode] [char](5) NULL,
[Source] [char](5) NULL,
[Asset4Descrip] [varchar](100) NULL,
[Orgname2] [varchar](40) NULL,
[Ultorg2] [varchar](40) NULL,
[Realcode2] [char](5) NULL,
[Source2] [char](5) NULL,
[Asset4Purchased] [char](1) NULL,
[Asset4Sold] [char](1) NULL,
[Asset4Exchanged] [char](1) NULL,
[Asset4Date] [smalldatetime] NULL,
[Asset4DateText] [varchar](50) NULL,
[Asset4TransAmt] [char](2) NULL,
[Asset4ExactAmt] [decimal](18, 0) NULL,
[CofD] [char](1) NULL,
[TransNotes] [varchar](100) NULL,
[Dupe] [char](1) NULL
) ON [PRIMARY]

CREATE TABLE [dbo].[Travel](
[ID] [varchar](15) NOT NULL,
[Chamber] [char](1) NULL,
[CID] [char](9) NULL,
[CalendarYear] [char](2) NULL,
[ReportType] [char](1) NULL,
[TravelSource] [varchar](100) NULL,
[Orgname] [varchar](40) NULL,

6/14/2015

59 of 62

[Ultorg] [varchar](40) NULL,
[Realcode] [char](5) NULL,
[Source] [char](5) NULL,
[SourceCity] [varchar](50) NULL,
[SourceState] [varchar](2) NULL,
[BeginDate] [smalldatetime] NULL,
[BeginDateText] [varchar](25) NULL,
[EndDate] [smalldatetime] NULL,
[EndDateText] [varchar](25) NULL,
[DepartCity] [varchar](50) NULL,
[DepartState] [char](2) NULL,
[DestCity] [varchar](50) NULL,
[DestState] [char](2) NULL,
[PofRCity] [varchar](50) NULL,
[PofRState] [char](2) NULL,
[Descrip] [varchar](255) NULL,
[Lodging] [char](1) NULL,
[Food] [char](1) NULL,
[FamilyIncl] [char](1) NULL,
[TimeAtOwnExpense] [varchar](25) NULL,
[Dupe] [char](1) NULL
) ON [PRIMARY]

6/14/2015

60 of 62

SCRIPTS TO GENERATE 527 TABLES:
CREATE TABLE [dbo].[Cmtes527](
[Cycle] [char](4) NULL,
[Rpt] [char](4) NULL,
[EIN] [char](9) NOT NULL,
[CRP527Name] [varchar](40) NULL,
[Affiliate] [varchar](40) NULL,
[UltOrg] [varchar](40) NULL,
[RecipCode] [char](2) NULL,
[CmteID] [char](9) NULL,
[CID] [char](9) NULL,
[ECCmteID] [char](10) NULL,
[Party] [char](1) NULL,
[PrimCode] [char](5) NULL,
[Source] [char](10) NULL,
[FFreq] [char](1) NULL,
[Ctype] [char](10) NULL,
[CSource] [char](5) NULL,
[ViewPt] [char](1) NULL,
[Comments] [char](250) NULL,
[State] [char](2) NULL
) ON [PRIMARY]
CREATE TABLE [dbo].[receipts527](
[ID] [int] NOT NULL,
[Rpt] [char](4) NULL,
[FormID] [varchar](38) NULL,
[SchAID] [varchar](38) NULL,
[ContribID] [char](12) NULL,
[Contrib] [varchar](50) NULL,
[Amount] [int] NULL,
[Date] [smalldatetime] NULL,
[Orgname] [varchar](50) NULL,
[Ultorg] [varchar](50) NULL,
[Realcode] [char](5) NULL,
[RecipID] [char](9) NULL,
[RecipCode] [char](2) NULL,
[Party] [char](1) NULL,
[Recipient] [varchar](50) NULL,
[City] [varchar](50) NULL,
[State] [char](2) NULL,
[Zip] [char](5) NULL,
[Zip4] [char](4) NULL,
[PMSA] [char](4) NULL,
[Employer] [varchar](70) NULL,

6/14/2015

61 of 62

[Occupation] [varchar](70) NULL,
[YTD] [varchar](17) NULL,
[Gender] [char](1) NULL,
[Source] [char](5) NULL
) ON [PRIMARY]

CREATE TABLE [dbo].[Expenditures527](
[Rpt] [char](4) NULL,
[FormID] [varchar](38) NULL,
[SchBID] [varchar](38) NULL,
[Orgname] [varchar](70) NULL,
[EIN] [char](9) NULL,
[Recipient] [varchar](50) NULL,
[RecipientCRP] [varchar](50) NULL,
[Amount] [int] NULL,
[Date] [smalldatetime] NULL,
[ExpCode] [char](3) NULL,
[Source] [char](5) NULL,
[Purpose] [varchar](512) NULL,
[Addr1] [varchar](50) NULL,
[Addr2] [varchar](50) NULL,
[City] [varchar](50) NULL,
[State] [char](2) NULL,
[Zip] [char](5) NULL,
[Employer] [varchar](70) NULL,
[Occupation] [varchar](70) NULL
) ON [PRIMARY]

6/14/2015

62 of 62



---

Source Exif Data:

File Type                       : PDF
File Type Extension             : pdf
MIME Type                       : application/pdf
PDF Version                     : 1.5
Linearized                      : No
Page Count                      : 62
Language                        : en-US
Tagged PDF                      : Yes
Title                           : OpenSecrets
Author                          : salger
Creator                         : Microsoft® Office Word 2007
Create Date                     : 2015:06:14 14:35:15-04:00
Modify Date                     : 2015:06:14 14:35:15-04:00
Producer                        : Microsoft® Office Word 2007

EXIF Metadata provided by EXIF.tools