OpenSecrets User Guide

User Manual:

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

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

6/14/2015 of 62

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,

6/14/2015 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 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 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 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 how

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:

 "Source: CRP"

6/14/2015 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 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 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 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 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 = <Party> + <Status>

For Cmtes:

a) Party Cmtes = <Party> + P

b) Outside spending Cmtes = O + <CmteBLIO>

c) All other Cmtes = P+ <CmteBLIO>

 <Party>

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

 <Status>

“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.

 <CmteBLIO>

“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 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 those

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 someone 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 distinct 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 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 of 62

527 DATA

There are 3 527 tables:

527 Committees

Receipts

Expenditures

6/14/2015 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 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 companies (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 used

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 average for these

rankings avoids much of the distortion caused when a filer is highly leveraged. Due to the various ranges reported on the forms, filers 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 of 62

Note that the ethics law does not require filers to report property or liabilities, including personal residences and their related 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 of 62

DATA DICTIONARIES FOR OPENSECRETS TABLES

 Data Dictionary – Campaign Finance tables

OpenSecrets Data Definition: Candidates – FROM FEC’s foiacn

Field

Definition

Type (Length)

Source

Cycle

Last year (even year) of the federal two year

election cycle

Text (4)

FEC

FECCandID

Assigned by FEC and selected by CRP as the

active, should multiples exist.

Text (9)

FEC

CID

Unique identifier for each candidate. Every

candidate should have one and only one CID

throughout all cycles. Recipid for candidates is

based on CID.

Text (9)

CRP

FirstLastP

Candidate name in format of firstname lastname

and party in parens, like Steve Kagen (D)

Text (50)

CRP

Party

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.

Text (1)

CRP

DistIDRunFor

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.

Text (4)

CRP

DistIDCurr

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

Text (4)

CRP

6/14/2015 of 62

field is frozen on election day. For cycles prior to

the current cycle, DistidCurr reflects office held

on Election Day of the Cycle.

CurrCand

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.

Text (1)

CRP

CycleCand

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.

Text (1)

CRP

CRPICO

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.”

Text (1)

CRP

RecipCode

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 (2)

CRP

6/14/2015 of 62

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.

NoPacs

Indicates whether candidate has publicly

committed to forego contributions from PACs

Text (1)

CRP

6/14/2015 of 62

OpenSecrets Table Definition: FEC Committee table

Field

Definition

Type

Length

Source

Cycle

Last year (even year) of the federal 2-year election cycle

Text

FEC

CmteID

Unique ID given by FEC to each committee.

Text

FEC

PACShort

Standardized committee name based on PAC’s sponsor.

Text

CRP

Affiliate

Usually blank. For leadpacs, shows the sponsoring member.

Text

CRP

Ultorg

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.

Text

CRP

RecipID

For candidate committees this will be the candidate’s CID.

Otherwise, it will be the same as CmteID.

Text

CRP

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.

Text

CRP

FECCandID

Unique ID given to candidates by FEC.

Text

FEC

Party

(D,R,3,I,L) Will be null or empty if committee is not a party,

joint fundraising, leadership or candidate committee.

Text

CRP/FEC

PrimCode

The standard five character code identifying the committee’s

industry or ideology.

Text

CRP

Source

Indicates how the PrimCode was determined.

Text

CRP

Sensitive

If "Y", the committee has significant business in multiple

industries, some of which fall under the jurisdiction of specific

congressional committees.

Text

CRP

Foreign

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

Bit

CRP

Active

Determines if cmte is active in the cycle – 0 is no and 1 is yes

Integer

CRP

6/14/2015 of 62

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

Definition

Type

Length

Source

Cycle

Last year (even year) of the federal 2-year election cycle

Text

FEC

FECTransID

A unique record identifier within a given cycle.

Text

FEC

ContribID

A unique identifier for individual donors. Family groups

match on first 11 chars

Text

CRP

Contrib

The name of the contributor, usually in the format Last

name, First Name.

Text

FEC

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.

Text

CRP

Orgname

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)

Text

CRP

UltOrg

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.

Text

CRP

RealCode

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.)

Text

CRP

Date

The reported date of the contribution.

MM/DD/

YYYY

FEC

Amount

The amount contributed. This will be negative for refunds.

Integer

FEC

Street

The donor’s street address. Federal law PROHIBITS use of

this information for solicitation, fundraising or other

commercial purposes.

Text

FEC

City

The donor’s city. This could be based on a home address or

an employer’s address.

Text

FEC

State

The donor’s state. This could be based on a home address

or an employer’s address.

Text

FEC

Zip

The donor’s zip code. This could be based on a home

address or an employer’s address.

Text

FEC

6/14/2015 of 62

Field

Definition

Type

Length

Source

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 bothm the second

character is “B” for Business, “L” for Labor”, “I” for

Ideological, “O” for “Other” and “U” for unknown.

Text

CRP

Type

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.

Text

FEC

CmteID

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.

Text

FEC

OtherID

The committee id number for the intermediary party to

earmarked contributions.

Text

FEC

Gender

The donor’s gender. Can also be “U” if unknown or “N” if

the name is ambiguous.

Text

CRP

Microfilm

Refers to specific page of FEC report images on which this

transaction appears.

Text

FEC

Occupation

The donor’s disclosed occupation from electronic filing.

Text

FEC

Employer

The donor’s disclosed employer from electronic filing.

Text

FEC

Source

Indicates how the RealCode was determined

Text

6/14/2015 of 62

OpenSecrets Data Definition: PAC table (PACs to Candidates) – FROM FEC’s itpas2

Field

Definition

Type

Length

Source

Cycle

Last year (even year) of the federal 2-year election cycle

Text

FEC

FECRecNo

A unique record identifier within a given cycle.

Text

FEC

PACID

The committee id number for the PAC making the

contribution.

Text

FEC

CID

A unique identifier for candidates that is constant

throughout cycles.

Text

CRP

Amount

The amount contributed. This will be negative for

refunds.

Float

FEC

Date

The reported date of the contribution.

Date

FEC

RealCode

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.

Text

CRP

Type

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

Text

FEC

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.

Text

CRP

FECCandID

FECCandid of candidate

Text

FEC

6/14/2015 of 62

OpenSecrets Data Definition for PAC to PAC Data – FEC table itoth

Field

Definition

Type

Length

Source

Cycle

Last year (even year) of the federal 2-year election cycle

Text

FEC

FECRecNo

A unique record identifier within a given cycle.

Text

FEC

Filerid

The committee id number for the PAC making the

filing. Refers to donor if Type 2* or recipient if

Type=1*.

Text

FEC

DonorCmte

The standardized name for the donor based on the name

of the PAC’s sponsor.

Text

CRP

ContribLendTrans

Reported name of the donor if Type=1* or the recipient

if Type=2*.

Text

FEC

City

The donor’s city. This could be based on a home address

or an employer’s address.

Text

FEC

State

The donor’s state. This could be based on a home

address or an employer’s address.

Text

FEC

Zip

The donor’s zip code. This could be based on a home

address or an employer’s address.

Text

FEC

FECOccEmp

The donor’s disclosed employer and/or occupation.

Text

FEC

Primcode

The primary industry/ideological code for the donor

PAC’s sponsor.

Text

CRP

Date

The reported date of the contribution.

Date

FEC

Amount

The amount contributed. This will be negative for

refunds.

Float

FEC

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.

Text

CRP

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.

Text

CRP

Otherid

Committee id for donor if Type=1* or recipient if

Type=2*.

Text

FEC

RecipCode

A two character code defining the type of recipient. For

candidates, the first character is party (“D” for

Text

CRP

6/14/2015 of 62

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.

RecipPrimcode

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.

Text

CRP

Amend

Whether the record comes from an amended report

Text

FEC

Report

The type of report – 1st quarter, year end, etc.

Text

FEC

Whether the contribution is for a Primary (“P”) or

General (“G”) election.

Text

FEC

Microfilm

The FEC microfilm record for the contribution

Text

FEC

Type

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

Text

FEC

RealCode

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.

Text

CRP

Source

Indicates how the Realcode was determined.

Text

CRP

6/14/2015 of 62

OpenSecrets Data Dictionary for Expenditure Data – from FEC electronic filings

Field

Definition

Type/Len

Source

Cycle

Last year (even) of a federal 2-yr election cycle

Text (4)

CRP

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.

Integer

CRP

TransID

A unique record identifier within a given cycle.

Text (20)

FEC

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)

Text (9)

CRP

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.

Text (2)

CRP

Pacshort

The standardized name of the filing committee

Text (50)

CRP

CRPRecipname

The recipient of the disbursement. This field is

standardized when possible

Text (90)

CRP

Expcode

The expenditure code assigned by the

researcher, based on the old codes.

Text (3)

CRP

Amount

The amount spent. This will be negative for

refunds.

Float

FEC

6/14/2015 of 62

Date

The reported date of the expenditure

mm/dd/yyyy

FEC

City

City of the recipient

Text (30)

FEC

State

State of the recipient

Text (2)

FEC

Zip

Zip of the recipient

Text (5)

FEC

CmteID_EF

Alternate committee ID

Text (9)

FEC

Candid

candidate id

Text (9)

FEC

Type

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

Text (3)

FEC

Descrip

Description of the expenditure

Text (100)

FEC

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

Text (5)

FEC

ElecOther

Explanation if there is anything other than ‘P’ or

‘G’ in previous field

Text (20)

FEC

EntType

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

Text (3)

FEC

Source

The source researcher used to assign a certain

code to a disbursement. A list can be found in

the Expenditures methodology

Text (5)

CRP

6/14/2015 of 62

 Data Dictionary – Lobby tables

OpenSecrets Data Definitions for Lobbying

Field

Definition

Type

Length

Source

Uniqid

Corresponds to a particular report from SOPR

Varchar

SOPR

Registrant_raw

Raw registrant

Varchar

110

SOPR

Registrant

Standardized registrant

Varchar

CRP

Isfirm

Notes whether or not the firm is a lobbying firm.

Char

CRP

Client_raw

Raw client

Varchar

110

SOPR

Client

Standardized client

Varchar

CRP

Ultorg

Parent company to the client

Varchar

CRP

Amount

Lobbying income/expenses

Float

SOPR

Catcode

The standard five character code identifying the donor’s

industry or ideology.

Char

CRP

Source

Souce of catcode

char

CRP

Self

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

Char

CRP

6/14/2015 of 62

IncludeNSFS

To indicate if the filer included its non self filers

affiliates activities

Char

CRP

Use

To indicate if this report should be used or ignored. The

general method is to use the latest report.

Char

CRP

Ind

To indicate if the amount on this report should be

included to calculate industry totals.

Char

CRP

Year

The year.

Char

CRP

Type

A CRP short version of reports’ types. Look at the

reference table “ReportTypes” for possible values

Char

CRP

Typelong

The long version of reports’ types. Look at the reference

table “ReportTypes” for possible values

Varchar

SOPR

Affiliate

To indicate if the major political contributor is an

affiliate or not

Char

CRP

6/14/2015 of 62

OpenSecrets Data Definition for Lobbyists

Field

Definition

Type

Length

Source

UniqID

Corresponds to a particular report from SOPR

Varchar

SOPR

Lobbyist_raw

Raw value of lobbyist’s name.

Varchar

SOPR

Lobbyist

Standardized lobbyist.

Varchar

CRP

Lobbyist_id

An ID assigned to each unique lobbyist.

Varchar

CRP

Year

The year.

Char

SOPR

OfficialPosition

Any previous government official position the

individual lobbyist had

Varchar

100

SOPR

CID

An ID assigned to members of congress and federal

candidates. It will have a value if the lobbyist is a

former member of congress

Varchar

CRP

Formercongmem

Notes whether the lobbyist is a former member.

CRP

OpenSecrets Data Definitions for Lobbying Data: Lobby Issues

Field

Definition

Type

Length

Source

SI_ID

Unique identifier for this table

int

Uniqid

Corresponds to a particular report from SOPR

Varchar

SOPR

IssueID

A three-letter code corresponding to the general issue

area.

Char

CRP

Issue

The long version of the three letter general issue.

Varchar

SOPR

SpecificIssue

The specific issue.

Varchar

Max

SOPR

Year

The Year.

Char

SOPR

OpenSecrets Data Definitions for Lobbying Industries

Field

Definition

Type

Length

Source

Client

Standardized client company.

Varchar

CRP

Sub

Standardized subsidiary

Varchar

CRP

Total

Total amount by catcode

Float

CRP

Year

The year.

Char

SOPR

Catcode

The standard five character code identifying the donor’s

industry or ideology.

Char

CRP

6/14/2015 of 62

OpenSecrets Data Definitions for Lobbying Agency

Field

Definition

Type

Length

Source

Uniqid

Corresponds to a particular report from SOPR

Varchar

SOPR

AgencyID

An agency unique identifier

Char

CRP

Agency

The government agency lobbied

Varchar

SOPR

OpenSecrets Data Definition for Lobbying Bills

Field

Definition

Type

Length

Source

B_ID

A bill unique identifier

int

CRP

SI_ID

It is the specific issue unique identifier. It is a foreign

key on this table

int

CRP

CongNo

Congress number

char

CRP

Bill_Name

The bill name

Char

CRP

OpenSecrets Data Definition for Report Types

Field

Definition

Type

Length

Source

Type Long

Text

SOPR

Type code

Text

CRP

6/14/2015 of 62

 Data Dictionary – 527 tables

OpenSecrets Data Definition: 527 Committees

Field

Description

Type

Length

Source

Cycle

Last year (even year) of the federal 2-year election cycle

Text

CRP

Rpt

Indicates reporting period. First two characters indicate the quarter (e.g., Q3) and last two characters

indicate year (e.g., 03)

Text

CRP

EIN

ID assigned to each 527 committee by IRS

Text

IRS

CRP527Name

Standardized committee name based on 527’s sponsor.

Text

CRP

Affiliate

Usually blank. For leadpacs, shows the sponsoring member.

Text

CRP

UltOrg

The standardized parent organization for the organization listed in the CRP527Name field. If there is

no parent identified, this field will be equal to CRP527Name.

Text

CRP

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.

Text

CRP

CmteID

FEC id for PAC if the same organization also has a PAC.

Text

CRP

CID

Text

CRP

ECCmteID

FEC id for electioneering communications PAC if the same organization also has one.

Text

CRP

Party

(D,R,3,I,L, U) Will be null or empty if committee is not a party, joint fundraising, leadership or

candidate committee.

Text

CRP

PrimCode

The standard five character code identifying the committee’s industry or ideology.

Text

CRP

Source

Indicates how the PrimCode was determined.

Text

CRP

FFreq

‘Q’ if the 527 files quarterly, ‘M’ if it files monthly.

Text

IRS

Ctype

Committee type indicates whether the 527 is focused on state level politics (‘S’) or federal level (‘F’).

‘U’ is unknown.

Text

CRP

CSource

Source used to determine Ctype.

Text

CRP

ViewPt

‘L’ for liberal, ‘C’ for conservative, ‘N’ for none, ‘U’ for unknown

Text

CRP

Comments

Additional information about the 527, including web addresses, sources of information, and other

notes about the organization.

Text

250

CRP

State

State where organization is based.

Text

IRS

6/14/2015 of 62

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

Field

Definition

Type/Len

Source

A unique record identifier. This is added by CRP and is NOT

consistent from download to download.

Integer

CRP

Rpt

The quarter and year in which the contribution was reported. The

first two characters indicate the quarter, the last two the year.

Text (4)

CRP

FormID

IRS field

Text (38)

IRS

SchAID

An id field supplied by the IRS.

Text (38)

IRS

ContribID

A unique identifier for individual donors and their immediate

families

Text (12)

CRP

Contrib

The name of the contributor, usually in the format Last name,

First Name.

Text (50)

IRS

Amount

The amount contributed. This will be negative for refunds.

Float

IRS

Date

The reported date of the contribution.

mm/dd/yyyy

IRS

Orgname

The standardized organization name for the contributor. This is

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)

Text (50)

CRP

UltOrg

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.

Text (50)

CRP

RealCode

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.)

Text (5)

CRP

RecipID

The recipient’s id number. This is the IRS employer

identification number.

Text (9)

IRS

6/14/2015 of 62

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.

Text (2)

CRP

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.

Text (1)

CRP

Recipient

The standardized name for the recipient.

Text (50)

CRP

City

The donor’s city. This could be based on a home address or an

employer’s address.

Text (50)

IRS

State

The donor’s state. This could be based on a home address or an

employer’s address.

Text (2)

IRS

Zip

The donor’s zip code. This could be based on a home address or

an employer’s address.

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 of 62

OpenSecrets Data Dictionary 527 Expenditure Data – from IRS Form 8872B

Field

Type

Length

Description

Source

Rpt

text

The quarter and year in which the contribution was reported. The first two characters indicate

the quarter, the last two the year.

CRP

FormID

text

IRS field

IRS

SchBID

text

An id field supplied by the IRS.

IRS

Orgname

text

Standardized organization name for the 527 making the distribution.

CRP

EIN

text

ID assigned to each 527 committee by IRS

IRS

Recipient

text

The recipient of the disbursement.

IRS

RecipientCRP

text

The recipient of the disbursement. This field is standardized when possible

CRP

Amount

Integer

Amount of expenditure

IRS

Date

Date of expenditure

IRS

ExpCode

text

Indicates category of expenditure.

CRP

Source

text

Source used to determine ExpCode or ExpCode_Old.

CRP

Purpose

text

512

Description of the expenditure

IRS

Addr1

text

Street address of recipient

IRS

Addr2

text

Further address information for recipient

IRS

City

text

City of recipient

IRS

State

text

State of recipient

IRS

Zip

text

Zip of recipient

IRS

Employer

text

Employer of recipient

IRS

Occupation

text

Occupation of recipient

IRS

6/14/2015 of 62

 Data Dictionary – Personal Finances tables

OpenSecrets Data Definition:

Personal Finances Agreements

Field

Description

Type

Length

Source

Unique id within a year.

Text

CRP

Chamber

Refers to chamber the report was filed with. "H" for House, "S" for Senate, "E" for executive branch

Text

Report

CID

Unique id for each filer.

Text

CRP

CalendarYear

Calendar year that is covered by report and that this record applies to.

Text

Report

ReportType

Y=Annual filing, A=Amendment, T=Termination

text

Report

AgreementDate1

Date when agreement was made or began.

Date

Report

AgreementDate1Text

Date when agreement was made or began.

Text

Report

AgreementDate2

Date when agreement ended. Agreement was not concluded if null or empty.

Date

Report

AgreementDate2Text

Date when agreement ended. Agreement was not concluded if null or empty.

Text

Report

AgreementParty1

First party to the agreement.

Text

100

Report

Orgname

Standardized version of first party to the agreement. Based on AgreementParty1.

Text

CRP

Ultorg

Standardized parent organization of first party to the agreement. If none, this field will be null or empty.

Text

CRP

Realcode

Industry code for AgreementParty1

Text

CRP

Source

Source used to determine Realcode

Text

CRP

AgreementParty1Loc

Location information for AgreementParty1. City and state for example.

Text

Report

AgreementParty2

Second party to the agreement. Based on AgreementParty2. Understood to be the filer if null or empty.

Text

100

Report

Orgname2

Standardized version of second party to the agreement. Based on AgreementParty2.

Text

CRP

Ultorg2

Standardized parent organization of second party to the agreement. If none, this field will be null or empty.

Text

CRP

Realcode2

Industry code for AgreementParty2

Text

CRP

Source2

Source used to determine Realcode2

Text

CRP

AgreementTerms

Description of purpose and terms of the agreeement

Memo

Report

Dupe

"D" if record is duplicated in same or subsequent reports and should not be calculated or displayed.

Text

CRP

6/14/2015 of 62

OpenSecrets Data Definition:

Personal Finances Assets

Field Name

Description

Type

Length

Source

Reference

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.

Text

CRP

Chamber

Refers to chamber the report was filed with. "H" for

House, "S" for Senate, "E" for executive branch

Text

Report

CID

Unique CRP identifier for each candidate or

executive branch member

Text

CRP

CalendarYear

Year covered by report

Text

Report

ReportType

Indicates if the report is an Annual Filing (Y), an

Amendment (A), or a Termination (T)

Text

Report

SenAB

Senate forms separate publicly traded assets (A)

and non-publicly traded assets (B)

Text

Report

AssetSpouseJointDep

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.

Text

Report

AssetSource

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.

Text

100

Report

Orgname

Standardized version of AssetSource

Text

CRP

Ultorg

Standardized parent organization of AssetSource. If

none, this field will be null or empty.

Text

CRP

RealCode

Industry code for AssetSource

Text

CRP

Source

Source used to determine Realcode

Text

CRP

AssetDescrip

Name of asset as reported by filer. Actual assets,

when they are held in an account.

Text

100

Report

Orgname2

Standardized version of AssetDescrip

Text

CRP

Ultorg2

Standardized parent organization of AssetDescrip. If

none, this field will be null or empty.

Text

CRP

RealCode2

Industry code for AssetDescrip

Text

CRP

Source2

Source used to determine Realcode2

Text

CRP

AssetSourceLocation

Combines City, State info into one field

Text

Report

AssetValue

Value range of asset as reported by filer

Text

Report

RangesAssets.Code

6/14/2015 of 62

AssetExactValue

Exact value of asset when provided.

Number

(Decimal)

Report

AssetDividends

Asset produced Dividend income

Text

Report

AssetRent

Asset produced Rental income

Text

Report

AssetInterest

Asset produced Interest income

Text

Report

AssetCapitalGains

Asset produced Capital Gains income

Text

Report

AssetExemptedFund

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

Text

Report

AssetExemptedTrust

Income from an exempted trust

Text

Report

AssetQualifiedBlindTrust

Income from a qualified blind trust

Text

Report

AssetTypeCRP

Type of investment. Stock, real estate, etc. Used to

determine which to count in industry totals.

Text

CRP

AssetTypeCodes.AssetTypeCode

OtherTypeIncome

Other than Div, Rent, Interest, CG

Text

100

Report

AssetIncomeAmtRange

Indicates how much income was produced by asset.

Text

Report

RangesAssetIncome.AssetIncomeAmtRange

(Also see DoleAssetFactors)

AssetIncomeAmountText

Description of income amount or raw version of

precise amount in AssetIncomeAmt

Text

Report

AssetIncomeAmt

Precise numbers when available

Currency

Report

AssetPurchased

Asset was purchased during reporting period

Text

Report

AssetSold

Asset was sold during reporting period

Text

Report

AssetExchanged

Date

DateText

Asset was exchanged during reporting period

Date provided by filer

Alternate date value

Text

Date

Text

Report

AssetNotes

Comments or further information

Text

100

CRP

Dupe

"D" if record is duplicated in same or subsequent

reports and should not be calculated or displayed.

Text

CRP

6/14/2015 of 62

OpenSecrets Data Definition:

Personal Finances Compensation

Field

Description

Type

Length

Source

unique id within a year

Text

CRP

Chamber

Refers to chamber the report was filed with. "H" for House, "S" for Senate, "E" for executive branch

Text

Report

CID

Unique id for each filer.

Text

CRP

CalendarYear

Year covered by report

Text

Report

ReportType

"Y" for annual report, "A" for amendment, "N" for nominee or candidate, "T" for termination

Text

Report

CompSource

Source of compensation as reported by filer

Text

100

Report

Orgname

Standardized source of compensation

Text

CRP

Ultorg

Standardized parent organization of Orgname

Text

CRP

Realcode

Industry code for CompSource

Text

CRP

Source

Source used to determine Realcode

Text

CRP

CompSourceLocation

City, State of CompSource

Text

Report

CompDuties

Description of duties performed in return for compensation

Text

100

Report

dupe

"D" if record is duplicated in same or subsequent reports and should not be calculated or displayed.

Text

CRP

6/14/2015 of 62

OpenSecrets Data Definition:

Personal Finances Gifts

Field

Description

Type

Length

Source

unique id within a year

Text

CRP

Chamber

Refers to chamber the report was filed with. "H" for House, "S" for Senate, "E"

for executive branch

Text

Report

CID

Unique id for each filer.

Text

CRP

CalendarYear

Year covered by report

Text

Report

ReportType

"Y" for annual report, "A" for amendment, "N" for nominee or candidate, "T" for

termination

Text

Report

GiftSpouseJointDep

Gift recipient is filer jointly with spouse, spouse alone, or dependent child. Null

or empty presumes filer was recipient.

Text

Report

GiftSource

Organization or individual who made the gift as reported by filer

Text

200

Report

Orgname

Standardized GiftSource

Text

CRP

Ultorg

Standardized parent organization of GiftSource

Text

CRP

Realcode

Industry code for GiftSource

Text

CRP

Source

Source used to determine Realcode

Text

CRP

GiftLocation

City, state of GiftSource

Text

Report

GiftDate

Date the gift was given

Date

Report

GiftDateText

Date the gift was given

Text

Report

GiftDescrip

Description of gift

Text

200

Report

GiftInfo

Additional information about gift

Text

100

Report

GiftValue

Value of gift

Currency

Report

GiftValueText

Value of gift

Text

Report

Dupe

"D" if record is duplicated in same or subsequent reports and should not be

calculated or displayed.

Text

CRP

6/14/2015 of 62

OpenSecrets Data Definition:

Personal Finances Honoraria

Field

Description

Type

Length

Source

unique id within a year

text

CRP

Chamber

Refers to chamber the report was filed with. "H" for House, "S" for Senate,

"E" for executive branch

text

Report

CID

Unique id for each filer.

text

CRP

CalendarYear

Year covered by report

text

Report

ReportType

"Y" for annual report, "A" for amendment, "N" for nominee or candidate, "T"

for termination

text

Report

HonorariaSource

Organization that paid honoraria, as reported by filer

text

100

Report

Orgname

Standardized version of HonorariaSource

text

CRP

Ultorg

Standardized parent organization of HonorariaSource

text

CRP

Realcode

Industry code of HonorariaSource

text

CRP

Source

Source used to determine Realcode

text

CRP

HonorariaSourceLoc

City, state of Honoraria Source

text

Report

HonorariaActivity

Description of activities that earned the honoraria

text

255

Report

HonorariaDate

Date activity was performed

date

Report

HonorariaDateText

Date activity was performed

text

Report

HonorariaAmt

Value of honoraria paid

currency

Report

HonorariaAmtText

Value of honoraria paid

text

Report

Dupe

"D" if record is duplicated in same or subsequent reports and should not be

calculated or displayed.

text

CRP

6/14/2015 of 62

OpenSecrets Data Definition:

Personal Finances Income

Field

Description

Type

Length

Source

unique id within a year

text

CRP

Chamber

Refers to chamber the report was filed with. "H" for House, "S"

for Senate, "E" for executive branch

text

Report

CID

Unique id for each filer.

text

CRP

CalendarYear

Year covered by report

text

Report

ReportType

"Y" for annual report, "A" for amendment, "N" for nominee or

candidate, "T" for termination

text

Report

IncomeSource

Organization that paid income, as reported by filer

text

100

Report

Orgname

Standardized version of IncomeSource

text

CRP

Ultorg

Standardized parent organization of IncomeSource

text

CRP

Realcode

Industry code of IncomeSource

text

CRP

Source

Source used to determine Realcode

text

CRP

IncomeLocation

City, state of IncomeSource

text

Report

IncomeSpouseDep

Income recipient is filer jointly with spouse, spouse alone, or

dependent child. Null or empty presumes filer was recipient.

text

Report

IncomeType

Description of income type. e.g., "salary"

text

Report

IncomeAmt

Value of income. Non-filer items can be reported as "Over

$1,000"

currency

Report

IncomeAmtText

Value of income. Non-filer items can be reported as "Over

$1,000"

text

Report

Dupe

"D" if record is duplicated in same or subsequent reports and

should not be calculated or displayed.

text

CRP

6/14/2015 of 62

OpenSecrets Data Definition:

Personal Finances Liabilities

Field

Description

Type

Length

Source

Reference

unique id within a year

text

CRP

Chamber

Refers to chamber the report was filed with. "H" for House, "S" for Senate, "E"

for executive branch

text

Report

CID

Unique id for each filer.

text

CRP

CalendarYear

Year covered by report

text

Report

ReportType

"Y" for annual report, "A" for amendment, "N" for nominee or candidate, "T" for

termination

text

Report

LiabilitySpouseJointDep

Person liable is filer jointly with spouse, spouse alone, or dependent child. Null

or empty presumes filer is liable alone.

text

Report

Creditor

Organization or individual who made the loan as reported by filer

text

100

Report

Orgname

Standardized Creditor

text

CRP

Ultorg

Standardized parent organization of Creditor

text

CRP

Realcode

Industry code for Creditor

text

CRP

Source

Source used to determine Realcode

text

CRP

TypeofLiability

Description of loan type. e.g., "mortgage"

text

100

Report

LiabilityLoc

City, state of Creditor

text

Report

LiabilityDate

Date the liability originated

date

Report

LiabilityDateText

Date the liability originated

text

Report

LiabilityTerm

Length of loan term

text

Report

LiabilityInterestRate

Interest rate paid on loan

text

Report

LiabilityAmt

Value of liability

text

Report

RangesLiability.Code

Dupe

"D" if record is duplicated in same or subsequent reports and should not be

calculated or displayed.

text

CRP

6/14/2015 of 62

OpenSecrets Data Definition:

Personal Finances Positions

Field

Description

Type

Length

Source

unique id within a year

text

Chamber

Refers to chamber the report was filed with. "H" for House, "S" for Senate,

"E" for executive branch

text

CID

Unique id for each filer.

text

CalendarYear

Year covered by report

text

ReportType

"Y" for annual report, "A" for amendment, "N" for nominee or candidate,

"T" for termination

text

PositionHeld

Position held with organization listed in PositionOrg

text

100

PositionOrg

Organization with which position is held

text

100

Orgname

Standardized version of PositionOrg

text

Ultorg

Standardized parent of PositionOrg

text

Realcode

Industry code of PositionOrg

text

Source

Source used to determine Realcode

text

PositionOrgLoc

City, state of PositionOrg

text

PositionOrgType

Description of organization

text

PositionFromDate

Date filer began holding this position

date

PositionFromDateText

Date filer began holding this position

text

PositionToDate

Date filer stopped holding this position

date

PositionToDateText

Date filer stopped holding this position

text

Dupe

"D" if record is duplicated in same or subsequent reports and should not

be calculated or displayed.

text

6/14/2015 of 62

OpenSecrets Data Definition:

Personal Finances Transactions

Field

Description

Type

Length

Source

Reference

text

CRP

Chamber

Refers to chamber the report was filed with. "H" for

House, "S" for Senate, "E" for executive branch

text

Report

CID

Unique code for filer

text

CRP

CalendarYear

Indicates Year coverd by report

text

Report

ReportType

Y=Annual filing, A=Amendment, T=Termination

text

Report

Asset4SJD

S=Spouse, J=Joint, D=Dependent Child, Null=Filer

Text

Report

Asset4Transacted

Raw asset name. If the asset is held within an account,

the raw account name.

Text

100

Report

Orgname

Standardized version of Asset4Transacted. (More

reliable than Asset4Transacted. If errors are found in

Asset4Descrip, they are corrected here but not in the

raw field.)

Text

CRP

Ultorg

Standardized parent organization of Orgname

Text

CRP

RealCode

Text

CRP

Source

Text

CRP

Asset4Descrip

If the asset is held within an account, the raw asset

name.

Text

100

Report

Orgname2

Standardized version of Asset4Descrip. (More reliable

than Asset4Descrip. If errors are found in

Asset4Descrip, they are corrected here but not in the

raw field.)

Text

CRP

Ultorg2

Standardized parent organization of Orgname2

Text

CRP

RealCode2

Text

CRP

Source2

Text

CRP

Asset4Purchased

Asset was purchased

Text

Report

Asset4Sold

Asset was sold

Text

Report

Asset4Exchanged

Asset was exchanged

Text

Report

Asset4Date

Date of Transaction

Date

Report

Asset4DateText

Date of Transaction

Text

Report

Asset4TransAmt

Code indicating amount of transaction.

Text

Report

If Chamber='H', then

RangesTransHouseOnly.Code.

Otherwise, RangesAssets.Code (Also

see DoleTransFactors

Asset4ExactAmt

Exact amount of transaction, if given.

Number

Report

CofD

Certificate of Divestiture

Text

Report

6/14/2015 of 62

TransNotes

Notes including other types than sold, purchased,

exchanged.

Text

100

CRP

Dupe

"D" if record is duplicated in same or subsequent

reports and should not be calculated or displayed.

Text

CRP

6/14/2015 of 62

OpenSecrets Data Definition:

Personal Finances Travel

Field

Description

Type

Length

Source

unique id within a year

text

CRP

Chamber

Refers to chamber the report was filed with. "H" for House, "S" for Senate, "E"

for executive branch

text

Report

CID

Unique id for each filer.

text

CRP

CalendarYear

Year covered by report

text

Report

ReportType

"Y" for annual report, "A" for amendment, "N" for nominee or candidate, "T" for

termination

text

Report

TravelSource

Travel sponsor as reported by filer

text

100

Report

Orgname

standardized version of TravelSource

text

CRP

Ultorg

Standardized parent organization of TravelSource

text

CRP

Realcode

Industry code for TravelSource

text

CRP

Source

Source used to determine Realcode

text

CRP

SourceCity

City where TravelSource is located

text

Report

SourceState

State where TravelSource is located

text

Report

BeginDate

Date that trip began

date

Report

BeginDateText

Date that trip began

text

Report

EndDate

Date that trip ended

date

Report

EndDateText

Date that trip ended

text

Report

DepartCity

City from which filer traveled to start trip

text

Report

DepartState

State from which filer traveled to start trip

text

Report

DestCity

Destination city where trip took place

text

Report

DestState

Destination state where trip took place

text

Report

PofRCity

City that filer returned to at end of trip (point of return)

text

Report

PofRState

City that filer returned to at end of trip (point of return)

text

Report

Descrip

Description of trip and purpose

text

255

Report

Lodging

Was lodging provided by TravelSource during the trip

text

Report

Food

Was food provided by TravelSource during the trip

text

Report

FamilyIncl

Was travel for family members also provided by TravelSource

text

Report

TimeAtOwnExpense

Description of portions of trip that were paid for at filer's expense

text

Report

Dupe

"D" if record is duplicated in same or subsequent reports and should not be

calculated or displayed.

text

CRP

6/14/2015 of 62

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 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) NULL,

[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 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 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 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 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 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 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 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 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 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 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 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 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]

OpenSecrets User Guide

Navigation menu

Versions of this User Manual:

Views

Navigation