https://github.com/catalyst-cooperative/ferc-xbrl-extractor

A tool for converting FERC filings published in XBRL into SQLite databases

https://github.com/catalyst-cooperative/ferc-xbrl-extractor

Keywords

Keywords from Contributors

Repository

A tool for converting FERC filings published in XBRL into SQLite databases

README.rst

===============================================================================
FERC XBRL Extractor
===============================================================================


.. readme-intro

.. image:: https://www.repostatus.org/badges/latest/active.svg
   :target: https://www.repostatus.org/#active
   :alt: Project Status: Active

.. image:: https://github.com/catalyst-cooperative/ferc-xbrl-extractor/actions/workflows/tox-pytest.yml/badge.svg
   :target: https://github.com/catalyst-cooperative/ferc-xbrl-extractor/actions/workflows/tox-pytest.yml
   :alt: Tox-PyTest Status

.. image:: https://img.shields.io/codecov/c/github/catalyst-cooperative/ferc-xbrl-extractor?style=flat&logo=codecov
   :target: https://codecov.io/gh/catalyst-cooperative/ferc-xbrl-extractor
   :alt: Codecov Test Coverage

.. image:: https://img.shields.io/readthedocs/catalystcoop-ferc-xbrl-extractor?style=flat&logo=readthedocs
   :target: https://catalystcoop-ferc-xbrl-extractor.readthedocs.io/en/latest/
   :alt: Read the Docs Build Status

.. image:: https://img.shields.io/pypi/v/catalystcoop.ferc-xbrl-extractor
   :target: https://pypi.org/project/catalystcoop.ferc-xbrl-extractor/
   :alt: PyPI Latest Version

.. image:: https://img.shields.io/conda/vn/conda-forge/catalystcoop.ferc_xbrl_extractor
   :target: https://anaconda.org/conda-forge/catalystcoop.ferc_xbrl_extractor
   :alt: conda-forge Version

.. image:: https://img.shields.io/pypi/pyversions/catalystcoop.ferc-xbrl-extractor
   :target: https://pypi.org/project/catalystcoop.ferc-xbrl-extractor/
   :alt: Supported Python Versions

.. image:: https://img.shields.io/badge/code%20style-black-000000.svg
   :target: https://github.com/psf/black
   :alt: Any color you want, so long as it's black.

.. image:: https://results.pre-commit.ci/badge/github/catalyst-cooperative/ferc-xbrl-extractor/main.svg
   :target: https://results.pre-commit.ci/latest/github/catalyst-cooperative/ferc-xbrl-extractor/main
   :alt: pre-commit CI

.. image:: https://zenodo.org/badge/471019769.svg
  :target: https://zenodo.org/doi/10.5281/zenodo.10020145
   :alt: Zenodo DOI

The Federal Energy Regulatory Commission (FERC) has moved to collecting and distributing
data using `XBRL `__. XBRL is primarily designed for
financial reporting, and has been adopted by regulators in the US and other countries.
Much of the tooling in the XBRL ecosystem is targeted towards filers, and rendering
individual filings in a human readable way, but there is very little targeted towards
accessing and analyzing large collections of filings. This tool is designed to provide
that functionality for FERC XBRL data. Specifically, it can extract data from a set of
XBRL filings, and write that data to a SQLite database whose structure is generated from
an XBRL Taxonomy. While each XBRL instance contains a reference to a taxonomy,
this tool requires a path to a single taxonomy that will be used to interpret all
instances being processed. This means even if instances were created from different
versions of a Taxonomy, the provided taxonomy will be used when processing all of these
instances, so the output database will have a consistent structure. For more information
on the technical details of the XBRL extraction, see the docs.

We are currently using this tool to extract and publish the following FERC data:

.. list-table::
   :header-rows: 1

   * - FERC Form
     - Archived XBRL
     - SQLite DB
   * - `Form 1 `__
     - https://doi.org/10.5281/zenodo.4127043
     - https://data.catalyst.coop/ferc1_xbrl
   * - `Form 2 `__
     - https://doi.org/10.5281/zenodo.5879542
     - https://data.catalyst.coop/ferc2_xbrl
   * - `Form 6 `__
     - https://doi.org/10.5281/zenodo.7126395
     - https://data.catalyst.coop/ferc6_xbrl
   * - `Form 60 `_
     - https://doi.org/10.5281/zenodo.7126434
     - https://data.catalyst.coop/ferc60_xbrl
   * - `Form 714 `__
     - https://doi.org/10.5281/zenodo.4127100
     - https://data.catalyst.coop/ferc714_xbrl

Usage
-----

Installation
^^^^^^^^^^^^

To install using conda, run the following command, and activate the environment.

.. code-block:: console

    $ conda env create -f environment.yml

Activate:

.. code-block:: console

    $ conda activate ferc-xbrl-extractor


CLI
^^^

This tool can be used as a library, as it is in `PUDL `__,
or there is a CLI provided for interacting with XBRL data. The only required options
for the CLI are a path to the filings to be extracted, and a path to the output
SQLite database. The path to the filings can point to a directory full of XBRL
Filings, a single XBRL filing, or a zipfile with XBRL filings. If
the path to the database points to an existing database, the ``--clobber`` option
can be used to drop all existing data before performing the extraction.

.. code-block:: console

    $ xbrl_extract {path_to_filings} {path_to_database}

This repo contains a small selection of FERC Form 1 filings from 2021, along with
an archive of taxonomies in the ``examples`` directory. To test the tool on these
filings, use the command:

.. code-block:: console

    $ xbrl_extract examples/ferc1-2021-sample.zip --db-path ./ferc1-2021-sample.sqlite \
        --taxonomy examples/ferc1-xbrl-taxonomies.zip

The tool expects the ``--taxonomy`` option to point to a zipfile containing archived
taxonomies produced by the `pudl-archiver `__.
The extractor will parse all taxonomies in the archive, then use the taxonomy referenced
in each filing while parsing it.

Parsing XBRL filings can be a time consuming and CPU heavy task, so this tool
implements some basic multiprocessing to speed this up. It uses a
`process pool `__
to do this. There are two options for configuring the process pool, ``--batch-size``
and ``--workers``. The batch size configures how many filings will be processed by
each child process at a time, and workers specifies how many child processes to
create in the pool. It may take some experimentation to get these options
optimally configured. The following command will use 5 worker processes to process
batches of 50 filings at a time.

.. code-block:: console

    $ xbrl_extract examples/ferc1-2021-sample.zip .--db-path /ferc1-2021-sample.sqlite \
        --taxonomy examples/ferc1-xbrl-taxonomies.zip
        --workers 5 \
        --batch-size 50

There are also several options included for extracting metadata from the taxonomy.
First is the ``--datapackage-path`` command to save a
`frictionless datapackage `__
descriptor as JSON, which annotates the generated SQLite database. There is also the
``--metadata-path`` option, which writes more extensive taxonomy metadata to a json
file, grouped by table name. See the ``ferc_xbrl_extractor.arelle_interface`` module
for more info on the extracted metadata. To create both of these files using the example
filings and taxonomy, run the following command.

.. code-block:: console

    $ xbrl_extract examples/ferc1-2021-sample.zip .--db-path /ferc1-2021-sample.sqlite \
        --taxonomy examples/ferc1-xbrl-taxonomies.zip
        --metadata-path metadata.json \
        --datapackage-path datapackage.json

PUDL Sustainers
---------------

This package is part of the `Public Utility Data Liberation (PUDL) project
`__.

The PUDL Sustainers provide ongoing financial support to ensure the open data keeps
flowing, and the project is sustainable long term. They're also involved in our
quarterly planning process. To learn more see `the PUDL Project on Open Collective
`__.

Owner

GitHub Events

Committers

Last synced: about 2 years ago

Issues and Pull Requests

Last synced: 5 months ago

Packages