How to Map a CUSIP to a Ticker

In this article, we explain how formthirteen.com is able to map a security's CUSIP to a ticker.

Fetching the Fails-to-Deliver data

The SEC.gov site has a section that lists all securities that failed to be delivered on the listed settlement date. This data contains both the CUSIP and the ticker, so by fetching and parsing this data we are able to map CUSIPs to tickers.

Each row in a Fails-to-Deliver data file contains the following:

  • Date
  • CUSIP
  • Ticker
  • Issuer name
  • Price
  • Total number of fails-to-deliver

For example, the following row tells us that Alphabet's ticker is GOOGL and the CUSIP is 02079K305:

20151006|02079K305|GOOGL|104775|ALPHABET INC CAP STK CL A|671.68

A ticker having a ZZZZ or XXXX suffix indicates a special event such as, for example, a spinoff or merger. For example, when Google became Alphabet the ticker was incorrectly listed as GOOGXXXX:

20151014|38259P706|GOOGXXXX|2|GOOGLE INC CLASS C|626.91

formthirteen.com removes the XXXX and ZZZZ suffixes automatically.

Storing the data

The Fails-to-Deliver data is stored in a database for later retrieval.

The following SQL schema can be used to store the data:

CREATE TABLE fails_to_deliver
(
  id serial primary key,
  settlement_date date not null,
  cusip varchar not null,
  ticker varchar not null,
  name varchar not null
);

Found an error? Have any tips or tricks you want to share with us? Please contact us at [email protected].