Wrds

WRDS (Wharton Research Data Services)

Reusable patterns for connecting to WRDS and querying its databases from Python.

Prerequisites

1. Install the client library:

``bash pip install wrds ``

1. Credentials live in a libpq .pgpass file with the line format:

`` wrds-pgdata.wharton.upenn.edu:9737:wrds:your_wrds_username:PASSWORD ` Point the environment variable PGPASSFILE at that file, or place it at the OS-default libpq location (%APPDATA%\postgresql\pgpass.conf on Windows, ~/.pgpass elsewhere). Set $WRDS_USERNAME (or $PGUSER`) to your account.

1. Never hardcode passwords in scripts, CLAUDE.md, or any version-controlled file. The .pgpass mechanism handles authentication automatically.

Authentication: fail fast, never retry

WRDS locks an account after a few consecutive failed logins, and unlocking it requires a request to WRDS support. The wrds package makes this easy to trigger by accident. wrds.Connection() authenticates through the .pgpass file, and when that file is missing or its password has expired, the package silently falls back to an interactive input()/getpass() prompt and then retries. In an unattended agent session the prompt has no human to answer it, so the run either hangs on stdin or the attempt fails and gets repeated, where each repeat is another failed login against the account.

To prevent that, always connect through the bundled guard scripts/wrds_connect.py, and never call wrds.Connection() directly. The guard validates .pgpass offline before touching the network, suppresses the interactive prompt, makes at most one connection attempt, and on any authentication problem raises WRDSAuthError carrying an explanatory message rather than prompting or retrying.

When a connection raises WRDSAuthError, stop. Surface the message to the user and wait for them to refresh the credentials. Do not re-run the script, do not call wrds.Connection() as a fallback, and do not enter a password, because every retry moves the account closer to a lockout. Recovery is the user's to perform: they update the password in the file PGPASSFILE points to, whose single line has the form wrds-pgdata.wharton.upenn.edu:9737:wrds:your_wrds_username:PASSWORD.

An offline credential check, which costs zero login attempts, is worth running before a batch of WRDS work:

python ~/.claude/skills/wrds/scripts/wrds_connect.py --check

Connection Pattern

import os
import sys
sys.path.insert(0, os.path.expanduser("~/.claude/skills/wrds/scripts"))
from wrds_connect import connect, WRDSAuthError

try:
    db = connect()                 # one guarded attempt; account from $WRDS_USERNAME/$PGUSER
except WRDSAuthError as e:
    print(e)                       # STOP — surface to the user, do not retry
    raise
try:
    df = db.raw_sql("SELECT * FROM comp.funda LIMIT 10")
finally:
    db.close()

connect() resolves the account and the credentials file at call time, so a different login needs no code change. The account is taken from username=, then $WRDS_USERNAME, then $PGUSER — with none of those set the guard raises WRDSAuthError instead of guessing an account; the credentials file is taken from pgpass=, then $PGPASSFILE, then the OS-default libpq location. A file named explicitly, by the argument or $PGPASSFILE, is authoritative, so a missing path fails fast rather than silently falling back to the default account. When a password is supplied through password= or $WRDS_PASSWORD, connect() authenticates with the username and that password directly and the .pgpass file is bypassed. The function returns the ordinary wrds.Connection object that the rest of this skill documents.

To work as another account or against another credentials file, set the variables in the shell before invoking any WRDS script — no edit to the skill or your analysis code is required:

$env:WRDS_USERNAME = "alice"
$env:PGPASSFILE    = "D:\creds\alice.pgpass"
python "$env:USERPROFILE\.claude\skills\wrds\scripts\wrds_connect.py" --check

Using credentials supplied in chat

By default the account comes from $WRDS_USERNAME/$PGUSER with the .pgpass file, and a bare request such as "use the wrds skill" should use that environment unchanged. When the user instead supplies a userid and password in the request — for example, "use the wrds skill with userid jdoe and password …" — authenticate as that account for the session by passing the credentials through the environment, scoped to the command that runs the WRDS work. Shell state does not persist between separate commands here, so setting the variables inline in the same invocation keeps the password out of any file and confined to that one run:

$env:WRDS_USERNAME = "jdoe"; $env:WRDS_PASSWORD = ""; python your_wrds_script.py

A username on its own, with no password, is looked up in the .pgpass file as usual; a supplied password switches connect() to direct authentication and the .pgpass file is bypassed. The fail-fast contract is unchanged: a wrong password counts as one failed login, and WRDS locks the account after a few, so when connect() raises WRDSAuthError here, stop and ask the user to confirm the userid and password instead of retrying. Never write the password into a script or any other file, and do not echo it back.

Schema-peek helper

Before writing a real query against an unfamiliar WRDS table, use the bundled exploration script:

python ~/.claude/skills/wrds/scripts/peek_tables.py

scripts/peek_tables.py takes a list of (library, table) pairs and prints each table's column list, types, comments, and a few sample rows — far faster than navigating the WRDS web manual. Edit the TABLES list at the top of the script for whatever you're exploring. The default list maps the SEC Analytics Suite (DEF 14A + CIK linking tables) that this skill documents.

Core Operations

| Operation | Method | |-----------|--------| | List all databases | db.list_libraries() | | List tables in a database | db.list_tables(library='comp') | | Describe a table's columns | db.describe_table(library='comp', table='funda') | | Raw SQL query | db.raw_sql("SELECT ...") | | Fetch table as DataFrame | db.get_table(library='comp', table='funda', rows=10) |

raw_sql() and get_table() both return pandas DataFrames. Convert to polars downstream if needed for performance.

Available Libraries

Databases this skill documents (availability depends on your WRDS subscription; non-exhaustive):

| Library | Description | |---------|-------------| | comp | Compustat (fundamentals, segments, pricing) | | crsp | CRSP (stock prices, returns, events) | | factset | FactSet (ownership, fundamentals, prices) | | ibes | I/B/E/S (analyst estimates, recommendations) | | wrdsapps | WRDS-derived datasets (linking tables, beta suites) | | wrds_sec_search, wrdssec_all, wrdssec_common | SEC EDGAR filings mirror — see "SEC Analytics Suite" below |

To discover others: db.list_libraries().

FactSet Ownership Tables

FactSet ownership tables use an _eq suffix for equity-specific views. These are the most commonly used:

| Table | Description | |-------|-------------| | factset.own_inst_stakes_detail_eq | Institutional position-level holdings (columns: factsetentityid, fsymid, asofdate, position, indirectoptions, sourcecode, currentflag) | | factset.own_stakes_detail_eq | All ownership stakes (institutional + insider) | | factset.own_fund_detail_eq | Fund-level holdings detail | | factset.own_sec_coverage_eq | Security identifier concordance (fsym_id to ISIN, SEDOL, CUSIP) | | factset.own_sec_entity_eq | Security-to-entity mapping | | factset.own_sec_prices_eq | Security prices from FactSet | | factset.own_ent_institutions | Institution metadata (name, type, country) | | factset.own_ent_funds | Fund metadata | | factset.own_sec_13f_reportable_eq | 13F-reportable securities |

Other useful FactSet tables (no _eq suffix):

| Table | Description | |-------|-------------| | factset.sym_entity | Entity master (entity names, types) | | factset.sym_coverage | Symbol coverage and identifiers | | factset.sym_isin | ISIN identifiers | | factset.sym_cusip | CUSIP identifiers | | factset.sym_sedol | SEDOL identifiers | | factset.sym_sec_entity | Security-to-entity linkage | | factset.edm_standard_entity | Standardized entity data |

SEC Analytics Suite (EDGAR filings)

SEC EDGAR filings are mirrored on WRDS, with filing payloads stored in-database for the common form types. Speed comparisons with EDGAR depend on the workload and have not been benchmarked here; the SEC's current fair-access cap is 10 req/sec with a declared user agent. Use WRDS when the query is filtered (specific CIKs, date range, form type), you need to join filings to Compustat / CRSP / FactSet in the same database, or you want to avoid building and maintaining a rate-limited scraper. Use EDGAR directly when you need every filing of a given form type at scale — pair it with the SEC index files / full-index feed, on-disk caching, and compliant throttling.

| Library | What it has | |---------|-------------| | wrds_sec_search | Per-form-type tables (filing_def, filing_10_k, filing_10_q, filing_8_k, filing_13f_hr, filing_sc_13d, filing_sc_13g, filing_pre, filing_4, etc.) with the filing payload in a TEXT column and a filing_tsv TSVECTOR GIN index for full-text search. Pre-processing of the ` block varies by era — see warning below. Also includes registrant (accession × CIK × role with registrant metadata) and filingview (filing-level view with a registrants JSONB column) | | wrdssecall | Master indexes across all ~27M filings. wrdsforms has accession-level rows (CIK, form, fdate, **accession**) and is the right table for accession joins. forms is CIK × fdate × form **without an accession column** — useful for time-series counts, not for joining to filing* | | wrdsseccommon | Form-code dictionary (secformdescriptions; column is form) and CIK linking (wciklinkgvkey, wciklinkcusip, wciklinkticker, wciklink_names`) |

> ⚠️ HTML preservation in wrds_sec_search.filing_* varies by era — sample before parsing. Modern large filings appear stripped: Tesla 2024 DEF 14A (accession 0001104659-24-053333, 3.2 MB) has 51 `/ SGML wrapper tags and zero //// tags. Older filings can retain table markup: 1994-era accessions in filingdef (0000890613-94-000001), filing10k (0000912057-94-000263), filing8k (0000088948-94-000001), and filingsc13d (0000899657-94-000005) still contain blocks. The pre-processing rule is **not** corpus-wide. Re-run scripts/checkhtmlpreservation.py against the exact table, form, and date range you intend to parse before committing. > > Routing by extraction goal: > - **WRDS filing text** for full-text search (use the filingtsv GIN index), keyword filtering, NLP, LLM-based extraction over running prose, and any task tolerant of missing HTML in the modern slice. > - **EDGAR raw HTML** (e.g. via edgartools`) when you need DOM structure — Item 403 ownership tables, exec-comp tables, footnote-to-row anchors — and your sample shows the WRDS slice is stripped. > - Hybrid: use WRDS as a filtered accession index (form / date / CIK / FTS), fetch only the filings you actually parse from EDGAR.

Proxy statements (DEF 14A) live in wrds_sec_search.filing_def — 196K DEF 14A filings, 1994 → present. All DEF variants (DEFA14A additional, DEFM14A merger, DEFC14A contested, DEFR14A revised, DEFS14A special-meeting, DEFN14A non-management) share this table; filter on form. Preliminary proxies (PRE 14A and variants) are in wrds_sec_search.filing_pre.

# WRDS as filterable index — cheap, fast, joinable to Compustat/CRSP
accessions = db.raw_sql("""
    SELECT f.accession, r.cik, r.role, f.filing_date
    FROM wrds_sec_search.filing_def AS f
    JOIN wrds_sec_search.registrant AS r ON f.accession = r.accession
    WHERE f.form = 'DEF 14A' AND f.filing_date >= '2020-01-01'
""")
# Then fetch raw HTML from EDGAR by accession for each row that needs parsing.

wrds_sec_search.filing_* tables do not expose cik as a SQL column. Three routes to attach CIK, in preference order:

1. wrds_sec_search.registrant (preferred) — clean (accession, cik, role) triples with registrant metadata.
2. wrds_sec_search.filing_view.registrants — JSONB array of registrants per filing; flatten with jsonb_array_elements.
3. wrdssec_all.wrds_forms (fallback) — works syntactically (TEXT ↔ VARCHAR(20) on accession), but is many-to-many for material cases: ~6,000 of ~357K filing_def accessions match multiple wrds_forms rows (max observed ≈110 CIK rows for a single accession), and ~37 very recent accessions can be missing from wrds_forms entirely. Use only if (1) and (2) don't cover the rows you need, and aggregate with DISTINCT or pick a single registrant role.

Linking CIK to firm-level identifiers — wrdssec_common.wciklink_*

WRDS maintains the canonical CIK crosswalks. Build your panel against these tables rather than fuzzy-matching company names; they track mergers, name changes, and the historical identifier coverage that pure name matching does not. (CIKs themselves are unique per filer per SEC policy — these tables resolve identifier changes over time, not CIK reuse.)

| Table | Maps CIK to | Key columns | Approx. rows (as of 2026-05) | |-------|-------------|-------------|------------------------------| | wciklink_gvkey | Compustat gvkey (+ per-form filing counts: n10k, ndef, n8k, n13d, n13g, n13f, ...) | cik, gvkey, source, link_desc, sec_start_date / sec_end_date, link_start_date / link_end_date | ~1.07M rows total — but many are link_desc = 'No Link' with gvkey IS NULL; filter accordingly | | wciklink_cusip | 8- and 9-digit CUSIP (+ validated quality flag 0–3) | cik, cusip, cusip_full, validated, cikdate1 / cikdate2 | ~85K | | wciklink_ticker | Ticker symbol | cik, ticker, ftdate / ltdate | ~34K | | wciklink_names | gvkey, cusip, ticker, company_name together in one row with per-identifier date ranges | per-identifier first_*_date / last_*_date | ~1.2M |

Practical gotchas:

• CIK format: 10-character zero-padded string (e.g. '0000001750').
• wciklink_gvkey row inflation: one (cik, gvkey) pair can have several rows from different source values (sample CIK 0000001750 returns multiple rows on a single date). Aggregate with DISTINCT on (cik, gvkey) or pick a single source/link_desc your project trusts. Always require gvkey IS NOT NULL.
• Two date pairs, two meanings: link_start_date / link_end_date are the link source's validity window; sec_start_date / sec_end_date are when the CIK actually filed. For filings-to-fundamentals work, key off the SEC dates so the link covers the actual filing date.

# CIK -> gvkey: distinct valid links covering a filing date
db.raw_sql("""
    SELECT DISTINCT cik, gvkey, source, link_desc,
                    sec_start_date, sec_end_date
    FROM wrdssec_common.wciklink_gvkey
    WHERE cik = '0000001750'
      AND gvkey IS NOT NULL
      AND DATE '2022-06-30' BETWEEN COALESCE(sec_start_date, DATE '1900-01-01')
                              AND COALESCE(sec_end_date, CURRENT_DATE)
""")

For CRSP permno, go gvkey → permno via the standard CRSP-Compustat link (CCM lnkhist).

Index constituents: comp.idxcst_his holds only CURRENT members

Verified 2026-07-21 (Specialist Directors project): on this WRDS deployment, comp.idxcst_his for the S&P 500 (gvkeyx='000003', confirmed via comp.idx_index) returns only the ~503 current constituents — every thru is nul

…

Source & license

This open-source skill is cataloged on AgentStack and links to its original source — we do not rehost the code.

• Author: kennethkhoocy
• Source: kennethkhoocy/applied-micro-skills
• License: MIT

Install and usage instructions live in the source repository linked above.