Home Forums The XBRL API CIK and Ticker

This topic contains 1 reply, has 2 voices, and was last updated by  Jason Zanfardino 8 months, 1 week ago.

  • Author
  • #147463

    Tim Bui

    I spent several days looking for the most up-to-date list of CIKs and their correspondent TICKERs and I found a solution that wanted to share with some of you, in case someone is trying to do the same thing.

    1. The SEC has a link where it posts all of the quarterly filings (Financial Statement Data Sets)
    2. You can download the zip file of the most recent quarterly filing (as of now 2019 Q2). After extracting, within each zip file are num.txt, pre.txt, sub.txt, tag.txt and a readme link.
    3. The sub.txt holds all of the information of each of the submissions. Within it is a column named INSTANCE. The readme describes INSTANCE as “The name of the submitted XBRL Instance Document (EX-101.INS) type data file. The name often begins with the company ticker symbol.”
    The INSTANCES for MSFT would look like the list below:
    4. You can use your favorite program such as Excel or Python to strip out the characters to the left of the hyphen to get the tickers.

    I use this code in SQL to get the tickers:
    upper(substring(sub.instance,0,CHARINDEX(‘-‘,sub.instance))) as Ticker

    Since some of the filings do not have tickers at the beginning,
    I use this SQL code to get tickers with 5 characters or less

    where len(substring(sub.instance,0,CHARINDEX(‘-‘,sub.instance)) ) between 1 and 5



  • #148521

    Jason Zanfardino

    Thanks for the tip!

You must be logged in to reply to this topic.