Data Pipeline Documentation

This document explains the exact data loading, processing, and merging operations in the quantitative backtest pipeline.

1. CRSP Data Loading

1.1 What We Call from WRDS

Source Table: crsp.msf (CRSP Monthly Stock File)

Exact SQL Query:

SELECT 
    a.permno,           -- Permanent number (stock identifier)
    a.date,             -- Trading date
    a.ret,              -- Monthly return
    a.prc,              -- Price at month-end
    a.shrout,           -- Shares outstanding (in thousands)
    a.hexcd as exchcd,  -- Exchange code (1=NYSE, 2=AMEX, 3=NASDAQ)
    a.hsiccd as siccd   -- Historical SIC code
FROM crsp.msf AS a
WHERE a.date >= '1985-01-01' 
AND a.date <= '2024-12-31'
AND a.hexcd IN (1, 2, 3)  -- Only NYSE, AMEX, NASDAQ

Additional Table: crsp.stocknames (for share codes)

SELECT permno, shrcd, namedt, nameenddt
FROM crsp.stocknames
WHERE namedt <= '2024-12-31'
AND (nameenddt >= '1985-01-01' OR nameenddt IS NULL)

1.2 Operations Performed on CRSP Data

Date Processing:
- Convert date to datetime format
- Extract year and month columns
Share Code Merge:
- Merge with crsp.stocknames using pd.merge_asof() (backward direction)
- Match share codes (shrcd) based on date ranges (namedt to nameenddt)
- Filter to keep only common stocks: shrcd IN (10, 11)
Return Cleaning:
- Convert returns to numeric (coerce errors to NaN)
- Fill NaN returns with 0
Output Files Created:
- crsp_monthly_raw.parquet: Full monthly data (3,350,013 rows × 10 columns)
  - Columns: permno, date, ret, prc, shrout, exchcd, siccd, year, month, shrcd
- annual_returns.parquet: Annual returns computed by compounding monthly returns (299,118 rows × 3 columns)
  - Operation: (1 + monthly_ret).prod() - 1 grouped by (permno, year)
  - Columns: permno, year, annual_ret
- december_snapshot.parquet: December data for universe filtering (279,291 rows × 8 columns)
  - Filter: month == 12
  - Computed columns:
    - price = abs(prc)
    - mktcap = price * shrout / 1000 (in millions)
  - Columns: permno, year, date, price, mktcap, siccd, exchcd, shrcd

2. OSAP Data Loading

2.1 What We Download from OSAP

Source: Open Asset Pricing (OSAP) library via openassetpricing package

Signals Downloaded: 110+ factors, downloaded one-by-one, including:

Value factors: BM, BMdec, BPEBM, EP, SP, cfp, EntMult, PS, IntanBM
Profitability: GP, OperProf, OperProfRD, roaq
Growth/Investment: AssetGrowth, Investment, InvGrowth, NOA, dNoa, GrLTNOA, RDS, OrgCap, InvestPPEInv
Accruals: Accruals, AbnormalAccruals, AccrualsBM, TotalAccruals, PctAcc, PctTotAcc, ChNWC
Capital Structure: BookLeverage, Leverage, NetDebtFinance, NetDebtPrice, NetEquityFinance, CompEquIss, ShareIss1Y, NetPayoutYield
Distress: OScore, Tax
Momentum: Mom12m, Mom6m, ResidualMomentum, IndMom, LRreversal, STreversal, MaxRet, MomSeason
Liquidity: Illiquidity, DolVol, std_turn, ShareVol, BidAskSpread, zerotrade1M, zerotrade6M, zerotrade12M, VolumeTrend
Risk: Beta, BetaDimson, BetaTailRisk, DownsideBeta, IdioVol3F, IdioVolAHT, VolSD, VolMkt, ReturnSkew, ReturnSkew3F
Analyst: AnalystRevision, ChangeInRecommendation, ConsRecomm, UpRecomm, DownRecomm, FEPS, ForecastDispersion, EarningsForecastDisparity, Recomm_ShortInterest
Size/Events: Size, DivInit, Spinoff, IndIPO

2.2 Operations Performed on OSAP Data

Signal Validation:
- Check each signal name against OSAP documentation
- Skip invalid signals
Download Process:
- Download each signal individually using openap.dl_signal('pandas', [signal_name])
- Each signal returns a DataFrame with columns: permno, time dimension (yyyymm or date), and the signal value
Time Normalization:
- Convert time dimension to year and month:
  - If yyyymm exists: year = yyyymm // 100, month = yyyymm % 100
  - If date exists: extract year and month
- Filter to keep only December snapshots (formation_month = 12)
Panel Construction:
- Merge all signals into a single wide panel
- Merge operation: panel.merge(df_small, on=['permno', 'year'], how='outer')
- Final structure: (permno, year, factor1, factor2, ..., factorN)
Output File:
- osap_signals_december.parquet: Wide panel with all OSAP factors (323,073 rows × 79 columns)
  - Columns: permno, year, plus 77 OSAP factor columns

3. Compustat Annual Data Loading

3.1 What We Call from WRDS

Source Table 1: comp.funda (Compustat Fundamentals Annual)

Exact SQL Query:

SELECT 
    gvkey, datadate, fyear, fyr, conm, sich,
    at, sale, ni, cogs, ap, rect, invt, act, lct, che,
    dlc, dltt
FROM comp.funda
WHERE indfmt='INDL'      -- Industrial format
AND datafmt='STD'        -- Standard format
AND popsrc='D'           -- Primary source
AND consol='C'           -- Consolidated
AND fyear BETWEEN 1980 AND 2024

Source Table 2: crsp.ccmxpf_linktable (CRSP-Compustat Link Table)

Exact SQL Query:

SELECT 
    gvkey, 
    lpermno AS permno,
    linktype, linkprim,
    linkdt, linkenddt
FROM crsp.ccmxpf_linktable
WHERE linktype IN ('LU','LC')  -- Link types
AND linkprim IN ('P','C')      -- Primary or consolidated

3.2 Operations Performed on Compustat Data

Date Processing:
- Convert datadate to datetime
- Convert linkdt and linkenddt to datetime
- Fill NULL linkenddt with 2099-12-31
Linking:
- Merge funda with ccmxpf_linktable on gvkey (left join)
- Filter to keep only valid links where datadate falls within link date range:
```
valid = (datadate >= linkdt) & (datadate <= linkenddt)
```
Output File:
- compustat_annual.parquet: Annual fundamentals linked to CRSP permnos (279,628 rows × 23 columns)
  - Columns: gvkey, datadate, fyear, fyr, conm, sich, at, sale, ni, cogs, ap, rect, invt, act, lct, che, dlc, dltt, permno, linktype, linkprim, linkdt, linkenddt

3.3 Custom Compustat Factors

Function: compute_compustat_factors(comp_annual)

Factors Computed:

DSO (Days Sales Outstanding):
```
DSO = (rect / sale) * 365
```
DIO (Days Inventory Outstanding):
```
DIO = (invt / cogs) * 365
```
DPO (Days Payable Outstanding):
```
DPO = (ap / cogs) * 365
```
CCC (Cash Conversion Cycle):
```
CCC = DSO + DIO - DPO
```
OpLev (Operating Leverage):
```
OpLev = 1 - (cogs / sale)
```
FinLev (Financial Leverage):
```
FinLev = (dltt + dlc) / at
```

Output:

DataFrame with columns: permno, year (or fyear), CCC, OpLev, FinLev, DSO, DIO, DPO
279,628 rows (same as input Compustat data)

4. Data Merging Operations

4.1 Step 1: OSAP + CRSP December Snapshot Merge

Location: After loading OSAP and CRSP data

Operation:

merged_data = osap_signals.merge(
    dec_data[['permno', 'year', 'date', 'price', 'mktcap', 'siccd']],
    on=['permno', 'year'],
    how='inner'
)

Purpose: Combine OSAP factors with CRSP universe information (price, market cap, SIC code)

Result: 279,291 observations (inner join keeps only stocks present in both datasets)

4.2 Step 2: Universe Filtering

Filters Applied:

Price filter: price >= $5.00
Market cap filter: mktcap >= $100 million
Exchange filter: Already applied in CRSP query (exchcd IN 1,2,3)
Share code filter: Already applied in CRSP loading (shrcd IN 10,11)

Result: filtered_data with 151,247 observations

4.3 Step 2.5: Compustat Factors Merge

Location: After universe filtering, before z-scoring

Operation:

filtered_data = filtered_data.merge(
    comp_factors[['permno', 'year', 'CCC', 'OpLev', 'FinLev', 'DSO', 'DIO', 'DPO']],
    on=['permno', 'year'],
    how='left'
)

Purpose: Add custom Compustat factors to the filtered universe

Result: 151,256 observations (left join keeps all filtered_data, adds Compustat factors where available)

Note: Custom factors (e.g., VolOfVol) can also be merged at this step using the same pattern.

4.4 Step 3: Industry Z-Scoring

No new merges - operations are performed on existing filtered_data:

Map SIC codes to Fama-French 49 industries
Compute industry-adjusted z-scores for each factor

Output: factors_with_zscores_filtered.parquet (151,256 rows × 170 columns)

Original factors + z-scored versions (suffix _z)

4.5 Step 4: Fama-MacBeth Estimation

Merge Operation:

merged = factors_t.merge(
    returns_t1,  # Annual returns for year t+1
    on='permno',
    how='inner'
)

Purpose: Match factor values at time t with returns at time t+1 for cross-sectional regression

Note: This merge happens year-by-year during Fama-MacBeth estimation

4.6 Step 5: Portfolio Construction

No explicit merges - uses factors_with_zscores_filtered.parquet directly

4.7 Step 6: Performance Evaluation

Merge Operations:

CAPM Regression:

merged = portfolio_returns.merge(
    ff_factors[['date', 'mktrf', 'rf']],
    on='date',
    how='inner'
)

Carhart 4-Factor Regression:

merged = portfolio_returns.merge(
    ff_factors[['date', 'mktrf', 'smb', 'hml', 'umd', 'rf']],
    on='date',
    how='inner'
)

Purpose: Match portfolio returns with Fama-French factors for risk-adjusted performance evaluation

Summary: Data Flow

1. CRSP Monthly (WRDS)
   └─> crsp_monthly_raw.parquet
   └─> annual_returns.parquet (computed)
   └─> december_snapshot.parquet (filtered)

2. OSAP Signals (Open Asset Pricing)
   └─> osap_signals_december.parquet

3. Compustat Annual (WRDS)
   └─> compustat_annual.parquet
   └─> comp_factors (computed: CCC, OpLev, FinLev, DSO, DIO, DPO)

MERGE 1: OSAP + CRSP December
   └─> merged_data

FILTER: Universe filters (price, mktcap)
   └─> filtered_data

MERGE 2: Compustat factors
   └─> filtered_data (with Compustat factors)

Z-SCORING: Industry-adjusted z-scores
   └─> factors_with_zscores_filtered.parquet

FAMA-MACBETH: Year-by-year merge with returns
   └─> Factor selection

PORTFOLIO CONSTRUCTION
   └─> portfolio_returns_monthly.csv

PERFORMANCE EVALUATION: Merge with FF factors
   └─> Performance metrics

Key Points

CRSP data provides stock returns, prices, market caps, and identifiers
OSAP data provides 110+ pre-computed quantitative factors
Compustat data provides fundamentals for computing custom factors
Merges happen at specific steps:
- OSAP + CRSP: After data loading (Step 1)
- Compustat factors: After universe filtering (Step 2.5)
- Returns: During Fama-MacBeth estimation (Step 4)
- FF factors: During performance evaluation (Step 6)
All data is saved to parquet files for efficient storage and loading
Final dataset (factors_with_zscores_filtered.parquet) contains all factors with industry-adjusted z-scores ready for portfolio construction

FilesExpand file tree

data.md

Latest commit

History

data.md

File metadata and controls

Data Pipeline Documentation

1. CRSP Data Loading

1.1 What We Call from WRDS

1.2 Operations Performed on CRSP Data

2. OSAP Data Loading

2.1 What We Download from OSAP

2.2 Operations Performed on OSAP Data

3. Compustat Annual Data Loading

3.1 What We Call from WRDS

3.2 Operations Performed on Compustat Data

3.3 Custom Compustat Factors

4. Data Merging Operations

4.1 Step 1: OSAP + CRSP December Snapshot Merge

4.2 Step 2: Universe Filtering

4.3 Step 2.5: Compustat Factors Merge

4.4 Step 3: Industry Z-Scoring

4.5 Step 4: Fama-MacBeth Estimation

4.6 Step 5: Portfolio Construction

4.7 Step 6: Performance Evaluation

Summary: Data Flow

Key Points