Repository files navigation

The Federal Energy Regulatory Commission (FERC) has moved to collecting and distributingdata usingXBRL. XBRL is primarily designed forfinancial 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 renderingindividual filings in a human readable way, but there is very little targeted towardsaccessing and analyzing large collections of filings. This tool is designed to providethat functionality for FERC XBRL data. Specifically, it can extract data from a set ofXBRL filings, and write that data to a SQLite database whose structure is generated froman 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 allinstances being processed. This means even if instances were created from differentversions of a Taxonomy, the provided taxonomy will be used when processing all of theseinstances, so the output database will have a consistent structure. For more informationon the technical details of the XBRL extraction, see the docs.

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

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

$conda env create -f environment.yml

Activate:

$conda activate ferc-xbrl-extractor

This tool can be used as a library, as it is inPUDL,or there is a CLI provided for interacting with XBRL data. The only required optionsfor the CLI are a path to the filings to be extracted, and a path to the outputSQLite database. The path to the filings can point to a directory full of XBRLFilings, a single XBRL filing, or a zipfile with XBRL filings. Ifthe path to the database points to an existing database, the--clobber optioncan be used to drop all existing data before performing the extraction.

$xbrl_extract {path_to_filings} {path_to_database}

This repo contains a small selection of FERC Form 1 filings from 2021, along withan archive of taxonomies in theexamples directory. To test the tool on thesefilings, use the command:

$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 archivedtaxonomies produced by thepudl-archiver.The extractor will parse all taxonomies in the archive, then use the taxonomy referencedin each filing while parsing it.

Parsing XBRL filings can be a time consuming and CPU heavy task, so this toolimplements some basic multiprocessing to speed this up. It uses aprocess poolto do this. There are two options for configuring the process pool,--batch-sizeand--workers. The batch size configures how many filings will be processed byeach child process at a time, and workers specifies how many child processes tocreate in the pool. It may take some experimentation to get these optionsoptimally configured. The following command will use 5 worker processes to processbatches of 50 filings at a time.

$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 africtionless datapackagedescriptor as JSON, which annotates the generated SQLite database. There is also the--metadata-path option, which writes more extensive taxonomy metadata to a jsonfile, grouped by table name. See theferc_xbrl_extractor.arelle_interface modulefor more info on the extracted metadata. To create both of these files using the examplefilings and taxonomy, run the following command.

$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

This package is part of thePublic Utility Data Liberation (PUDL) project.

The PUDL Sustainers provide ongoing financial support to ensure the open data keepsflowing, and the project is sustainable long term. They're also involved in ourquarterly planning process. To learn more seethe PUDL Project on Open Collective.