Tagged: Tickers associated with CIKs
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 #148521
Thanks for the tip!
You must be logged in to reply to this topic.
CIK and Ticker 1 week, 5 days ago
Extracting Data From A Particular Financial Statement 2 weeks, 3 days ago
Missing years 1 month ago
Period details for facts 1 month, 3 weeks ago
Extracting Data From A Particular Financial Statement 1 month, 3 weeks ago
XBRL API Resources
- The XBRL API
- XBRL Data Community
- XBRL API Documentation (PDF)
- Get Data with Google Sheets
- Sample Queries (Insomina REST client) - requires account provision
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.
Ready to work with the API in Excel's Power Query, or with your own system or app?
Contact us at firstname.lastname@example.org to have your existing XBRL US Web account provisioned to generate client ID/Secret pairs to work with the XBRL API in a REST client or other application, including Excel's Power Query.
NOTE: You do not need to generate client ID/Secret pairs if you use the Google Add-on and Google Sheet exclusively to access data - the XBRL API Authentication Add-on handles this automatically.
Your account needs to be provisioned before you can login and generate client ID/secret pairs.
Login or register for a free account.
Join XBRL US
- Individual Options - Basic, Power User & Sole Practitioner
- For Your Team - Startup, Non-Profit, Academic & Corporate options
- Member Benefits Comparison Table