quantdata.widb.client#

WIDB connection and OptionMetrics query helpers#

Wraps the wrds Python library to execute SQL queries against the OptionMetrics (IvyDB) dataset into a quantdata.widb.WIDBClient class which provides access via the following functions:

  • quantdata.widb.WIDBClient.lookup_secid(ticker)(): look up the OptionMetrics secid for a given ticker symbol.

  • quantdata.widb.WIDBClient.zero_curve(date)(): fetch the zero-coupon yield curve for a given date.

  • quantdata.widb.WIDBClient.spot_price(secid, date)(): fetch the spot price of the underlying for a given secid and date.

  • quantdata.widb.WIDBClient.option_prices(secid, date)(): fetch raw option price records for a given secid and date.

  • quantdata.widb.WIDBClient.forward_prices(secid, date)(): fetch forward prices for a given secid and date

class quantdata.widb.client.WRDSCredentials(username, wrds_off=False)[source]#

Bases: object

WIDB login credentials read from environment variables.

wrds_off: bool = False#

If True, WIDB access is disabled. To disable WIDB access, set the environment variable WRDS_OFF to 1.

static create()[source]#

Returns dys.core.Environment.WRDSCredentials with WIDB credentials read from WRDS_USERNAME

class quantdata.widb.client.WIDBClient(connect=False)[source]#

Bases: object

WIDB connectivity. See the IvyDB reference manual for details on the OptionMetrics dataset and table schemas.

OPTION_COLS = {'am_settlement', 'best_bid', 'best_offer', 'contract_size', 'cp_flag', 'delta', 'exdate', 'exercise_style', 'expiry_indicator', 'gamma', 'impl_volatility', 'open_interest', 'optionid', 'ss_flag', 'strike_price', 'symbol', 'theta', 'vega', 'volume'}#

Columns returned by option_prices() queries.

static credentials()[source]#

Returns WIDB credentials read from environment variables.

At the moment this is just the username.

static connect()[source]#

Establish a WIDB connection if not already connected. Returns a wrds.Connection object.

Users usually do not have to call this function directly when using any of the prepared cached functions below as those will obtain a connection lazily themselves.

This function is provided for native WRDS access.

ticker_to_secid_map()[source]#

Fetch a mapping of all tickers to secids from the securd table. Use quantdata.widb.client.WIDBClient.lookup_secid() for individual lookups, which is cached separately.

lookup_secid(ticker)[source]#

Look up the OptionMetrics (IvyDB) secid for a given ticker.

Parameters:

ticker (str) – Ticker symbol, e.g. ‘SPX’.

Returns:

secid – The secid.

Return type:

int

Raises:

Not found – LookupError: If the ticker is not found.

lookup_ticker(secid)[source]#

Look up the ticker symbol for a given OptionMetrics (IvyDB) secid.

Parameters:

secid (int) – The secid.

Returns:

ticker – Ticker symbol, e.g. ‘SPX’.

Return type:

str

Raises:

Not found – LookupError: If the secid is not found.

options(secid, date, *, verbose=<cdxcore.verbose.Context object>)[source]#

Fetch all option price records for a given secid on a single date.

Columns returned are contained in quantdata.widb.client.WIDBClient.OPTION_COLS, which includes: ‘symbol’, ‘optionid’, ‘am_settlement’, ‘cp_flag’, ‘exercise_style’, ‘strike_price’, ‘exdate’, ‘best_bid’, ‘best_offer’, ‘volume’, ‘open_interest’, ‘impl_volatility’, ‘delta’, ‘gamma’, ‘vega’, ‘theta’, ‘ss_flag’, ‘expiry_indicator’, ‘contract_size’. See the IvyDB reference manual page 20.

Parameters:
Returns:

Raw option prices for the date.

Return type:

pd.DataFrame

zero_curve(date, *, verbose=<cdxcore.verbose.Context object>)[source]#

Fetch the zero-coupon yield curve for a given date.

Returns columns: ‘date’, ‘days’, ‘rate’ Rate is continuously compounded using ACT/365, i.e. df = exp(-rate * calendar_days/365).

Use quantdata.widb.client.interpolate_zero_curve() to interpolate discount factors for specific days to expiry.

Parameters:
  • date (date) – Observation date.

  • verbose (Context) – Channel to print verbosity information, if any.

Returns:

Zero curve – Zero-curve data. Use quantdata.widb.client.interpolate_zero_curve() to interpolate discount factors for specific days to expiry.

Return type:

pandas.DataFrame

forwards(secid, date, *, verbose=<cdxcore.verbose.Context object>)[source]#

Fetch forward prices for a given secid on a date.

Returns columns: date, exdate, am_settlement, forward_price

Parameters:
Returns:

Forward table – Forward price data with columns date, exdate, am_settlement, forward_price.

Return type:

pandas.DataFrame

spot_price(secid, date, *, verbose=<cdxcore.verbose.Context object>)[source]#

Fetch the spot (close) price of the underlying for a given date.

Parameters:
Returns:

Spot – Spot price.

Return type:

float

Raises:

Spot not found – LookupError: If no spot price is available. This likely means either the secid is not covered by Option Metrics, or date is not a business day for this underlying.

quantdata.widb.client.interpolate_zero_curve(zero_curve, days_to_expiry)[source]#

Interpolate discount factors from the zero-coupon curve using ACT/365.

Parameters:
  • zero_curve (DataFrame) – Must have columns days and rate.

  • days_to_expiry (ndarray) – Calendar days to each expiry, as floats

Returns:

Discount factors – Discount factors exp(-rate * days / 365).

Return type:

numpy.ndarray