UQO Working Paper No. 0001 (FIRC-funded) — From Corporate Disclosures to a Russell 3000 Strategic Project Database
Identification, Measurement, Validation, and Research Applications — Manel Kammoun, Djerrycharli Tandjambianda, Simon-Pierre Boucher (order to be confirmed; see docs/contributions.md). Version 1.1, November 2026 — revised after peer review; working draft, research database under validation (v1.0 of October 28, 2026 is the reviewed baseline, tag review-baseline-20261103); revision branch review-20261103 (Stages D–E, November 2026). Research funded by the FIRC (Fonds institutionnel de recherche et de création, UQO).
TL;DR — SPID turns 527,548 SEC filings (10-K/10-Q/8-K, 2010–Oct 2026) of 2,564 issuers (S&P 500 ∪ iShares IWV Russell 3000 proxy, 85.3 % of the index's 2,996 constituents) into 308,065 section-linked candidate project mentions and 121,099 candidate projects under the current resolution key (21-category taxonomy, status, years, locations, technologies, partners, amounts). The paper documents the universe, the distributed acquisition/parsing architecture, the LLM extraction contract (two runs under one prompt lineage), the resolution rule and its failure modes, descriptive patterns, and a validation protocol — stating explicitly which gates are met and which are not.
Layout
paper/ main.tex, sections/, appendix/, tables/ (generated), figures/ (generated), references.bib → main.pdf
code/ export_stats.py (master node, read-only DuckDB → data/) · make_tables.py (data/ + results/ → tables, figures, macros)
code/audit/ Stage D read-only audit scripts a01…a13 (M3U96b) and the frozen pipeline snapshot spid_snapshot/ (SHA256SUMS)
data/ CSV exports + stats.json (export_stats.py) · documented_inputs.json (the few non-query values, each with its source)
datamodel_audit.csv (Appendix C rows) · example_project.json (written by make_tables.py) · metadata/ (universe artefacts + hashes, N10)
results/ Stage D audit outputs (≤ 5 MB each, with manifest.json) · inline_numbers.json · russell_coverage.json · tables_diff.txt
docs/ implementation_audit.md · universe_definition.md · universe_coverage.md · project_ontology.md · data_dictionary.md
DATA_AVAILABILITY.md (DAS draft, provenance table, determinism paragraph) · annotation_protocol.md (N11) · contributions.md · research_log.md
tests/ test_tables.py — export reconciliation, byte-identical regeneration, no data literal in the generator
reproducibility/ validation_report.md (verdict), environment/requirements-exhibits.txt; node lock files at the root (requirements.lock, .python-version, environment.md)
run_all.sh single entry point (exhibits → tests → paper; --with-node / --with-audit for the node steps)Database and pipeline code: uqo-research-db.git (revision 2324b58) on the UQO Git server; raw filings are not redistributed (docs/DATA_AVAILABILITY.md).
Rebuild the exhibits
./run_all.sh # laptop: python3 -I code/make_tables.py → tests → latexmk (≈ 1 min)
./run_all.sh --with-node # master node (M3U96b): export_stats.py first (read-only DuckDB, threads=4, ≈ 10 min + bootstrap)
./run_all.sh --with-audit # master node: Stage D audits (code/audit/run_all.sh) before the exhibits
python3 -I -m pytest -q tests # checks onlymake_tables.py never writes data/stats.json; the example project of Table 9 goes to data/example_project.json. Inline numbers quoted in the text are LaTeX macros \SPID<name> generated in paper/tables/numbers.tex (loaded in the preamble of main.tex); their value, source file and formula are listed in results/inline_numbers.json.
Program-to-exhibit map
| exhibit | generated file | script | inputs |
|---|---|---|---|
| Table 1 universe reconciliation | paper/tables/tab_reconciliation.tex |
make_tables.py |
stats.json:universe, documented_inputs.json:iwv_equity_lines_reported |
| Table 2 filings by form | tab_filings.tex |
make_tables.py |
filings_by_form.csv, parse_status.csv, stats.json |
| Table 3 sections and ledger (+ note) | tab_sections.tex, tab_sections_note.tex |
make_tables.py |
sections_by_form.csv, ledger_by_form.csv, mentions_by_form.csv, results/ledger/table3_ledger_vs_stored.csv, results/ledger/ledger_collisions.csv |
| Table 4 items in scope | tab_items.tex |
make_tables.py |
ledger_10k_items.csv, ledger_10q_items.csv, results/ledger/gate_pass_by_form_item.csv |
| Table 5 projects by type (+ note) | tab_types.tex, tab_types_note.tex |
make_tables.py |
projects_by_type.csv, projects_by_type_ci.csv (N13), results/resolver/overmerge_by_type.csv, amount_dist.csv |
| Table 6 by sector | tab_sectors.tex |
make_tables.py |
projects_by_sector.csv, companies_by_sector.csv |
| Table 7 status / mentions / span | tab_status.tex |
make_tables.py |
projects_by_status.csv, mentions_per_project.csv, duration.csv |
| Table 8 amounts and projects per issuer (+ note) | tab_amounts.tex, tab_amounts_note.tex |
make_tables.py |
amount_dist.csv, projects_per_issuer.csv, stats.json:amount_* |
| Table 9 provenance example | tab_provenance.tex |
make_tables.py |
example_provenance.csv, documented_inputs.json:snippet_chars |
| Table 12 data-model audit (longtable) | tab_datamodel.tex |
make_tables.py |
data/datamodel_audit.csv |
| Tables 14–17 (appendix) | tab_coverage_year.tex, tab_top_issuers.tex, tab_confidence.tex, tab_graph.tex |
make_tables.py |
issuers_by_year.csv, filings_by_year_form.csv, mentions_by_year.csv, projects_by_first_seen_year.csv, top_issuers.csv, confidence_dist.csv, kg.csv |
| Appendix A taxonomy | tab_taxonomy.tex |
make_tables.py |
code/audit/spid_snapshot/taxonomy.py |
| Appendix B prompt and gate regex | prompt_verbatim.tex, gate_regex.tex |
make_tables.py |
code/audit/spid_snapshot/llm.py, rules.py (read with ast, never retyped) |
Stage D tables (when results/ is present) |
tab_spec_curve.tex, tab_top_name_tokens.tex, tab_overmerge.tex, tab_june_october.tex, tab_table3_calls.tex, tab_grounding.tex, tab_focus.tex, tab_cohort.tex, tab_fp_classes.tex, tab_timeseries_cohort.tex, tab_benchmark_8k.tex, tab_rules_agreement.tex |
make_tables.py (generic CSV → tabular) |
results/<package>/*.csv from code/audit/a*.py |
| Figure 1 coverage (integer year ticks) | paper/figures/fig_coverage.pdf |
make_tables.py |
issuers_by_year.csv |
| Figure 3 projects by type | fig_types.pdf |
make_tables.py |
projects_by_type.csv |
| Figure 4 confidence | fig_confidence.pdf |
make_tables.py |
confidence_dist.csv |
| Figure 5 mentions and new projects by year (y from 0; second panel per issuer; 2010 / 2026 shaded) | fig_mentions_year.pdf |
make_tables.py |
mentions_by_year.csv, projects_by_first_seen_year.csv |
| Figure 2 pipeline diagram | fig_pipeline.pdf |
hand-drawn (TikZ/PDF, not generated) | — |
inline numbers (\SPID…) |
paper/tables/numbers.tex, results/inline_numbers.json |
make_tables.py |
all of the above; results/**.json (N1, N3, L5) |
data/*.csv, data/stats.json |
29 CSV + 3 new (ledger_collisions, cohort_by_run, calls_by_form) + projects_by_type_ci.csv |
export_stats.py (node, read-only) |
edgar_research.duckdb, spid.duckdb, data/metadata/*.json, run logs |
Figure numbers follow the order of main.tex; the .png copies under paper/figures/ are not produced by any script and are kept only for the Word export (code/tex2docx).