Monday, July 29, 2019 at 1:50 PM #147463
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
Monday, August 5, 2019 at 3:13 PM #148521Jason ZanfardinoParticipant
Thanks for the tip!
Thursday, April 16, 2020 at 3:23 AM #175411keo macaoParticipant
Saturday, July 4, 2020 at 9:31 PM #184377
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”.
Saturday, July 4, 2020 at 10:14 PM #184378
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
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.
Sunday, July 5, 2020 at 12:24 AM #184383
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 email@example.com?
Sunday, July 5, 2020 at 2:21 AM #184384
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
Sunday, July 5, 2020 at 3:02 AM #184385
I have found the other way to get this ticker. you can use the one of the provided functions (TICKER)
Sunday, July 5, 2020 at 6:33 AM #184386
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.
Sunday, July 5, 2020 at 9:36 AM #184387
Thanks Tim for the file. I agree the logic you provided works for 10-K and not for the others. 10
Sunday, July 19, 2020 at 1:53 PM #184700
Sunday, July 19, 2020 at 2:11 PM #184704
Thanks so much for sending the link, Arthur! I didn’t even know this file exists. Thanks again!
- You must be logged in to reply to this topic.
Documentation & Discussion
- Get started with Google Sheets OR
- Get started with Microsoft Excel OR
- Get access to as-filed data from us for other tools or your own app
- XBRL API Interactive Documentation
- The XBRL API
- XBRL Data Community
- 2022 US GAAP Taxonomy Viewer
- Live support - Monday, 3:30 - 4:30 PM ET
Join XBRL US
- Individual Options - Basic, Power User & Sole Practitioner
- For Your Team - Startup, Non-Profit, Academic & Corporate options
- Member Benefits Comparison Table
Using the XBRL API with the Public Filings Database
Unless otherwise agreed to in writing, any and all use of the XBRL API to authenticate and retrieve data from the XBRL US Database of Public Filings implies user consent and agreement with the XBRL US API Agreement. If you are unable to agree to these terms, do not use the XBRL API.
To use the XBRL API outside of Google Sheets, your account needs to be provisioned for OAuth2 access.