Download Financial Statements from EDGAR Filings as Excel File

In this tutorial, we will guide you through the process of downloading the Financial_Report.xlsx Excel files attached to 10-K and 10-Q filings using Python.

Here is an overview of the steps involved:

1. Query API: We will start by using the Query API to filter the EDGAR database and build a list of URLs for the financial reports we are interested in. For this example, we will focus on a small universe of companies, including Microsoft, Amazon, Netflix, Apple, and Tesla. However, you can easily expand the search universe by adjusting the search query to include more companies or even all filings.
2. Render API: Once we have the list of URLs, we will utilize the Render API to download the Financial_Report.xlsx files. These files will be saved in the reports folder on our local disk. The Render API allows us to retrieve any filing and exhibit on EDGAR, including the financial reports in Excel format.

The resulting folder structure will look similar to the following:

By following this tutorial, you will be able to download the financial statements from EDGAR filings for the specified companies and store them locally as Excel files.

Overview of Financial_Report.xlsx

The Financial_Report.xlsx Excel file contains essential tables and footnotes extracted from 10-K and 10-Q EDGAR filings. These financial statements are also available in XBRL format. The Excel file includes various sheets that provide comprehensive information, such as:

• Cover page: Contains general information about the filing.
• Income statement (also known as CONDENSED CONSOLIDATED STATEMENTS OF OPERATIONS): Presents the company's revenue, expenses, and net income or loss for a specific period.
• Balance sheet: Shows the company's assets, liabilities, and shareholders' equity at a specific point in time.
• Cash flow statement: Illustrates the company's cash inflows and outflows from operating, investing, and financing activities.
• Summary of significant accounting policies: Describes the company's accounting principles and policies.
• Footnotes to all financial statements: Provides additional information and explanations for the financial data.
• Disaggregated revenues by product and services: Breaks down the company's revenues by different product lines or services.

Here is an example of the income statement sheet from the Financial_Report.xlsx file attached to Apple's 10-Q filing for Q1 2023. You can access the Excel file here.

You can find the corresponding 10-Q filing here.

Another example from the same file is the net sales disaggregated by products and services, as shown below:

Getting Started

To begin, we need to install the sec-api Python package. This package provides the necessary functionalities for interacting with the Query API. Once installed, you can use the QueryApi class from the sec-api package to make API calls and retrieve filing metadata for 10-K and 10-Q filings.

To install the sec-api package, you can use the following command:

          1
          !pip install -q sec-api
        

After installing the package, you can instantiate the QueryApi class by providing your API key. The API key is required to authenticate your requests to the Query API. Once instantiated, you can utilize the .get_filings(search_query) function provided by the QueryApi class to make API calls and retrieve the desired filing metadata for 10-K and 10-Q filings, or any other filing of interest.

          1
          API_KEY = 'YOUR_API_KEY'
        

          1
          from sec_api import QueryApi
        

          2
          
        

          3
          queryApi = QueryApi(api_key=API_KEY)
        

Find URLs of All Financial Reports Excel Files

In the first step, we will retrieve the metadata for 10-K and 10-Q filings, including information such as the ticker and name of the filer, filed at date, form type, URL to the filing, and exhibits.

To accomplish this, we will utilize the Query API, which allows us to search the EDGAR database using various metadata fields. By constructing a Lucene search query, we can retrieve up to 200 objects that represent the metadata for each filing. To ensure that we retrieve all matching objects and not just the first 200, we have implemented the get_filings(query) function, which handles pagination.

The search expression is written in Lucene syntax. The Lucene queries used to build the list of URLs for the Excel files associated with each company and EDGAR filing consist of three main parts:

Form Type Filter

The form type filter searches for all 10-K and 10-Q EDGAR filings while excluding amended filings (10-K/A and 10-Q/A) as well as NT filings (notifications of late filings).

formType:("10-K", "10-Q") AND NOT formType:("10-K/A", "10-Q/A", NT)

Company Filter

The company or ticker filter matches the metadata field ticker with one of the following tickers: MSFT, TSLA, AMZN, NFLX, or AAPL.

ticker:(MSFT, TSLA, AMZN, NFLX, AAPL)

Date Range Filter

The date range filter searches for filings filed between 2022-01-01 and 2022-12-31, including both dates.

filedAt:[2022-01-01 TO 2022-12-31]

By combining these filters using the AND operator, we can construct the final search query.

For more information on the Lucene query syntax, you can refer to the overview of the Lucene query syntax here.

          1
          import pandas as pd
        

          2
          
        

          3
          def get_filings(query):
        

          4
            from_param = 0
        

          5
            size_param = 200
        

          6
            all_filings = []
        

          7
          
        

          8
            while True:
        

          9
              query['from'] = from_param
        

          10
              query['size'] = size_param
        

          11
          
        

          12
              response = queryApi.get_filings(query)
        

          13
              filings = response['filings']
        

          14
          
        

          15
              if len(filings) == 0:
        

          16
                break
        

          17
          
        

          18
              all_filings.extend(filings)
        

          19
          
        

          20
              from_param += size_param
        

          21
          
        

          22
            return pd.json_normalize(all_filings)
        

          23
          
        

          24
          form_type_query  = 'formType:("10-K", "10-Q") AND NOT formType:("10-K/A", "10-Q/A", NT)'
        

          25
          ticker_query     = 'ticker:(MSFT, TSLA, AMZN, NFLX, AAPL)'
        

          26
          date_range_query = 'filedAt:[2022-01-01 TO 2022-12-31]'
        

          27
          
        

          28
          lucene_query = f"{form_type_query} AND {ticker_query} AND {date_range_query}"
        

          29
          
        

          30
          search_query = {
        

          31
            "query": lucene_query,
        

          32
            "from": "0",
        

          33
            "size": "200",
        

          34
            "sort": [{ "filedAt": { "order": "desc" } }]
        

          35
          }
        

          36
          
        

          37
          filings = get_filings(search_query)
        

          1
          filings.head()
        

Now that you have created the complete list of filing metadata and saved it in the dataframe filings, let's convert the metadata of all the found filings into a new dataframe called urls. This new dataframe will contain the following information for each filing:

• Ticker
• Form type
• Filed at date
• Filing URL
• URL to the corresponding Financial_Report.xlsx file

          1
          urls = filings[['ticker',
        

          2
                          'formType',
        

          3
                          'periodOfReport',
        

          4
                          'filedAt',
        

          5
                          'linkToFilingDetails']].rename(columns={'linkToFilingDetails': 'filingUrl'})
        

          6
          
        

          7
          urls['financialReportsUrl'] = urls['filingUrl'].apply(lambda url: '/'.join(url.split('/')[:-1]) + '/Financial_Report.xlsx')
        

          8
          
        

          9
          urls.head(10)
        

Download Financial_reports.xlsx of All EDGAR Filings

The Render API provides a convenient way to download any EDGAR filing or exhibit, allowing for up to 40 downloads per second. It offers a straightforward interface to facilitate the download process.

For instance, if we consider the original URL of Apple's Financial_Report.xlsx file:

sec.gov/Archives/edgar/data/320193/000032019323000064/Financial_Report.xlsx

To download the file using the Render API, we can make a simple modification to the URL:

archive.sec-api.io/320193/000032019323000064/Financial_Report.xlsx

In essence, we replace sec.gov/Archives/edgar/data with archive.sec-api.io in the URL.

To download the file and save it to our local disk, we can utilize the requests.get(URL) function. The download_report(filing) function serves as a convenient wrapper that encapsulates this entire logic.

          1
          import os, requests
        

          2
          
        

          3
          folder_path = './reports'
        

          4
          
        

          5
          if not os.path.exists(folder_path):
        

          6
            os.makedirs(folder_path)
        

          7
          
        

          8
          def download_report(filing):
        

          9
            reports_path = filing['financialReportsUrl'].replace('https://www.sec.gov/Archives/edgar/data/', '')
        

          10
            base_url = 'https://archive.sec-api.io/' + reports_path
        

          11
            render_api_url = base_url + '?token=' + API_KEY
        

          12
          
        

          13
            response = requests.get(render_api_url)
        

          14
          
        

          15
            file_name = f"{filing['ticker']}-{filing['periodOfReport']}-{filing['formType']}.xlsx"
        

          16
            file_path = f"{folder_path}/{file_name}"
        

          17
          
        

          18
            output = open(file_path, 'wb')
        

          19
            output.write(response.content)
        

          20
            output.close()
        

Python typically executes commands in a blocking manner, allowing only one download at a time. Consequently, downloading thousands of financial statements would be a time-consuming process.

However, you can optimize the task by parallelizing it using the pandarallel package. This package offers a convenient way to parallelize operations on dataframes, reducing the need for additional boilerplate code.

To illustrate the concept, let's download four reports at a time in parallel by setting the number_of_workers parameter to 4.

          1
          !pip install -q pandarallel
        

          1
          from pandarallel import pandarallel
        

          2
          
        

          3
          number_of_workers = 4
        

          4
          pandarallel.initialize(progress_bar=True, nb_workers=number_of_workers, verbose=0)
        

          1
          urls.parallel_apply(download_report, axis=1)
        

          2
          print(f"✅ Downloaded {len(urls)} reports")
        

VBox(children=(HBox(children=(IntProgress(value=0, description='0.00%', max=5), Label(value='0 / 5'))), HBox(c…

✅ Downloaded 20 reports