Home Forums The XBRL API CIK and Ticker

Tagged: 

Viewing 11 reply threads
  • Author
    Posts
    • #147463
      Tim Bui
      Participant

      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)
      https://www.sec.gov/dera/data/financial-statement-data-sets.html
      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:
      msft-20090930.xml
      msft-20091231.xml
      msft-20100331.xml
      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

      Regards,

      Tim

    • #148521
      Jason Zanfardino
      Participant

      Thanks for the tip!

    • #175411
      keo macao
      Participant

      yes tks

    • #184377
      Hasnain jaffery
      Participant

      I tried this for APPLE and it does not work at all. SEC must provide something standard as per document “XBRL Application Programing Interface (API) version 1.4 Draft” Page 17 talks about table REPORTS where there is a field by the name entity.ticker. But in actual this field does not exist in the table “Field”.
      Regards,
      Hasnain

    • #184378
      Tim Bui
      Participant

      Hi Hasnain, would you please elaborate what data that you wanted to get but couldn’t? Per your email, The CIK of “320193” is correct for APPLE
      (https://www.sec.gov/cgi-bin/viewer?action=view&cik=320193&accession_number=0001193125-20-139112&xbrl_type=v)

      I am able to get all of the CIK and their corespondent ticker from the SEC Financial Statement Data Set (FSDS).

      I am not sure if XBRL US allows, but if it does, I can upload the list of CIK and Ticker that I derived form the FSDS for you to peruse.

      Tim

    • #184383
      Hasnain jaffery
      Participant

      Thanks Tim, I agree based on CIK list you can pull all the data from FSDS. but to me one the basic thing is missing which is the ticker and its history. For example some companies they merged together or bought out by another cone and ticker got changed. Without having ticker the SEC information seems to be little incomplete.
      All I am trying to do it build a search to FSDS reports (including historic as well) based on ticker and at the moment it doesn’t seems to be that easy.
      Can you share that with me at hjaffery@yahoo.com?
      thanks

    • #184384
      Hasnain jaffery
      Participant

      I am new to XBRL and I am a student at a very basic level, so I might be incorrect, if so please correct me I would highly appreciate it.
      I need to cite the example of APPLE and compare it with MSFT (as you have used as an example)
      If you check MSFT for this last quarter
      CIK NAME FORM Period Instance
      789019 MSFT 8-K 20200131 d875975d8k_htm.xml
      789019 MSFT 10-Q 20191231 msft-10q_20191231_htm.xml
      320193 AAPL 8-K 20200131 a8-kq1202012282019_htm.xml
      320193 AAPL 10-Q 20200129 a10-qq1202012282019_htm.xml

      May be I am wrong, please correct me. But based what I see the logic you have provided is not going to work.
      Would be happy to discuss this with you in more detail
      Regards,
      Hasnain

    • #184385
      Hasnain jaffery
      Participant

      I have found the other way to get this ticker. you can use the one of the provided functions (TICKER)

    • #184386
      Tim Bui
      Participant

      Hello, Hasnain,
      As you can see from the examples you provided, the “funny” codes on the xml lines related to the ‘8-K’ reports. The ticker codes are correct for ’10-K’ or ’10-Q’. I had to narrow to screen down to only ’10-K’ or ’10-Q’ to get the ‘Instance’ that contain the tickers.

      I had emailed you the list of CIKs and Tickers that I derived from the FSDS.

      I have not attempted to get historical Tickers or CIKs, but from seeing that the sub.txt file within FSDS has columns named “former” and “changed” for former name sand change dates, I think it is possible to get that information.

      Regards,
      Tim

    • #184387
      Hasnain jaffery
      Participant

      Thanks Tim for the file. I agree the logic you provided works for 10-K and not for the others. 10

    • #184700
      Arthur Olevskiy
      Participant

      Hi guys, I don’t know if it helps you but I’m using this json file
      https://www.sec.gov/files/company_tickers.json

    • #184704
      Tim Bui
      Participant

      Thanks so much for sending the link, Arthur! I didn’t even know this file exists. Thanks again!

Viewing 11 reply threads
  • You must be logged in to reply to this topic.

Comment