Mapping CUSIP to Ticker: An Important Step for Stock Market Investors

Investing in the stock market requires thorough research and analysis. One of the key aspects of this process is mapping a security's CUSIP (Committee on Uniform Securities Identification Procedures) to its ticker. In this article, we'll explain how accomplishes this task, and why it matters for stock market investors.

Fetching the Fails-to-Deliver data

The website provides a fails-to-deliver data section, which lists all securities that failed to be delivered on the listed settlement date. This data contains both the CUSIP and the ticker, which can be used to map CUSIPs to tickers.

As a stock analyst, you can use this data to gain insights into the market. For example, you can identify stocks with high fails-to-deliver rates, which may indicate short selling or other market inefficiencies.

The fails-to-deliver data includes the following information for each security:

  • Date
  • 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

It's worth noting that a ticker with a ZZZZ or XXXX suffix indicates a special event, such as 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 removes the XXXX and ZZZZ suffixes automatically.

Storing the data

The fails-to-deliver data is a valuable resource for stock market investors, and should be stored for later analysis.

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


By storing the data in a database, you can easily access it and use it to inform your investment decisions.

In conclusion, mapping CUSIPs to tickers is an important step for stock market investors. By fetching and storing fails-to-deliver data, you can gain valuable insights into the market and make informed investment decisions. If you have any questions or suggestions, please don't hesitate to contact us at [email protected].


  • CUSIP: A unique identification number assigned to stocks and bonds registered in the United States.
  • Ticker: A short symbol representing a particular stock or security that is traded on a stock exchange.
  • Fails-to-Deliver data: Information on securities that have failed to be delivered on the scheduled settlement date.
  • SEC: The United States Securities and Exchange Commission, a government agency responsible for regulating the securities industry.
  • Database: A structured collection of data that is organized and stored in a computer system.
  • Parsing: The process of analyzing data to extract relevant information.
  • Issuer: The entity that issues a security and is responsible for meeting the associated obligations.
  • SQL schema: A blueprint that defines the structure of a database and the relationships between its components.
  • Settlement date: The date on which securities trades must be settled, typically three business days after the trade date.

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