Backfilling DOE Fuel-Index Gaps in Surcharge Calculations

This page fixes the fuel surcharge that goes wrong on an entire gap window because the DOE weekly diesel index skipped a holiday or published late and your calc picked the wrong week’s peg.

The Failure You Are Hitting

Your fuel-surcharge (FSC) calculation reads the DOE weekly retail diesel index, finds the price for the shipment’s peg week, and applies the contractual formula. It is correct in steady state and wrong whenever the index series has a hole — which it does more often than a demo ever shows:

  • The DOE does not publish on federal holidays, so a Monday-published series skips a week around Memorial Day, Labor Day, and the winter holidays. A lookup for that missing week either raises KeyError, returns NaN, or — worse — silently grabs the nearest row.
  • On a late-publish week the row exists but arrives days after your batch runs, so the calc uses the prior week’s value without flagging that it did so.
  • The contract’s peg rule is ambiguous in code: is the peg the Monday on or before the ship date, or the most recently published Monday? Pick the wrong one and every line in the window pegs to the wrong index.

The observable symptom is a fuel-surcharge variance on every line in the gap window at once — a burst of flags that all trace to a single missing or misapplied index row, not to any real overbill. Left alone it either buries the real leakage in noise or, if the calc silently forward-fills the wrong direction, hides a genuine surcharge error.

Root Cause Analysis

The formula is fine. The defect is treating the index series as if it were complete and unambiguous when it is neither.

  1. Missing index rows. The DOE series is weekly but not gap-free. Holidays remove publish dates, and the calc has no explicit policy for “no row for this week”, so behaviour falls back to whatever the lookup library does by default — an exception, a null, or a silent nearest-match.
  2. Wrong peg-week rule. Contracts peg the surcharge to a specific Monday relative to the ship date, but “the applicable Monday” is under-specified in code. A calc that pegs to the ship week’s Monday and a calc that pegs to the most recently published Monday disagree exactly during a gap.
  3. Forward-fill vs no-fill ambiguity. When a week is missing, carrying forward the last published price is usually contractually correct, but doing it silently is not. Without an audit flag, a carried-forward value is indistinguishable from a real index reading, so nobody can tell a legitimate backfill from a stale-data bug.
DOE fuel-index gap backfill into the surcharge calc A weekly DOE diesel index series runs left to right with a visible hole where a holiday week is missing between two published prices. The series feeds a peg-week rule box that selects the most recently published Monday on or before the ship date. A backfill policy box then carries forward the last published price into the gap and stamps a carried-forward audit flag. The pegged, possibly backfilled price flows into the fuel-surcharge calculation, which multiplies the applicable rate by billable miles and emits either a clean surcharge or a surcharge marked estimated-from-carry-forward. DOE weekly index holiday gap present gap Peg-week rule most recent published Monday ≤ ship_date contract-defined Backfill policy carry forward last published price stamp carried_forward flag FSC calc rate(peg_price) × billable miles Decimal, banker's round Clean surcharge real index reading Estimated FSC carried-forward peg auditable, not silent

Reproducible Diagnostic

Before touching the formula, confirm the gap exists and find which invoices pegged into it. This snippet reindexes the DOE series onto a complete weekly Monday calendar and lists the missing weeks, then flags any invoice whose peg week landed on a hole:

import polars as pl
from datetime import date, timedelta

index = pl.read_csv("doe_diesel.csv", try_parse_dates=True)  # week_start (Monday), price_usd_gal

# build the full Monday calendar the series *should* cover
start, end = index["week_start"].min(), index["week_start"].max()
mondays = []
d = start
while d <= end:
    mondays.append(d)
    d += timedelta(days=7)

full = pl.DataFrame({"week_start": mondays}).join(index, on="week_start", how="left")
gaps = full.filter(pl.col("price_usd_gal").is_null())
print("missing index weeks:", gaps["week_start"].to_list())

invoices = pl.read_csv("fsc_invoices.csv", try_parse_dates=True)  # invoice_id, peg_week
affected = invoices.join(gaps, left_on="peg_week", right_on="week_start", how="inner")
print("invoices pegged into a gap:", affected["invoice_id"].to_list())

Read the result as a decision table:

Signal Interpretation Action
missing index weeks around a federal holiday expected publish gap carry forward last published (Step 2)
Peg week exists but price is None on recent weeks late publish, row not yet loaded reload index; re-peg after publish (Step 1)
Every invoice in one week flagged, same delta wrong peg-week rule applied fix peg selection to published Monday (Step 1)
Isolated single-invoice variance not an index gap — real overbill route to normal variance flow

A whole week of invoices flagging together is the fingerprint of an index gap, not leakage. Confirm the hole before you dispute a single line.

Resolution Path

The fix loads the index onto a complete calendar, applies the contractual peg rule explicitly, carries forward the last published price into any gap with an audit flag, and recomputes. Pin the toolchain:

# requirements.txt
polars==1.12.0
pydantic==2.10.6

Step 1 — Load the index and resolve the peg week explicitly

Encode the contract’s peg rule as one function so every caller agrees. The common rule is “the most recently published Monday on or before the ship date” — which naturally steps back over a gap instead of failing:

from datetime import date, timedelta

def peg_week(ship_date: date, published_weeks: set[date]) -> date | None:
    """Most recent published Monday on or before ship_date (contract rule)."""
    # walk back to this ship week's Monday, then step back while unpublished
    monday = ship_date - timedelta(days=ship_date.weekday())
    for _ in range(6):                       # bound the walk: >5 missing weeks is an alarm
        if monday in published_weeks:
            return monday
        monday -= timedelta(days=7)
    return None                              # exhausted → escalate, do not guess

A common mistake is pegging to ship_date’s Monday unconditionally and letting the lookup return null in a gap. Stepping back to the last published Monday is the whole point — but bound the walk so a genuinely stale index raises rather than silently reaching back a month.

Step 2 — Backfill the gap with a carried-forward price and an audit flag

Fill missing weeks from the last known price, but never silently. Every backfilled row carries a carried_forward flag and the source week, so a downstream reviewer can tell an estimate from a real reading:

from decimal import Decimal

def backfill_index(full_calendar: list[dict]) -> list[dict]:
    """
    full_calendar: [{'week_start': date, 'price_usd_gal': Decimal | None}, ...]
    sorted ascending. Carry the last published price forward into gaps,
    flagging each fill so it is auditable rather than indistinguishable.
    """
    out, last_price, last_week = [], None, None
    for row in full_calendar:
        price = row["price_usd_gal"]
        if price is not None:
            last_price, last_week = price, row["week_start"]
            out.append({**row, "carried_forward": False, "source_week": row["week_start"]})
        elif last_price is not None:
            out.append({
                "week_start": row["week_start"],
                "price_usd_gal": last_price,           # carry forward last published
                "carried_forward": True,
                "source_week": last_week,
            })
        else:
            # gap before the first publish — cannot carry forward, must escalate
            out.append({**row, "carried_forward": None, "source_week": None})
    return out

Never back-fill backwards from a later week — that leaks future information into a past shipment and is the classic wrong-direction fill. Only carry forward.

Step 3 — Recompute the surcharge and propagate the flag

Apply the contractual rate for the pegged price against billable miles, and carry the carried_forward flag onto the computed surcharge so the audit downstream knows the peg was estimated:

from decimal import Decimal, ROUND_HALF_EVEN

def fsc_amount(peg_price: Decimal, rate_per_mile: Decimal, billable_miles: Decimal,
               carried_forward: bool) -> dict:
    """Fuel surcharge = pegged rate × miles, with the estimate flag propagated."""
    amount = (rate_per_mile * billable_miles).quantize(
        Decimal("0.01"), rounding=ROUND_HALF_EVEN     # banker's rounding, match carrier
    )
    return {
        "fsc_amount": amount,
        "peg_price": peg_price,
        "audit_flag": "ESTIMATED_CARRY_FORWARD" if carried_forward else "CLEAN",
    }

Lines flagged ESTIMATED_CARRY_FORWARD are legitimate, not disputes — they should be excluded from the fuel-variance flow so they do not swamp Detecting Fuel-Surcharge Drift Across Invoice Batches. The base formula and rate-table lookup live in Calculating Dynamic Fuel Surcharges with Python Formulas.

Verification

Prove that gaps are filled correctly and never silently. These belong in the suite that runs whenever the index loads:

from datetime import date
from decimal import Decimal

def test_peg_steps_back_over_a_gap():
    published = {date(2026, 5, 18), date(2026, 6, 1)}   # 5/25 missing (holiday)
    assert peg_week(date(2026, 5, 27), published) == date(2026, 5, 18)

def test_backfill_flags_the_carried_row():
    cal = [
        {"week_start": date(2026, 5, 18), "price_usd_gal": Decimal("3.90")},
        {"week_start": date(2026, 5, 25), "price_usd_gal": None},   # gap
    ]
    filled = backfill_index(cal)
    assert filled[1]["carried_forward"] is True
    assert filled[1]["price_usd_gal"] == Decimal("3.90")
    assert filled[1]["source_week"] == date(2026, 5, 18)

def test_surcharge_carries_the_estimate_flag():
    out = fsc_amount(Decimal("3.90"), Decimal("0.52"), Decimal("410"), carried_forward=True)
    assert out["audit_flag"] == "ESTIMATED_CARRY_FORWARD"

def test_gap_before_first_publish_does_not_guess():
    cal = [{"week_start": date(2026, 1, 5), "price_usd_gal": None}]
    assert backfill_index(cal)[0]["carried_forward"] is None   # escalates, no fabricated price

In production the health signal is the count of ESTIMATED_CARRY_FORWARD lines per batch: a small, holiday-aligned count is normal; a spike means the DOE feed stopped loading, and the fix is to reload the index, not to dispute the carriers.

Preventive Configuration

Encode the peg rule, the carry-forward policy, and the escalation bounds so behaviour in a gap is a decision, not an accident:

fuel_index:
  source: DOE_WEEKLY_RETAIL_DIESEL
  peg_rule: MOST_RECENT_PUBLISHED_MONDAY_ON_OR_BEFORE_SHIP   # never ship-week Monday blindly
  backfill:
    policy: CARRY_FORWARD_LAST_PUBLISHED   # forward only — never backfill from a later week
    max_carry_forward_weeks: 5             # beyond this, escalate rather than trust stale data
    flag: ESTIMATED_CARRY_FORWARD          # every filled row is auditable
  late_publish:
    reload_window_days: 7                  # re-peg affected invoices after a late row lands
  alert_on_estimated_share: 0.15           # >15% estimated lines pages the FSC owner
  • Forward-only, always. A carry-backward fill leaks a future index into a past shipment; CI should assert backfill_index never populates a null from a later week.
  • Bound the carry. More than max_carry_forward_weeks of stale data is a broken feed, not a holiday — escalate instead of computing a surcharge from a month-old peg.
  • Re-peg late publishes. When a late DOE row lands inside reload_window_days, recompute the affected invoices so a carried-forward estimate is replaced by the real reading and the flag clears.

FAQ

Should I forward-fill or interpolate a missing DOE week?

Forward-fill — carry the last published price. The DOE weekly retail diesel index is the contractual peg, and contracts reference the most recently published value, not a modelled estimate between two weeks. Interpolation invents a number that appears in no official series, so it cannot be defended in a carrier dispute. Carry forward the last real reading and flag it as an estimate.

Which Monday does the surcharge peg to when the ship-week index is missing?

The most recently published Monday on or before the ship date. During a holiday gap that steps back to the prior published week rather than failing. Encode this as one peg-week function so every caller agrees; the frequent bug is pegging to the ship week’s Monday unconditionally and getting a null in the gap.

How do I tell a legitimate backfill from a stale-data bug?

Flag every filled row. A carried-forward price stamped ESTIMATED_CARRY_FORWARD with its source week is auditable; an unflagged one is indistinguishable from a real reading and hides both legitimate holiday fills and broken-feed staleness. Then bound the carry to about five weeks so a genuinely stale feed escalates instead of quietly pegging to month-old data.

The DOE published the week late and my batch already ran. Now what?

Re-peg. Keep a reload window of about a week: when the late row lands, recompute the invoices whose peg week was carried forward, replace the estimate with the real reading, and clear the ESTIMATED_CARRY_FORWARD flag. Do not dispute the resulting difference as leakage — it is the expected correction from a late publish, not a carrier overbill.

Up one level: Fuel Surcharge Formula Implementation · Section: Freight Contract Architecture & Rate Mapping