Data Collection Process

Differentiators Data | NC Campaign Finance Data Project

Author

Garrick Aden-Buie

Reports by Year

The campaign finance data gathering begins by collecting a table with the list of all quarterly or semi-annual reports filed by committees in the NC SBOE system by reporting year. The NC SBOE system allows you to search for reports by year and report type, which I use to construct a URL that lists all reports of a given type for a given year.

An individual link leads us to a master list of every report type, say 2nd Quarter Report, filed in a year, e.g. 2022. This table includes links to the actual report in the Data column on the right, as well as a link to download the table as a .csv file via the Export list to .csv link at the top of the page.

Master list of 2nd Quarter Reports filed in 2022

Unfortunately, the table that you get in the CSV does not include the link to the actual report, instead that field includes only the word DATA in place of the link. Furthermore, neither the CSV table nor the table displayed on the web page include the actual report ID, although it’s used to construct the URLs in the DATA links.

To get around these limitations, I discovered that the source data for the displayed table is included in the HTML file when you view the page source (right click anywhere on the page and choose “View Page Source”). Somewhere in the middle of the file is a bit of JavaScript that starts with var data = [ .... This is essentially a JSON representation of the table data, and I could extract and read it into a table format. This table is actually very well formatted and importantly includes the bare report id as well as the committee’s SBOE ID.

By scraping the table source data from every report list for years from 2016 through 2023, and for every available report type, I was able to put together a master list of every report of interest filed in the NC SBOE system. We found 55,408 reports in total; here’s a small preview of the values we have in this table:

Rows: 55,408
Columns: 12
$ year           <int> 2016, 2016, 2016, 2016, 2016, 2016, 2016, 2016, 2016, 2…
$ doc_name       <chr> "Mid Year Semi-Annual", "Mid Year Semi-Annual", "Mid Ye…
$ sboe_id        <chr> "FED-C4688N-C-001", "STA-C3853N-C-001", "STA-C3329N-C-0…
$ committee_name <chr> "3M COMPANY POLITICAL ACTION COMMITTEE", "ANSON DEC", "…
$ report_id      <chr> NA, NA, NA, "147216", NA, "141437", "142938", "141347",…
$ doc_type       <chr> "Disclosure Report", "Disclosure Report", "Disclosure R…
$ amended        <lgl> FALSE, FALSE, FALSE, FALSE, FALSE, FALSE, FALSE, FALSE,…
$ received_image <date> 2016-07-27, 2016-08-30, 2016-07-27, 2016-07-21, 2016-0…
$ received_data  <date> NA, NA, NA, NA, NA, 2016-07-29, NA, 2016-07-21, NA, NA…
$ start_date     <date> 2016-01-01, 2016-01-01, 2016-01-01, 2016-01-01, 2016-0…
$ end_date       <date> 2016-06-30, 2016-06-30, 2016-06-30, 2016-06-30, 2016-0…
$ img_link       <chr> "ViewDocumentImage/?DID=200793", "ViewDocumentImage/?DI…

Looking at the total reports by report type and year shows a predictable pattern: on the whole, quarterly reports are filed in election (even) years, and semi-annual reports are filed in non-election (odd) years. There are also a few exceptions to this pattern, where a small number of semi-annual reports are filed in even years, and a larger but relatively small number of quarterly reports are filed in odd years.

Reports by Committee

The next step is to collect the actual report data for each of the reports in our master list. Fortunately, if we can use the sboe_id and report_id from the previous step to jump straight to the report page in the NC SBOE system. Here’s an example taken at random from John Bell’s reports:

https://cf.ncsbe.gov/CFOrgLkup/ReportDetail/?RID=164085&TP=ALL

This single page appears to have all of the data we want in one place, and it even has an Export data to .csv link at the top of the page. The format of that exported data is a little unusual – it’s not really a CSV file but instead a text file with several CSV files concatenated together. Here’s what you get when you download the export linked to in the above screenshot:

164085.txt
COVER
SBoE ID,Committee Name,Street 1,Street 2,City,State,Zip Code,Country,Postal Code,Committee Type,Report Type,Fund Type,Fund Name,Date From,Date To,Date Filed
,JOHN BELL COMMITTEE,501 HOLLAND HILL DRIVE,,GOLDSBORO,NC,27530,United States,,Candidate Committee,2018 Second Quarter,,,04/22/2018,06/30/2018,01/25/2019

OFFICERS
Type,Name,Address,Phone
Treasurer,BRANDON R GRAY,"280 MARK EDWARDS ROAD, LAGRANGE, NC, 28551",(919) 735-6300
Candidate,JOHN BELL,,

ACCOUNTS
Code,Type,Purpose,Name,Address,Begin Balance,End Balance
01,Checking,TO RAISE FUNDS AND PAY EXPENSES RELATED TO CAMPAIGN,FIRST CITIZENS BANK,,$0.00,$0.00

SUMMARY
Section,Period,Cycle
Cash on Hand at Beginning,179686.3900,126528.5300
RECEIPTS,0.0000,0.0000
Aggregated Contributions from Individuals,0.0000,415.0000
Contributions from Individuals,54277.0000,145452.0000
Political Party Committees,0.0000,0.0000
Other Political Committees (such as PACs),41750.0000,123250.0000
Loan Proceeds,0.0000,0.0000
Refunds/Reimbursements To the Committee,0.0000,982.3500
Interest on Bank Accounts,0.0000,0.0000
Contributions from Not-For-Profit Organizations,0.0000,0.0000
Outside Sources of Income,0.0000,0.0000
Legal Expense Fund - Other Sources,0.0000,0.0000
Exempt Purchase Price Sales,0.0000,0.0000
Total Receipts,96027.0000,270099.3500
EXPENDITURES,0.0000,0.0000
Operating Expenditures,99494.4300,170553.1400
Contributions to Candidates/Political Committees,0.0000,49330.6100
Coordinated Party Expenditures,0.0000,0.0000
Aggregated Non-Media Expenditures,126.7500,651.9200
Loan Repayments,0.0000,0.0000
Refunds/Reimbursements From the Committee,0.0000,0.0000
In-Kind Contributions,0.0000,0.0000
Total Expenditures,99621.1800,220535.6700
Cash on Hand at End of Reporting Period,176092.2100,176092.2100
ADDITIONAL INFORMATION,0.0000,0.0000
Non-Monetary Gifts Given to Other Committees,0.0000,0.0000
Outstanding Loans (incl. ones from other campaigns),0.0000,0.0000
Debts and Obligations owed BY the Committee,0.0000,0.0000
Debts and Obligations owed TO the Committee,0.0000,0.0000
Account Transfers Within the Committee,0.0000,0.0000
Administrative Support,0.0000,0.0000
Forgiven Loans,0.0000,0.0000
48-Hour Notice Reports Sum,0.0000,0.0000
Contributions to be Refunded,0.0000,0.0000

RECEIPTS
Date,Is Prior,Name,Street 1,Street 2,City,State,Full Zip,Country Name,Outside US Postal Code,Profession,Employers Name,Purpose,Receipt Type Desc,Account Abbr,Form Of Payment Desc,Description,Amount,Sum To Date
05/09/2018,,AMERICAN AIRLINES PAC,1101 17TH NW,STE 600,WASHINGTON,DC,20036,United States,,,,,Other Political Committee Contribution,01,Check,,1500.0000,1500.0000
05/01/2018,,KENNETH R BLIZZARD,1005 WALKER DRIVE,,KINSTON,NC,28501,United States,,PRESIDENT,,,Individual Contribution,01,Check,,3000.0000,3000.0000
05/04/2018,,CAPA-PAC,1101 HAYNES STREET,STE 209,RALEIGH,NC,27604,United States,,,,,Other Political Committee Contribution,01,Check,,2000.0000,2000.0000
05/15/2018,,"CAROLINAS AGC, INC NC PAC",4824 PARKWAY PLAZA BOULEVARD,STE 115,CHARLOTTE,NC,28217,United States,,,,,Other Political Committee Contribution,01,Check,,1000.0000,1000.0000
05/21/2018,,CAROLINK PAC,PO BOX 368,,YADKINVILLE,NC,27055,United States,,,,,Other Political Committee Contribution,01,Check,,750.0000,750.0000
05/03/2018,,D J CECILE,505 EAGLE DRIVE,,BOONE,NC,28607,United States,,VICE PRESIDENT,,,Individual Contribution,01,Credit Card,,1000.0000,1000.0000
05/15/2018,,COCA-COLA CONSOLIDATED EMPLOYEES,4100 COCA COLA PLAZA,STE 100,CHARLOTTE,NC,28211,United States,,,,,Other Political Committee Contribution,01,Check,,500.0000,500.0000
05/01/2018,,JAMES R CREEL,3419 BUENA VISTA COURT,,KINSTON,NC,28504,United States,,WASTE MANAGEMENT,,,Individual Contribution,01,Check,,1000.0000,1000.0000
05/01/2018,,DAWN DAVIS,3309 QUAIL RIDGE ROAD,,KINSTON,NC,28504,United States,,HOMEMAKER,,,Individual Contribution,01,Check,,2000.0000,2000.0000
05/18/2018,,ELECTRICITIES PAC,1427 MEADOW WOOD BOULEVARD,,RALEIGH,NC,27604,United States,,,,,Other Political Committee Contribution,01,Check,,500.0000,500.0000
05/09/2018,,"ENTERPRISE HOLDINGS, INC PAC",600 CORPORATE PARK DRIVE,,ST LOUIS,MO,63105,United States,,,,,Other Political Committee Contribution,01,Check,,1000.0000,1000.0000
04/25/2018,,BRUCE E GATES,148 WOODS MILL ROAD,,GOLDSBORO,NC,27534,United States,,REAL ESTATE AGENT,,,Individual Contribution,01,Check,,500.0000,500.0000
05/01/2018,,NED T GRADY,PO BOX 1435,,KINSTON,NC,28503,United States,,DEVELOPER,,,Individual Contribution,01,Check,,2500.0000,2500.0000
05/01/2018,,MICHAEL J HENSLEY,715 STALEY COURT,,RALEIGH,NC,27609,United States,,FINANCIAL ADVISOR,,,Individual Contribution,01,Check,,3000.0000,3000.0000
05/01/2018,,IIANC PAC,PO BOX 1165,,CARY,NC,27512,United States,,,,,Other Political Committee Contribution,01,Check,,1000.0000,1000.0000
05/14/2018,,INTERNATIONAL PAPER PAC,1101 PENNSYLVANIA AVENUE NW,STE 200,WASHINGTON,DC,20004,United States,,,,,Other Political Committee Contribution,01,Check,,1500.0000,1500.0000
05/14/2018,,LIBERTY MUTUAL INSURANCE PAC,1511 SUNDAY DRIVE,STE 200,RALEIGH,NC,27607,United States,,,,,Other Political Committee Contribution,01,Check,,500.0000,500.0000
05/01/2018,,BRENT MIZELLE,122 ALLEN DRIVE,,NEW BERN,NC,28562,United States,,DERMATOLOGIST,,,Individual Contribution,01,Check,,1000.0000,1000.0000
05/01/2018,,ROBERT B MOORE JR,1968 CORNERSTONE DRIVE,,WINTERVILLE,NC,28590,United States,,PRESIDENT,,,Individual Contribution,01,Check,,3000.0000,3000.0000
05/01/2018,,NATIONWIDE CAROLINA POLITICAL PARTICIPATION PAC,ONE NATIONWIDE PLAZA,,COLUMBUS,OH,43215,United States,,,,,Other Political Committee Contribution,01,Check,,2000.0000,2000.0000
05/15/2018,,NC ASSOCIATION OF NURSE ANESTHETISTS PAC,PO BOX 2023,,GARNER,NC,27529,United States,,,,,Other Political Committee Contribution,01,Check,,1000.0000,1000.0000
04/25/2018,,NC BAIL AGENTS ASSOCIATION,1220 EASTCHESTER DRIVE,STE 104,HIGH POINT,NC,27265,United States,,,,,Other Political Committee Contribution,01,Check,,1000.0000,1000.0000
05/01/2018,,NC HOSPITAL ASSOCIATION PAC,PO BOX 4449,,CARY,NC,27519,United States,,,,,Other Political Committee Contribution,01,Check,,4000.0000,4000.0000
05/09/2018,,NC MEDICAL SOCIETY PAC,222 NORTH PERSON ST,,RALEIGH,NC,27601,United States,,,,,Other Political Committee Contribution,01,Check,,1000.0000,1000.0000
05/14/2018,,NC MERCHANTS PAC,PO BOX 1030,,RALEIGH,NC,27602,United States,,,,,Other Political Committee Contribution,01,Check,,500.0000,500.0000
04/25/2018,,NC REALTORS PAC,4511 WEYBRIDGE LANE,,GREENSBORO,NC,27407,United States,,,,,Other Political Committee Contribution,01,Check,,4000.0000,4000.0000
05/01/2018,,NCPGA PAC,5109 HOLLYRIDGE DRIVE,,RALEIGH,NC,27612,United States,,,,,Other Political Committee Contribution,01,Check,,1000.0000,1000.0000
05/01/2018,,NORTH CAROLINA AUTOMOBILE DEALS ASSOCIATION PAC,PO BOX 12167,,RALEIGH,NC,27605,United States,,,,,Other Political Committee Contribution,01,Check,,2500.0000,2500.0000
05/04/2018,,NORTH CAROLINA DENTAL PAC,1600 EVANS ROAD,,CARY,NC,27513,United States,,,,,Other Political Committee Contribution,01,Check,,2500.0000,2500.0000
05/01/2018,,NORTH CAROLINA HEALTH CARE FACILITIES ASSOCIATION PAC,5109 BUR OAK CIRCLE,,RALEIIGH,NC,27612,United States,,,,,Other Political Committee Contribution,01,Check,,5000.0000,5000.0000
05/01/2018,,G TIMOTHY PATE,1417 ALEXANDER LANE,,TRENT WOODS,NC,28562,United States,,MANAGER,,,Individual Contribution,01,Check,,3000.0000,3000.0000
05/14/2018,,PEROLEUM AND CONVENIENCE MARKETERS PAC,7300 GLENWOOD AVENUE,,RALEIGH,NC,27612,United States,,,,,Other Political Committee Contribution,01,Check,,2000.0000,2000.0000
05/09/2018,,ELY J PERRY III,PO DRAWER 1475,,KINSTON,NC,28503,United States,,PRESIDENT,,,Individual Contribution,01,Check,,1000.0000,1000.0000
05/09/2018,,JAMES A PERRY,3504 LAKEVIEW TRAIL,,KINSTON,NC,28504,United States,,COO,,,Individual Contribution,01,Check,,2077.0000,2077.0000
05/01/2018,,JULIAN W RAWL,PO BOX 8068,,GREENVILLE,NC,27835,United States,,REAL ESTATE,,,Individual Contribution,01,Check,,2500.0000,2500.0000
05/01/2018,,R H RHODES JR,1501 GREENBRIAR ROAD,,KINSTON,NC,28501,United States,,PRESIDENT,,,Individual Contribution,01,Check,,3000.0000,3000.0000
05/01/2018,,THOMAS J SEGRAVE,3407 TIMBERLAKE COURT,,KINSTON,NC,28504,United States,,ENTREPRENEUR,,,Individual Contribution,01,Check,,5200.0000,5200.0000
05/01/2018,,JEFFREY B TURNER,PO BOX 246,,PINK HILL,NC,28572,United States,,MANAGER,,,Individual Contribution,01,Check,,3000.0000,3000.0000
05/01/2018,,"WARD AND SMITH, PA PAC",PO BOX 867,,NEW BERN,NC,28563,United States,,,,,Other Political Committee Contribution,01,Check,,4000.0000,4000.0000
05/01/2018,,WESTERN NORTH CAROLINA ADVOCATES FOR QUALITY CARE,11 TIMOTHY LANE,,CANDLER,NC,28715,United States,,,,,Other Political Committee Contribution,01,Check,,1000.0000,1000.0000
05/01/2018,,GARY E WHALEY,3635 BIG OAK ROAD,,KINSTON,NC,28504,United States,,MANAGER,,,Individual Contribution,01,Check,,3000.0000,3000.0000
05/01/2018,,WOODY WHICHARD,PO BOX 1863,,GREENVILLE,NC,27835,United States,,PRESIDENT,,,Individual Contribution,01,Check,,3000.0000,3000.0000
05/01/2018,,ISAAC W WHITFIELD,3508 LAKEVIEW TRAIL,,KINSTON,NC,28504,United States,,FARMER,,,Individual Contribution,01,Check,,500.0000,500.0000
05/01/2018,,WILLIAM N WILDER JR,1300 GREENBRIAR ROAD,,KINSTON,NC,28501,United States,,RETAIL GROCER,,,Individual Contribution,01,Check,,3000.0000,3000.0000
05/09/2018,,ELAINE WORDSWORTH,111 W CHURCH STREET,,NASHVILLE,NC,27856,United States,,HOMEMAKER,,,Individual Contribution,01,Check,,2000.0000,2000.0000
05/09/2018,,JERRY L WORDSWORTH,120 CANDLEWOOD ROAD,,ROCKY MOUNT,NC,27802,United States,,OWNER,,,Individual Contribution,01,Check,,4000.0000,4000.0000
05/09/2018,,STEVE A WORDSWORTH,111 W CHURCH STREET,,NASHVILLE,NC,27856,United States,,OWNER,,,Individual Contribution,01,Check,,2000.0000,2000.0000

EXPENDITURES
Date,Name,Street 1,Street 2,City,State,Full Zip,Country Name,Outside US Postal Code,Profession,Employer Name,Purpose Type Code,Purpose,Candidate,Office Sought,Declaration,Amount,Expenditure Type Desc,Account Abbr,Form Of Payment Desc,Description,Amount1,Sum To Date
04/30/2018,Aggregated Non-Media Expense,,,,,,,,,,O,BANK FEE,,,,,Operating Expense,01,Electronic Funds Transfer,,7.5000,7.5000
04/30/2018,Aggregated Non-Media Expense,,,,,,,,,,O,BANK FEE,,,,,Operating Expense,01,Electronic Funds Transfer,,5.0000,5.0000
05/03/2018,Aggregated Non-Media Expense,,,,,,,,,,O,BANK FEE,,,,,Operating Expense,01,Electronic Funds Transfer,,39.3000,39.3000
05/31/2018,Aggregated Non-Media Expense,,,,,,,,,,O,BANK FEE,,,,,Operating Expense,01,Electronic Funds Transfer,,5.0000,5.0000
05/31/2018,Aggregated Non-Media Expense,,,,,,,,,,O,BANK FEE,,,,,Operating Expense,01,Electronic Funds Transfer,,7.5000,7.5000
06/20/2018,Aggregated Non-Media Expense,,,,,,,,,,O,OFFICE SUPPLIES,,,,,Operating Expense,01,Debit Card,,49.9500,49.9500
06/29/2018,Aggregated Non-Media Expense,,,,,,,,,,O,BANK FEE,,,,,Operating Expense,01,Electronic Funds Transfer,,7.5000,7.5000
06/29/2018,Aggregated Non-Media Expense,,,,,,,,,,O,BANK FEE,,,,,Operating Expense,01,Electronic Funds Transfer,,5.0000,5.0000
06/15/2018,CAPTAIN STANLEY'S SEAFOOD,3333 S WILMINGTON STREET,,RALEIGH,NC,27603,United States,,,,O,MEAL FOR STAFF MEETING,,,,,Operating Expense,01,Debit Card,,89.6300,89.6300
05/01/2018,FACEBOOK,ONE HACKER WAY,,MENLO PARK,CA,94025,United States,,,,A,FACEBOOK AD,,,,,Operating Expense,01,Debit Card,,100.1000,100.1000
05/15/2018,FACEBOOK,ONE HACKER WAY,,MENLO PARK,CA,94025,United States,,,,A,FACEBOOK AD,,,,,Operating Expense,01,Debit Card,,100.0000,100.0000
05/11/2018,NATIONBUILDER,520 S GRAND AVENUE,,LOS ANGELES,CA,90071,United States,,,,A,WEBSITE HOSTING,,,,,Operating Expense,01,Debit Card,,199.0000,199.0000
06/11/2018,NATIONBUILDER,520 S GRAND AVENUE,,LOS ANGELES,CA,90071,United States,,,,A,WEBSITE HOSTING,,,,,Operating Expense,01,Debit Card,,199.0000,199.0000
05/31/2018,NC REPUBLICAN HOUSE CAUCUS,1506 HILLSBOROUGH STREET,,RALEIGH,NC,27605,United States,,,,G,,,,,,Operating Expense,01,Check,,90000.0000,90000.0000
06/21/2018,NC STATE BOARD OF ELECTIONS & ETHICS ENFORCEMENT,430 N SALISBURY STREET,,RALEIGH,NC,27603,United States,,,,O,NC CIVIL PENALTY & FORFEITURE FUND,,,,,Operating Expense,01,Check,,1000.0000,1000.0000
05/14/2018,RHONDA FOR HOUSE,16 W JONES STREET,,RALEIGH,NC,27601,United States,,,,D,,,,,,Operating Expense,01,Debit Card,,1500.0000,1500.0000
04/30/2018,USPS,1851 S GLENBURNIE ROAD,,NEW BERN,NC,28562,United States,,,,I,,,,,,Operating Expense,01,Debit Card,,6.7000,6.7000
04/24/2018,WAYNE COUNTY GOP,147 S CENTER STREET,,GOLDSBORO,NC,27530,United States,,,,G,,,,,,Operating Expense,01,Check,,1300.0000,1300.0000
05/23/2018,MALIA ZAYTOUN,105 DRUMMOND PLACE,,NEW BERN,NC,28562,United States,,,,E,,,,,,Operating Expense,01,Check,,5000.0000,5000.0000

Receipts and Expenditures

The full report text (a.k.a. 7 CSV files in a trench coat) is so so close to what we want, but at scale it doesn’t work out that well. It appears that the NC SBOE hand-rolled their CSV exports and unfortunately don’t create properly-formatted CSV files in some edge cases. These edge cases are mostly places where the SBOE would have reasonably expected users to enter a short text description, and instead users have entered longer text with commas and newlines, which break the CSV format.

These “all report sections” text file exports are worth downloading because they include several tables that we’d want to collect anyway, and we want to reduce the number of files and HTML pages we have to request from the NC SBOE system.

For the two most important tables, receipts and expenditures, it turns out that the “all reports page” listed above actually calls out to two unlisted, internal API endpoints to create the Receipts and Expenditures tables. These endpoints return JSON data which is guaranteed to be correctly formatted and easier to process into a table format.

They say there’s no such thing as a free lunch, and apparently this rule also applies to the NC SBOE campaign finance website. These API endpoints return at most 300 records at a time, meaning that for most reports we have to make multiple requests to gather all of the report data.

Here are two examples of the URLs I used to get the receipts and expenditures data for John Bell’s report:

Final Set of Files

In the end, there are three files that we download directly from the NC SBOE system for each report:

  1. The “all reports” text file export
  2. The receipts JSON data, saved as a (correctly-formatted) CSV file
  3. The expenditures JSON data, saved as a CSV file

Locally, I’ve organized these into a directory structure that looks like this, where STA-8S285O-C-001 is John Bell’s committee’s SBOE ID:

data-raw/reports/STA-8S285O-C-001
├── all
│   ├── ...
│   ├── 164085_2019-01-25.txt
│   └── ...
├── expenditures
│   ├── ...
│   ├── 164085_2019-01-25_expenditures.csv
│   └── ...
└── receipts
    ├── ...
    ├── 164085_2019-01-25_receipts.csv
    └── ....

The end result is a complete set of files for every quarterly or semi-annual campaign finance report filed with the NC SBOE.