Generating FERC EQR transaction reports in Python

A settlement ledger sums perfectly against the quarter’s invoices, yet the transaction CSV built from it bounces at FERC’s validator because a price landed as a binary float 0.10000000000000001, a delivery timestamp collapsed a fall-back DST hour, or the columns arrived out of dictionary order. This guide gives you a complete, copy-pasteable Python routine that reads a settlement or trade ledger DataFrame and emits a FERC Electric Quarterly Report transaction file that clears those checks. It is the reference implementation behind the mapping step in FERC EQR Filing Automation; that guide covers the datasets and standards, while this one is the exact column-accurate emitter.

The diagram below traces the transformation: ledger rows are projected onto the EQR transaction columns, money is quantized with Decimal, delivery bounds are formatted in the market clock, and the ordered CSV is written alongside a SHA-256 manifest.

Prerequisites #

• Python packages: pandas for the ledger frame and python 3.9+ for the standard-library zoneinfo module (or backports.zoneinfo on 3.8). The money and hashing work uses only decimal, hashlib, csv, and json from the standard library — no third-party financial types.
• Data dependencies: a reconciled settlement ledger DataFrame with, per row, a stable transaction_id, a resolved contract_unique_id, timezone-aware delivery_start / delivery_end timestamps, a trade_date, firmness / cadence / product fields, and Decimal-safe price, quantity, rate_units, and transaction_charge values. Producing that frame is the job of the Settlement Calculation & Validation Engines; this routine assumes it exists and is already reconciled.
• Reference data: the EQR market time zone code for the delivery point (for example CP for Central Prevailing), and the coded-vocabulary maps that translate your internal firmness and cadence labels into the FERC dictionary’s enumerations.

Implementation #

The routine is deliberately split into small pure functions: a timestamp formatter that respects the market clock and DST occurrences, a Decimal quantizer that never touches a float, a row projector, and a writer that pins column order and emits the manifest. Keeping the money path in Decimal from ingestion to CSV is what prevents the float-rendering rejections; deferring string formatting to the boundary keeps the projection fast.

import csv
import hashlib
import json
from datetime import datetime
from decimal import Decimal, ROUND_HALF_UP
from zoneinfo import ZoneInfo

import pandas as pd

# EQR transaction dataset columns, in the exact order FERC's data dictionary
# expects them in the submitted CSV. Do not reorder.
EQR_TXN_COLUMNS = [
    "transaction_unique_id",
    "seller_company_name",
    "customer_company_name",
    "contract_unique_id",
    "transaction_begin_date",
    "transaction_end_date",
    "trade_date",
    "time_zone",
    "point_of_delivery_ba",
    "point_of_delivery_specific_location",
    "class_name",
    "term_name",
    "increment_name",
    "increment_peaking_name",
    "product_name",
    "transaction_quantity",
    "price",
    "rate_units",
    "standardized_quantity",
    "standardized_price",
    "total_transmission_charge",
    "transaction_charge",
]

# Market-clock time zones behind each EQR two-letter zone code. The report keeps
# the market's prevailing clock, so DST transitions must be rendered explicitly.
EQR_ZONE_TO_IANA = {
    "EP": "America/New_York",
    "CP": "America/Chicago",
    "MP": "America/Denver",
    "PP": "America/Los_Angeles",
}

# Internal ledger vocabularies mapped to the dictionary's enumerated codes.
CLASS_MAP = {"firm": "F", "non_firm": "NF", "unit_power": "UP"}
TERM_MAP = {"long": "LT", "short": "ST"}
INCREMENT_MAP = {"hourly": "H", "daily": "D", "weekly": "W", "monthly": "M", "yearly": "Y"}
PEAKING_MAP = {"full": "FP", "peak": "P", "off_peak": "OP"}


def money(value) -> Decimal:
    """Quantize any money value to cents via Decimal, never through float."""
    return Decimal(str(value)).quantize(Decimal("0.01"), rounding=ROUND_HALF_UP)


def quantity(value) -> Decimal:
    """Quantize a volume to the EQR's standard precision (no float path)."""
    return Decimal(str(value)).quantize(Decimal("0.001"), rounding=ROUND_HALF_UP)


def eqr_timestamp(ts: pd.Timestamp, zone_code: str) -> str:
    """
    Render a timezone-aware instant in the market's prevailing clock as
    YYYYMMDDHHMMSS. Converting into the market zone makes the spring-forward
    gap and fall-back overlap render as distinct wall-clock values rather
    than collapsing, which is what keeps DST-boundary intervals valid.
    """
    if ts.tzinfo is None:
        raise ValueError("delivery timestamps must be timezone-aware")
    market = ts.astimezone(ZoneInfo(EQR_ZONE_TO_IANA[zone_code]))
    return market.strftime("%Y%m%d%H%M%S")


def project_row(row: pd.Series, seller: str, zone_code: str) -> dict:
    """Project one reconciled ledger row onto the EQR transaction columns."""
    qty = quantity(row["quantity"])
    unit_price = money(row["price"])
    return {
        "transaction_unique_id": str(row["transaction_id"]),
        "seller_company_name": seller,
        "customer_company_name": row["counterparty_name"],
        "contract_unique_id": str(row["contract_unique_id"]),
        "transaction_begin_date": eqr_timestamp(row["delivery_start"], zone_code),
        "transaction_end_date": eqr_timestamp(row["delivery_end"], zone_code),
        "trade_date": pd.Timestamp(row["trade_date"]).strftime("%Y%m%d"),
        "time_zone": zone_code,
        "point_of_delivery_ba": row["delivery_ba"],
        "point_of_delivery_specific_location": row["delivery_node"],
        "class_name": CLASS_MAP[row["firmness"]],
        "term_name": TERM_MAP[row["term"]],
        "increment_name": INCREMENT_MAP[row["cadence"]],
        "increment_peaking_name": PEAKING_MAP[row["peaking"]],
        "product_name": row["product"].upper(),
        # Signed values are preserved: a congestion-driven negative price is valid.
        "transaction_quantity": f"{qty}",
        "price": f"{unit_price}",
        "rate_units": row["rate_units"],
        "standardized_quantity": f"{qty}",
        "standardized_price": f"{unit_price}",
        "total_transmission_charge": f"{money(row['transmission_charge'])}",
        "transaction_charge": f"{money(row['transaction_charge'])}",
    }


def generate_eqr_transactions(
    ledger_df: pd.DataFrame,
    output_path: str,
    seller_company_name: str,
    zone_code: str,
    run_id: str,
) -> dict:
    """
    Read a reconciled settlement ledger frame and write the EQR transaction CSV
    plus a SHA-256 audit manifest. Returns the manifest dict.
    """
    if ledger_df.empty:
        raise ValueError("ledger frame is empty; nothing to report for the quarter")

    rows = [project_row(r, seller_company_name, zone_code)
            for _, r in ledger_df.iterrows()]

    # Fail closed on any unmapped code before a byte is written.
    for r in rows:
        if r["class_name"] not in {"F", "NF", "UP"}:
            raise ValueError(f"unmapped class for txn {r['transaction_unique_id']}")

    # Write the CSV in strict dictionary column order.
    with open(output_path, "w", newline="", encoding="utf-8") as fh:
        writer = csv.DictWriter(fh, fieldnames=EQR_TXN_COLUMNS)
        writer.writeheader()
        writer.writerows(rows)

    # Audit manifest: hash the file bytes and total the charges with Decimal.
    with open(output_path, "rb") as fh:
        file_sha256 = hashlib.sha256(fh.read()).hexdigest()

    charge_total = sum((Decimal(r["transaction_charge"]) for r in rows),
                       start=Decimal("0.00"))

    manifest = {
        "run_id": run_id,
        "output_path": output_path,
        "row_count": len(rows),
        "charge_total": str(charge_total),
        "seller_company_name": seller_company_name,
        "time_zone": zone_code,
        "generated_at": datetime.now(ZoneInfo("UTC")).isoformat(),
        "file_sha256": file_sha256,
        "column_order_sha256": hashlib.sha256(
            json.dumps(EQR_TXN_COLUMNS).encode()
        ).hexdigest(),
    }
    with open(output_path + ".manifest.json", "w", encoding="utf-8") as fh:
        json.dump(manifest, fh, indent=2, sort_keys=True)

    return manifest

The three failure modes that most often reach FERC’s validator are all closed here: money never passes through a binary float, so no 0.1 renders as 0.09999…; timestamps are converted into the market clock before formatting, so a delivery window crossing a DST boundary renders each occurrence distinctly instead of collapsing the missing or duplicated hour; and the CSV column order is pinned to a constant that the manifest hashes, so a reordering regression is detectable. The astimezone handling is the same discipline covered in DST gap and overlap in pandas timestamp normalization.

Verification #

Confirm the output before it is staged for submission:

1. Row count and header. The CSV row count equals manifest["row_count"] + 1 (the header), and the header line equals EQR_TXN_COLUMNS joined by commas. A header mismatch means a column drifted out of dictionary order.
2. Charge reconciliation. The charge_total in the manifest must equal the ledger’s independently summed transaction_charge for the quarter, to the cent — the single most important check binding the filing back to settlement.
3. Timestamp shape. Every transaction_begin_date and transaction_end_date matches ^\d{14}$, and a delivery window that spans a DST fall-back renders the repeated wall-clock hour as two distinct rows rather than one.
4. Manifest integrity. Re-hashing the written file reproduces file_sha256; a mismatch means the file was touched after generation.

import re

def verify_eqr_output(output_path: str, manifest: dict, ledger_total: Decimal) -> None:
    """Assert structural, reconciliation, and integrity checks on the emitted CSV."""
    df = pd.read_csv(output_path, dtype=str)
    assert list(df.columns) == EQR_TXN_COLUMNS, "column order drifted from dictionary"
    assert len(df) == manifest["row_count"], "row count disagrees with manifest"

    ts_ok = df["transaction_begin_date"].str.match(r"^\d{14}$").all()
    assert ts_ok, "a begin timestamp is not YYYYMMDDHHMMSS"

    csv_total = sum((Decimal(v) for v in df["transaction_charge"]), start=Decimal("0.00"))
    assert csv_total == ledger_total, f"charge total {csv_total} off ledger {ledger_total}"

    with open(output_path, "rb") as fh:
        assert hashlib.sha256(fh.read()).hexdigest() == manifest["file_sha256"]

Compliance Note #

This routine produces the transaction dataset of the FERC Electric Quarterly Report required under FERC Order No. 2001 and now filed on FERC Form No. 920. The emitted columns, their order, and the enumerated codes (Class Name, Term Name, Increment Name, Increment Peaking Name, Rate Units) must be validated against the current FERC EQR Data Dictionary in force for the reporting quarter, because FERC revises those enumerations periodically — the CLASS_MAP and companion maps above should be loaded from the versioned dictionary rather than trusted as permanent. Every reported figure must trace back to a settled interval, which is why the manifest records the file hash, the pinned column order, the row count, and the Decimal charge total for the run: an auditor can replay the same ledger snapshot and reproduce a byte-identical file. The tamper-evident manifest follows the same append-only, hash-anchored discipline detailed in Building tamper-evident audit logs for NERC CIP. Because a filing that fails FERC’s validation is not considered filed, run this generator and its verification as a dry pass several days ahead of the 30-day post-quarter deadline.

Frequently Asked Questions #

Why must the price and quantity use Decimal instead of a float here? #

The EQR is reconciled to the cent against your settlement ledger, and a binary float cannot represent most decimal fractions exactly — a price of 0.1 can render as 0.09999999999999999, which FERC’s validator rejects and which breaks the charge reconciliation. Quantizing every money and volume value with Decimal from ingestion through to the CSV string keeps the file exact and reproducible.

How does this handle a delivery window that crosses a daylight-saving boundary? #

Delivery timestamps enter as timezone-aware instants and are converted into the market’s prevailing clock with astimezone before formatting to YYYYMMDDHHMMSS. That conversion renders the spring-forward gap and the fall-back overlap as distinct wall-clock values, so a window spanning the transition reports the correct hours instead of collapsing a missing or duplicated hour into a naive index.

What is the audit manifest for and what does it contain? #

The manifest is a JSON sidecar written next to the CSV that records the run identifier, the row count, the Decimal charge total, the generation timestamp, a SHA-256 of the file bytes, and a hash of the pinned column order. It makes the filing tamper-evident and replayable: re-running the same ledger snapshot reproduces the same file and the same hashes, which is what lets you prove to an auditor that the submitted numbers match the settled ones.

Related #

• FERC EQR Filing Automation
• Building tamper-evident audit logs for NERC CIP
• DST gap and overlap in pandas timestamp normalization