---
title: Getting CTF Data from WRDS
source: https://jkpfactors.com/ctf/dataset-access
markdown: https://jkpfactors.com/ctf/dataset-access.md
json: https://jkpfactors.com/ctf/dataset-access.json
updated_at: 2026-10-05
---

# Getting CTF Data from WRDS

How to pull the three CTF input tables from WRDS with Python or R, and write them to the Parquet files a submission receives.

## 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)](https://papers.ssrn.com/sol3/papers.cfm?abstract_id=5242901).

> **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](https://wrds-www.wharton.upenn.edu/)** account with access to:

  - **[CRSP Monthly Stock File](https://wrds-www.wharton.upenn.edu/pages/get-data/center-research-security-prices-crsp/annual-update/stock-security-files/monthly-stock-file/)**
  - **[CRSP Daily Stock File](https://wrds-www.wharton.upenn.edu/pages/get-data/center-research-security-prices-crsp/annual-update/stock-security-files/daily-stock-file/)**
  - **[Compustat North America](https://wrds-www.wharton.upenn.edu/pages/get-data/compustat-capital-iq-standard-poors/compustat/north-america-daily/)**

#### Tool-specific setup

**Python**

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

   ```bash
   uv add pandas sqlalchemy psycopg2-binary keyring
   ```
2. Store WRDS password securely (replace `WRDS_USERNAME` and `WRDS_PASSWORD`):

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

**R**

1. Install packages:

   ```r
   install.packages(c("DBI", "RPostgres", "keyring"))
   ```
2. Store WRDS password securely (replace `WRDS_USERNAME`):

   ```r
   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):
>
> ```python
> 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):
>
> ```r
> 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**

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

**R**

```r
# --- 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")
```