FERC EQR Filing Automation

A quarter closes, a settlement analyst exports three months of wholesale power transactions into a spreadsheet, and the manual reformatting begins — until a mislabeled Class Name, a missing contract identifier, or a price expressed in the wrong rate unit bounces the whole submission back from FERC’s validation engine days before the deadline. That failure mode is what a FERC Electric Quarterly Report (EQR) automation pipeline exists to eliminate. Within the Regulatory Filing Preparation framework, this component takes the same reconciled ledger that priced and settled each trade and re-projects it into the exact transaction and contract layout FERC’s Order No. 2001 data dictionary demands, validates every field against the enumerated code lists, and produces a submission package that clears FERC’s own checks on the first attempt. It draws its source figures directly from the Settlement Calculation & Validation Engines so that what you report to the regulator is byte-for-byte reconcilable with what you invoiced the counterparty.

The distinction that governs the whole design is that an EQR is not one dataset but two linked ones — an infrequently changing set of contracts and a high-volume set of transactions that reference them — and the automation must keep referential integrity between them while reshaping ledger rows that were never structured for FERC in the first place.

The diagram below traces the pipeline this component implements, from the settlement ledger through filable-transaction extraction, schema mapping, and validation into the packaged CSV datasets submitted to FERC’s EQR system.

Everything downstream of extraction is a deterministic transformation: given the same reconciled ledger snapshot and the same code revision, the pipeline emits identical EQR files, so a resubmission or an audit response reproduces the original filing exactly. The sections that follow fix the regulatory specification the files must satisfy, walk the production build in numbered steps, catalogue the failure modes that bounce a filing, and describe the reconciliation and alerting that keep the quarterly cycle boring.

Specification & Standards #

The EQR was established by FERC Order No. 2001, which requires public utilities with market-based rate or cost-based authority to report their wholesale electric sales and transmission transactions every calendar quarter. The obligation now rides on FERC Form No. 920, and the field-level contract is the FERC EQR Data Dictionary — the authoritative enumeration of every column, its data type, its allowed values, and whether it is mandatory. An automation pipeline treats that dictionary as versioned input data, exactly the way the Settlement Calculation & Validation Engines treat a tariff revision as versioned input rather than hard-coded logic, so a dictionary revision is a data change rather than a code rewrite.

An EQR submission carries three logical parts: identification (the filer and any agent), contracts, and transactions. The identification block is nearly static across quarters. The interesting engineering is the relationship between the contract and transaction datasets, because every transaction row must reference a contract that is either present in the same filing or was established in a prior one. Getting that referential link wrong is the most common structural rejection.

The transaction dataset is where a settlement ledger maps most directly. The dictionary fixes both the columns and their coded vocabularies; the table below lists the transaction fields that a ledger-to-EQR mapper must populate and the enumerations FERC validates them against.

The reporting calendar is unforgiving and drives the automation’s scheduling. Each quarter’s report is due within 30 days of the quarter’s close, and a filing that fails FERC’s validation is not considered filed — so the pipeline must leave slack for a rejection-and-resubmit loop rather than aiming at the deadline itself.

The concrete mechanics of emitting the transaction CSV — exact column order, Decimal price and quantity handling, timezone-correct timestamp formatting, and an audit manifest — are worked end to end in Generating FERC EQR transaction reports in Python. This section stays at the level of what the datasets are and why; that guide is the reference implementation of how.

Step-by-Step Implementation #

A production EQR pipeline is a short, auditable sequence of pure transformations over a reconciled ledger snapshot. Each step below is idempotent and keyed so a re-run replaces only the affected rows.

1. Pin the reporting window and the ledger snapshot. The pipeline resolves the quarter to an explicit UTC-anchored delivery window and selects the finalized ledger as of a fixed snapshot time, so a re-run for an amended filing reads the same source rows.

from datetime import datetime, timezone
from decimal import Decimal

def resolve_quarter(year: int, quarter: int) -> dict:
    """Map a reporting quarter to explicit delivery-window bounds."""
    starts = {1: (1, 1), 2: (4, 1), 3: (7, 1), 4: (10, 1)}
    ends = {1: (3, 31), 2: (6, 30), 3: (9, 30), 4: (12, 31)}
    m0, d0 = starts[quarter]
    m1, d1 = ends[quarter]
    return {
        "run_id": f"EQR-{year}Q{quarter}",
        "window_start": datetime(year, m0, d0, 0, 0, tzinfo=timezone.utc),
        "window_end": datetime(year, m1, d1, 23, 59, 59, tzinfo=timezone.utc),
    }

2. Filter to FERC-jurisdictional, filable transactions. Not every ledger row is reportable — retail deliveries, bookouts handled elsewhere, and non-jurisdictional sales are excluded per the filer’s reporting posture. The filter is explicit and logged so an auditor can see exactly what was and was not reported.

def select_filable(ledger_df, window: dict):
    """Keep only FERC-reportable wholesale rows inside the delivery window."""
    in_window = (
        (ledger_df["delivery_start"] >= window["window_start"])
        & (ledger_df["delivery_start"] <= window["window_end"])
    )
    reportable = ledger_df["ferc_reportable"] & (ledger_df["market_segment"] == "WHOLESALE")
    filable_df = ledger_df[in_window & reportable].copy()
    filable_df["run_id"] = window["run_id"]
    return filable_df

3. Resolve each transaction to its contract. Every filable row must carry a contract_unique_id that resolves to a contract in this filing or a prior one. A transaction whose contract cannot be resolved is an exception, never a silent blank.

def attach_contract_refs(filable_df, contract_master: dict):
    """Bind each transaction to its governing contract; flag orphans."""
    filable_df["contract_unique_id"] = filable_df["deal_id"].map(contract_master)
    orphans = filable_df[filable_df["contract_unique_id"].isna()]
    if not orphans.empty:
        # Route to the exception queue rather than emit an unlinked row.
        raise ValueError(f"{len(orphans)} transactions have no resolvable contract")
    return filable_df

4. Map ledger columns onto the transaction dataset. This is the projection into the dictionary’s coded vocabularies — firmness to Class Name, delivery cadence to Increment Name, ledger money into Decimal price and charge fields. The full column-accurate mapper is the subject of Generating FERC EQR transaction reports in Python.

5. Validate, package, and stage for submission. The two datasets are written as CSVs in the dictionary’s column order, a manifest hash is computed over each, and the package is staged for the FERC EQR system. The transaction-charge total is reconciled back against the ledger before anything is submitted.

Because contract data mutates only on execution or amendment, most quarters re-emit an unchanged contract dataset and a wholly new transaction dataset — a property the mapper exploits by caching the contract projection keyed on the contract master version.

Edge Cases & Failure Modes #

The rejections that consume a filing team’s week are rarely arithmetic — they are structural and temporal. The table catalogues the recurring ones and the deterministic handling each requires.

Negative prices and DST boundaries are the two that most often survive a naive spreadsheet process and detonate at FERC’s validator. The sign-preservation discipline is the same one the settlement layer applies to negative congestion, and the timestamp discipline mirrors the timezone-aware handling used throughout Trade Ingestion & Matching Workflows. Treating the dictionary itself as versioned data — and failing closed when a code no longer maps — is what stops silent schema drift from producing a technically valid but substantively wrong filing.

Thresholds & Alerting #

An EQR pipeline earns its keep by surfacing problems while there is still time to fix them, which means alerting keys off the calendar as much as off the data. Configurable parameters govern how early the pipeline runs a dry pass and how aggressively it escalates.

• dry_run_offset_days — how many days before the deadline the pipeline builds and validates the package without submitting, so a rejection is discovered with slack to spare. A common posture is 10 days.
• charge_reconciliation_tolerance — the maximum permitted absolute difference, in dollars, between the summed Transaction Charge and the ledger’s settled total for the quarter. A Decimal("0.00") target with a small non-zero band accommodates documented rounding.
• orphan_alert_threshold — the count of unresolved-contract transactions that escalates from informational to blocking.

Alerts route by tier much as settlement exceptions do. Informational alerts (a handful of rows normalized) log and continue; review-required alerts (a rate-unit normalization touched many rows, or the reconciliation residual approached its band) notify the filing owner; blocking alerts (any orphan transaction, a dictionary code that failed to map, or a reconciliation breach) halt submission and page the on-call analyst. Routing severity by tier keeps a single malformed contract from silently degrading a filing while also refusing to drown the team in noise for benign normalizations — the same discipline described in Settlement Calculation & Validation Engines.

Testing & Reconciliation #

The controlling correctness property is that the EQR is a faithful re-projection of the ledger, so the primary test is a shadow reconciliation: independently sum the settled charges for the quarter straight from the Settlement Calculation & Validation Engines output and assert it equals the summed Transaction Charge across the emitted transaction dataset, to the cent.

from decimal import Decimal

def reconcile_eqr_to_ledger(eqr_rows, ledger_total: Decimal,
                            tolerance: Decimal = Decimal("0.00")) -> None:
    """Assert the EQR transaction charges reconcile to the ledger's settled total."""
    eqr_total = sum((Decimal(str(r["transaction_charge"])) for r in eqr_rows),
                    start=Decimal("0.00"))
    residual = abs(eqr_total - ledger_total)
    if residual > tolerance:
        raise AssertionError(
            f"EQR charge total {eqr_total} off ledger total {ledger_total} "
            f"by {residual} (tolerance {tolerance})"
        )

Unit tests pin the coded-vocabulary mappings against fixtures: a firm hourly energy sale must map to Class Name F, Increment Name H, Product Name ENERGY; a negative-price row must survive the mapper with its sign; a delivery window crossing a DST boundary must emit the correct number of hourly occurrences. Contract referential integrity is tested by asserting that the set of contract_unique_id values in the transaction dataset is a subset of the union of this filing’s contracts and the prior-filing contract master. A golden-file test — compare the generated CSV byte-for-byte against a hand-verified fixture for a known quarter — catches column-order regressions that FERC’s validator would otherwise catch far later. The schema-enforcement approach mirrors Schema Validation Frameworks, applied to the EQR dictionary rather than an internal trade contract.

Frequently Asked Questions #

What are the two datasets in a FERC EQR and how do they relate? #

An EQR carries a contract dataset and a transaction dataset (plus a mostly static identification block). Contracts change only on execution or amendment and are keyed by a Contract Unique ID; transactions are the high-volume, per-delivery rows and each must reference a contract that appears in the same filing or was established in a prior one. Maintaining that referential link is the main structural constraint — an unresolved reference is the most common cause of rejection.

How do I map a settlement ledger to EQR transaction rows? #

Filter the ledger to FERC-reportable wholesale rows inside the quarter’s delivery window, resolve each row to its governing contract, then project ledger columns onto the dictionary’s coded vocabularies — firmness to Class Name, cadence to Increment Name, money into Decimal price and charge fields, and delivery bounds into explicit YYYYMMDDHHMMSS timestamps in the correct market time zone. The column-exact mapper and CSV emitter are covered in Generating FERC EQR transaction reports in Python.

When is the EQR due and what happens if it fails validation? #

Each quarter’s report is due within 30 days of the quarter’s close — roughly April 30, July 31, October 31, and January 31. A filing that fails FERC’s validation is not considered filed, so a pipeline should run a validating dry pass several days early to leave slack for a rejection-and-resubmit loop rather than aiming at the deadline itself.

Why treat the FERC EQR data dictionary as versioned input data? #

FERC periodically revises column definitions and enumerated code lists. Loading the dictionary as versioned data — rather than hard-coding its codes — means a revision is a data change, and the pipeline can fail closed whenever a ledger value no longer maps to a valid code. That prevents silent schema drift from producing a filing that passes structural checks but reports substantively wrong values.

Related #

• Regulatory Filing Preparation
• Generating FERC EQR transaction reports in Python
• Settlement Calculation & Validation Engines
• Schema Validation Frameworks