edwinhu/npx-ownership-panel

N-PX × Ownership Panel

Builds out.pass_npx — item-level ownership joined to each item's per-block observed For/Against/Abstain split — entirely on the WRDS grid.

Verified end-to-end from a clean checkout on 2026-07-25. One bash command, WRDS credentials, nothing else: 34m 46s wall at full scale, zero errors, out.pass_npx at 2,018,866 rows. Cold from nothing — including building the crosswalk and the 13F holdings — is ≈47 min. Both are in Verification; the one-command claim is measured, not asserted.

Why this exists as a skill

npx_agreement.sas sat in one project for five months doing exactly what another project needed, and nothing surfaced it. The second project rebuilt it locally in Python and shipped 144,376,253 rows to a laptop for want of a leg that already existed. The failure was findability, not capability.

The one command

scp -r scripts/* wrds:~/projects/myproject/          # + npx_link.csv (see below)
ssh wrds "cd ~/projects/myproject && bash run_pipeline.sh"

It submits the whole DAG with qsub -hold_jid and returns. SGE sequences the chain; nothing local stays alive. Disconnect, come back, collect the panel.

| Output | Grain | What it is | |---|---|---| | out.pass_npx | (itemonagendaid, block) | the deliverable — ownership panel + per-block vote direction | | out.pass | itemonagendaid | item-level ownership panel alone |

The four legs

| # | Leg | Script | Depends on | |---|---|---|---| | 1 | Mutual-fund holdings | split_s12.sas → tfn_holdings_parallel.sas ×N | — | | 2 | Institutional holdings | build_inst_own.py (SEC EDGAR 13F) — canonical. build_inst_own.sas (Thomson S34) is fallback-only | — | | 3 | N-PX fund votes | build_npx.sas (SGE array, one task per year) | leg 4 | | 4 | ISS→CRSP crosswalk | npx_linking/ → stage_npx_link.sas | — | | 5 | Short interest | build_short_interest.py — comp.sec_shortint × CCM, feeds ior_net | — |

Legs 1, 2, 4 start together. Leg 4 hard-gates leg 3 — the array hash-merges the crosswalk; without it every task opens a missing dataset and exits 0 having written nothing.

Leg 2 has two sources for one quantity, and they are NOT interchangeable. Thomson S34 decayed after 2013 and undercounts; the EDGAR scrape exists for that reason. EDGAR wins where present, S34 is fallback-only, never blended.

build_inst_own.py (EDGAR) is the canonical builder and carries every data-quality fix. build_inst_own.sas reads tfn.s34type3 only — despite what an earlier version of this table said, it does not consume EDGAR at all.

The two use different cfacshr join dates and both are right. EDGAR is as-filed and not pre-adjusted, so the correct factor is at rdate — fdate can be far later on a late filing or a carried-forward vintage, and a split in between would apply a factor from a date the shares were not held. Thomson pre-adjusts shares from rdate to fdate (reference D2: fdate is "the date Thomson's share adjustments are made to"), so there the correct factor is at fdate. Changing either to match the other introduces a bug. The SAS header says so at the join site.

One entry point, and what is reachable from it

run_pipeline.sh is the only pipeline entry point. Everything in scripts/ is reachable from it, or from the documented crosswalk prerequisite below — there is no second DAG and no dead script. If you add one, keep that true; an orphan script in a skill is an invitation to run the wrong thing, and this skill shipped three builders of one quantity before it was cleaned up.

Two roots, deliberately:

| Root | What it is | |---|---| | run_pipeline.sh | the DAG. Submits everything with qsub -hold_jid and returns | | npx_linking/ (python -m npx_linking run) | the crosswalk, built locally and scp'd in (below). Not part of the DAG because leg 3 hard-gates on its output existing |

npx_link_to_csv.py and npx_linking/family_overlay.example.csv are used by hand in that prerequisite step, not invoked by the DAG.

Legs 2 and 5 are Python and need polars; every other leg is SAS. The preflight imports them before submitting anything, so a missing package fails in one second instead of thirty minutes into a grid run.

For S12 data-quality controls (the 2017Q4 feed change, coverage end, the MFLINKS gap) use the wrds skill's references/tfn-ownership.md D5, and the detectors in skills/wrds/scripts/ownership_dq.py. Deliberately not duplicated here.

The linking chain is vendored, not reimplemented

npx_linking/ is a copy of mirror's scripts/linking/ — the implementation that produced the frozen npx_crsp_link baseline. It replaced a second, flattened single-file linker that shared zero function names with it: not drift, a reimplementation of the same six-tier ladder. Two rival linkers deciding every fund's block is the one duplication worth paying to remove.

Verified on adoption: the vendored package reproduces mirror's fingerprints exactly — npx_crsp_link 4fdf9818…, fundid_seriesid 93583072…, sec_series_master_series 5919545e….

config_obs.py is vendored with it. The chain reads 26 symbols from it: 15 paths/scalars and 11 L2_/L3B_ tuning constants — the TF-IDF n-gram range and candidate threshold, the legal-suffix and "formerly" regexes, family stopwords, the succession share bar. Those constants are the matching behaviour, so re-declaring them would fork the ladder's semantics while looking like config.

Sync the directory and config_obs.py together. A partial sync is how the two forked the first time.

Run it standalone with:

NPX_LINK_ROOT=/path/to/project python -m npx_linking run
python -m npx_linking stages        # what runs, in dependency order
python -m npx_linking fingerprint   # byte-identity of each stage output
python -m npx_linking verify        # rebuild in a sandbox and diff

Before you run: build the crosswalk

Leg 4's input is a fundid → block crosswalk you build once, locally. It is the hard half — see references/npx-crsp-linking.md.

cd scripts/npx_linking
export SEC_USER_AGENT="your name [email protected]"
./download_sec_series_class.py --out data/raw/sec_series_class
SEC_SERIES_RAW=data/raw/sec_series_class SEC_SERIES_OUT=data/processed ./build_sec_series_master.py
./smoke_test.sh 2023                       # 8 assertions, ~2 min — run this first
./pull_npx_funds.py  --out npx_funds.parquet
./pull_crsp_funds.py --out crsp_funds.parquet
./build_npx_crsp_link.py --npx-funds npx_funds.parquet --crsp-funds crsp_funds.parquet \
    --sec-series-master data/processed/sec_series_master.parquet \
    --sec-series-names-long data/processed/sec_series_names_long.parquet \
    --out npx_crsp_link.parquet
cd .. && ./npx_link_to_csv.py --in npx_linking/npx_crsp_link.parquet --out npx_link.csv

Runs from WRDS credentials alone. Links 80.8% of vote rows.

Two invariants, both in SAS

They must hold for an unsupervised bash run_pipeline.sh with no harness anywhere, so neither lives in an orchestrator.

One universe. pipeline_config.sas declares the date window, meeting types and vote results once; both legs %include it. Before it they disagreed — 2003-2024 with filters vs 2005-2025 with none — and nothing detected it.

Three hard gates in merge_panel.sas, all abort abend:

• Prerequisites: every expected output exists. -hold_jid releases on completion, not success, so a dead leg otherwise lets the merge start.
• S12 partitions: all of S12_RANGES present. A refused PostgreSQL connection (per-role cap is 7, hence -tc 6) leaves a partition missing, which shows up as an ownership-column gap the universe assertion cannot see.
• Universe: every out.meetings item exists in out.npx_items.

tfn_holdings_parallel.sas also no longer silently falls back to a full 47.4 GB tfn.s12 scan when its partition is absent — that produced plausible output and hid the gap. Override with S12_ALLOW_FULLSCAN=1 only deliberately.

Both fired during verification and caught real failures. That is the point.

Verification

STALE — re-measure before quoting. The timings and the identity digest below were measured 2026-07-25, before the data-quality pass. They describe a pipeline that no longer matches the canonical builder: leg 2 is now EDGAR (build_inst_own.py) rather than the Thomson SAS path, and leg 5 (short interest) did not exist.

Wall time should be close to unchanged — this table's own conclusion is that the critical path is tfn_holdings ×9 and the N-PX array, and leg 2 is not on it. The fixes are cheap: the msf_v2 splice is one extra query, the value = 0 filter is one predicate on an existing pass, disabling the cusip6 fallback removes 2.66M rows of join work, and the short-interest pull ran 9.5s.

The identity digest WILL change and that is intended, not a regression to chase: disabling the cusip6 fallback removes rows and the msf_v2 splice adds 2025. Re-freeze it deliberately once the corrected DAG has run clean.

A timed run includes the detector sweep (dq_panel.py, seconds, held on the merge) so the number means "a panel you can use", not "a panel that exists". This was a standing instruction that nothing implemented — no script referenced ownership_dq.py at all — so every wall time quoted before 2026-07-27 was the time to build a panel of unmeasured quality.

Clean checkout on WRDS, one bash run_pipeline.sh, 2026-07-25. Two runs, both clean:

| Run | S12 scope | Wall | ERROR lines | |---|---|---:|---:| | Reduced | 2 of 9 partitions | 12m 17s | 0 | | Full | 9 of 9, sequential PG split | 34m 46s | 0 | | Full + S12 array, build_meetings native | 9 of 9 | 36m 1s | 0 |

Identity: PASS. The last run's canonical dump is byte-identical to the frozen baseline — same 2,018,866 lines, same sha256 22f13e7679a3f9c15843116195625620dfe835a661ba6c593b1bd9d93503a955. Converting build_meetings from a PostgreSQL pass-through to a native indexed read moved no value at 12 significant digits.

The S12 array cut the partition write from 910s sequential to ~270s (9 tasks, -tc 6), but total wall did not drop: the critical path is tfn_holdings ×9 and the N-PX array, not the split. The win is real and in the wrong place to shorten the pipeline — worth having, not worth claiming as a speedup.

Gates on the full run: PREREQ mf_own_chunks=9 npx_cell_years=21/21 · UNIVERSE meetings_items=623,642 npx_items=712,466 orphans=0.

S12 partitions (237.5M rows total): 34,212,190 · 27,141,268 · 29,551,018 · 22,369,087 · 23,985,898 · 25,693,786 · 26,528,617 · 25,076,427 · 22,936,627.

N-PX leg: 140,382,295 vote rows kept, 2,130,231 cells pre-aggregation, 0 unlinked. Crosswalk: 26,686 fundids, 712,466-item frame.

Final out.pass_npx, grain (itemonagendaid, block), 2.28 GB:

    n_rows      n_items   items_no_npx      vote_rows
 2,018,866      623,642         27,294    134,723,487

block           cells       vote_rows
active        572,426      75,563,301
index         585,916      48,517,584
passive       533,516       8,045,939
asset_owner   299,714       2,596,663

Both runs produced an identical panel. The S12 leg populates mutual-fund ownership columns through MERGE_ASOF; it does not add rows. More partitions means more of those columns are non-missing, not a bigger panel.

Cold vs warm

"Warm" = the crosswalk and the 13F holdings already on disk. Cold builds them.

| Term | Time | Basis | |---|---:|---| | WARM — 4-leg pipeline | 34m 46s | MEASURED, full-scale clean-room run | | (a) SEC series/class download | 1m 43s | MEASURED — 17 vintages, 128 MB, 0.5 s inter-request sleep | | (a) build_sec_series_master | 10s | MEASURED | | (b) pull_npx_funds | 6m 31s | MEASURED — server-side aggregation over 238M rows | | (b) pull_crsp_funds | 7s | MEASURED | | (b) crosswalk ladder | 2m 30s | MEASURED — TF-IDF over 26,929 fundids | | (c) 13F EDGAR parse | 1m 23s | EXTRAPOLATED — see below | | COLD TOTAL | ≈ 47 min | sum of the above |

(c) method. One recent dense quarter measured on a 4-slot compute node, reading /wrds/sec/archives directly — never per-filing SEC.gov HTTP (edgar.md iron law; on the grid the archive is a local mount, so no rclone leg at all):

2024Q2: 7,680 filings · 1.44 GB input · 27 s parse wall · 2,489,014 rows · 66.5 MB gz output → 284 filings/s at GOMAXPROCS=4, concurrency 32.

Extrapolated across the 38 quarters 2016Q4–2026Q1, weighted by actual filings per quarter from wrdssec_all.wrds_forms — 237,094 filings, 44 GB input, ~77M output rows. Weighting is not cosmetic: a flat 38 × 2024Q2 would say 291,840 filings, +23.1% too high, because quarterly volume ranges 4,255 to 9,076 over the span.

• Serial (one 4-slot task): 13.9 min
• 38-shard SGE array, 10 concurrent (the observed slot count on this cluster): 1m 23s — the figure used in the table
• 38-shard array, full parallelism: 32 s (not claimed; the scheduler does not give 38 slots)

The 35-minute local N-PX pull is gone from the cold path entirely. The old design downloaded 144,376,253 joined rows to a laptop — measured at ~35 min sequential — before any analysis could start. The array reads risk.voteanalysis_npx on the grid and ships 2.25M cells instead. That single change is worth more than every other term in the cold budget combined, and it is why cold-from-nothing is ~47 min rather than ~80.

Straggler risk. A 21-task array over shared NFS will hit one often enough that the with-straggler figure is the planning number, not the best case. One task took 742 s against a 60 s median and the array still reported clean at 20 of 21 outputs — which is why merge_panel.sas asserts coverage.

Scope caveats

• The reduced run used 2 of 9 S12 partitions because that account's /scratch quota is 22 GB against the ~41 GB the full set needs — measured, dd fails with "Disk quota exceeded". The full run used a directory with headroom. Check quota and trim S12_RANGES in pipeline_config.sas to fit; the chain completes either way over a narrower holdings window.
• (c) is the only extrapolated term. Everything else was run.

Three defects the clean-room run surfaced, all fixed

• tfn_holdings_parallel.sas used open-code %IF, which errors on this SAS deployment. As shipped it had never run — it died before reading a row.
• split_s12.sas and run_pipeline.sh hardcoded the partition list separately. Now single-sourced from pipeline_config.sas.
• A greedy sed reading that list swallowed a ; inside a trailing comment and submitted 11 jobs for a 2-partition list — the bash/SAS divergence the shared list exists to prevent.

Data quality: run the detectors before you use the panel

This skill builds the panel; it does not certify the numbers in it. The sources have documented defects, several of which produce a complete-looking panel with plausible values, so they cannot be caught by eyeballing output.

run_pipeline.sh now does this for you — dq_sweep is the last node, held on the merge, and it reports rather than gates. Read it out of the run:

grep -rE 'PREREQ|UNIVERSE|OPTIONAL|DQ|ERROR' logs/    # every gate, one grep

Measured on the 2026-07-27 run (675,639 × 22 leg-2 panel):

DQ rows=675,639 coverage_end=0 duplicate_grain=0 join_coverage_tail=0
   unit_discontinuity=0 split_factor_ratio=2747 owner_dropout=5577
DQ impossible_ratio=11,761/378,129=3.110% testable=56.0%_of_panel

Read that denominator. impossible_ratio can only fire on rows carrying both io_total and a positive tso — 378,129 of 675,639. The remaining 297,510 (44%) are invisible to it, so "3.110%" does not mean "the panel is 96.9% clean". The check is also one-sided: a denominator that is too small trips >100%, one that is too large never trips anything and just biases ownership downward.

To run it by hand, or against a panel built elsewhere:

uv run python3 tests/ownership_dq_test.py          # 105 assertions, stdlib only
qsub -v OWNERSHIP_DQ=/path/to/ownership_dq.py run_dq.sh    # on the grid

Read skills/wrds/references/tfn-ownership.md → Known Data Defects (D1-D9) first. The ones that bite this pipeline specifically:

| | What it does to this panel | |---|---| | D1 split mis-adjustment | Thomson pre-adjusts shares wrongly around split dates — worse in S12 than 13F (40.7% vs 34.5% outlier rate at >4:1 splits). WRDS's own conclusion is that there is no clean fix; winsorize split-adjacent quarters. | | D5 S12 feed change at 2017Q4 | Legacy SP → strategic collection: +613% CUSIPs, +113% funds. A genuine coverage expansion, so no level comparison may span 2017Q4. Count-based measures are unusable across it; share-based ones are mildly contaminated. | | D8 Int8 date overflow | Not a vendor defect — yours. dt.month() * 100 overflows polars' Int8 and silently yields a valid-looking wrong date key. It once left a reference panel holding only March and December, which zeroed ownership for 49% of a panel and read as D1 for weeks. | | D9 ownership > 100% | Partly real: 13F is long-only, so lent shares are counted twice and >100% is correct for heavily shorted stocks (0.51% → 22.95% violation rate across short-interest buckets). Do not clip at 100% — that destroys real information. |

Two habits worth carrying over, both learned the expensive way here:

1. Run detect_calendar_bucket_gap on every reference/dimension table at build time, not just on the output panel. It is the one detector that catches a root cause rather than a symptom — a reference table missing a calendar bucket makes every downstream join fall back to a default, silently.
2. A lent share carries no vote for the lender. If you are using ownership as a voting weight, it overstates the block by roughly the securities-lending rate — ~1.5pp at the median, >12pp in heavily shorted names. Net it out or report robustness excluding high-short-interest firm-quarters.

References

| File | What | |---|---| | references/pipeline.md | full pipeline detail, benchmark, SAS traps | | references/npx-crsp-linking.md | the crosswalk — digit guard, trust-prefix, cross-family veto, uint32 and TNA traps | | references/linking.md | running the linking ladder; tier coverage |

Key facts

• risk.voteanalysis_npx is 238,445,215 rows / 329 GB. Never download it. Aggregate on the grid: 2.25M cells instead of 144M rows, 20.8 MB instead of 304 MB, 839s instead of a 35-minute sequential pull.
• The date range is not the analysis universe. npx.meetingdate over 2005-2025 is 237,057,808 rows; items present in vavoteresults are 144,375,860. Date alone inflates every block denominator by ~64%.
• vavoteresults is not unique on itemonagendaid, so an INNER JOIN fans out. A hash keyed on it cannot.
• Write meetingdate between "01jan&year."d and "31dec&year."d, never year(meetingdate) = &year. — a function on the indexed column defeats the 15 GB index and full-scans 329 GB per task.
• Budget for a straggler. One array task took 742s against a 60s median and the array still reported clean at 20 of 21 outputs.

See also

wrds skill — connection patterns, references/iss-voting.md for the tables themselves, references/postgres-vs-sas.md for engine choice.