Data Explorer#
A quick tour of all datasets assembled for the Chen, Kelly, and Xiu (2022) replication. Data sources: RavenPack news analytics, CRSP stock/index data, and FRED macro series.
from pathlib import Path
import polars as pl
from settings import config
DATA_DIR = Path(config("DATA_DIR"))
RavenPack#
Dow Jones newswire articles scored by RavenPack’s NLP analytics engine.
ravenpack_djpr.parquet#
Raw RavenPack Dow Jones Press Release analytics — one row per news article/entity event.
rp = pl.scan_parquet(DATA_DIR / "ravenpack_djpr.parquet")
shape = rp.select(pl.len()).collect().item()
cols = rp.collect_schema().names()
print(f"Rows: {shape:,} | Columns: {len(cols)}")
print(f"Columns: {cols}")
Rows: 28,808,984 | Columns: 25
Columns: ['timestamp_utc', 'rp_story_id', 'rp_entity_id', 'entity_type', 'entity_name', 'country_code', 'relevance', 'event_sentiment_score', 'event_relevance', 'event_similarity_key', 'event_similarity_days', 'topic', 'rp_group', 'rp_type', 'sub_type', 'property', 'fact_level', 'category', 'news_type', 'rp_source_id', 'source_name', 'provider_id', 'provider_story_id', 'headline', 'css']
rp.head(5).collect()
shape: (5, 25)
| timestamp_utc | rp_story_id | rp_entity_id | entity_type | entity_name | country_code | relevance | event_sentiment_score | event_relevance | event_similarity_key | event_similarity_days | topic | rp_group | rp_type | sub_type | property | fact_level | category | news_type | rp_source_id | source_name | provider_id | provider_story_id | headline | css |
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| datetime[ns] | str | str | str | str | str | f64 | f64 | f64 | str | f64 | str | str | str | str | str | str | str | str | str | str | str | str | str | f64 |
| 2000-01-01 17:52:28 | "52D513EA9040443376A6B1747B0FA8… | "047E26" | "COMP" | "Sprint Corp." | "US" | 92.0 | null | null | null | null | null | null | null | null | null | null | null | "FULL-ARTICLE" | "B5569E" | "Dow Jones Newswires" | "DJ" | "DN20000101000112" | "Sprint Says No Y2K Rollover Pr… | -0.06 |
| 2000-01-01 17:52:28 | "96F65B2BE41EEFA2ACE91BC7D0F550… | "047E26" | "COMP" | "Sprint Corp." | "US" | 92.0 | null | null | null | null | null | null | null | null | null | null | null | "FULL-ARTICLE" | "B5569E" | "Dow Jones Newswires" | "DJ" | "DN20000101000112" | "Sprint Says No Y2K Rollover Pr… | -0.06 |
| 2000-01-01 19:11:58 | "32E2DF0E449A5530F32029BAC016B8… | "248F44" | "COMP" | "Motors Liquidation Co." | "US" | 92.0 | null | null | null | null | null | null | null | null | null | null | null | "FULL-ARTICLE" | "B5569E" | "Dow Jones Newswires" | "DJ" | "DN20000101000121" | "Air Traffic -2: Amtrak,General… | 0.0 |
| 2000-01-01 19:11:58 | "5827127FF74AC65C491DBCC82DE130… | "248F44" | "COMP" | "Motors Liquidation Co." | "US" | 92.0 | null | null | null | null | null | null | null | null | null | null | null | "FULL-ARTICLE" | "B5569E" | "Dow Jones Newswires" | "DJ" | "DN20000101000121" | "Air Traffic -2: Amtrak,General… | 0.0 |
| 2000-01-01 18:02:50 | "64A24375781C0F561C0C1E6F160682… | "61B81B" | "COMP" | "PNC Financial Services Group I… | "US" | 91.0 | null | null | null | null | null | null | null | null | null | null | null | "FULL-ARTICLE" | "B5569E" | "Dow Jones Newswires" | "DJ" | "DN20000101000113" | "PNC Bank Says Computer Systems… | 0.04 |
rp.select(
pl.col("timestamp_utc").min().alias("earliest"),
pl.col("timestamp_utc").max().alias("latest"),
).collect()
shape: (1, 2)
| earliest | latest |
|---|---|
| datetime[ns] | datetime[ns] |
| 2000-01-01 00:24:51 | 2019-06-29 22:00:11.548 |
ravenpack_djpr_with_permno.parquet#
RavenPack articles linked to CRSP PERMNOs via the crosswalk table.
rp_permno = pl.scan_parquet(DATA_DIR / "ravenpack_djpr_with_permno.parquet")
shape = rp_permno.select(pl.len()).collect().item()
cols = rp_permno.collect_schema().names()
print(f"Rows: {shape:,} | Columns: {len(cols)}")
print(f"Columns: {cols}")
Rows: 28,147,995 | Columns: 27
Columns: ['timestamp_utc', 'rp_story_id', 'rp_entity_id', 'entity_type', 'entity_name', 'country_code', 'relevance', 'event_sentiment_score', 'event_relevance', 'event_similarity_key', 'event_similarity_days', 'topic', 'rp_group', 'rp_type', 'sub_type', 'property', 'fact_level', 'category', 'news_type', 'rp_source_id', 'source_name', 'provider_id', 'provider_story_id', 'headline', 'css', 'permno', '__index_level_0__']
rp_permno.head(5).collect()
shape: (5, 27)
| timestamp_utc | rp_story_id | rp_entity_id | entity_type | entity_name | country_code | relevance | event_sentiment_score | event_relevance | event_similarity_key | event_similarity_days | topic | rp_group | rp_type | sub_type | property | fact_level | category | news_type | rp_source_id | source_name | provider_id | provider_story_id | headline | css | permno | __index_level_0__ |
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| datetime[ns] | str | str | str | str | str | f64 | f64 | f64 | str | f64 | str | str | str | str | str | str | str | str | str | str | str | str | str | f64 | i64 | i64 |
| 2000-01-01 17:52:28 | "52D513EA9040443376A6B1747B0FA8… | "047E26" | "COMP" | "Sprint Corp." | "US" | 92.0 | null | null | null | null | null | null | null | null | null | null | null | "FULL-ARTICLE" | "B5569E" | "Dow Jones Newswires" | "DJ" | "DN20000101000112" | "Sprint Says No Y2K Rollover Pr… | -0.06 | 39087 | 0 |
| 2000-01-01 17:52:28 | "96F65B2BE41EEFA2ACE91BC7D0F550… | "047E26" | "COMP" | "Sprint Corp." | "US" | 92.0 | null | null | null | null | null | null | null | null | null | null | null | "FULL-ARTICLE" | "B5569E" | "Dow Jones Newswires" | "DJ" | "DN20000101000112" | "Sprint Says No Y2K Rollover Pr… | -0.06 | 39087 | 1 |
| 2000-01-01 19:11:58 | "32E2DF0E449A5530F32029BAC016B8… | "248F44" | "COMP" | "Motors Liquidation Co." | "US" | 92.0 | null | null | null | null | null | null | null | null | null | null | null | "FULL-ARTICLE" | "B5569E" | "Dow Jones Newswires" | "DJ" | "DN20000101000121" | "Air Traffic -2: Amtrak,General… | 0.0 | 12079 | 2 |
| 2000-01-01 19:11:58 | "5827127FF74AC65C491DBCC82DE130… | "248F44" | "COMP" | "Motors Liquidation Co." | "US" | 92.0 | null | null | null | null | null | null | null | null | null | null | null | "FULL-ARTICLE" | "B5569E" | "Dow Jones Newswires" | "DJ" | "DN20000101000121" | "Air Traffic -2: Amtrak,General… | 0.0 | 12079 | 3 |
| 2000-01-01 18:02:50 | "64A24375781C0F561C0C1E6F160682… | "61B81B" | "COMP" | "PNC Financial Services Group I… | "US" | 91.0 | null | null | null | null | null | null | null | null | null | null | null | "FULL-ARTICLE" | "B5569E" | "Dow Jones Newswires" | "DJ" | "DN20000101000113" | "PNC Bank Says Computer Systems… | 0.04 | 60442 | 4 |
# PERMNO coverage
total = rp_permno.select(pl.len()).collect().item()
matched = rp_permno.filter(pl.col("permno").is_not_null()).select(pl.len()).collect().item()
n_permnos = rp_permno.select(pl.col("permno").n_unique()).collect().item()
print(f"Total rows: {total:,}")
print(f"Rows with PERMNO: {matched:,} ({matched / total * 100:.1f}%)")
print(f"Unique PERMNOs: {n_permnos:,}")
Total rows: 28,147,995
Rows with PERMNO: 28,147,995 (100.0%)
Unique PERMNOs: 10,626
raven_crsp_crosswalk.parquet#
Maps RavenPack entity IDs to CRSP PERMNOs.
crosswalk = pl.read_parquet(DATA_DIR / "raven_crsp_crosswalk.parquet")
print(f"Shape: {crosswalk.shape}")
crosswalk.head(5)
Shape: (16376, 2)
shape: (5, 2)
| permno | rp_entity_id |
|---|---|
| i64 | str |
| 57293 | "5F8A7F" |
| 14704 | "8FEF3B" |
| 21971 | "23E688" |
| 12030 | "812BD2" |
| 80804 | "ECC614" |
print(f"Unique RavenPack entities: {crosswalk['rp_entity_id'].n_unique()}")
print(f"Unique PERMNOs: {crosswalk['permno'].n_unique()}")
Unique RavenPack entities: 15799
Unique PERMNOs: 16242
CRSP#
Stock and index returns from the Center for Research in Security Prices.
CRSP_MSF_INDEX_INPUTS.parquet#
Monthly stock file — individual security returns and market cap used as index inputs.
msf = pl.read_parquet(DATA_DIR / "CRSP_MSF_INDEX_INPUTS.parquet")
print(f"Shape: {msf.shape}")
print(f"Columns: {msf.columns}")
msf.head(5)
Shape: (4447518, 24)
Columns: ['permno', 'permco', 'date', 'ret', 'retx', 'shrout', 'prc', 'vol', 'cfacshr', 'cfacpr', 'primaryexch', 'siccd', 'naics', 'issuertype', 'securitytype', 'securitysubtype', 'sharetype', 'usincflg', 'tradingstatusflg', 'altprc', 'adj_shrout', 'adj_prc', 'market_cap', '__index_level_0__']
shape: (5, 24)
| permno | permco | date | ret | retx | shrout | prc | vol | cfacshr | cfacpr | primaryexch | siccd | naics | issuertype | securitytype | securitysubtype | sharetype | usincflg | tradingstatusflg | altprc | adj_shrout | adj_prc | market_cap | __index_level_0__ |
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| i64 | i64 | datetime[ns] | f64 | f64 | f64 | f64 | f64 | f64 | f64 | str | i64 | str | str | str | str | str | str | str | f64 | f64 | f64 | f64 | i64 |
| 10000 | 7952 | 1986-01-31 00:00:00 | 0.707317 | 0.707317 | 3.68e6 | 4.375 | 177082.0 | 1.0 | 1.0 | "Q" | 3990 | "0" | "ACOR" | "EQTY" | "COM" | "NS" | "Y" | "A" | 4.375 | 3.68e6 | 4.375 | 1.61e7 | 0 |
| 10000 | 7952 | 1986-02-28 00:00:00 | -0.257143 | -0.257143 | 3.68e6 | 3.25 | 82800.0 | 1.0 | 1.0 | "Q" | 3990 | "0" | "ACOR" | "EQTY" | "COM" | "NS" | "Y" | "A" | 3.25 | 3.68e6 | 3.25 | 1.196e7 | 1 |
| 10000 | 7952 | 1986-03-31 00:00:00 | 0.365385 | 0.365385 | 3.68e6 | 4.4375 | 107801.0 | 1.0 | 1.0 | "Q" | 3990 | "0" | "ACOR" | "EQTY" | "COM" | "NS" | "Y" | "A" | 4.4375 | 3.68e6 | 4.4375 | 1.633e7 | 2 |
| 10000 | 7952 | 1986-04-30 00:00:00 | -0.098592 | -0.098592 | 3.793e6 | 4.0 | 95700.0 | 1.0 | 1.0 | "Q" | 3990 | "0" | "ACOR" | "EQTY" | "COM" | "NS" | "Y" | "A" | 4.0 | 3.793e6 | 4.0 | 1.5172e7 | 3 |
| 10000 | 7952 | 1986-05-30 00:00:00 | -0.222656 | -0.222656 | 3.793e6 | 3.109375 | 107362.0 | 1.0 | 1.0 | "Q" | 3990 | "0" | "ACOR" | "EQTY" | "COM" | "NS" | "Y" | "A" | 3.109375 | 3.793e6 | 3.109375 | 1.1794e7 | 4 |
date_col = [c for c in msf.columns if "date" in c.lower()][0]
print(f"Date range: {msf[date_col].min()} to {msf[date_col].max()}")
print(f"Unique PERMNOs: {msf['permno'].n_unique():,}")
Date range: 1925-12-31 00:00:00 to 2024-12-31 00:00:00
Unique PERMNOs: 31,716
CRSP_DSI.parquet#
Daily stock index — aggregate market returns and counts.
dsi = pl.read_parquet(DATA_DIR / "CRSP_DSI.parquet")
print(f"Shape: {dsi.shape}")
dsi.head(5)
Shape: (26051, 11)
shape: (5, 11)
| date | vwretd | vwretx | ewretd | ewretx | totval | totcnt | usdval | usdcnt | sprtrn | spindx |
|---|---|---|---|---|---|---|---|---|---|---|
| datetime[ns] | f64 | f64 | f64 | f64 | f64 | i64 | f64 | f64 | f64 | f64 |
| 1925-12-31 00:00:00 | null | null | null | null | 2.7487e7 | 503 | null | null | null | null |
| 1926-01-02 00:00:00 | 0.005689 | 0.005689 | 0.009516 | 0.009516 | 2.7600e7 | 497 | 2.7367e7 | 494.0 | null | null |
| 1926-01-04 00:00:00 | 0.000706 | 0.000706 | 0.00578 | 0.00578 | 2.7578e7 | 502 | 2.7480e7 | 495.0 | null | null |
| 1926-01-05 00:00:00 | -0.004821 | -0.004867 | -0.001927 | -0.00203 | 2.7530e7 | 501 | 2.7562e7 | 499.0 | null | null |
| 1926-01-06 00:00:00 | -0.000423 | -0.000427 | 0.001182 | 0.001155 | 2.7619e7 | 505 | 2.7527e7 | 500.0 | null | null |
date_col = [c for c in dsi.columns if "date" in c.lower()][0]
print(f"Date range: {dsi[date_col].min()} to {dsi[date_col].max()}")
Date range: 1925-12-31 00:00:00 to 2024-12-31 00:00:00
CRSP_MSIX.parquet#
Monthly stock index returns.
msix = pl.read_parquet(DATA_DIR / "CRSP_MSIX.parquet")
print(f"Shape: {msix.shape}")
msix.head(5)
Shape: (1189, 35)
shape: (5, 35)
| caldt | vwretd | vwindd | vwretx | vwindx | ewretd | ewindd | ewretx | ewindx | sprtrn | spindx | decret1 | decind1 | decret2 | decind2 | decret3 | decind3 | decret4 | decind4 | decret5 | decind5 | decret6 | decind6 | decret7 | decind7 | decret8 | decind8 | decret9 | decind9 | decret10 | decind10 | totval | totcnt | usdval | usdcnt |
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| datetime[ns] | f64 | f64 | f64 | f64 | f64 | f64 | f64 | f64 | f64 | f64 | f64 | f64 | f64 | f64 | f64 | f64 | f64 | f64 | f64 | f64 | f64 | f64 | f64 | f64 | f64 | f64 | f64 | f64 | f64 | f64 | f64 | i64 | f64 | f64 |
| 1925-12-31 00:00:00 | null | 1.474607 | null | 12.20626 | null | 0.404439 | null | 2.596484 | null | 12.46 | null | 0.101595 | null | 0.421553 | null | 0.856283 | null | 0.971508 | null | 0.784855 | null | 0.767571 | null | 0.828015 | null | 1.631464 | null | 0.935128 | null | 1.754715 | 2.7487e7 | 503 | null | null |
| 1926-01-30 00:00:00 | 0.000561 | 1.475434 | -0.001395 | 12.18923 | 0.023174 | 0.413812 | 0.021395 | 2.652037 | 0.022472 | 12.74 | 0.14366 | 0.11619 | 0.043982 | 0.440093 | 0.003839 | 0.859571 | 0.021334 | 0.992234 | 0.026445 | 0.805611 | -0.001635 | 0.766316 | 0.004253 | 0.831537 | 0.002818 | 1.636062 | 0.004726 | 0.939547 | -0.003837 | 1.747983 | 2.7624e7 | 506 | 2.7413e7 | 496.0 |
| 1926-02-27 00:00:00 | -0.033046 | 1.426677 | -0.036587 | 11.74326 | -0.05351 | 0.391668 | -0.055547 | 2.504724 | -0.043956 | 12.18 | -0.045718 | 0.110878 | -0.067602 | 0.410342 | -0.045779 | 0.820221 | -0.079699 | 0.913154 | -0.07086 | 0.748526 | -0.053558 | 0.725274 | -0.05208 | 0.78823 | -0.056464 | 1.543682 | -0.035508 | 0.906186 | -0.022392 | 1.708843 | 2.6752e7 | 514 | 2.7601e7 | 500.0 |
| 1926-03-31 00:00:00 | -0.064002 | 1.335367 | -0.070021 | 10.92098 | -0.096824 | 0.353745 | -0.101404 | 2.250734 | -0.059113 | 11.46 | -0.139863 | 0.095371 | -0.095192 | 0.371281 | -0.125909 | 0.716948 | -0.116952 | 0.806359 | -0.093662 | 0.678417 | -0.088367 | 0.661184 | -0.099882 | 0.7095 | -0.075299 | 1.427445 | -0.06946 | 0.843242 | -0.051874 | 1.620198 | 2.5083e7 | 519 | 2.6684e7 | 507.0 |
| 1926-04-30 00:00:00 | 0.037029 | 1.384814 | 0.034043 | 11.29277 | 0.032975 | 0.36541 | 0.030156 | 2.318607 | 0.022688 | 11.72 | 0.046633 | 0.099818 | 0.011215 | 0.375445 | 0.010318 | 0.724345 | 0.055662 | 0.851242 | 0.062817 | 0.721033 | 0.021249 | 0.675233 | 0.04172 | 0.7391 | 0.044396 | 1.490817 | 0.034477 | 0.872314 | 0.036293 | 1.679 | 2.5887e7 | 521 | 2.4921e7 | 513.0 |
FRED#
Macroeconomic time series from the Federal Reserve Economic Data service.
fred = pl.read_parquet(DATA_DIR / "fred.parquet")
print(f"Shape: {fred.shape}")
print(f"Columns: {fred.columns}")
fred.head(5)
Shape: (9699, 25)
Columns: ['GDP', 'CPIAUCNS', 'GDPC1', 'INDPRO', 'PAYEMS', 'DPCREDIT', 'EFFR', 'OBFR', 'SOFR', 'DFEDTARU', 'DFEDTARL', 'WALCL', 'TOTRESNS', 'TREAST', 'CURRCIR', 'GFDEBTN', 'WTREGEN', 'RRPONTSYAWARD', 'RRPONTSYD', 'RPONTSYD', 'WSDONTL', 'Gen_IORB', 'ONRRP_CTPY_LIMIT', 'ONRP_AGG_LIMIT', 'DATE']
shape: (5, 25)
| GDP | CPIAUCNS | GDPC1 | INDPRO | PAYEMS | DPCREDIT | EFFR | OBFR | SOFR | DFEDTARU | DFEDTARL | WALCL | TOTRESNS | TREAST | CURRCIR | GFDEBTN | WTREGEN | RRPONTSYAWARD | RRPONTSYD | RPONTSYD | WSDONTL | Gen_IORB | ONRRP_CTPY_LIMIT | ONRP_AGG_LIMIT | DATE |
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| f64 | f64 | f64 | f64 | f64 | f64 | f64 | f64 | f64 | f64 | f64 | f64 | f64 | f64 | f64 | f64 | f64 | f64 | f64 | f64 | f64 | f64 | f64 | f64 | datetime[ns] |
| null | 9.8 | null | null | null | null | null | null | null | null | null | null | null | null | null | null | null | null | null | null | null | null | null | null | 1913-01-01 00:00:00 |
| null | 9.8 | null | null | null | null | null | null | null | null | null | null | null | null | null | null | null | null | null | null | null | null | null | null | 1913-02-01 00:00:00 |
| null | 9.8 | null | null | null | null | null | null | null | null | null | null | null | null | null | null | null | null | null | null | null | null | null | null | 1913-03-01 00:00:00 |
| null | 9.8 | null | null | null | null | null | null | null | null | null | null | null | null | null | null | null | null | null | null | null | null | null | null | 1913-04-01 00:00:00 |
| null | 9.7 | null | null | null | null | null | null | null | null | null | null | null | null | null | null | null | null | null | null | null | null | null | null | 1913-05-01 00:00:00 |
date_col = [c for c in fred.columns if "date" in c.lower()][0]
print(f"Date range: {fred[date_col].min()} to {fred[date_col].max()}")
Date range: 1913-01-01 00:00:00 to 2026-02-12 00:00:00
# Non-null counts per series
fred.select(pl.all().is_not_null().sum()).unpivot(
variable_name="series",
value_name="non_null_count",
).sort("non_null_count")
shape: (25, 2)
| series | non_null_count |
|---|---|
| str | u32 |
| "GFDEBTN" | 239 |
| "GDP" | 315 |
| "GDPC1" | 315 |
| "PAYEMS" | 1045 |
| "INDPRO" | 1284 |
| … | … |
| "WTREGEN" | 7872 |
| "WSDONTL" | 7872 |
| "TOTRESNS" | 9147 |
| "CURRCIR" | 9644 |
| "DATE" | 9699 |
Market Data#
Computed market-level return and volatility measures.
market = pl.read_parquet(DATA_DIR / "market_data.parquet")
print(f"Shape: {market.shape}")
market.head(5)
Shape: (402, 3)
shape: (5, 3)
| market_return | market_volatility | date |
|---|---|---|
| f64 | f64 | datetime[ns] |
| -0.925869 | 0.62667 | 1984-01-01 00:00:00 |
| -3.963452 | 1.012833 | 1984-02-01 00:00:00 |
| 1.340771 | 0.799722 | 1984-03-01 00:00:00 |
| 0.545069 | 0.737019 | 1984-04-01 00:00:00 |
| -6.119101 | 0.605125 | 1984-05-01 00:00:00 |
market.describe()
shape: (9, 4)
| statistic | market_return | market_volatility | date |
|---|---|---|---|
| str | f64 | f64 | str |
| "count" | 402.0 | 402.0 | "402" |
| "null_count" | 0.0 | 0.0 | "0" |
| "mean" | 0.66851 | 0.961621 | "2000-09-15 09:43:52.835820" |
| "std" | 4.337423 | 0.600189 | null |
| "min" | -24.542803 | 0.256884 | "1984-01-01 00:00:00" |
| "25%" | -1.693331 | 0.620898 | "1992-05-01 00:00:00" |
| "50%" | 1.061761 | 0.804608 | "2000-10-01 00:00:00" |
| "75%" | 3.458121 | 1.095015 | "2009-02-01 00:00:00" |
| "max" | 12.378003 | 6.119488 | "2017-06-01 00:00:00" |