Module 4 Lab: WRDS Pull to Analysis-Ready Panel

Download the starter pack (zip) Module 4 slides (PDF) Module 4 notes

This lab builds one pipeline on two tracks: live WRDS data if your account and tunnel are ready, or an equivalent public dataset if they aren’t. Both tracks run through the same five steps: a feasibility-assessment prompt, a filtered pull, mandatory inspection, a Parquet/DuckDB landing, and a metadata table. The two tracks count equally. What you are learning is the procedure, and the data source underneath it can be swapped. Some students choose the fallback track even with a working WRDS connection because it is faster to iterate on. That is a legitimate choice.

The lab starter pack (lab4_starter.zip) contains env_example.txt (the credential-file template), the iron-law checklist, the metadata-table template, the verify checklist, the Stata bridge extension, and the fallback-sample build script with its README. The zip never contains WRDS data. The fallback sample is built entirely from public SEC data, and every WRDS-derived file you produce in this module stays in your own ~/wrds_data or project folder.

NoteWhat you’re building in this module

By the end of the lab you will have: a funda-shaped panel, either pulled live from Compustat under all four iron-law filters or built from the public SEC fallback sample; four inspection checks written down with actual numbers; a Parquet-backed DuckDB database with an analysis_ready table and a metadata table documenting at least eight variables; and, if you choose the extension, a .dta export and a working leverage–ROA regression in Stata with fyear and SIC2 fixed effects.

Before you start: which track are you on?

If your WRDS account is active, Duo is enrolled, and you completed the WRDS MCP Setup checklist with the tunnel coming up cleanly, you’re on the live track. If Duo is unresolved, your tunnel won’t come up, or something fails mid-lab and you would rather practice the pipeline than spend the session debugging a connection, switch to the fallback track. You can switch at any point, with no penalty. Switching mid-lab is normal.

Live track

Working WRDS account and tunnel, your own credentials.

  1. Environment. Activate ~/.wrds-mcp-env, confirm ~/.wrds-mcp.env is filled in (with export on every line) and sits outside your project, source it, and bring the tunnel up with tunnel_up.sh. Approve the single Duo push. Confirm with tunnel_status.sh before doing anything else.
  2. Feasibility-assessment prompt. Before asking for any SQL, ask Claude the exact question from the module page: “I want a firm-year panel with leverage and ROA for US industrials 2000–2023 from funda. Tell me what’s involved before you try anything complicated. What might be missing?” Read the answer before you go on. It should raise the iron-law filters and at least one linking or coverage caveat.
  3. Pull. Request the funda extract with an explicit variable list (gvkey, fyear, sich, sale, at, dltt, ni, plus anything else your panel needs; sich is the historical SIC code, needed for the industry fixed effects in the Stata extension) and all four iron-law filters applied. Ask Claude to use wrds_download_data with format='parquet' and max_rows set above the row count you expect. The tool’s default silently caps a download at 100,000 rows; if your count comes back exactly 100,000, you were truncated. Do not use SELECT * in any script you produce in this module.
  4. Inspect — write all four down, with real numbers, before proceeding:
    • Row count, compared to what you expected going in.
    • .head() or .sample() eyeball of the actual values.
    • Null check: the share of missing values in each column you plan to use.
    • fyear min/max, checked against your requested range.
    Then check one benchmark firm’s value against a source Claude did not touch (its own 10-K, or a number you already knew).
  5. Land it. Land the extract as Parquet (wrds_download_data can write it directly; to see the compression yourself, also save a CSV once and compare the sizes), attach it in DuckDB, and create an analysis_ready table with lev = dltt / at and roa = ni / at (set both to null where at <= 0). Then build a compustat_metadata table documenting at least eight variables (varname, label, units, one screening note each) using the template from the starter pack.
  6. Fresh-session check. Close your session entirely, open a new one, and orient it with nothing but SELECT * FROM compustat_metadata. Confirm the fresh session can describe your variables correctly from that alone.
  7. Tunnel teardown. Run tunnel_down.sh and confirm with tunnel_status.sh that the tunnel is closed. This step is required, and it is last on the list on purpose.

Fallback track

Duo unresolved, tunnel failure, or your own choice — identical pipeline, public data.

The fallback sample (fallback_panel.parquet) is built entirely from the SEC’s public Financial Statement Data Sets by build_fallback_sample.py in the starter pack. It needs no WRDS account, credentials, or institutional access. Its columns are named to mirror a funda-style pull: firm_id (CIK, standing in for gvkey), company_name (for conm), period (for datadate), at, sale, ni. It is not Compustat. XBRL tags vary by filer and period in ways Compustat’s standardization smooths over, and firm_id cannot be joined to CRSP or Compustat without a crosswalk, which this sample doesn’t include. Every mechanical step below, however, is the same as on the live track.

  1. Environment. You need no tunnel, credential file, or Duo. Build the sample once, from the starter-pack folder: pip install requests pandas pyarrow, then export SEC_USER_AGENT="Your Name your.email@yale.edu" (SEC requires your name and email on every request), then python build_fallback_sample.py --quarter 2024q1 --out-dir .. Confirm that fallback_panel.parquet and fallback_panel.csv appear. See README_fallback.md for details.
  2. Feasibility-assessment prompt. Ask the same question, adapted to the fallback panel’s actual columns: “I want a firm-year panel with leverage and profitability measures from this sample. Tell me what’s involved before you try anything complicated. What might be missing?” The answer should still raise real issues, such as missing revenue tags for some filers and the CIK/gvkey gap, even without WRDS involved.
  3. Pull. There’s no live query here, but treat the load the same way. Select columns explicitly; never read every column in the file. Document which SEC XBRL tags feed each variable. This is the fallback’s equivalent of the iron-law filters: write down which tags you used and why, even though there’s no WHERE clause to paste.
  4. Inspect — the same four checks, on the fallback data:
    • Row count, compared to what the README states (≤ 5,000 firm-year observations).
    • .head() or .sample() eyeball.
    • Null check: the share of missing at, sale and ni (expect sale to be missing more often, because revenue tags vary by filer).
    • period min/max. period is the fiscal period end, so 10-Ks filed in 2024 Q1 mostly cover fiscal years ending in 2023. Expect most period ends in mid-to-late 2023 and look into any outliers.
    Then check one firm’s at or sale value against that company’s own public 10-K filing.
  5. Land it. Follow the same Parquet/DuckDB/metadata steps as the live track. Create an analysis_ready table with roa = ni / at (null where at <= 0). Build a metadata table with at least eight variables documented (the panel has seven columns, so add at least one variable you construct, such as roa), including a screening note that flags where SEC tag coverage is inconsistent.
  6. Fresh-session check. Same as the live track: close, reopen, orient with SELECT * FROM metadata alone.
  7. No tunnel to tear down. Skip this step and go to the verify checklist below.

Extension: the Stata bridge

Export to .dta only the analysis-ready table you built, never a full funda pull, on either track. On the live track, run the Stata code in Step 3 of the starter pack’s stata_bridge_extension.md, which regresses ROA on leverage with fiscal-year and two-digit-SIC fixed effects. The fallback panel has no SIC code, no debt item, and essentially one fiscal year, so that regression can’t be run on it. Fallback-track students regress roa (ni / at) on log(at) with no fixed effects and heteroskedasticity-robust errors (with about one row per firm, clustering by firm would amount to the same thing), and use that model for the two-language check below. Match each variable’s Stata label variable line to your metadata table. If you documented dltt as “long-term debt, millions of dollars” in DuckDB, use that same label in Stata rather than writing a new one.

Then run the two-language check from this module’s notes. In a fresh session, give Claude only a written description of the model — outcome, regressors, the two sets of fixed effects, how standard errors are clustered — and ask for a Python version that reads the same .dta file. Don’t show it the .do file. Compare the leverage coefficient from both runs to six decimal places and paste both numbers into your notes. If the standard errors differ, find the default that explains the gap and write it down.

Extension (either track): fan it out with sub-agents

This exercise answers the question from the Module 2 exit ticket. Ask Claude to split a profile of your landed Parquet file across parallel sub-agents, one per block of fiscal years (three or four blocks is enough). The fallback panel covers essentially one fiscal year, so on that track split it into three blocks of firm_id instead. Each sub-agent reports row counts, the share of missing at and sale, and any duplicate firm-year pairs, and writes those numbers to its own file in a checks/ folder. In your prompt, tell each sub-agent to read your metadata table before it queries anything. Then:

  • Ask the main session to compare the files and flag the block that looks least like the others.
  • Open one of the checks/ files yourself and confirm one number in it against a DuckDB query you run directly.
  • Type /context before and after the exercise to see how much of your main session’s context it used. Compare that with what the same profile would have cost run in the main session.

Verify checklist

Work through every line below. It is self-graded and pass/fail, as in every other module.

Paste your WHERE clause (or, on the fallback track, your XBRL tag list) here:

Debrief and exit ticket

On your own, write down three things (when the course runs with a group, these are the debrief questions). First, what anomaly your inspection step caught. There is usually at least one: missing at values, an unexpected duplicate row, or a benchmark firm that didn’t match. Second, which iron-law filter would have caused a problem in your pull had you skipped it (fallback track: which XBRL tag choice). Third, if you’re on the fallback track, whether the pipeline felt different from the live track. It should not have.

Complete the standard three-question exit ticket (one concept that clicked, one thing that broke, one thing you’d want to verify before trusting a similar result), plus this module’s item:

TipExit ticket — Module 4 item

Write the feasibility-assessment prompt for a real pull from your own dissertation or working paper. Use your actual next step, not this module’s demo panel. Name the table, the variables, the date range, and ask what might be missing before you try anything complicated.