Analytics mindset
EDGAR Explorer
Company Facts API
The SEC’s company facts API is accessed via https://data.sec.gov/api/xbrl/companyfacts/CIK##########.json by replacing the 10 hashes with the entity’s unique 10-digit central index key (CIK). For example, using Tesla’s CIK “0001318605” the company facts file for Tesla can be found at:
https://data.sec.gov/api/xbrl/companyfacts/CIK0001318605.json
The company facts file contains both metadata about the entity (the CIK and Company Name), and a large amount of data included under the header ‘facts’. Nested inside ‘facts’ is the document and entity information (‘dei’) and financial statements prepared under US GAAP (‘us-gaap’). Financial data is provided within the ‘us-gaap’ section and includes:
Information on each account, including the label and description of the account, for example, in our using Tesla’s company facts file, the header data on Accounts Receivable can be seen as follows:
"us-gaap":{"AccountsAndNotesReceivableNet":{"label":"Accounts and Financing Receivable, after Allowance for Credit Loss","description":"Amount, after allowance for credit loss, of accounts and financing receivable. Includes, but is not limited to, notes and loan receivable."
Following the descriptive data, which appears for every account, financial data is contained under the heading ‘units’ which contains information on the currency the account is reported in, the amount and information on the period and form the account relates to. In our Tesla example:
"end":"2021-12-31","val":299000000,"accn":"0000950170-22-000796","fy":2021,"fp":"FY","form":"10-K","filed":"2022-02-07","frame":"CY2021Q4I"
Note here that the currency is US Dollars, and that Tesla’s accounts receivable as at the 31st of December ("end":"2021-12-31") was $299 million ("val":299000000) which relates to the fiscal year 2021 ("fy":2021,"fp":"FY") as reported in the 10-K ("form":"10-K") which was filed with the SEC on February 7th, 2022 ("filed":"2022-02-07").
Whereas the data does not appear easy to read for us as humans, it is highly structured, which is great for computer processing.
To prepare this data for analysis will require extracting data from the nested JSON structure. Fortunately, as the data is provided in this structure for all entities reporting to the SEC, the extract, transform, and load task is a great candidate for automation.
Required – Alteryx
Pick a company and find its CIK using the EDGAR Company Filings CIK Lookup Tool at https://www.sec.gov/edgar/searchedgar/companysearch.
Using Alteryx (or Python):
Use the download tool to download the submissions filing for your company. Remember to declare your University Name and University email address in the User-Agent header.
Deserialize the JSON formatted submissions filing for your company.
Use the output to:
Identify how the data is indexed using the “JSON_Name” field.
Use the index to create a flat file that can be output to a csv file that has the following fields:
Account (name of the financial statement item)
Amount (dollar value of the financial statement item)
Form (the form from which the financial statement item was extracted)
FiscalPeriod (the period the financial statement item relates to)
FilingDate (the date of the form’s filing for each financial statement item)
FiscalPeriodEnd (the end date for the financial statement item)
FiscalYear (the fiscal year for the financial statement item)
Limit the data to full year results by filtering on FiscalPeriod = FY (which stands for full year).
Output the file to csv for further analysis.
The following questions relate to the csv file you generate in the steps above:
Required – Python
Pick a company and find its CIK using the EDGAR Company Filings CIK Lookup.
Using Google Colab (or another Python integrated development environment (IDE)), download the company facts json file for your company. Remember to declare your University Name and University email address in the User-Agent header. An example of the Python syntax to achieve this is provided in the Appendix.
Extract the financial statement data from the SEC’s Company Facts API and save the output to a csv or excel file for future analysis. This is a challenging task, below are a few hints:
The json file is heavily nested, which means that the dictionary structure is one that contains multiple dictionaries (key-value pairs) within dictionaries (i.e., for a higher-level key such as ‘facts’, the value includes additional key-value pairs). In addition, the data arrays (lists of data) that contain financial statement data are also within dictionaries.
This task will require a loop. In python, the loop you can use is a “for loop.”
As mentioned above, the financial statement data is within the dictionary ‘us-gaap’ which is within the dictionary ‘facts’ also recall that each account has a label and a unit, ideally each financial statement variable you extract will be saved as the label of the variable underscore the unit it is measured in (usually USD). For example, the cost of goods sold amounts should be stored in the variable “CostOfGoodsSold_USD.” Please note that some of the XBRL labels are quite long.
Examine the csv file output and be prepared to discuss the XBRL data. Here are some potential starting points to investigate:
Note that many of the accounts will not be reported in all years or quarters. Which accounts are the most populated and which accounts are the least populated? Find an example or two of each.
Depending on how you program the for loop, in most cases the data returned will not be sorted. Examine the various dates and the variable “frame” how might you consider sorting the XBRL output?
Compare your company’s XBRL data to the Financial Statement data on Yahoo Finance. What do you notice about the XBRL tags? (note that there are many detailed tags available, see: https://xbrl.us/xbrl-taxonomy/2022-us-gaap/)
Does your firm have an amended filing (either a 10-K\A or a 10-Q\A)? If so, how would you deal with this data? Does it cover all the same XBRL as is seen in the corresponding 10-K or 10-Q?
What other steps would you undertake to get the data ready for analysis?
Appendix
Using Python on Google Colab
Google Colab provides a free cloud-based Python integrated development environment (IDE) allowing users to write, edit and execute Python scripts within a browser. Google Colab can be accessed here: https://colab.research.google.com/. Users are required to set up an account and/or login using Google Account (i.e., Gmail) credentials.
Google Colab provides a Python IDE that is very similar to Jupyter Notebooks, allowing users to add both code and comment (markdown) sections. The top menu of Google Colab includes “File” and selecting “New Notebook” for file allows you to start a new Python script. You can edit the name of the new notebook by clicking on the name of the file (located on the top bar) and can access any files uploaded or created in script by clicking the folder icon on the left (the folder should include a sub-folder “sample_data” even if you have not uploaded or created any files on Gooble Colab.
Google Colab provides a free cloud-based Python interface, however, for this case, any python IDE you have access to can be used to create Python scripts to access the SEC EDGAR APIs. Other popular environments include using Jupyter Notebooks within the Anaconda package for those seeking a non-cloud-based approach with similar editing characteristics.
Useful Python Syntax
This section provides some of the syntax used in the solution to this case. This section is written with some basic understanding of Python syntax:
First steps: Importing required modules. Python is an open-source programming language and makes use of modules to perform various tasks. For this exploratory exercise we will require the requests, json, and pandas (often imported using the acronym “pd”) modules which can be imported using the following syntax at the beginning of the script:
import json
import requests
import pandas as pd
Using requests to download data. For the SEC’s API, the syntax to download data can use the requests library using the get function. The get function requires a URL and as mentioned above the SEC requires User-Agent information. Often the URL and header information can be stored in a variable and used in the get function, for example:
url = "https://url of the API"
headers = {'User-Agent' : 'University Name email@XX.edu'}
response = requests.get(url, headers=headers)
data=response.json()
Exploring the JSON data object. Using the syntax above, the JSON object containing the data will be stored in the object data, and in Python, this object will be recognized as a dictionary. The complexity in these JSON files is that dictionaries can nest other dictionaries and lists (and lists can also nest dictionaries and lists). This means that extracting the data you are seeking can take several steps. For example:
Obtaining dictionary keys. Python dictionaries provide keys which work as an index to obtain data. Remember that in the EDGAR API setting, the data associated with a key can be either a single value, another dictionary or a list. To explore nested dictionaries, you need to use Python syntax that will allow you to (i) identify the data type and nested data type, and (ii) identify the keys associated with the data (stored as values in a dictionary) for each of the nested levels. For example, you can use the Python function type in combination with print to identify whether the JSON object you downloaded from the SEC is a dictionary:
print(type(data))
Should return:
<class 'dict'>
Which suggests that the JSON object is a dictionary. To identify keys in the dictionary, the Python function keys() can be used on the object or on nested dictionaries. For example:
data.keys()
data['filings'].keys()
data['facts']['us-gaap']['units'].keys()
In the first example, you examine the top-level keys, which will include metadata including the CIK of the filing entity. The second two examples will recover the keys associated with nested dictionaries. In the second example, the keys of the nested dictionary 'filings' will be returned and in the third example the keys associated with the nested dictionary 'units' which is nested within the 'us-gaap' dictionary, which is nested within the 'facts' dictionary will be recovered. Those who are automation-inclined could consider writing a for-loop to recover all data types for keys within the dictionary. For example:
for k,v in data.items():
dt=type(data.items())
print (k,dt)
In both the submissions and company facts JSON files all the top-level keys are associated with nested dictionaries. Automation concepts will be covered in further detail in the related Innovation Mindset Case called “Innovation Mindset: Automating data extraction from EDGAR using XBRL.”
Using dictionary keys to find values. Python keys can be used like an index to look-up values within a Python dictionary. dictionaries provide keys which work as an index to obtain data. As these values can be both single values (e.g., CIK and Company Name) or dictionaries (key-value pairs) or lists of values it can be useful to break the original filing into smaller datasets or objects by saving the values associated with the dictionary keys. For example:
data1 = data['cik']
data2 = data['filings']['recent']['form']
In the first example, Python will save the CIK of the filing’s entity into an integer variable as data1, and the second example will save a list of forms filed by the entity into the variable data2.
Using pandas dataframes and functions. The Python module pandas provides useful functions for data analysis and data exporting functions (e.g., exporting to a csv for subsequent analysis in a different program). The first step is to read the desired data into a pandas dataframe. For example:
df2 = pd.DataFrame(data2)
df2 = pd.DataFrame(data['filings']['recent']['form'])
Note that the first example follows from the prior example and the second combines the two. In both cases, this example saves the forms filed by the entity into a pandas dataframe. There are many useful pandas functions, including unique(), which returns only the unique values from a list. For example:
udf2 = pd.unique(df2[0])
Another useful function in pandas is counting variables. For example, you can count the frequency of any item in a list using groupby and count. For example:
df.groupby(df[0])[0].count()
where the syntax [0] refers to the first item in the list. Additional guidance on pandas dataframes can be found at: https://pandas.pydata.org/pandas-docs/stable/user_guide/index.html