Computing preliminary-to-final settlement deltas

The preliminary invoice for a trading day is already out the door when the validated meter file arrives and moves a few hundred intervals; the task now is to produce the exact signed difference between what was invoiced and what the final run computes — line by line, to the cent — without recomputing or disturbing the intervals that never changed. This is the concrete diff step behind Resettlement & True-Up Processing: given two persisted runs of the same day, join them on (run_id, settlement_point, interval_start), compute a Decimal delta per line, and emit an adjustment ledger that bridges the preliminary charge to the final one and reconciles against the ISO’s own resettlement statement.

The flow below is what this page builds: two run ledgers are keyed and outer-joined, each matched pair yields a signed delta, and the non-zero deltas plus any appeared/disappeared lines are written to a restatement ledger.

Prerequisites #

• Python packages: pandas for the keyed join over the two ledgers, and the standard-library decimal and hashlib modules — no third-party numeric library touches the money path. pandas here only aligns rows; every arithmetic operation on an amount is done in Decimal.
• Data dependencies: two persisted run ledgers for the same trading day, each carrying at minimum settlement_point, interval_start (timezone-aware ISO-8601), settlement_amount as a decimal string, and a run_id. Amounts must be stored as strings or Decimal, never as floats, so no binary-float error is baked in before the diff runs. The frozen preliminary ledger is read-only; the final ledger is the freshly recomputed output for the changed intervals merged over the unchanged carry-forward.
• Permissions / access: read access to the run-ledger store (the storage-format trade-off is covered in CSV vs Parquet for settlement ledger storage) and, for the reconciliation step, read-only credentials to pull the ISO’s resettlement statement. No write scope to the prior run is needed or wanted — this step appends a new ledger, it never edits an existing one.

Implementation #

The diff is an outer join on the settlement key so that three cases are all handled: a line present in both runs (compute the delta), a line only in the final run (an appeared charge, delta equals the full final amount), and a line only in the preliminary run (a disappeared charge, delta equals the negative of the preliminary amount). The Decimal cast happens as each amount is read, and every delta is quantized to cents before it is emitted. Zero deltas are dropped so the restatement ledger contains only genuine movements.

import pandas as pd
import hashlib
import json
from decimal import Decimal, ROUND_HALF_UP
from datetime import datetime, timezone

CENTS = Decimal("0.01")

def _to_amount(value) -> Decimal:
    """Read a persisted amount as an exact Decimal. Never route money through float."""
    if value is None or (isinstance(value, float) and pd.isna(value)):
        return Decimal("0.00")
    return Decimal(str(value)).quantize(CENTS, rounding=ROUND_HALF_UP)

def compute_settlement_deltas(
    preliminary: pd.DataFrame,
    final: pd.DataFrame,
    prior_run_id: str,
    run_id: str,
    cause: str,
) -> pd.DataFrame:
    """
    Diff two settlement runs of one trading day and emit a signed restatement ledger.

    Both frames must carry: settlement_point, interval_start (tz-aware ISO-8601),
    settlement_amount (decimal string). The result has one row per changed line,
    keyed by (run_id, settlement_point, interval_start), with a signed delta_amount.
    """
    key = ["settlement_point", "interval_start"]

    # Outer join so appeared and disappeared lines survive, not just matched pairs.
    merged = preliminary[key + ["settlement_amount"]].merge(
        final[key + ["settlement_amount"]],
        on=key, how="outer",
        suffixes=("_prior", "_new"), indicator=True,
    )

    rows = []
    for record in merged.to_dict("records"):
        prior_amount = _to_amount(record["settlement_amount_prior"])
        new_amount = _to_amount(record["settlement_amount_new"])
        delta = (new_amount - prior_amount).quantize(CENTS, rounding=ROUND_HALF_UP)

        # Drop unchanged lines: an input may have moved below the rounding boundary.
        if delta == Decimal("0.00"):
            continue

        presence = record["_merge"]           # 'both', 'left_only', 'right_only'
        line_cause = {
            "left_only": "line_removed_at_final",
            "right_only": "line_added_at_final",
        }.get(presence, cause)

        rows.append({
            "run_id": run_id,
            "prior_run_id": prior_run_id,
            "settlement_point": record["settlement_point"],
            "interval_start": record["interval_start"],
            "prior_amount": str(prior_amount),
            "new_amount": str(new_amount),
            "delta_amount": str(delta),        # signed: + charge, - refund
            "cause": line_cause,
            "restated_at": datetime.now(timezone.utc).isoformat(),
        })

    ledger = pd.DataFrame(rows, columns=[
        "run_id", "prior_run_id", "settlement_point", "interval_start",
        "prior_amount", "new_amount", "delta_amount", "cause", "restated_at",
    ])

    # Tamper-evident hash binding each adjustment to its bridged amounts.
    ledger["adjustment_hash"] = ledger.apply(
        lambda r: hashlib.sha256(json.dumps({
            "run_id": r["run_id"], "prior_run_id": r["prior_run_id"],
            "settlement_point": r["settlement_point"],
            "interval_start": r["interval_start"],
            "prior_amount": r["prior_amount"], "new_amount": r["new_amount"],
            "delta_amount": r["delta_amount"],
        }, sort_keys=True).encode()).hexdigest(),
        axis=1,
    ) if not ledger.empty else pd.Series(dtype="object")

    return ledger

The function is deliberately pure: it reads two frames and returns a third, with no in-place mutation of either input and no side effect beyond the returned ledger. That property is what lets it be replayed — feeding the same two runs always yields the same restatement ledger, hash for hash. The indicator=True flag is what makes the appeared/disappeared cases explicit rather than silently coerced to zero, which matters when a settlement point is remapped between runs and a charge legitimately moves from one key to another. The same deviation recomputation that feeds a changed settlement_amount when a schedule is corrected is documented in Imbalance Allocation Algorithms.

Verification #

Three checks confirm the restatement ledger is correct before it is persisted or sent to billing:

1. Composition identity. For every key, prior_amount + delta_amount must equal new_amount exactly. Because all three are quantized to cents in Decimal, this is an exact equality, not a tolerance check — any failure means a float slipped into the path.
2. Aggregate reconciliation against the ISO. The sum of delta_amount across the restatement ledger must equal the ISO resettlement statement’s net change for the day, within the tariff’s rounding tolerance. A residual points to a version mismatch — the final run pinned a different price publication than the ISO recalculation — not a code bug.
3. No unchanged rows. The ledger must contain only non-zero deltas. A zero-delta row surviving into the output means the drop guard failed.

from decimal import Decimal

def verify_restatement(ledger: pd.DataFrame, iso_net_change: Decimal,
                       tol: Decimal = Decimal("0.01")) -> None:
    """Assert composition identity and reconcile the net delta against the ISO."""
    for r in ledger.to_dict("records"):
        prior = Decimal(r["prior_amount"])
        new = Decimal(r["new_amount"])
        delta = Decimal(r["delta_amount"])
        assert prior + delta == new, f"composition broke at {r['settlement_point']}"
        assert delta != Decimal("0.00"), "zero-delta row leaked into the ledger"

    net = sum((Decimal(r["delta_amount"]) for r in ledger.to_dict("records")),
              Decimal("0.00"))
    residual = abs(net - iso_net_change)
    if residual > tol:
        raise ValueError(f"net delta {net} off ISO change {iso_net_change} by {residual}")

The expected shape is one row per changed (settlement_point, interval_start) pair and no rows for the unchanged remainder of the day, so on a typical true-up the ledger is a small fraction of the day’s line count. The adjustment_hash column lets a later audit prove that a stored adjustment was not altered after the fact: re-hash the canonical fields and compare. The run identifiers the diff keys off are assigned by Settlement Cycle Mapping, which fixes what “preliminary” and “final” mean for each market.

Compliance Note #

This diff is the artifact that satisfies the FERC true-up obligation. Under the FERC Uniform System of Accounts and market-monitoring rules, a charge that changes between the preliminary and final settlement must be traceable to the specific input that moved and the specific delta it produced — a new total alone is not sufficient. The signed restatement ledger produced here supplies exactly that: each row bridges a named prior run to the current run for one settlement key, records the cause, and carries a content hash for tamper evidence. Persist it append-only alongside the frozen prior ledger so the settled position at any point in the day’s history can be reconstructed by summing the preliminary charge and every subsequent delta.

Two constraints must be validated against the governing tariff. First, the resettlement must fall inside the dispute or recalculation window fixed by the market’s settlements business practice manual — PJM’s settlements timeline, MISO BPM-005, CAISO’s Settlements and Billing BPM, or ERCOT Nodal Protocol Section 9 — and a correction arriving after the window closes routes to a governed exception rather than an automatic delta. Second, the final run must pin the price and loss-factor publications in force for the original trading day, not the current ones, so the delta reflects only the corrected input and not a retroactive rule change. Reconcile the net delta against the ISO’s resettlement statement before the adjustment reaches billing.

Frequently Asked Questions #

How do I handle a settlement line that exists in one run but not the other? #

Use an outer join with an indicator, not an inner join. A line present only in the final run is an appeared charge — its delta is the full final amount, and its cause is recorded as added-at-final. A line present only in the preliminary run disappeared — its delta is the negative of the preliminary amount, a full refund of that line. An inner join silently drops both cases, so the ledger’s net delta would fail to reconcile against the ISO by exactly the value of the appeared and disappeared lines. The _merge indicator column is what lets each case carry its own cause.

Why compute the delta in Decimal rather than letting pandas subtract the columns? #

A vectorized final["amount"] - prelim["amount"] runs in binary float, and most settlement amounts (cents) cannot be represented exactly in float, so the subtraction leaves sub-cent residue that never nets cleanly to zero. Across thousands of intervals that residue accumulates until it flips a rounding boundary and the restatement fails to reconcile with the ISO to the cent. Reading each amount as a Decimal and quantizing the difference keeps every delta exact, which is why pandas here only aligns rows and never touches the money arithmetic.

What does a zero delta mean and why drop it? #

A zero delta means the interval’s input changed — so it was correctly scoped as affected — but the recomputed amount rounds to the same cent as the prior amount, for instance a price correction smaller than the rounding boundary. Emitting it would fill the restatement ledger with rows that assert nothing moved, obscuring the genuine adjustments and inflating the audit trail. Dropping zero deltas keeps the ledger a precise record of what actually changed, while the change-detection step upstream still logs that the interval was examined.

Related #

• Resettlement & True-Up Processing
• Imbalance Allocation Algorithms
• CSV vs Parquet for settlement ledger storage