# Boston Checkbook Explorer city expenditures, FY2012 to present

## Facts

- 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: public, readable without an account
- Category: Urban & Civic
- Source: https://data.boston.gov/dataset/checkbook-explorer
- Page: https://scigantic.com/archive/446b29e0-5c9f-5254-b137-6c1c18992947
- Keywords: Urban & Civic, CSV

## About

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.

## Load the data

```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

| Field | Type |
| --- | --- |
| `_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:

```text
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.csv
```

Example 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`

## Open it

- In a hosted notebook with the data mounted: https://scigantic.com/archive/446b29e0-5c9f-5254-b137-6c1c18992947
- Full record as JSON (schema card included): https://api.scigantic.com/api/archives/446b29e0-5c9f-5254-b137-6c1c18992947
- Over MCP: `get_schema_card` with id `446b29e0-5c9f-5254-b137-6c1c18992947` at https://api.scigantic.com/mcp

## Related topics

- [CSV datasets](https://scigantic.com/topics/csv)
- [Urban & Civic datasets](https://scigantic.com/topics/urban-and-civic)
