Energy Settlement Reconciliation Automation Hub

Setting adaptive tolerance bands for settlement variance

A tolerance band fixed at a round number — plus or minus two hundred dollars per interval, say — misfires the moment the variance distribution it was meant to police shifts: overnight off-peak intervals settle inside a few dollars, so a static band never fires there even on a real break, while a peak-summer interval routinely swings by four figures and buries the queue in false alerts. The fix is to let the band learn from the recent, seasonally-matched history of the variance itself, so its width tracks the distribution rather than a guessed constant. This guide computes rolling and seasonal adaptive tolerance bands over settlement variance in Python, using a robust median-and-MAD center-and-scale and an empirical quantile fence, then flags the intervals that fall outside. It is a step inside Threshold Tuning & Alerts, the layer that decides which deviations survive to become exceptions before any interval invoices.

The diagram below traces the band-fitting flow this page implements: a trailing window of settlement variance is grouped into a seasonal key, reduced to a robust center and scale, expanded into a band, and tested against the current interval.

Adaptive tolerance band fitting and breach test A trailing window of per-interval settlement variance is grouped by a seasonal key of hour-ending and day type. Each group is reduced to a robust center, the median, and a robust scale, the median absolute deviation. The center and scale expand into an adaptive band. The current interval's variance is tested against the band: inside the band it clears, outside it is flagged as a breach for the exception queue. Variance windowtrailing N days Seasonal keyhour × day-type Robust statsmedian, MAD Adaptive bandmed ± k·σ̂ Insideband? Clearedto settlement Breachexception queue yes no

Prerequisites

  • Python packages: pandas for the interval-indexed frames and the group-by, the standard-library statistics module for a Decimal-safe median, and decimal for every monetary field. No modelling library is required — the band is a closed-form reduction over a window, not a fitted regression.
  • Data dependencies: a per-interval settlement-variance series in USD (the signed difference between the settled charge and the expected charge for each settlement_interval), each row carrying a node_id, an hour_ending, and a day_type label. The variance itself is only trustworthy once the Loss Factor Mapping Strategies layer has removed loss-mapping artifacts and the Pricing Logic Implementation layer has fixed the price components, so a band is fit on real economic variance rather than on a calculation bug.
  • Permissions / access: read access to the historical settlement ledger for the trailing window. No write scope is needed — this component fits a band and flags breaches; routing the breaches onward is a separate concern handled by Routing settlement exceptions by severity tier.

Implementation

The band is built from two robust statistics so a handful of extreme intervals — a resettlement spike, a one-off dispute credit — cannot inflate it and mask the next real break. The center is the median rather than the mean, and the scale is the median absolute deviation (MAD) rather than the standard deviation. For a variance sample \(d\), the MAD and its normal-consistent scale estimate are

$$\text{MAD} = \operatorname{median}\big(\lvert d_i - \tilde{d}\rvert\big), \qquad \hat{\sigma} = 1.4826 \cdot \text{MAD}$$

where \(\tilde{d}\) is the sample median and the constant \(1.4826\) makes \(\hat{\sigma}\) a consistent estimator of the standard deviation when the underlying variance is Gaussian. A robust z-score then measures how many scaled deviations a given interval sits from the seasonal center, and the band is the interval that keeps \(\lvert z_i\rvert \le k\):

$$z_i = \frac{d_i - \tilde{d}}{\hat{\sigma}}, \qquad \big[\tilde{d} - k\hat{\sigma},; \tilde{d} + k\hat{\sigma}\big]$$

Step 1 — Reduce a variance sample to a robust center and scale. Every value is a Decimal, and the MAD is computed on Decimal absolute deviations so the band boundary is exact rather than a binary-float approximation that could flip a breach decision.

from decimal import Decimal, ROUND_HALF_EVEN
from statistics import median          # operates exactly on a list of Decimal
from dataclasses import dataclass

USD = Decimal("0.01")                  # settlement variance to the cent
MAD_TO_SIGMA = Decimal("1.4826")       # normal-consistency constant

@dataclass(frozen=True)
class Band:
    center_usd: Decimal
    scale_usd: Decimal                 # sigma-hat = 1.4826 * MAD
    lower_usd: Decimal
    upper_usd: Decimal
    sample_size: int
    config_version: str

def robust_center_scale(sample_usd: list[Decimal]) -> tuple[Decimal, Decimal]:
    """Median center and MAD-derived scale, both exact in Decimal."""
    center = median(sample_usd)
    abs_dev = [(v - center).copy_abs() for v in sample_usd]
    mad = median(abs_dev)
    scale = (MAD_TO_SIGMA * mad).quantize(USD, rounding=ROUND_HALF_EVEN)
    return center.quantize(USD, rounding=ROUND_HALF_EVEN), scale

Step 2 — Expand the center and scale into a band, with an absolute floor. When a seasonal group is quiet its MAD can collapse to zero, which would pin the band to a razor-thin line and flag every non-identical interval. A configurable absolute floor keeps the band from degenerating, mirroring the floor-versus-proportional discipline the parent layer applies to volumetric thresholds.

def fit_band(sample_usd: list[Decimal], k: Decimal,
             floor_usd: Decimal, config_version: str,
             min_sample: int = 30) -> Band | None:
    """Build a k-sigma band; return None when the sample is too thin to trust."""
    if len(sample_usd) < min_sample:
        return None                    # fall back to a static band upstream
    center, scale = robust_center_scale(sample_usd)
    half_width = max(k * scale, floor_usd)          # never narrower than the floor
    lower = (center - half_width).quantize(USD, rounding=ROUND_HALF_EVEN)
    upper = (center + half_width).quantize(USD, rounding=ROUND_HALF_EVEN)
    return Band(center, scale, lower, upper, len(sample_usd), config_version)

Step 3 — Key the band by season, and fit one band per group from a trailing window. Settlement variance is not stationary across the day or the week: peak hours, weekends, and holidays have distinct dispersions. Grouping on (hour_ending, day_type) lets each band breathe with its own regime instead of averaging a calm night into a volatile afternoon. The interval grid the window is sliced against is the one produced upstream by the Settlement Cycle Mapping engine.

import pandas as pd
from datetime import timedelta

def fit_seasonal_bands(history_df: pd.DataFrame, as_of, window_days: int,
                       k: Decimal, floor_usd: Decimal,
                       config_version: str) -> dict[tuple, Band]:
    """Fit one adaptive band per (hour_ending, day_type) over a trailing window."""
    window_start = as_of - timedelta(days=window_days)
    trailing = history_df[(history_df["settlement_interval"] >= window_start)
                          & (history_df["settlement_interval"] < as_of)]

    bands: dict[tuple, Band] = {}
    for key, group in trailing.groupby(["hour_ending", "day_type"]):
        sample = [Decimal(str(v)) for v in group["variance_usd"]]
        band = fit_band(sample, k, floor_usd, config_version)
        if band is not None:
            bands[key] = band
    return bands

Step 4 — Test the current cycle against its matched band and flag breaches. Each interval is looked up by its seasonal key, scored, and marked. Rows whose group never reached the minimum sample fall back to a static floor band so a thinly-traded node is never left unpoliced. The output carries the band boundaries and the config version so a reviewer can see exactly which band judged the interval.

def flag_breaches(current_df: pd.DataFrame, bands: dict[tuple, Band],
                  static_floor: Band) -> pd.DataFrame:
    """Score each interval against its seasonal band; emit band bounds inline."""
    rows = []
    for rec in current_df.to_dict("records"):
        variance = Decimal(str(rec["variance_usd"]))
        band = bands.get((rec["hour_ending"], rec["day_type"]), static_floor)

        # Robust z-score; guard a degenerate (zero-scale) band.
        if band.scale_usd > 0:
            z = (variance - band.center_usd) / band.scale_usd
        else:
            z = Decimal("0")
        breached = variance < band.lower_usd or variance > band.upper_usd

        rows.append({**rec,
                     "band_lower_usd": band.lower_usd,
                     "band_upper_usd": band.upper_usd,
                     "robust_z": z.quantize(Decimal("0.01"), rounding=ROUND_HALF_EVEN),
                     "band_breach": breached,
                     "config_version": band.config_version})
    return pd.DataFrame(rows)

Quantile fences as an alternative to the MAD z-score

When the variance distribution is heavily skewed — common on nodes that credit disputes asymmetrically — a symmetric center-and-scale band mis-fits, and an empirical quantile fence is the better tool. Instead of a center plus a multiple of scale, take the band directly from the sample’s lower and upper quantiles, so the two sides can be asymmetric.

def quantile_band(sample_usd: list[Decimal], lower_q: Decimal, upper_q: Decimal,
                  floor_usd: Decimal, config_version: str,
                  min_sample: int = 30) -> Band | None:
    """Asymmetric band from empirical quantiles, for skewed variance."""
    if len(sample_usd) < min_sample:
        return None
    ordered = sorted(sample_usd)
    def q(frac: Decimal) -> Decimal:
        idx = int((Decimal(len(ordered) - 1) * frac).to_integral_value())
        return ordered[idx]
    lower = min(q(lower_q), q(Decimal("0.5")) - floor_usd)   # keep floor width
    upper = max(q(upper_q), q(Decimal("0.5")) + floor_usd)
    center = median(ordered)
    return Band(center.quantize(USD), (upper - lower).quantize(USD),
                lower.quantize(USD), upper.quantize(USD), len(ordered), config_version)

The two band styles trade off cleanly, and the choice is per-node configuration rather than a code change:

Band style Center Width from Best when Weakness
MAD z-score Median \(k \cdot 1.4826 \cdot \text{MAD}\) Roughly symmetric variance Mis-fits heavy skew
Quantile fence Median Empirical [q_lo, q_hi] Skewed / one-sided variance Needs a larger sample
Static floor Fixed Configured ± floor Thin history, new node Ignores regime shifts
Seasonal MAD Per hour × day-type median \(k\hat{\sigma}\) per group Strong daily/weekly pattern Fragments the sample

Whichever style a node uses, the band is stamped with the config_version and the window that produced it, so a replay reconstructs the exact fence that judged a historical interval rather than refitting on today’s data.

Verification

Confirm the band and its flags before any breach is trusted:

  1. Shape and coverage. flag_breaches returns one row per input interval, each with a non-null band_lower_usd, band_upper_usd, and band_breach. A null band boundary means a seasonal key fell through without hitting the static floor — a routing bug, not a quiet node.
  2. Order invariant. For every row, band_lower_usd <= band_upper_usd must hold; a crossed band is a fit error, usually a negative or mis-signed floor.
  3. Reasonable breach rate. On a well-fit band with \(k = 3.5\), the flagged fraction should sit near a low single-digit percentage. A breach rate near zero means the band is too wide (raise the sample floor or lower \(k\)); a rate above roughly ten percent means it is too tight or the window straddles a regime change.
  4. Replay hash. Hash the sorted flagged-interval set together with the config_version and window bounds; re-running the same window against the same history must reproduce the identical digest, because every boundary is Decimal-quantized.
import hashlib, json

def verify_bands(flagged_df: pd.DataFrame, config_version: str,
                 max_breach_rate: float = 0.10) -> str:
    """Assert band sanity and return a replayable audit digest."""
    assert (flagged_df["band_lower_usd"] <= flagged_df["band_upper_usd"]).all(), \
        "crossed band boundary detected"
    breach_rate = flagged_df["band_breach"].mean()
    assert breach_rate <= max_breach_rate, f"breach rate {breach_rate:.3f} too high"

    breached = flagged_df[flagged_df["band_breach"]][
        ["node_id", "settlement_interval", "band_lower_usd", "band_upper_usd"]]
    snapshot = json.dumps(breached.to_dict("records"), default=str, sort_keys=True)
    return hashlib.sha256((config_version + snapshot).encode()).hexdigest()

Compliance Note

An adaptive band changes which intervals settle unexamined, so it is a regulated control point, not a private engineering tunable. FERC Order 888 and the associated Open Access Transmission Tariff provisions require transparent, auditable reconciliation, which means every band must be reconstructible: log the window bounds, the seasonal keys, the k or quantile parameters, the resulting boundaries, and a hash of the flagged set for each run, versioned by effective date. PJM Manual 28, MISO BPM-005, and ERCOT Nodal Protocols Section 9 each fix the dispute and resettlement windows a band must respect — a band whose trailing window silently absorbs a resettlement spike will under-flag the next one, so exclude known resettlement intervals from the fitting sample rather than letting them widen the fence. Validate the band methodology against your market’s tariff before it governs a final invoice, and keep a bounded static-floor fallback for any node whose history is too thin to fit, so no interval is ever left with no band at all.

Frequently Asked Questions

Why use a MAD-based band instead of a mean and standard deviation?

Because settlement variance is contaminated by rare, large intervals — resettlement true-ups, one-off dispute credits, curtailment events — and the mean and standard deviation are both pulled by exactly those outliers. A band built from them widens to swallow the very breaks it should catch. The median and the median absolute deviation have a high breakdown point, so up to nearly half the sample can be extreme without moving the center or the scale much. Multiplying the MAD by 1.4826 recovers a standard-deviation-equivalent scale under normal conditions, so a familiar three-sigma-style band still applies, but the fit no longer collapses when the history is dirty.

How long should the trailing window be?

Long enough that each seasonal group clears the minimum sample — typically thirty or more observations per (hour_ending, day_type) bucket — but short enough that the band still tracks the current regime. In practice a rolling window of thirty to sixty days balances the two: it captures a full billing cycle’s worth of each hour while staying responsive to a seasonal shift. If a window that long still leaves groups under the sample floor, widen the seasonal key (drop day_type, or bucket hours into on-peak and off-peak) rather than extending the window until it averages away the pattern you are trying to model.

What keeps an adaptive band from drifting so wide it stops catching breaks?

Three guards. A configurable absolute floor sets a minimum half-width so a quiet group’s band cannot collapse toward zero or, symmetrically, be raised without limit. Excluding known resettlement and dispute intervals from the fitting sample stops a single large event from permanently inflating the fence. And the verification step asserts a maximum breach rate near zero triggers a review, because a band that never fires is as much a failure as one that fires constantly — both mean the band has stopped tracking the distribution it is supposed to police.