Wrds

WRDS (Wharton Research Data Services)

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

Prerequisites

1. Install the client library:

   pip install wrds
   

2. 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.

3. 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 = "<the password the user gave>"; 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

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):

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:

Other useful FactSet tables (no _eq suffix):

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.

⚠️ 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 <DOCUMENT>/<TEXT> SGML wrapper tags and zero <html>/<table>/<tr>/<td>/<p> tags. Older filings can retain table markup: 1994-era accessions in filing_def (0000890613-94-000001), filing_10_k (0000912057-94-000263), filing_8_k (0000088948-94-000001), and filing_sc_13d (0000899657-94-000005) still contain <TABLE> blocks. The pre-processing rule is not corpus-wide. Re-run scripts/check_html_preservation.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 filing_tsv 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.)

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 null, so it carries no historical join/leave spells. For historical index membership use crsp.msp500list (2,064 spells with real start/end dates) and map permno → gvkey through the CCM link crsp.ccmxpf_lnkhist (linktype IN ('LU','LC'), linkprim IN ('P','C'), date-window overlap). CRSP's "still a member" sentinel is an end date equal to the file end — convert it to null rather than treating it as an exit. Pre-Compustat-era permnos (~176, back to 1925) have no gvkey link; keep them with null gvkey. Note also: comp.company has no tic column here — tickers live in comp.security (prefer iid='01', secstat='A').

Best Practices

• Filter in SQL, not in Python. Always push country, date, and identifier filters into the SQL query to minimize data transfer. WRDS queries over the network are slow for large tables — pulling millions of rows and filtering locally wastes time.

• Cache downloads as parquet. Save query results to data/processed/wrds/ (or an equivalent project-specific path) as .parquet files. Include a downloaded_date column or note in the filename so you know when the data was fetched. Do not re-download data that hasn't changed.

• Close connections. Call db.close() when finished. WRDS may terminate idle sessions, and leaving connections open ties up limited slots.

• Use raw_sql() for anything non-trivial. get_table() is convenient for quick peeks, but raw_sql() gives full control over joins, filters, and aggregation — use it for real queries.

Example: Download FactSet Institutional Holdings

import os
import sys
import polars as pl
from datetime import date

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

try:
    db = connect()
except WRDSAuthError as e:
    print(e)            # STOP — do not retry on auth failure
    raise

try:
    df = db.raw_sql("""
        SELECT a.fsym_id, a.factset_entity_id, a.as_of_date,
               a.position, a.source_code, a.current_flag,
               b.proper_name
        FROM factset.own_inst_stakes_detail_eq AS a
        LEFT JOIN factset.own_ent_institutions AS b
            ON a.factset_entity_id = b.factset_entity_id
        WHERE a.as_of_date >= '2023-01-01'
        LIMIT 1000
    """)
finally:
    db.close()

# Convert to polars and save
result = pl.from_pandas(df).with_columns(
    pl.lit(date.today().isoformat()).alias("downloaded_date")
)
result.write_parquet("data/processed/wrds/factset_inst_holdings.parquet")

Troubleshooting