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
Every table is a self-contained float written by make_tables.py to paper/tables/<name>.tex (caption, label, threeparttable notes, generated-by comment); every figure is a vector PDF plus a float wrapper paper/figures/<name>.tex. The prose files only \input them. Labels are tab:<name without tab_> and fig:<name without fig_>.
| exhibit (label) | generated file | script | inputs |
|---|---|---|---|
| tab:reconciliation universe reconciliation + Russell coverage | tab_reconciliation.tex |
make_tables.py |
stats.json:universe, documented_inputs.json, results/russell_coverage.json, results/universe/universe_artefacts.json |
| tab:filings filings by form | tab_filings.tex |
make_tables.py |
filings_by_form.csv, parse_status.csv, results/universe/filings_status_missing.csv |
| tab:sections ledger vs stored, calls | tab_sections.tex |
make_tables.py |
sections_by_form.csv, ledger_by_form.csv, mentions_by_form.csv, results/ledger/table3_ledger_vs_stored.csv, ledger_collisions.csv |
| tab:ledger_calls rows / calls / productive by form x run | tab_ledger_calls.tex |
make_tables.py |
results/ledger/gate_pass_by_form_run.csv, gate_pass_by_run.csv, gate_pass_totals.json |
| tab:outage outage reconciliation | tab_outage.tex |
make_tables.py |
results/ledger/log_summary.json, throughput.json, backfill_block.json, resume_batch1_vs_expected.csv |
| tab:items items in scope (+ calls, shown share, financing share) | tab_items.tex |
make_tables.py |
ledger_10k_items.csv, ledger_10q_items.csv, results/ledger/gate_pass_by_form_item.csv, results/focus/, results/fp_classes/ |
| tab:fp_classes financing-class mentions | tab_fp_classes.tex |
make_tables.py |
results/fp_classes/*.csv, summary.json |
| tab:focus focus-window coverage | tab_focus.tex |
make_tables.py |
results/focus/focus_coverage_by_form_item.csv, _by_form.csv, stats_keys.json |
| tab:types projects by category (+ CI, excl. financing, over-merge) | tab_types.tex |
make_tables.py |
projects_by_type.csv, projects_by_type_ci.csv, results/fp_classes/table5_restated_excl_financing.csv, results/resolver/overmerge_by_type.csv |
| tab:sectors by sector | tab_sectors.tex |
make_tables.py |
projects_by_sector.csv, companies_by_sector.csv |
| tab:cohort cohort x run, issuer-level OLS | tab_cohort.tex |
make_tables.py |
results/runs/table6_cohort.csv, table6_cohort_regression.json |
| tab:status status / mentions / span | tab_status.tex |
make_tables.py |
projects_by_status.csv, mentions_per_project.csv, duration.csv, results/resolver/ties_idempotence.json |
| tab:amounts amounts and projects per issuer | tab_amounts.tex |
make_tables.py |
amount_dist.csv, projects_per_issuer.csv, stats.json:amount_*, results/grounding/amount_tail.json |
| tab:provenance provenance example | tab_provenance.tex |
make_tables.py |
example_provenance.csv, documented_inputs.json:snippet_chars, results/resolver/composition.json |
| tab:spec_curve specification curve of the project count | tab_spec_curve.tex |
make_tables.py |
results/resolver/spec_curve.csv, composition.json, ties_idempotence.json |
| tab:grounding mechanical grounding | tab_grounding.tex |
make_tables.py |
results/grounding/grounding_by_group.csv, grounding_element_level.csv, grounding_all_structured.csv, fallback_bounds.json |
| tab:benchmark_8k 8-K item-code consistency | tab_benchmark_8k.tex |
make_tables.py |
results/benchmark_8k/item_vs_type.csv, summary.json |
| tab:rules_agreement rules vs LLM, focus recall bound | tab_rules_agreement.tex |
make_tables.py |
results/rules/agreement_by_form.csv, agreement_overall.csv, focus_recall_loss.csv |
| tab:datamodel data-model audit (longtable, no float) | tab_datamodel.tex |
make_tables.py |
data/datamodel_audit.csv |
| tab:taxonomy, Appendix B prompt and gate regex | tab_taxonomy.tex, prompt_verbatim.tex, gate_regex.tex |
make_tables.py |
code/audit/spid_snapshot/{taxonomy,llm,rules}.py (read with ast, never retyped) |
| tab:june_october, tab:universe_artefacts, tab:coverage_year, tab:timeseries_cohort, tab:top_issuers, tab:confidence, tab:graph, tab:top_name_tokens, tab:overmerge (appendix) | tab_<name>.tex |
make_tables.py |
results/resolver/, results/universe/, results/timeseries/, data/*.csv |
| fig:coverage, fig:types, fig:confidence, fig:mentions_year, fig:grounding, fig:spec_curve, fig:focus | fig_<name>.pdf + .png + .tex float |
make_tables.py (Okabe-Ito palette, markers + line styles, 9 pt serif, width = textwidth) |
data/*.csv, results/timeseries/, results/grounding/, results/resolver/, results/focus/ |
| fig:pipeline pipeline diagram | TikZ in paper/sections/methodology.tex |
hand-drawn (not generated) | counts via \SPID macros |
inline numbers (\SPID…) |
paper/tables/numbers.tex, results/inline_numbers.json |
make_tables.py |
all of the above; results/**.json |
data/*.csv, data/stats.json |
29 CSV + ledger_collisions, cohort_by_run, calls_by_form, projects_by_type_ci |
export_stats.py (node, read-only; IS DISTINCT FROM amount_raw check, October wall-clock from the first October flush, deterministic ORDER BY, spill directory outside the repo) |
edgar_research.duckdb, spid.duckdb, data/metadata/*.json, run logs |
Exhibit numbers follow the order of main.tex. The .png copies under paper/figures/ are written by make_tables.py at documented_inputs.json:png_dpi for the Word export (code/tex2docx). The render-and-inspect audit of every exhibit is recorded in UQO-WP-REVIEW/quality_control/_FIRC-0001_{table,figure}_audit.csv.