Boston Checkbook Explorer city expenditures, FY2012 to present
- Provider
- City of Boston (Department of Innovation and Technology)
- License
- Open Data Commons Public Domain Dedication and License (PDDL) 1.0
- Size
- 16 files
- Records
- 1,942,660 rows
- Format
- CSV (in sampled files)
- Stored at
- data.boston.gov
- Access
- Free and public. No account with the data provider is needed.
- Category
- Urban & Civic
- Source
- https://data.boston.gov/dataset/checkbook-explorer
About Boston Checkbook Explorer city expenditures, FY2012 to present
Line-item municipal spending for the City of Boston from July 2011 (fiscal year 2012) to the present: 1,942,660 payment lines across 16 DataStore-loaded resources, one per fiscal year with the current year split into monthly increments. One row is one distribution line of one voucher. Each carries the voucher number with its voucher-line and distribution-line index, the entry date decomposed into month, month number, fiscal month, fiscal year and calendar year, the vendor name, the account code and description (for example 54906 'Current Software Licen/Maint'), the paying department code and name, a six-digit organisational unit name, and the monetary amount. Paired with the Employee Earnings Report, which covers the payroll the Checkbook deliberately excludes, this is a near-complete picture of where the city's money goes: vendor concentration and dependency, spending by function over fifteen years, procurement seasonality against the June fiscal year end, and the growth of specific cost categories such as software licensing or emergency shelter. The data is published under Mayor Walsh's 2014 open data executive order, with personal information withheld and independent city agencies excluded. Three things are documented in the schema card. Amounts are text, including negatives for credits and reversals, so they need a cast before aggregation and a reversal is a real row that must not be dropped as an error. Row counts per fiscal year are not comparable as a proxy for activity: the FY2024 resource holds 8,750 lines against 118,859 for FY2023 and 235,796 for FY2025, which reflects how that file was published rather than a collapse in city spending, so any year-over-year total must be computed from the amount column and cross-checked against the published budget. And the current fiscal year arrives as dated monthly snapshot files (checkbook_explorer_20260701, checkbook_explorer_20260801) whose contents overlap, so taking every resource in the package double counts the current year. Published by the Department of Innovation and Technology under PDDL 1.0.
How to load Boston Checkbook Explorer city expenditures, FY2012 to present in Python
import io, json, urllib.error, urllib.parse, urllib.request
import pandas as pd
CKAN = "https://data.boston.gov/api/3/action"
def ckan(action, **params):
"""Any CKAN action endpoint. Plain urllib works; no auth, no headers."""
url = f"{CKAN}/{action}?" + urllib.parse.urlencode(params)
with urllib.request.urlopen(url, timeout=300) as r:
return json.load(r)["result"]
def sql(query):
"""Server-side SQL over the DataStore. Warns when the 32,000-row cap clips."""
res = ckan("datastore_search_sql", sql=query)
if res.get("records_truncated"):
print("WARNING: clipped at the 32,000-row cap; aggregate or page with OFFSET")
return pd.DataFrame(res["records"])
FY2025 = "84dfc1af-28bd-4f17-804a-9cc0c09a237e" # 235,796 payment lines
# Where FY2025 money went, by department and expense account.
# Every column name has a space in it, so quote each one exactly.
df = sql(f'''
SELECT "Dept Name", "Account Descr",
count(*) AS lines,
sum("Monetary Amount"::numeric) AS amount
FROM "{FY2025}"
GROUP BY 1, 2
ORDER BY amount DESC
LIMIT 25
''')
print(df.to_string(index=False, float_format=lambda v: f"{v:,.0f}"))
# Sanity check that the negatives are present and material.
neg = sql(f'SELECT count(*) n, sum("Monetary Amount"::numeric) amt '
f'FROM "{FY2025}" WHERE "Monetary Amount"::numeric < 0')
print("\ncredit/reversal lines:", neg.to_dict("records"))Data structure
File types in sampled files: CSV
Fields
_id | int |
Voucher | text |
Voucher Line | text |
Distribution Line | text |
Entered | text |
Month(Number) | text |
Month | text |
Fiscal Month | text |
Fiscal Year | text |
Year | text |
Vendor Name | text |
Account | text |
Account Descr | text |
Dept | text |
Dept Name | text |
6 Digit Org Name | text |
Monetary Amount | text |
Files
One row is one distribution line of one voucher: the finest grain at which the city publishes a payment. 1,942,660 rows across 16 DataStore resources covering FY2012 (July 2011) to the present.
COLUMNS: Voucher, "Voucher Line" and "Distribution Line" together identify the payment line; Entered is the entry date as M/D/YYYY text; Month, "Month(Number)", "Fiscal Month", "Fiscal Year" and Year are pre-derived date parts; "Vendor Name" is the payee; Account and "Account Descr" are the expense classification (54906 "Current Software Licen/Maint"); Dept and "Dept Name" are the paying department (231100 "Emergency Preparedness"); "6 Digit Org Name" is the organisational unit ("Homeland Security"); "Monetary Amount" is the amount. Note that most column names contain SPACES and one contains parentheses, so every reference in SQL must be double-quoted exactly as written.
BOSTON'S FISCAL YEAR RUNS JULY TO JUNE. "Fiscal Year" 2025 means July 2024 to June 2025, and "Fiscal Month" 1 is July. Year and Month are the calendar equivalents, which is why a single row can read Fiscal Year 2025, Year 2024, Month July.
WHAT IS DELIBERATELY ABSENT: payroll (published separately as the Employee Earnings Report), personal information withheld under Mayor Walsh's 2014 open data executive order, and independent city agencies. This is the operating checkbook of city government, not of every entity with "Boston" in its name.
PER-FISCAL-YEAR ROW COUNTS: FY2012 168,180 | FY2013 134,478 | FY2014 147,111 | FY2015 129,713 | FY2016 133,543 | FY2017 149,969 | FY2018 154,617 | FY2019 139,037 | FY2020 112,315 | FY2021 89,090 | FY2022 113,491 | FY2023 118,859 | FY2024 8,750 | FY2025 235,796 | plus two current monthly snapshots (101,465 and 6,246).
RESOURCES:
Checkbook Explorer (data.boston.gov/dataset/checkbook-explorer): 16 DataStore resources of 16 total
7afd05bc-34ec-4a1c-b470-bf43ea584f81 6246 rows checkbook_explorer_20260801.csv
d22fdd5c-7e4c-41b7-a3eb-dfc57a87b245 101465 rows checkbook_explorer_20260701.csv
84dfc1af-28bd-4f17-804a-9cc0c09a237e 235796 rows checkbook_explorer_fy25_updated.csv
0b7c9c5f-d1c2-46e7-b738-6ab37a110eef 8750 rows checkbook_explorerfy24.csv
5ce2ff98-3313-40d2-88bd-47eae9e5a654 118859 rows checkbook_explorerfy23.csv
0a261d4e-3eec-4bac-bf72-b9a7aa77b033 113491 rows checkbook_explorerfy22.csv
32897eeb-d9ca-494f-93b1-991c50bcd6a6 89090 rows checkbook_explorerfy21.csv
c093700f-d78a-49de-a8fe-508ba834ff6f 112315 rows checkbook-explorer-fy20.csv
38227f56-46ed-47fe-9e1c-5d2fce52908d 139037 rows checkbook-explorer-fy19.csv
5d8e373f-29a0-472c-b39b-9aa249e86fd5 154617 rows checkbook-explorer-fy18.csv
01a5c35c-19e3-419e-a8b5-cb623525b96d 149969 rows checkbook-explorer-fy17.csv
ae5a15cc-8bd3-455d-8cbb-9221e07c1426 133543 rows checkbook-explorer-fy16.csv
5714ab9f-52d3-4c41-b2a6-2700b41438fc 129713 rows checkbook-explorer-fy15.csv
69eab395-07d3-41b8-a021-a0d314bd8046 147111 rows checkbook-explorer-fy14.csv
c2bc5615-9478-4a9b-b71c-f63f6364e409 134478 rows checkbook-explorer-fy13.csv
fd5c56a7-224f-41c4-a011-969b8aee457d 168180 rows checkbook-explorer-fy12.csvExample file: Largest vendors in FY2025 by total paid:
SELECT "Vendor Name", count(*) lines, sum("Monetary Amount"::numeric) AS paid
FROM "84dfc1af-28bd-4f17-804a-9cc0c09a237e"
GROUP BY 1 ORDER BY paid DESC LIMIT 20
Sample notebook
Sample notebook for Boston Checkbook Explorer city expenditures, FY2012 to present: worked code with the output it printed.
Analyze this dataset without downloading it
On Scigantic, Boston Checkbook Explorer city expenditures, FY2012 to present opens in a hosted JupyterLab notebook with the data already mounted, so you can read files where they are stored instead of copying them first. Each notebook includes an AI assistant that can see the mounted files and write and run analysis code. Open the dataset page to launch a notebook, or browse the full catalog.
This page is also available as Markdown and as JSON.
Related Urban & Civic datasets
- Boston Crime Incident Reports, 2012 to present
- Boston Property Assessment Rolls, FY2004 to FY2026
- Boston Police Field Interrogation and Observation (FIO) records, 2011 to 2025
- Analyze Boston, the City of Boston open data portal (232 datasets)
- Boston 311 Service Requests, 2011 to present
- Philadelphia Zoning Districts
- Boston Fire Incident Reporting, 2012 to 2025
- Flateau V2: PLATEAU Building Footprints and Attributes for 303 Japanese Cities (GeoParquet)