One-line summary: Query Compustat, CRSP, and TAQ from WRDS using the Python API: financial fundamentals, stock returns, intraday microstructure, and CRSP-Compustat link table.
When to Use This Skill
When downloading Compustat balance sheet / income statement data
When fetching CRSP monthly or daily stock returns and market cap
When linking Compustat (gvkey) to CRSP (permno) via CCM link table
When extracting TAQ intraday trades and quotes for microstructure research
When building research-grade datasets (accounting ratios, anomalies)
When accessing I/B/E/S earnings forecasts or Execucomp compensation
# Credentials are stored securely in ~/.pgpass after first login# Set environment variables (do NOT hardcode)export WRDS_USERNAME="<your-wrds-username>"
Connect to WRDS
import os
import wrds
# Credentials via environment variable
wrds_username = os.getenv("WRDS_USERNAME", "")
ifnot wrds_username:
raise ValueError("Set WRDS_USERNAME environment variable")
db = wrds.Connection(wrds_username=wrds_username)
print("Connected to WRDS")
# List available libraries
libs = db.list_libraries()
print("Available libraries (first 10):", libs[:10])
Core Workflow
Step 1: Compustat Annual Fundamentals
import os
import pandas as pd
import numpy as np
import matplotlib.pyplot as plt
# ------------------------------------------------------------------ ## Download Compustat annual fundamentals for S&P 500 firms# Key variables for accounting research# ------------------------------------------------------------------ #try:
import wrds
db = wrds.Connection(wrds_username=os.getenv("WRDS_USERNAME", ""))
# Query Compustat annual (comp.funda)
query = """
SELECT
gvkey, datadate, fyear, conm, sich,
at, -- Total assets
lt, -- Total liabilities
ceq, -- Common equity (book value)
ni, -- Net income
sale, -- Net sales
oibdp, -- Operating income before depreciation
dp, -- Depreciation & amortization
capx, -- Capital expenditure
che, -- Cash and equivalents
dltt, -- Long-term debt
dlc, -- Short-term debt
csho, -- Common shares outstanding
prcc_f, -- Fiscal-year-end price
sich -- SIC code
FROM comp.funda
WHERE
indfmt = 'INDL' -- Industrial format
AND datafmt = 'STD' -- Standardized
AND popsrc = 'D' -- Domestic
AND consol = 'C' -- Consolidated
AND fyear BETWEEN 2010 AND 2023
AND at > 0
AND sale > 0
ORDER BY gvkey, datadate
LIMIT 50000
"""
comp = db.raw_sql(query, date_cols=['datadate'])
print(f"Compustat: {comp.shape[0]:,} firm-years downloaded")
except Exception as e:
print(f"WRDS connection failed ({e}), using synthetic data")
# Synthetic Compustat-like data
np.random.seed(42)
n = 5000
gvkeys = np.repeat(np.arange(1, 101), 50)[:n]
fyears = np.tile(np.arange(2010, 2024), 500)[:n]
comp = pd.DataFrame({
'gvkey': gvkeys,
'fyear': fyears,
'at': np.random.lognormal(7, 1.5, n), # Total assets'ceq': np.random.lognormal(6, 1.5, n), # Book equity'ni': np.random.normal(0.1, 0.3, n) * np.random.lognormal(6, 1.5, n)/10,
'sale': np.random.lognormal(6.5, 1.5, n),
'csho': np.random.lognormal(4, 1.5, n),
'prcc_f': np.random.lognormal(3.5, 0.8, n),
'dltt': np.random.lognormal(5, 2, n),
'capx': np.abs(np.random.normal(0, 0.05, n)) * np.random.lognormal(6.5, 1.5, n),
})
# ---- Compute key accounting ratios ----------------------------- #
comp = comp.copy()
comp['mve'] = comp['csho'] * comp['prcc_f'] # Market cap
comp['bm'] = comp['ceq'] / comp['mve'].replace(0, np.nan) # Book/Market
comp['roa'] = comp['ni'] / comp['at'].replace(0, np.nan) # ROA
comp['roe'] = comp['ni'] / comp['ceq'].replace(0, np.nan) # ROE
comp['lev'] = comp['dltt'] / comp['at'].replace(0, np.nan) # Leverage
comp['asset_growth'] = comp.groupby('gvkey')['at'].pct_change() # Asset growth# Winsorize at 1st/99th percentiledefwinsorize(series, low=0.01, high=0.99):
lb = series.quantile(low)
ub = series.quantile(high)
return series.clip(lb, ub)
for col in ['bm', 'roa', 'roe', 'lev', 'asset_growth']:
comp[col] = winsorize(comp[col])
print("\nAccounting ratios summary:")
print(comp[['bm', 'roa', 'roe', 'lev', 'asset_growth']].describe().round(4))
# ---- Portfolio B/M quintiles ----------------------------------- #
latest = comp[comp['fyear'] == comp['fyear'].max()].copy()
latest = latest[latest['mve'] > 0].dropna(subset=['bm'])
latest['bm_quintile'] = pd.qcut(latest['bm'], 5,
labels=['Growth','Q2','Q3','Q4','Value'])
fig, axes = plt.subplots(1, 2, figsize=(12, 5))
# ROA by BM quintile
roa_by_bm = latest.groupby('bm_quintile')['roa'].mean()
axes[0].bar(roa_by_bm.index, roa_by_bm.values, color='steelblue', edgecolor='black', linewidth=0.7)
axes[0].set_xlabel("B/M Quintile"); axes[0].set_ylabel("Mean ROA")
axes[0].set_title("ROA by Book-to-Market Quintile")
axes[0].grid(axis='y', alpha=0.3)
# Leverage distribution
axes[1].hist(latest['lev'].dropna(), bins=40, color='coral', edgecolor='white', linewidth=0.5)
axes[1].axvline(latest['lev'].median(), color='navy', linewidth=2, linestyle='--',
label=f"Median={latest['lev'].median():.2f}")
axes[1].set_xlabel("Leverage (LT Debt/Assets)"); axes[1].set_title("Leverage Distribution")
axes[1].legend(); axes[1].grid(alpha=0.3)
plt.tight_layout()
plt.savefig("compustat_analysis.png", dpi=150)
plt.show()
Step 2: CRSP Monthly Returns
import pandas as pd
import numpy as np
import matplotlib.pyplot as plt
# ------------------------------------------------------------------ ## Download CRSP monthly stock file and compute factor portfolio returns# ------------------------------------------------------------------ #try:
import wrds
import os
db = wrds.Connection(wrds_username=os.getenv("WRDS_USERNAME", ""))
crsp_query = """
SELECT
permno, date, ret, retx, shrout, prc, exchcd, shrcd
FROM crsp.msf
WHERE
date BETWEEN '2010-01-01' AND '2023-12-31'
AND shrcd IN (10, 11) -- Common shares, US incorporated
AND exchcd IN (1, 2, 3) -- NYSE, AMEX, NASDAQ
AND ret IS NOT NULL
ORDER BY permno, date
LIMIT 200000
"""
crsp = db.raw_sql(crsp_query, date_cols=['date'])
print(f"CRSP monthly: {crsp.shape[0]:,} obs downloaded")
except Exception as e:
print(f"Using synthetic CRSP data ({e})")
np.random.seed(42)
n = 50000
permnos = np.repeat(np.arange(1, 1001), 60)[:n]
dates = pd.bdate_range("2010-01-31", periods=60, freq='BM')
dates = np.tile(dates[:60], 1000)[:n]
crsp = pd.DataFrame({
'permno': permnos,
'date': dates,
'ret': np.random.normal(0.01, 0.08, n),
'prc': np.abs(np.random.lognormal(3.5, 0.8, n)),
'shrout': np.random.lognormal(8, 1.5, n) * 1000,
'exchcd': np.random.choice([1, 2, 3], n),
})
# ---- Market equity and value-weighted returns ----------------- #
crsp['me'] = crsp['prc'].abs() * crsp['shrout'] / 1000# in thousands# Value-weighted market return
crsp = crsp.sort_values(['permno', 'date'])
crsp['me_lag'] = crsp.groupby('permno')['me'].shift(1)
defvw_return(group):
w = group['me_lag'].clip(lower=0)
if w.sum() == 0:
return np.nan
return (group['ret'] * w).sum() / w.sum()
mkt_ret = crsp.groupby('date').apply(vw_return).rename('vw_mkt_ret')
ew_ret = crsp.groupby('date')['ret'].mean().rename('ew_mkt_ret')
comparison = pd.concat([mkt_ret, ew_ret], axis=1).dropna()
cum_vw = (1 + comparison['vw_mkt_ret']).cumprod()
cum_ew = (1 + comparison['ew_mkt_ret']).cumprod()
fig, axes = plt.subplots(1, 2, figsize=(13, 5))
axes[0].plot(cum_vw.index, cum_vw, label='Value-Weighted', linewidth=1.5, color='navy')
axes[0].plot(cum_ew.index, cum_ew, label='Equal-Weighted', linewidth=1.5, color='coral')
axes[0].set_title("Cumulative Market Return"); axes[0].set_ylabel("Cumulative return (×)")
axes[0].legend(); axes[0].grid(alpha=0.3)
# Cross-sectional return distribution
axes[1].hist(crsp['ret'].dropna(), bins=60, range=(-0.5, 0.5),
color='steelblue', edgecolor='white', linewidth=0.3, alpha=0.8)
axes[1].axvline(crsp['ret'].mean(), color='red', linewidth=2, linestyle='--',
label=f"Mean={crsp['ret'].mean()*100:.2f}%")
axes[1].set_xlabel("Monthly Return"); axes[1].set_title("Return Distribution (CRSP)")
axes[1].legend(); axes[1].grid(alpha=0.3)
plt.tight_layout()
plt.savefig("crsp_returns.png", dpi=150)
plt.show()
Step 3: CRSP-Compustat Merge
import pandas as pd
import numpy as np
# ------------------------------------------------------------------ ## Merge CRSP and Compustat via CCM link table# Follow standard academic convention (Fama-French style)# ------------------------------------------------------------------ #try:
import wrds
import os
db = wrds.Connection(wrds_username=os.getenv("WRDS_USERNAME", ""))
# Download CCM link table
ccm_query = """
SELECT
gvkey, lpermno AS permno,
linktype, linkprim, linkdt, linkenddt
FROM crsp.ccmxpf_lnkhist
WHERE
linktype IN ('LC', 'LU', 'LX', 'LD')
AND linkprim IN ('P', 'C')
"""
ccm = db.raw_sql(ccm_query, date_cols=['linkdt', 'linkenddt'])
print(f"CCM link table: {ccm.shape[0]:,} records")
# Fill missing end dates
ccm['linkenddt'] = ccm['linkenddt'].fillna(pd.Timestamp('2099-12-31'))
# Merge: given a date, find valid gvkey-permno linkdefget_permno(gvkey, date, ccm_df):
"""Return permno for a gvkey on a given date."""
valid = ccm_df[
(ccm_df['gvkey'] == gvkey) &
(ccm_df['linkdt'] <= date) &
(ccm_df['linkenddt'] >= date)
]
iflen(valid) == 0:
return np.nan
return valid['permno'].iloc[0]
print("CCM merge function ready")
print("Usage: permno = get_permno(gvkey='001234', date=pd.Timestamp('2020-06-30'), ccm_df=ccm)")
except Exception as e:
print(f"WRDS not available ({e})")
print("Demonstrating CCM merge logic with synthetic data...")
# ---- Standard merge protocol (Fama-French style) --------------- ## 1. Use Compustat fiscal year ending in year t# 2. Compute accounting variables in June of year t+1 (6-month lag)# 3. Match to CRSP returns from July t+1 to June t+2print("""
Standard CCM Merge Protocol:
1. Download comp.funda (fyear=t)
2. Set 'jdate' = fyear-end date + 6 months (June t+1)
3. Download crsp.msf for July t+1 onward
4. Merge via CCM: crsp.permno matched to comp.gvkey using jdate
5. Lag accounting variables by 6 months for look-ahead bias prevention
Example SQL (full merge):
SELECT a.*, b.permno
FROM comp.funda a
LEFT JOIN crsp.ccmxpf_lnkhist b
ON a.gvkey = b.gvkey
AND b.linktype IN ('LC','LU')
AND b.linkprim IN ('P','C')
AND b.linkdt <= a.datadate
AND (b.linkenddt >= a.datadate OR b.linkenddt IS NULL)
""")
Advanced Usage
TAQ Intraday Microstructure
import pandas as pd
import numpy as np
# ------------------------------------------------------------------ ## Effective spread and price impact from TAQ trades and quotes# ------------------------------------------------------------------ #try:
import wrds, os
db = wrds.Connection(wrds_username=os.getenv("WRDS_USERNAME", ""))
# TAQ daily trades for one stock (example: AAPL on 2023-01-03)
taq_query = """
SELECT time, price, size, tr_corr, tr_scond
FROM taq.ct_20230103
WHERE sym_root = 'AAPL'
AND tr_corr = '00' -- Regular trades only
AND size > 0
ORDER BY time
LIMIT 10000
"""
trades = db.raw_sql(taq_query)
print(f"TAQ trades: {len(trades):,} records")
except Exception as e:
print(f"TAQ not available ({e}) — using synthetic data")
# Simulate intraday trades
np.random.seed(42)
n = 5000
times = pd.date_range("2023-01-03 09:30", periods=n, freq='5s')
mid_price = 130 + np.cumsum(np.random.randn(n) * 0.01)
spread = 0.02# $0.02 spread
sign = np.random.choice([-1, 1], n) # Buy/sell indicator
trades = pd.DataFrame({
'time': times,
'price': mid_price + sign * spread/2 + np.random.randn(n) * 0.001,
'size': np.random.randint(100, 2000, n),
'mid': mid_price,
'sign': sign,
})
# Effective spread = 2 * |price - mid| / mid
trades['eff_spread'] = 2 * np.abs(trades['price'] - trades['mid']) / trades['mid']
print(f"Mean effective spread: {trades['eff_spread'].mean()*10000:.2f} bps")
# Amihud illiquidity: |return| / dollar volume
trades['dollar_vol'] = trades['price'] * trades['size']
trades['return_5min'] = trades['price'].pct_change(12) # ~1min return
trades['amihud'] = trades['return_5min'].abs() / (trades['dollar_vol'] / 1e6)
print(f"Amihud illiquidity (per $M): {trades['amihud'].mean():.4f}")
Troubleshooting
Error: ConnectionError — WRDS authentication
Fix:
# Re-authenticate
python -c "import wrds; db = wrds.Connection()"# Enter username and password when prompted — stored in ~/.pgpass
Fix: Use WRDS web query interface or connect via campus network/VPN.
Memory error: large SQL query
Fix: Add row limits or filter by date range:
# Download in annual chunksfor year inrange(2010, 2024):
chunk = db.raw_sql(f"""
SELECT * FROM comp.funda WHERE fyear = {year} AND at > 0
""")
chunk.to_parquet(f"compustat_{year}.parquet")
