Build the meeting-level proxy-voting × ownership panel on the WRDS SGE grid — ISS N-PX fund votes reduced to (item × block) direction cells, joined to institutional and mutual-fund ownership. Use when working with risk.voteanalysis_npx, N-PX fund-level votes, ISS→CRSP fund linking, index/passive/active voting blocks, or a proxy-voting panel that needs ownership attached.
Build the meeting-level proxy-voting × ownership panel on the WRDS SGE grid — ISS N-PX fund votes reduced to (item × block) direction cells, joined to institutional and mutual-fund ownership. Use when working with risk.voteanalysis_npx, N-PX fund-level votes, ISS→CRSP fund linking, index/passive/active voting blocks, or a proxy-voting panel that needs ownership attached.
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.
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
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.mdD5, 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_link4fdf9818…, fundid_seriesid93583072…,
sec_series_master_series5919545e….
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 your@email.edu"
./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. Re-freeze it deliberately
once the corrected DAG has run clean.DONE — re-frozen 2026-07-28, see below.
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 (2026-07-25 baseline, now superseded): that run's dump was
byte-identical to its frozen baseline — 2,018,866 lines,
sha256 22f13e7679…955. Converting build_meetings from a PostgreSQL
pass-through to a native indexed read moved no value at 12 significant digits.
What moved the digest, all deliberate, none a regression to chase:
change
effect
leg 2 Thomson S34 → SEC EDGAR
universe and level shift
CRSP SIZ → CIZ (#99/#100)
+3.2–5.0% distinct permnos per year-end; cfacshr basis changes, so io_total LEVELS are not comparable across this boundary — ratios are
denominator no longer filtered by the analysis universe (#115)
untestable rows 50.9% → 1.1%
cusip8 not cusip6 (#109)
drops spurious issuer-level attachments
__no_fund_votes__ block label (#126)
the block column now carries a label where it was null on 26,924 rows
Known and open at freeze time, stated so the baseline is not read as
"everything reconciles":
Against mirror's previous artifact the panel has 53,555 fewer vote-carrying
cells and 22,473 fewer items with vote data. Consistent with the tighter
universe, but that is an expectation, not a measurement — undecomposed.
15 rows in the inst_own reconciliation are unexplained (0.002%), recorded
WONT-FIX.
Both are differences from a superseded artifact rather than internal
inconsistencies, which is why they do not block the freeze. Full detail:
mirror docs/investigations/2026-07-28_grid_run_reconciliation.md.
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.
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):
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):
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:
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.
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.
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.
