CIK and Ticker

[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)
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

[Jason Zanfardino]

Thanks for the tip!

[keo macao]

yes tks

[Hasnain jaffery]

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

[Tim Bui]

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

[Hasnain jaffery]

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

[Hasnain jaffery]

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

[Hasnain jaffery]

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

[Tim Bui]

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

[Hasnain jaffery]

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

[Arthur Olevskiy]

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

[Tim Bui]

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

[Author]

Posts