Data Sources Overview

Data Sources Overview#

Background#

The paper Can ChatGPT Forecast Stock Price Movements? (Lopez-Lira and Tang, 2023) investigates whether a large language model can interpret financial news headlines and translate those interpretations into profitable trading signals. Each headline is presented to ChatGPT, which classifies it as implying a stock price increase (YES), decrease (NO), or no clear direction (UNKNOWN). These classifications are then used to construct daily long/short trading portfolios.

This case study replicates two of the paper’s core results:

  • Table 1 – predictive performance of the headline-based strategy

  • Figure 5 – cumulative returns under several sample restrictions

This first notebook gives a tour of the two underlying datasets used in the replication: daily stock data from CRSP and financial news headlines from independently scraped news sources (PR Newswire and GDELT), enriched with entity metadata from RavenPack via WRDS. RavenPack metadata provides the ticker mapping and relevance scores used for filtering, but the headline text itself comes from free sources – a distinction that matters because RavenPack’s licensing prohibits sending its data to third-party servers like OpenAI.

Setup#

from pathlib import Path

import pandas as pd
from IPython.display import Markdown, display

from settings import config

DATA_DIR = Path(config("DATA_DIR"))
OUTPUT_DIR = Path(config("OUTPUT_DIR"))
MANUAL_DATA_DIR = Path(config("MANUAL_DATA_DIR"))

paths = {
    "CRSP Stock Data": DATA_DIR / "CRSP_stock_daily.parquet",
    "RavenPack Metadata": DATA_DIR / "RAVENPACK.parquet",
    "Scraped Headlines": DATA_DIR / "RAVENPACK_scraped.parquet",
    "Cleaned Headlines": DATA_DIR / "RAVENPACK_cleaned.parquet",
    "Daily Headline Scores": DATA_DIR / "daily_headline_polarity.parquet",
    "Portfolio Returns": DATA_DIR / "portfolio_daily_returns.parquet",
    "Table1 (Paper Sample)": OUTPUT_DIR / "table1_overnight_paper_sample.csv",
    "Table1 (Full Sample)": OUTPUT_DIR / "table1_overnight_full_sample.csv",
    "Paper Table Data": MANUAL_DATA_DIR / "paper_table1.csv",
}

status = pd.DataFrame(
    {"file": list(paths.keys()), "exists": [p.exists() for p in paths.values()]}
)
display(status)
file exists
0 CRSP Stock Data True
1 RavenPack Metadata True
2 Scraped Headlines True
3 Cleaned Headlines True
4 Daily Headline Scores True
5 Portfolio Returns True
6 Table1 (Paper Sample) True
7 Table1 (Full Sample) True
8 Paper Table Data True

1. CRSP Stock Data#

CRSP (Center for Research in Security Prices) is the standard academic database for U.S. equity data, maintained by the University of Chicago. The pull fetches daily stock-level data – returns, open/close prices, and shares outstanding – needed to compute portfolio returns from the ChatGPT trading signals.

The SQL WHERE clause restricts the pull to match the paper’s universe (Section 4, common stocks with share codes 10 and 11):

WHERE
    primaryexch IN ('N', 'A', 'Q')
    AND conditionaltype = 'RW'
    AND tradingstatusflg = 'A'
    AND sharetype = 'NS'
    AND securitytype = 'EQTY'
    AND securitysubtype = 'COM'
    AND usincflg = 'Y'
    AND issuertype IN ('ACOR', 'CORP')
    AND dlycaldt >= '{start_date}'
    AND dlycaldt <= '{end_date}'

  • primaryexch IN ('N', 'A', 'Q') – only stocks listed on the three major U.S. exchanges: NYSE (N), AMEX (A), and NASDAQ (Q).

  • conditionaltype = 'RW' – keeps only “Regular Way” traded securities, i.e. standard exchange-traded stocks. In the legacy CRSP format this is equivalent to restricting to main exchange codes (exchcd in 1, 2, 3).

  • tradingstatusflg = 'A' – restricts to actively trading securities, excluding halted or delisted stocks.

  • sharetype = 'NS', securitytype = 'EQTY', securitysubtype = 'COM', usincflg = 'Y', issuertype IN ('ACOR', 'CORP') – restricts to domestic common stocks, the CRSP V2 equivalent of share codes 10 and 11. This excludes ETFs, REITs, ADRs, closed-end funds, and other non-common-equity securities.

  • dlycaldt >= ... AND dlycaldt <= ... – bounds the date range. The paper’s sample runs from October 2021 to May 2024, chosen so that all headlines post-date GPT-4’s training data cutoff (September 2021), avoiding look-ahead bias.

In short: the CRSP pull provides the daily price and return data for the universe of ordinary, actively-traded U.S. common stocks on the three major exchanges, over the paper’s sample period. This data is then joined with the headline-level ChatGPT scores to compute the long/short portfolio returns analyzed in Table 1 and Figure 5.

crsp = (
    pd.read_parquet(paths["CRSP Stock Data"])
    if paths["CRSP Stock Data"].exists()
    else pd.DataFrame()
)
print("CRSP shape:", crsp.shape)
display(crsp.head())

CRSP shape: (4479130, 13)
permno permco ticker primaryexch sharetype securitytype securitysubtype usincflg issuertype date dlycap dlyopen dlyclose
0 10026 7976 JJSF Q NS EQTY COM Y CORP 2021-10-01 2932065.76 153.85 153.64
1 10028 7978 ELA A NS EQTY COM Y CORP 2021-10-01 109584.75 4.11 4.07
2 10032 7980 PLXS Q NS EQTY COM Y CORP 2021-10-01 2554520.76 89.59 91.08
3 10044 7992 RMCF Q NS EQTY COM Y CORP 2021-10-01 43174.2 7.36 7.05
4 10051 7999 HNGR N NS EQTY COM Y CORP 2021-10-01 864334.6 22.19 22.34 
import create_summary_statistics as css

crsp_summary = css.create_crsp_summary()
display(crsp_summary)

del crsp
Metric Mean SD P25 Median P75
0 Market Cap ($M) 12.481358 94.483500 0.115490 0.652421 3.780629
1 Daily Return (%) -0.091481 4.670486 -1.622361 0.000000 1.345291

2. RavenPack Metadata (Entity Mapping and Filters)#

RavenPack provides real-time news analytics covering over 40,000 sources. In this pipeline, the RavenPack pull from WRDS (RAVENPACK.parquet) is used only as a metadata lookup – it supplies entity identifiers (rp_entity_id), ticker mappings (map_ticker), relevance scores, and deduplication keys. The headline text actually sent to OpenAI comes from independently scraped sources (see Section 3 below).

The RavenPack filters applied match the paper’s specifications:

  • Relevance score of 100

  • News reports and press releases only

  • Exclude headlines categorized as “stock gain” or “stock loss”

rp_full = (
    pd.read_parquet(paths["RavenPack Metadata"])
    if paths["RavenPack Metadata"].exists()
    else pd.DataFrame()
)
print("RavenPack metadata shape:", rp_full.shape)
display(rp_full.head())

RavenPack metadata shape: (1143333, 7)
rp_entity_id rpa_date_utc timestamp_utc map_ticker entity_name headline css
0 000127 2023-02-01 2023-02-01 11:15:37.880 BRIS PT Bank Syariah Indonesia Tbk Press Release: Fitch Affirms Bank Syariah Indo... 0.04
1 000127 2023-11-03 2023-11-03 12:16:16.366 BRIS PT Bank Syariah Indonesia Tbk Press Release: Fitch Upgrades Bank Syariah Ind... 0.02
2 000127 2024-10-23 2024-10-23 10:50:44.526 BRIS PT Bank Syariah Indonesia Tbk Press Release: Fitch Upgrades Bank Syariah Ind... 0.02
3 000127 2024-05-16 2024-05-16 02:15:10.040 BRIS PT Bank Syariah Indonesia Tbk Press Release: Fitch Affirms Bank Syariah Indo... 0.04
4 0005DF 2025-09-01 2025-09-01 06:00:15.367 PRD Predator Oil & Gas Holding PLC Predator Oil & Gas Holdings PLC Completion of ... 0.06 
news_by_year = css.create_news_by_year()
display(Markdown("### Headlines by Year"))
display(news_by_year)

Headlines by Year

Year Headlines Firms Days Per Day Per Firm
0 2021 83912 12228 92 912.1 6.9
1 2022 279018 19714 363 768.6 14.2
2 2023 263040 20321 364 722.6 12.9
3 2024 238383 19526 366 651.3 12.2
4 2025 233191 20376 365 638.9 11.4
5 2026 45789 10422 59 776.1 4.4 
news_by_timing = css.create_news_by_timing()
display(Markdown("### Headlines by Release Timing"))
display(news_by_timing)

del rp_full

Headlines by Release Timing

Timing Headlines % Total Firms Per Day
1 Overnight 1020420 89.2 30164 636.2
0 Intraday 122913 10.8 17841 81.2

The majority of news headlines are released outside regular trading hours, which motivates the focus on overnight signals in the trading strategy.

3. Scraped Headlines (Sent to OpenAI)#

The headline text used for LLM classification comes from the upstream news_headlines chartbook pipeline, which produces the scraped_headlines_with_rp_metadata dataset. This dataset joins independently scraped headlines from two free sources:

  • PR Newswire – press releases scraped via sitemap crawling (coverage from January 2010)

  • GDELT – Global Knowledge Graph headlines filtered to S&P 500 companies via BigQuery (coverage from February 2015)

These scraped headlines are fuzzy-matched against the RavenPack DJ Press Release archive to inherit entity metadata (tickers, relevance scores, event similarity keys). Crucially, the headline column contains text from the free scraped sources and is safe to send to external APIs like OpenAI – unlike RavenPack’s own headline text, which is subject to licensing restrictions.

The merge pipeline (merge_scraped_headlines.py) applies the same RavenPack filters listed above and outputs RAVENPACK_scraped.parquet.

rp_scraped = (
    pd.read_parquet(paths["Scraped Headlines"])
    if paths["Scraped Headlines"].exists()
    else pd.DataFrame()
)
print("Scraped headlines shape:", rp_scraped.shape)
display(rp_scraped.head())

del rp_scraped

Scraped headlines shape: (222036, 7)
rp_entity_id rpa_date_utc timestamp_utc map_ticker entity_name headline css
0 9CA8F4 2021-10-01 2021-10-01 10:00:00.562 KW Kennedy-Wilson Holdings Inc. Kennedy Wilson Acquires Two Multifamily Commun... 0.10
1 8665BA 2021-10-01 2021-10-01 10:00:02.797 TMO Thermo Fisher Scientific Inc. Thermo Fisher Scientific Opens Biologics Manuf... 0.00
2 723548 2021-10-01 2021-10-01 10:00:04.814 GNL Global Net Lease Inc. Global Net Lease, Inc. Announces Common Stock ... 0.04
3 D15833 2021-10-01 2021-10-01 10:32:58.298 GIH Gold Resource Corp. Gold Resource Corporation to Hold Q3 2021 Conf... 0.06
4 7D0853 2021-10-01 2021-10-01 10:54:13.413 PLNH Planet 13 Holdings Inc. Planet 13 Completes Acquisition of Florida Can... 0.00

4. Cleaned Headlines#

The cleaning pipeline (clean_ravenpack.py) reads the scraped headlines (RAVENPACK_scraped.parquet) and applies several filters to match the paper’s methodology:

  1. Ticker mapping – filter to tickers present in the CRSP universe

  2. Deduplication – remove near-duplicate firm-day headlines using Optimal String Alignment (OSA) similarity (threshold > 0.60)

  3. Timing alignment – convert timestamps to Eastern Time; exclude intraday headlines (9 AM – 4 PM ET); roll overnight headlines (after 4 PM) to the next trading day

This replication focuses exclusively on overnight news (the paper’s primary analysis in Table 1 Panel A and Figure 5). Intraday headlines are dropped during cleaning, and no TAQ-based intraday return windows are computed.

The output (RAVENPACK_cleaned.parquet) contains overnight-only scraped headline text ready for submission to OpenAI.

from notebook_helper import get_rp_timing_stats

rp_clean = (
    pd.read_parquet(paths["Cleaned Headlines"])
    if paths["Cleaned Headlines"].exists()
    else pd.DataFrame()
)
print("Cleaned headlines shape:", rp_clean.shape)
display(rp_clean.head())

Cleaned headlines shape: (50995, 9)
rp_entity_id map_ticker entity_name timestamp_utc rpa_date_utc timestamp_et headline_date headline css
0 9CA8F4 KW Kennedy-Wilson Holdings Inc. 2021-10-01 10:00:00.562 2021-10-01 2021-10-01 06:00:00.562000-04:00 2021-10-01 Kennedy Wilson Acquires Two Multifamily Commun... 0.1
1 8665BA TMO Thermo Fisher Scientific Inc. 2021-10-01 10:00:02.797 2021-10-01 2021-10-01 06:00:02.797000-04:00 2021-10-01 Thermo Fisher Scientific Opens Biologics Manuf... 0.0
2 9465C9 COGT Cogent Biosciences Inc. 2021-10-01 11:00:05.031 2021-10-01 2021-10-01 07:00:05.031000-04:00 2021-10-01 Cogent Biosciences to Present Preclinical Data... 0.0
3 14C7B2 KDP Keurig Dr Pepper Inc. 2021-10-01 11:00:06.079 2021-10-01 2021-10-01 07:00:06.079000-04:00 2021-10-01 Keurig Dr Pepper Announces New Share Repurchas... 0.1
4 1272B3 AMRC Ameresco Inc. 2021-10-01 11:15:00.310 2021-10-01 2021-10-01 07:15:00.310000-04:00 2021-10-01 Ameresco to Participate at Upcoming October Co... 0.0 
if not rp_clean.empty:
    stats, timing_table = get_rp_timing_stats(rp_clean)
    display(Markdown("### Timing Diagnostics"))
    display(stats)
    display(timing_table)

del rp_clean

Intraday rows remaining (expected ~0): 0

Timing Diagnostics

n_rows n_tickers min_ts max_ts min_headline_date max_headline_date
0 50995 3085 2021-10-01 06:00:00.562000-04:00 2026-01-30 17:00:00.348000-05:00 2021-10-01 2026-01-31
sample rows total headline dates rolled over
0 Oct 2021 - May 2024 33509 7156
1 Oct 2021 - Feb 2026 50995 11045

Summary#

This notebook introduced the two primary data sources used in the replication:

  • CRSP provides daily stock prices and market capitalization for firms traded on NYSE, AMEX, and NASDAQ.

  • Scraped headlines (from PR Newswire and GDELT) provide the news text sent to OpenAI for classification. These are free-source headlines matched against RavenPack metadata for entity identification, ticker mapping, and deduplication.

The next notebook walks through how these headlines are submitted to OpenAI for classification and how the resulting signals are translated into trading portfolios.

Contents