quantlab.acquisition.wrds.crsp.CrspQueries#

class quantlab.acquisition.wrds.crsp.CrspQueries[source]#

Bases: object

SQL statements for the CRSP tables on WRDS, and the calls that run them.

The builder methods are pure: they return a psycopg2.sql composable (an object that renders to safely quoted SQL) and open no connection, so their exact text can be tested without a server. The network methods take a session and only hand a built statement to the session’s schema_usable, fetch_rows or copy_csv. Every value is a sql.Literal and every table or column name a sql.Identifier. No SQL is assembled by string formatting, which also makes reserved column names such as from and thru in comp.idxcst_his safe to use.

Attributes:

STOCK_SCHEMAstr: The CRSP annual-update stock schema.
INDEX_SCHEMAstr: The CRSP annual-update index schema.
COMPUSTAT_SCHEMAstr: The Compustat schema (company fundamentals and index membership).
CCM_SCHEMAstr: The CRSP/Compustat Merged schema, which links the two databases.
DAILY_TABLEstr: The daily stock table inside STOCK_SCHEMA.

Examples

>>> where = CrspQueries.daily_where([14593], "2020-08-01", "2020-08-31")
>>> query = CrspQueries.copy_query(
...     "crsp_a_stock", "dsf_v2", ("permno", "dlycaldt", "dlyprc"), where
... )

Running it needs a live WrdsSession:

raw = CrspQueries.copy(
    session, "crsp_a_stock", "dsf_v2", ("permno", "dlycaldt"), where
)

DAILY_TABLE = 'dsf_v2'#: The daily table, unique on (permno, dlycaldt). If that ever stops holding, stkdlysecuritydata is the fallback, and this constant is the only thing to change.

classmethod assert_entitled(session, schemas)[source]#

Check that the account may read every schema in schemas.

Called before the first data query of a download, so a product the account has not subscribed to stops the run before any data is copied, instead of failing every batch.

Parameters:

sessionWrdsSession: The shared WRDS session.
schemasiterable of str: Schema names to check.

Raises:

WrdsEntitlementError: Naming every schema the account cannot read.

Examples

Needs a live WrdsSession:

CrspQueries.assert_entitled(session, (CrspQueries.STOCK_SCHEMA,))

classmethod columns_query(schema, table)[source]#

Build the query that lists one table’s columns and their positions.

It reads the standard information_schema.columns catalogue. Here the schema and table names are compared as values, so they are passed as literals. The result is sorted by ordinal_position in Python rather than with ORDER BY, which keeps this module free of ordering clauses.

Parameters:

schemastr: Schema name.
tablestr: Table name.

Returns:

psycopg2.sql.Composed: A query returning (column_name, ordinal_position) rows.

Examples

>>> query = CrspQueries.columns_query("crsp_a_stock", "dsf_v2")

which renders as:

SELECT "column_name", "ordinal_position"
FROM "information_schema"."columns"
WHERE "table_schema" = 'crsp_a_stock' AND "table_name" = 'dsf_v2'

classmethod copy(session, schema, table, columns, where)[source]#

Run the statement from copy_query and return the CSV bytes.

Parameters:

sessionWrdsSession: The shared WRDS session.
schemastr: Schema name.
tablestr: Table name.
columnsiterable of str: Column names to select, in this order.
wherepsycopg2.sql.Composable or None: The condition, or None for the whole table.

Returns:

bytes: The CSV output, starting with a header line.

Examples

Needs a live WrdsSession:

raw = CrspQueries.copy(
    session, "crsp_a_stock", "stkdelists", ("permno", "delret"), None
)

classmethod copy_query(schema, table, columns, where=None)[source]#

Build a COPY (SELECT ...) TO STDOUT statement in CSV format.

COPY ... TO STDOUT streams the query result to the client as CSV, which is much faster than fetching rows one by one. The statement has no ORDER BY, GROUP BY or DISTINCT, so rows arrive exactly as the server stores them.

Parameters:

schemastr: Schema name, quoted as an identifier.
tablestr: Table name, quoted as an identifier.
columnsiterable of str: Column names to select, in this order.
wherepsycopg2.sql.Composable or None, default None: An optional condition, such as the result of daily_where.

Returns:

psycopg2.sql.Composed: The complete statement. The CSV output includes a header line.

Examples

>>> query = CrspQueries.copy_query(
...     "crsp_a_stock", "dsf_v2", ("permno", "dlycaldt", "dlyprc"),
...     CrspQueries.daily_where([14593], "2020-08-01", "2020-08-31"),
... )

which renders as:

COPY (SELECT "permno", "dlycaldt", "dlyprc"
      FROM "crsp_a_stock"."dsf_v2"
      WHERE "permno" = ANY(ARRAY[14593])
        AND "dlycaldt" BETWEEN '2020-08-01' AND '2020-08-31')
TO STDOUT WITH (FORMAT csv, HEADER true)

classmethod count(session, schema, table, where)[source]#

Return the number of rows in the table that match where.

Parameters:

sessionWrdsSession: The shared WRDS session.
schemastr: Schema name.
tablestr: Table name.
wherepsycopg2.sql.Composable or None: The condition, or None to count the whole table.

Returns:

int: The row count.

Examples

Needs a live WrdsSession:

rows = CrspQueries.count(session, "crsp_a_stock", "stkdelists", None)

classmethod count_query(schema, table, where=None)[source]#

Build SELECT count(*) over the same where a COPY uses.

Passing the same condition object to both statements guarantees that a count and the download it checks select the same rows.

Parameters:

schemastr: Schema name.
tablestr: Table name.
wherepsycopg2.sql.Composable or None, default None: An optional condition.

Returns:

psycopg2.sql.Composed: The count statement.

Examples

>>> query = CrspQueries.count_query("crsp_a_stock", "stkdelists")

which renders as:

SELECT count(*) FROM "crsp_a_stock"."stkdelists" 

classmethod daily_where(permnos, start, end)[source]#

Build the WHERE condition selecting PERMNOs and a date range.

PERMNOs are converted to int, so a value that is not a PERMNO cannot reach the statement, even as a quoted literal.

Parameters:

permnositerable of int or str: The PERMNOs to select.
start, endstr, date or datetime: First and last calendar day, inclusive.

Returns:

psycopg2.sql.Composed: The condition, without the WHERE keyword.

Raises:

ValueError: If permnos is empty. Without the PERMNO condition the query would read the whole 110-million-row table.

Examples

>>> where = CrspQueries.daily_where(
...     ["14593", "10107"], "2020-01-01", "2020-12-31"
... )

which renders as:

"permno" = ANY(ARRAY[14593, 10107])
AND "dlycaldt" BETWEEN '2020-01-01' AND '2020-12-31'

classmethod product_end(session)[source]#

Return the last date in the daily table.

This date identifies the annual release (vintage) the account currently reads.

Parameters:

sessionWrdsSession: The shared WRDS session.

Returns:

datetime.date: The last dlycaldt in dsf_v2.

Raises:

CrspProductEndError: If the table reports no maximum date.

Examples

Needs a live WrdsSession:

product_end = CrspQueries.product_end(session)

classmethod product_end_query()[source]#

Build the query for the last date in the daily table.

The answer identifies which annual release (vintage) the account currently reads.

Returns:

psycopg2.sql.Composed: A query returning one row with one date.

Examples

>>> query = CrspQueries.product_end_query()

which renders as:

SELECT max("dlycaldt") FROM "crsp_a_stock"."dsf_v2" 

classmethod table_columns(session, schema, table)[source]#

Return the table’s column names in the server’s column order.

Parameters:

sessionWrdsSession: The shared WRDS session.
schemastr: Schema name.
tablestr: Table name.

Returns:

tuple of str: Column names, sorted by their position in the table.

Examples

Needs a live WrdsSession:

columns = CrspQueries.table_columns(session, "crsp_a_stock", "dsf_v2")