The ETL process: Extraction from APIs

Description

This session introduces application programming interfaces (APIs) as a method for programmatically extracting data from external systems and data providers. APIs are important because they allow analysts to obtain structured, reproducible, and potentially large-scale data without relying on manual downloads.

Motivation

How do we acquire data programmatically?

Review

The prior session introduced ETL as a general process for moving from raw data to analysis-ready information. This session focuses specifically on the first stage of the ETL process, extracting data, and introduces programmatic access as an alternative to manually acquiring files and datasets.

Preparation

• The materials for this class and the in-class exercises will form a basis of gaining data for the final project. The reading is relatively long, but I recommend reading it before class, and using the technical elements of it as a reference, which you add to, during class.

Class plan

• We will briefly review the conceptual model described in the course diagram.
• We will start by exploring XBRL filings manually on the SEC's EDGAR website.
• We will then use python to help us obtain data from various SEC API's and compare this data, both the schema and the content, to what is in the filing.
• The first API we will examine is the company filings API. We will use it to identify the different types of filings that can be downloaded from the SEC's EDGAR archives. We will demonstrate the retreival of the most recent 10-K (XBRL enhanced) filing.
• The second API we will examine is the company facts API. We will use it to obtain multiple years of financial data, saving the field names as the XBRL tags used by the company.
• We will conclude this class with an exercise aimed at thinking through the mapping of (highly detailed and granular) XBRL fields to common classifications of financial data, such as revenue, cost of sales, etc.

Team format

Discussion Teams

Materials

• Case

  EDGAR Explorer Inline XBRL

  View Online: EDGAR Explorer Inline XBRLPDF: EDGAR Explorer Inline XBRLWord: EDGAR Explorer Inline XBRL

• Case

  EDGAR Explorer Submissions API

  View Online: EDGAR Explorer Submissions APIPDF: EDGAR Explorer Submissions APIWord: EDGAR Explorer Submissions API

• Case

  EDGAR Explorer Company Facts

  View Online: EDGAR Explorer Company FactsWord: EDGAR Explorer Company Facts

• Slides

  Slides for Class 03

  View slides: Slides for Class 03

Tools

• VSCode
• python, python notebooks (Jupyter notebooks)
• Google Colab (a cloud-based python tool)

Concepts

• Live data extraction from Application Programming Interfaces (APIs)
• eXtensible Business Reporting Language (XBRL) for financial reporting

Data

all data is live from SEC API's, a git pull in datasets.io will extract ACCTG522/04_api_tools which is a set of python files that can be modified to extract SEC API data (as a backup). We will use the links on this page in google colab.