Getting CTF Data from WRDS

Overview

This guide shows how to use Python or R to get data for the Common Task Framework-inspired competition proposed by Hellum, Jensen, Kelly, and Pedersen (2025).

Note

We'll extract the following three tables from the WRDS database:

contrib_global_factor.ctff_features
contrib_global_factor.ctff_chars
contrib_global_factor.ctff_daily_ret

Prerequisites

General

A WRDS account with access to:
- CRSP Monthly Stock File
- CRSP Daily Stock File
- Compustat North America

Install packages (uv/pip/conda all fine):

uv add pandas sqlalchemy psycopg2-binary keyring

Store WRDS password securely (replace WRDS_USERNAME and WRDS_PASSWORD):

import keyring
keyring.set_password("wrds", "WRDS_USERNAME", "WRDS_PASSWORD")

Install packages:

install.packages(c("DBI", "RPostgres", "keyring"))

Store WRDS password securely (replace WRDS_USERNAME):

keyring::key_set(service = "wrds", username = "WRDS_USERNAME")

Data download

Row order matters

Every query below ends with an ORDER BY. Without one, WRDS can return the same rows in a different order each time you download.

ctff_chars is sorted by id, then eom
ctff_daily_ret is sorted by id, then date
ctff_features is sorted by features in byte order (COLLATE "C"), so the result does not depend on the database's language settings

Models that sample at random can give different weights when the same rows arrive in a different order, even with the same code and the same seed. Row subsampling in XGBoost or LightGBM is one example: XGBoost draws one random number per row, in order, so the same seed picks different rows once the order changes. The competition runs your code on data sorted as above, so develop on data in the same order.

Already downloaded the data? You do not need to download it again. The rows are the same; sort the tables you have and save them again.

In Python (pandas):

ctff_chars     = ctff_chars.sort_values(["id", "eom"], ignore_index=True)
ctff_daily_ret = ctff_daily_ret.sort_values(["id", "date"], ignore_index=True)
ctff_features  = ctff_features.sort_values("features", ignore_index=True)

In R (method = "radix" sorts text in byte order whatever your locale):

ctff_chars     <- ctff_chars[order(ctff_chars$id, ctff_chars$eom), ]
ctff_daily_ret <- ctff_daily_ret[order(ctff_daily_ret$id, ctff_daily_ret$date), ]
ctff_features  <- ctff_features[order(ctff_features$features, method = "radix"), , drop = FALSE]
rownames(ctff_chars) <- rownames(ctff_daily_ret) <- rownames(ctff_features) <- NULL

Python
R

import keyring
import pandas as pd
from sqlalchemy import create_engine, text
from sqlalchemy.engine import URL

# --- Credentials from OS keychain (keyring) ---
creds = keyring.get_credential("wrds", None)
if creds is None:
    raise RuntimeError(
        """No WRDS credentials stored.
        Run: keyring.set_password('wrds', 'WRDS_USERNAME', 'WRDS_PASSWORD')."""
    )
wrds_un, wrds_pw = creds.username, creds.password

# --- WRDS Postgres connection (SSL required) ---
# Use URL.create() to properly handle special characters in passwords
url = URL.create(
    drivername="postgresql+psycopg2",
    username=wrds_un,
    password=wrds_pw,
    host="wrds-pgdata.wharton.upenn.edu",
    port=9737,
    database="wrds",
    query={"sslmode": "require"},
)
engine = create_engine(url)

# --- Helper: simple fetch ---
def wrds_fetch(sql: str) -> pd.DataFrame:
    with engine.connect() as conn:
        return pd.read_sql_query(text(sql), conn)

# --- Download tables (keep the ORDER BY: row order matters, see above) ---
ctff_features  = wrds_fetch('SELECT * FROM contrib_global_factor.ctff_features ORDER BY features COLLATE "C";')
ctff_chars     = wrds_fetch("SELECT * FROM contrib_global_factor.ctff_chars ORDER BY id, eom;")
ctff_daily_ret = wrds_fetch("SELECT * FROM contrib_global_factor.ctff_daily_ret ORDER BY id, date;")

# --- Save locally ---
# For example, we use (requires pyarrow package):
#    ctff_features.to_parquet("data/raw/ctff_features.parquet", index=False)
#    ctff_chars.to_parquet("data/raw/ctff_chars.parquet", index=False)
#    ctff_daily_ret.to_parquet("data/raw/ctff_daily_ret.parquet", index=False)

Note

Memory issues? If memory is tight for, e.g., ctff_chars, fetch in chunks:

with engine.connect() as conn:
    parts = pd.read_sql_query(
        text("SELECT * FROM contrib_global_factor.ctff_chars ORDER BY id, eom;"),
        conn,
        chunksize=500_000,
    )
    ctff_chars = pd.concat(parts, ignore_index=True)

# --- Libraries ---
library(keyring)
library(DBI)
library(RPostgres)

# --- Credentials from OS keychain (keyring) ---
if (nrow(key_list("wrds"))==0) {
  stop(
    "No WRDS credentials stored.\n",
    "Run: keyring::key_set(service = 'wrds', username = 'WRDS_USERNAME')."
  )
}
wrds_un <- key_list(service = "wrds")$username[1]
wrds_pw <- key_get("wrds", wrds_un)

# --- Connect to WRDS (SSL required) ---
con <- dbConnect(
  RPostgres::Postgres(),
  host    = "wrds-pgdata.wharton.upenn.edu",
  port    = 9737,
  dbname  = "wrds",
  sslmode = "require",
  user    = wrds_un,
  password = wrds_pw
)
on.exit(dbDisconnect(con), add = TRUE)

# --- Helper: simple fetch ---
wrds_fetch <- function(sql) DBI::dbGetQuery(con, sql)

# --- Download tables (keep the ORDER BY: row order matters, see above) ---
ctff_features  <- wrds_fetch('SELECT * FROM contrib_global_factor.ctff_features ORDER BY features COLLATE "C";')
ctff_chars     <- wrds_fetch("SELECT * FROM contrib_global_factor.ctff_chars ORDER BY id, eom;")
ctff_daily_ret <- wrds_fetch("SELECT * FROM contrib_global_factor.ctff_daily_ret ORDER BY id, date;")

# --- Save locally ---
# For example, we use (requires arrow package):
#   arrow::write_parquet(ctff_features, "data/raw/ctff_features.parquet")
#   arrow::write_parquet(ctff_chars, "data/raw/ctff_chars.parquet")
#   arrow::write_parquet(ctff_daily_ret, "data/raw/ctff_daily_ret.parquet")