quantlab.acquisition.wrds.crsp_reference.CrspReferenceTables#

class quantlab.acquisition.wrds.crsp_reference.CrspReferenceTables(session, reference_dir)[source]#

Bases: object

Download the CRSP, Compustat and CCM reference tables into one directory.

An instance holds only the shared session and the destination directory. The release, the index universes and whether to download again are arguments to pull, so one instance can serve several releases.

Several safety checks run, in this order. Every table is counted before it is copied, and refused if it has more than MAX_REFERENCE_ROWS rows; because each table is copied whole, the count is the only guard against a renamed table turning into an unbounded download. The copy must then return exactly the counted number of rows. Each file is written to a temporary file in the destination directory and renamed into place. The manifest is written last, so an interrupted download never leaves a manifest naming a table it did not finish. Every value in SQL is a sql.Literal and every table or column name a sql.Identifier.

Parameters:

sessionWrdsSession: The shared WRDS session.
reference_dirstr or path-like: The destination directory, usually WrdsCrspDailyAcquisition.reference_dir_for(cfg).

Examples

Needs a live WrdsSession:

tables = CrspReferenceTables(WrdsSession.shared(), reference_dir)
manifest = tables.pull(product_end="2025-12-31", include_sp500=True)
sorted(manifest)
# ['product_end', 'pulled_at', 'tables']
sorted(manifest["tables"])
# ['dsp500list_v2', 'stkdelists', 'stkdistributions', 'stksecurityinfohist']

MAX_REFERENCE_ROWS = 5000000#: Refuse any single reference table larger than this. Well above the real sizes (the largest, stkdistributions, has about 1.1M rows), yet small enough that a table that turned into something else, such as a view over the daily prices, stops the run instead of streaming.

NASDAQ100_TABLES = ('idxcst_his', 'ccmxpf_lnkhist')#: Compustat index membership, then the CRSP/Compustat link. The order matters: the link table is filtered to the gvkeys that idxcst_his returned.

NDX_GVKEYX = '000208'#: The Nasdaq 100’s index id (gvkeyx) in comp.idx_index.

SP500_TABLES = ('dsp500list_v2',)#: CRSP’s own S&P 500 membership, by PERMNO.

STOCK_TABLES = ('stksecurityinfohist', 'stkdelists', 'stkdistributions')#: Always downloaded: security names and tickers over time, delistings, and distributions.

property manifest_path#

Return the manifest.json path inside the reference directory.

Examples

>>> CrspReferenceTables(None, "ref").manifest_path.name
'manifest.json'

path_for(name)[source]#

Return the parquet path for the reference table name.

Parameters:

namestr: The table name, for example "stkdelists".

Returns:

pathlib.Path: reference_dir / f"{name}.parquet".

Examples

>>> tables = CrspReferenceTables(None, "ref")
>>> tables.path_for("stkdelists").name
'stkdelists.parquet'

pull(*, product_end, include_sp500=True, include_nasdaq100=False, refresh=False)[source]#

Download the requested reference tables for one CRSP release.

The release check comes first, before the subscription check and before any count. If the manifest on disk already covers every requested table for this product_end, nothing is queried and the existing manifest is returned. A different product_end is a different release, and CRSP revises past data between releases, so every requested table is downloaded again rather than mixed with files from the previous release. The subscription is checked only for the requested tables’ schemas, so an S&P-only download never asks whether the account can read Compustat (comp).

Parameters:

product_endstr or date: The last day of the CRSP release, as returned by CrspQueries.product_end.
include_sp500bool, default True: Also download dsp500list_v2, CRSP’s S&P 500 membership.
include_nasdaq100bool, default False: Also download idxcst_his and ccmxpf_lnkhist, which need the Compustat and CCM subscriptions.
refreshbool, default False: Download again tables that are already on disk for this release.

Returns:

dict: The manifest written to manifest.json: product_end, pulled_at and a tables map of {schema, table, rows, where} entries.

Raises:

WrdsEntitlementError: If the account cannot read a needed schema.
ValueError: If a table is larger than MAX_REFERENCE_ROWS, a copy does not match its count or columns, or the Nasdaq 100 membership is empty.

Examples

Needs a live WrdsSession:

manifest = tables.pull(product_end="2025-12-31")
manifest["tables"]["stkdelists"]["schema"]
# 'crsp_a_stock'
tables.pull(product_end="2025-12-31") == manifest  # nothing queried
# True

read_manifest()[source]#

Return the manifest on disk, or None if there is none.

A manifest that cannot be parsed is treated as missing, with a warning, rather than raising. The only effect is that the caller downloads again, which is safe. Refusing to download because the manifest is corrupt would leave the directory fixable only by hand.

Returns:

dict or None: The parsed manifest, or None if it is missing, unreadable or not a JSON object.

Examples

>>> CrspReferenceTables(None, "no/such/dir").read_manifest() is None
True

requested_tables(*, include_sp500, include_nasdaq100)[source]#

Return the table names a download covers, in download order.

Parameters:

include_sp500bool: Whether to add the S&P 500 membership table.
include_nasdaq100bool: Whether to add the Nasdaq 100 membership and link tables.

Returns:

tuple of str: The table names.

Examples

>>> tables = CrspReferenceTables(None, "ref")
>>> tables.requested_tables(include_sp500=True, include_nasdaq100=False)
('stksecurityinfohist', 'stkdelists', 'stkdistributions', 'dsp500list_v2')