Using APIs to Download Bulk Spending Data Across Jurisdictions
Government spending data becomes far more useful when it can be collected, standardized, and analyzed across jurisdictions. A single portal may reveal payments in one country or state, but a repeatable download workflow makes it possible to compare agencies, suppliers, programs, dates, and spending categories across several public finance systems.
Publicspending.net brings together payment records from the United States, Greece, Australia, the United Kingdom, Chicago, Massachusetts, and Alaska. Its datasets, statistics, graphs, bulk downloads, SPARQL endpoint, and ontology-based tools support both exploratory research and automated analysis.
Working with multiple sources requires more than sending requests to several endpoints. You need a clear scope, consistent identifiers, a plan for pagination and file storage, and a method for documenting differences in fiscal years, currencies, classifications, and publication practices. The goal is a dataset that can be reproduced and interpreted correctly.
Define The Research Scope First
Begin by specifying the jurisdictions, time period, spending unit, and financial measure required for the project. “Health spending” might mean payments made by a health department, a broader government function, or a set of programs identified through agency names. “Defense spending” may likewise include procurement, personnel, grants, or only payments attributed to a particular department.
Write these choices into a short research specification before downloading records. Include the desired fields, date range, currency treatment, supplier information, agency hierarchy, and whether cancelled or amended payments should be retained. This prevents a common mistake: downloading a large volume of data before deciding what the records mean.
Jurisdictions can also publish at different levels of detail. One source may provide individual transactions, while another offers monthly totals or payments grouped by recipient. Preserve the original granularity rather than forcing every source into the same structure immediately. A reliable comparison begins by recording what each dataset actually represents.
Choose Between Bulk Files, APIs, And SPARQL
Bulk downloads are generally the most efficient choice for a complete historical extract. A compressed CSV, JSON, or similar file can be downloaded once, stored locally, and processed repeatedly without placing unnecessary demand on a public service. Bulk files are especially useful for large payment registers, annual archives, and research that requires many fields.
An API is better suited to incremental retrieval, focused queries, and scheduled updates. It can retrieve records for a selected period, agency, or jurisdiction and can make it easier to automate a regular refresh. When using an API, inspect its documentation or metadata for endpoint paths, accepted parameters, page limits, response formats, rate limits, and update timestamps.
SPARQL is useful when the data is exposed through linked concepts and relationships rather than only flat transaction rows. It can help identify entities, agencies, classifications, and jurisdictional connections before a detailed extract is made. A practical workflow may use SPARQL to discover the relevant entities, an API for targeted records, and bulk files for the full historical archive.
The most suitable method depends on the question. A researcher comparing a few departments over one year may need only a filtered API query. A project building a multi-year spending warehouse will usually benefit from bulk downloads, followed by local filtering and validation.
Plan A Repeatable Download Workflow
Create a separate configuration for each jurisdiction instead of embedding all request details in one script. The configuration can store the source name, download address or API endpoint, date parameters, page size, expected file format, currency, and the date on which the extract was retrieved. This makes the process easier to audit and adapt when a portal changes.
For API requests, retrieve data in pages and save each response before moving to the next page. Some services use page numbers, while others use offsets, cursors, or links supplied in the response. Stop only when the service indicates that no more records remain. Avoid assuming that an empty page always means the dataset is complete; a temporary error or malformed parameter can produce the same result.
A robust downloader should retry temporary network failures with increasing delays and record unsuccessful requests in a log. It should also check the HTTP status, content type, file size, and basic structure of every response. Saving raw responses alongside processed files preserves an evidence trail and allows a transformation error to be corrected without downloading everything again.
For bulk archives, calculate a checksum when possible and retain the original filename. Record the archive’s publication date, coverage period, source page, and any version number. These details matter because public datasets may be corrected, replaced, or republished without retaining the exact same file contents.
Normalize Records Without Erasing Meaning
After retrieval, map different schemas into a shared analytical model. Common fields include transaction identifier, payment date, fiscal year, amount, currency, payer agency, recipient, description, program, geographic area, source jurisdiction, and source URL. Keep the original fields too, because a standardized field can hide distinctions that are important for later research.
Dates require particular care. Calendar years, fiscal years, payment dates, invoice dates, and award dates are not interchangeable. Store dates in an unambiguous format such as ISO 8601, but retain the original date value if the source uses partial or unusual formats. Add a separate field explaining which date was selected for analysis.
Amounts should be stored as numeric values with a clear currency code and scale. Do not convert currencies by simply assuming that every amount is in the currency associated with the country. Some records may represent grants, contracts, refunds, or adjustments with different conventions. Keep both the published amount and any converted amount, along with the exchange-rate source and conversion date.
Names and identifiers also need a documented approach. Agency names may change over time, and supplier names may contain spelling variants, legal suffixes, or multiple registration identifiers. Use stable identifiers when available, but do not merge entities solely because their names appear similar. A separate crosswalk can propose relationships while preserving the original values.
| Analytical need | Useful source method | Main strength | Important caution |
|---|---|---|---|
| Complete historical archive | Bulk download | Efficient for large volumes and repeat processing | Track file versions and coverage |
| Scheduled incremental update | API | Automates retrieval of new or changed records | Handle pagination, limits, and retries |
| Entity and relationship discovery | SPARQL endpoint | Connects agencies, concepts, and jurisdictions | Learn the ontology and query structure |
| Narrow exploratory question | Filtered API query | Fast access to selected records | Results may omit fields or older records |
| Cross-jurisdiction comparison | Combined local dataset | Enables consistent analysis and visualization | Document schema and classification differences |
Compare Jurisdictions With Explicit Assumptions
Cross-jurisdiction analysis should begin with comparability, not visual presentation. A large payment total in one source may reflect a centralized procurement system, while another jurisdiction may distribute similar costs among departments, public corporations, or regional authorities. Apparent differences can therefore result from reporting structures rather than policy choices.
Use a jurisdiction field on every normalized record, even when it appears in the source filename or directory. Add source-specific classification fields beside any harmonized category. For example, a local “health” category can be mapped to a shared health grouping, while the original department, function code, and description remain available for review.
Currency conversion should be selected according to the research purpose. Nominal comparisons can retain each jurisdiction’s published currency and show separate totals. A purchasing-power or real-value analysis requires a stated conversion method, reference year, and price adjustment. Never present converted values without identifying the assumptions behind them.
For a policy-oriented comparison, inspect the underlying categories before interpreting the totals. A focused departmental spending comparison illustrates why department definitions and expenditure groupings matter when evaluating health and defense figures. The same discipline applies when extending a comparison across countries, states, or cities.
Validate Coverage And Data Quality
Validation should occur at both the request level and the analytical level. At the request level, check that every jurisdiction returned data, that all expected pages were collected, and that the date range in the response matches the requested range. At the analytical level, check totals, duplicate identifiers, missing dates, negative values, currency codes, and unexpectedly large transactions.
Compare downloaded totals with figures published in the source portal where those figures are available. The totals may not match exactly if the portal applies exclusions, uses a different update date, or groups amendments differently. That discrepancy is useful information, provided it is recorded rather than silently corrected.
Look for gaps in time series data. A missing month may reflect delayed publication, a changed agency structure, or a broken query parameter. Sudden drops in the number of records can indicate a source redesign or a shift from transaction-level reporting to summarized data. Plot record counts and total amounts by month or year to make these changes visible.
Keep a data-quality report with each release of your combined dataset. It can include row counts, date minima and maxima, null rates, duplicate counts, unmatched category values, and the number of records received from each jurisdiction. This report turns a one-time download into a traceable research product.
Automate Storage And Provenance
Use a directory structure that separates raw downloads, intermediate files, normalized data, validation reports, and final exports. A typical layout might contain one folder for each jurisdiction and subfolders for retrieval dates or publication periods. Avoid overwriting source files during cleaning; create new outputs instead.
A small metadata file should accompany every extract. Include the source, endpoint or file location, request parameters, retrieval timestamp, software version, schema version, row count, checksum, and transformation notes. If an API response includes a continuation token or update marker, preserve it so that later incremental downloads can resume accurately.
Automation should be designed for safe interruption. Save completed pages immediately, maintain a manifest of downloaded items, and allow the process to restart without duplicating existing files. For very large data collections, use compressed columnar formats such as Parquet after the raw files have been preserved. They support faster local queries and often reduce storage requirements.
Treat credentials and personal information carefully. Public spending records can contain supplier contacts, addresses, or other sensitive details even when the data is legally published. Store API keys outside source code, limit access to downloaded files where appropriate, and follow the terms and privacy guidance associated with each jurisdiction.
Recommendations For A Reliable Multi-Source Pipeline
A practical implementation can remain simple if its assumptions are visible and its stages are separate.
- Start with one jurisdiction and complete the full process from retrieval through validation before adding more sources.
- Preserve raw API responses and bulk archives so every transformed value can be traced back to published data.
- Use stable identifiers and source-specific fields before attempting to merge agencies, suppliers, or spending categories.
- Log requests, pagination results, errors, checksums, and row counts in machine-readable metadata.
- Publish the schema, conversion rules, and known limitations alongside any comparative statistics.
Once the pipeline works, schedule incremental retrievals according to each source’s publication cycle. A daily process may be unnecessary for a quarterly archive, while a frequently updated payment register can benefit from a regular cursor-based refresh. The schedule should follow the data, not an arbitrary automation target.
The finished dataset can support dashboards, statistical analysis, procurement research, budget monitoring, and reproducible academic work. It can also reveal where comparisons are not defensible, which is an important result in public finance research. A carefully documented limitation is more valuable than a precise-looking number built from incompatible records.
Download a focused set of records from Publicspending.net, preserve the source metadata, and build the normalized extract step by step. With clear assumptions, resilient API handling, and transparent validation, data from multiple jurisdictions can become a dependable foundation for examining how public money is collected, allocated, and spent.