Energy Settlement Reconciliation Automation Hub

How to map PJM settlement cycles to internal ledgers

A PJM Real-Time charge accrues to a June fiscal period, the Revised 2 run lands 40 days later carrying a corrected LMP, and unless both runs bind to the same OPERATING_DAY key the true-up posts to the wrong month and the close breaks. This page implements the delta-aware mapping that prevents that failure mode: it is the PJM-specific worked example under Settlement Cycle Mapping, turning PJM’s rolling Initial → Revised → Final cadence into GL-ready ledger rows without double-counting a single revision.

The state diagram below shows PJM’s multi-tier settlement lifecycle for one operating day, where each revision posts as a delta adjustment rather than a gross re-post until final settlement is reached.

PJM settlement lifecycle posting as delta adjustments to one operating-day key A left-to-right state diagram for a single operating day. A start marker enters the Initial provisional accrual, which advances through Revised 1 and Revised 2 and then to the Final settled state, each transition posting as a signed delta adjustment rather than a gross re-post. From Revised 2, a revision that lands outside the configured revision window branches down to a Manual review exception queue, which feeds an approved value back up into Final. A note beside Initial records that the initial accrual posts at T+2 to T+4 keyed by OPERATING_DAY; a note beside Final records that the delta-sum validation must equal FINAL_SETTLED_AMOUNT before the state is accepting. delta adjustment delta adjustment delta adjustment outside revision window approved Initial provisional accrual Revised 1 delta vs Initial Revised 2 delta vs Revised 1 Final settled · accepting Manual review exception queue Initial accrual posts T+2 to T+4 keyed by OPERATING_DAY Delta-sum validation equals FINAL_SETTLED_AMOUNT

Decoding PJM’s multi-tier settlement cadence

PJM settles on a staggered schedule that defies simple calendar alignment. Day-Ahead (DA) and Real-Time (RT) energy charges post on an initial window — typically T+2 to T+4 business days — followed by Revised 1, Revised 2, and Final runs that can extend months past the operating day. Ancillary services, Financial Transmission Rights (FTRs), and capacity auctions follow entirely different revision cadences and financial-recognition rules, which is why a single global posting offset never works.

Every settlement file carries a SETTLEMENT_CYCLE_ID, a POSTING_DATE, and an OPERATING_DAY that must be mapped to internal GL periods, cost centers, and accrual buckets. The critical failure point is treating each revision as a standalone transaction rather than a delta against the prior run — that is what inflates trial balances and breaks reconciliation. Correct mapping tracks the original operating day, the revision sequence, and the corresponding GL posting window as a unit. This structural alignment is a core component of any robust Core Architecture & Market Taxonomy for Energy Settlements implementation.

The reference below is the cadence the engine encodes — the publication offset and ledger treatment every PJM run is classified against.

PJM run Typical publication window Ledger treatment Idempotency key
Initial T+2 to T+4 business days Provisional accrual (node_id, operating_day, "INITIAL")
Revised 1 T+10 to T+20 Delta vs. Initial (node_id, operating_day, "REV1")
Revised 2 T+30 to T+55 Delta vs. Revised 1 (node_id, operating_day, "REV2")
Final T+60 to T+90 Delta vs. Revised 2 (node_id, operating_day, "FINAL")
FTR / Capacity Auction-cycle dependent Separate accrual bucket (node_id, operating_day, cycle_id)

PJM defines its operating day as hour-ending 01 through 24 in Eastern Prevailing Time — a single local calendar date. Once those timestamps are normalized to UTC for an enterprise data lake, the later hours roll onto the following UTC date, so the operating-day key must be preserved independently of the UTC instant used for storage and the billing period used for month-end close. The DST-boundary detail — 23-hour spring-forward days and 25-hour fall-back days — is handled the same way the parent Settlement Cycle Mapping engine enumerates intervals. Failure to decouple operating-day accounting from posting-date cash recognition creates material misstatements in FERC Uniform System of Accounts (USofA) reporting.

Deterministic mapping principles for GL alignment

To satisfy SOX 404 controls and FERC accounting standards, ledger routing must distinguish initial accruals, realized cash flows, and true-up adjustments. The mapping engine enforces three non-negotiable rules:

  1. Delta-first posting logic. A revision (REVISION_SEQ > 0) is posted as CURRENT_AMOUNT − PRIOR_AMOUNT for the same (operating_day, node_id, cycle) key, so the cumulative ledger amount always equals the latest settled value without double-counting.
  2. Period boundary enforcement. Runs crossing a fiscal month-end require split-period treatment: the POSTING_DATE dictates the cash period while the OPERATING_DAY dictates the accrual period.
  3. Immutable audit hashing. Every line item carries a deterministic SHA-256 hash over operating day, node ID, cycle type, revision sequence, and net amount, enabling automated reconciliation against prior ledger states without manual intervention.

Reference implementations should align with PJM Manual 11: Energy Settlements revision-tracking protocols and use standardized time-handling via Python’s datetime module to avoid daylight-saving transition errors. The delta-sum invariant every run must satisfy is:

$$\sum_{r=0}^{R} \Delta_r = \text{FINAL\_SETTLED\_AMOUNT}$$

where \(\Delta_0\) is the initial accrual and each \(\Delta_r\) for \(r>0\) is the signed revision delta.

Prerequisites

  • Python packages: pandas>=2.0, plus the standard-library decimal, hashlib, zoneinfo, and logging modules. No third-party timezone library is required — zoneinfo (PEP 615) ships with Python 3.9+.
  • Data dependencies: a raw PJM settlement export (CSV or the parsed frame) exposing SETTLEMENT_CYCLE_ID, OPERATING_DAY, POSTING_DATE, NODE_ID, AMOUNT, and REVISION_SEQ. Parsing the raw file into these columns is the job of the ISO/RTO Data Format Standards layer; the field contract itself is enforced upstream by the Schema Validation Frameworks.
  • Permissions: read access to the PJM member data feed (or the archived settlement drop) and write access to the append-only ledger store. Amount fields are read as strings and cast to Decimal — never float — so that charges quantized to the cent survive summation across thousands of intervals without binary rounding drift.

Implementation

The pattern below is a production-tested approach for cycle-to-ledger translation that prioritizes auditability, delta reconciliation, and type-safe execution. Amounts are handled with the decimal module end to end.

import pandas as pd
from datetime import datetime, timezone, timedelta
from zoneinfo import ZoneInfo
from decimal import Decimal, ROUND_HALF_UP
import hashlib
import logging
from typing import Dict

# Configure audit-safe logging for regulatory traceability
logging.basicConfig(
    filename="pjm_ledger_mapping_audit.log",
    level=logging.INFO,
    format="%(asctime)s | %(levelname)s | %(message)s",
)

CENTS = Decimal("0.01")
ET = ZoneInfo("America/New_York")

# Publication offset (days) and GL account per PJM run type. revision_window_days
# bounds how late a revision may still auto-post before manual review is required.
PJM_CYCLE_MAP: Dict[str, Dict] = {
    "DA":  {"ledger_period_offset": 0, "revision_window_days": 30, "gl_account": "5010"},
    "RT":  {"ledger_period_offset": 0, "revision_window_days": 45, "gl_account": "5020"},
    "FTR": {"ledger_period_offset": 1, "revision_window_days": 60, "gl_account": "5030"},
    "CAP": {"ledger_period_offset": 0, "revision_window_days": 90, "gl_account": "5040"},
}


def normalize_pjm_timestamp(raw_ts: str) -> datetime:
    """Convert a naive PJM Eastern Prevailing Time timestamp to UTC with explicit DST handling.

    Uses zoneinfo (PEP 615) so the standard datetime attachment semantics apply. The
    fold attribute disambiguates the repeated 01:00-01:59 wall-clock hour during the
    fall-back transition; callers needing the second occurrence set fold=1 before
    localization. Offset-aware inputs are converted straight through.
    """
    dt_naive = pd.to_datetime(raw_ts, format="mixed").to_pydatetime()
    if dt_naive.tzinfo is not None:
        return dt_naive.astimezone(timezone.utc)
    return dt_naive.replace(tzinfo=ET).astimezone(timezone.utc)


def to_cents(value) -> Decimal:
    """Cast a raw amount to a cent-quantized Decimal via str to avoid float contamination."""
    return Decimal(str(value)).quantize(CENTS, rounding=ROUND_HALF_UP)


def generate_line_hash(operating_day: str, node_id: str, cycle: str,
                       revision_seq: int, amount: Decimal) -> str:
    """Deterministic audit hash for duplicate detection and reconciliation."""
    payload = f"{operating_day}|{node_id}|{cycle}|{revision_seq}|{amount}"
    return hashlib.sha256(payload.encode("utf-8")).hexdigest()


def map_settlements_to_ledger(settlement_df: pd.DataFrame) -> pd.DataFrame:
    """Transform raw PJM settlement exports into GL-ready ledger rows.

    Enforces delta-first posting, posting/operating-day period split, and audit hashing.
    Each revision is posted as CURRENT - PRIOR for its (operating_day, node, cycle) key.
    """
    required = ["SETTLEMENT_CYCLE_ID", "OPERATING_DAY", "POSTING_DATE",
                "NODE_ID", "AMOUNT", "REVISION_SEQ"]
    missing = [c for c in required if c not in settlement_df.columns]
    if missing:
        raise ValueError(f"Missing required columns: {missing}")

    df = settlement_df.copy()
    # Deferred casting: normalize timestamps and quantize amounts before any grouping.
    df["OPERATING_DAY_UTC"] = df["OPERATING_DAY"].apply(normalize_pjm_timestamp)
    df["POSTING_DATE_UTC"] = df["POSTING_DATE"].apply(normalize_pjm_timestamp)
    df["AMOUNT_DEC"] = df["AMOUNT"].apply(to_cents)
    df["REVISION_SEQ"] = df["REVISION_SEQ"].astype(int)

    # Order revisions within each operating-day / node / cycle group so the delta
    # is computed against the immediately prior settled amount.
    df = df.sort_values(["NODE_ID", "OPERATING_DAY", "SETTLEMENT_CYCLE_ID", "REVISION_SEQ"])

    ledger_rows = []
    for (node_id, op_key, cycle), grp in df.groupby(
        ["NODE_ID", "OPERATING_DAY", "SETTLEMENT_CYCLE_ID"], sort=False
    ):
        if cycle not in PJM_CYCLE_MAP:
            logging.warning("Unknown cycle %s for node %s; skipping group.", cycle, node_id)
            continue
        cfg = PJM_CYCLE_MAP[cycle]
        prior_amount = Decimal("0.00")

        for _, row in grp.iterrows():
            op_day = row["OPERATING_DAY_UTC"]
            posting = row["POSTING_DATE_UTC"]
            current_amount = row["AMOUNT_DEC"]
            rev_seq = row["REVISION_SEQ"]

            # Delta-first: a revision is the signed change from the prior run.
            is_delta = rev_seq > 0
            delta_amount = current_amount - prior_amount
            transaction_type = "REVISION_DELTA" if is_delta else "INITIAL_POST"

            # POSTING_DATE drives the cash/GL period; OPERATING_DAY drives the accrual.
            gl_period_end = posting + timedelta(days=cfg["ledger_period_offset"])
            gl_period = f"{gl_period_end.year}-{gl_period_end.month:02d}"

            # Revision-window guard: stale revisions must not silently hit current P&L.
            age_days = (posting - op_day).days
            needs_review = is_delta and age_days > cfg["revision_window_days"]
            if needs_review:
                logging.warning(
                    "Revision for node %s op_day %s cycle %s is %d days old (window %d); "
                    "routing to manual review.",
                    node_id, op_key, cycle, age_days, cfg["revision_window_days"],
                )

            line_hash = generate_line_hash(
                op_day.strftime("%Y-%m-%d"), str(node_id), cycle, rev_seq, delta_amount
            )

            ledger_rows.append({
                "GL_PERIOD": gl_period,
                "GL_ACCOUNT": cfg["gl_account"],
                "OPERATING_DAY_UTC": op_day,
                "POSTING_DATE_UTC": posting,
                "TRANSACTION_TYPE": transaction_type,
                "DELTA_AMOUNT": delta_amount,       # signed adjustment posted to the GL
                "SETTLED_AMOUNT": current_amount,   # cumulative settled value for this run
                "REVISION_SEQ": rev_seq,
                "NEEDS_REVIEW": needs_review,
                "LINE_HASH": line_hash,
                "SETTLEMENT_CYCLE": cycle,
                "NODE_ID": node_id,
            })
            prior_amount = current_amount

    return pd.DataFrame(ledger_rows)

Verification steps

Confirm the mapping before it reaches the trial balance:

  • DataFrame shape. For an input frame of N settlement rows, map_settlements_to_ledger returns at most N ledger rows — fewer only when a group’s cycle is not in PJM_CYCLE_MAP (each such skip is logged). Assert set(out.columns) == {"GL_PERIOD", "GL_ACCOUNT", …, "LINE_HASH"} so downstream loaders never receive a renamed column.
  • Delta-sum reconciliation. The core invariant: for every (NODE_ID, OPERATING_DAY, SETTLEMENT_CYCLE) group, SUM(DELTA_AMOUNT) must equal the latest SETTLED_AMOUNT. Run out.groupby(["NODE_ID", "OPERATING_DAY_UTC", "SETTLEMENT_CYCLE"])["DELTA_AMOUNT"].sum() and diff it against the group’s final settled value — any non-zero residual (to the cent) is a mapping defect, not a rounding artifact, because all arithmetic is Decimal.
  • Hash stability. Re-running the mapper on the same input must reproduce identical LINE_HASH values. A changed hash on unchanged inputs signals non-determinism (usually a float leak or an unsorted group) and blocks the append-only post.
  • Review routing. Filter out[out["NEEDS_REVIEW"]] and confirm every row exceeds its cycle’s revision_window_days; these must land in the exception queue rather than auto-posting. Material deltas feed the same alerting fabric described in Threshold Tuning & Alerts.

Compliance note

This implementation is written to survive both internal audit and external regulatory review:

  • FERC Uniform System of Accounts. GL_ACCOUNT routing must map to FERC-prescribed accounts for energy purchases, sales, and transmission charges; validate against the FERC Uniform System of Accounts so quarterly and annual reporting stays consistent.
  • SOX 404 change management. Any edit to PJM_CYCLE_MAP or GL routing requires version-controlled deployment, peer review, and regression testing against historical settlement files — the mapping table is a financial control, not configuration.
  • Revision-window enforcement. PJM REVISION_SEQ values can exceed 5 for complex LMP disputes; entries outside the configured window are flagged rather than silently absorbed into current-period P&L, satisfying the auditable-lineage requirement that every mapped interval trace back to its source run.
  • Immutable audit trail. The SHA-256 LINE_HASH and append-only posting give a defensible chain of custody for dispute resolution; access to the mapped ledger is governed by the RBAC and audit controls covered in the Core Architecture & Market Taxonomy for Energy Settlements framework.

Frequently asked questions

Why post revisions as deltas instead of re-posting the gross amount?

Because a gross re-post double-counts. If the Initial run books $1,200 and Revised 1 books another gross $1,250, the trial balance shows $2,450 for a position that settled at $1,250. Posting Revised 1 as a $50 delta against the prior $1,200 keeps the cumulative ledger equal to the latest settled value, and the delta-sum invariant proves it to the cent.

Which timestamp drives the GL period — OPERATING_DAY or POSTING_DATE?

Both, for different books. OPERATING_DAY drives the accrual period (when the energy was delivered) and POSTING_DATE drives the cash/GL period (when the charge is recognized). Collapsing them onto one field is what produces month-end misstatements when a revision crosses a fiscal boundary.

Why Decimal instead of float for the amounts?

PJM charges are quantized to the cent and summed across thousands of intervals. Binary floating-point drift eventually flips a rounding boundary, so a float pipeline that looks correct on a sample fails the to-the-cent delta-sum reconciliation in production. Casting every amount through Decimal(str(value)) removes that failure class entirely.