Reconciling Merged Cells in Funder Budget Workbooks

Unmerge and forward-fill merged cells in funder .xlsx budgets with openpyxl, coerce currency to Decimal, and map to cost pools per 2 CFR 200.403.

This guide is part of the Excel Budget Template Sync section within the broader Data Ingestion & Grant Parsing Workflows framework, and it solves one narrow problem: a funder ships a .xlsx budget where category headers and subtotal rows are merged cells, and a naive worksheet.iter_rows() read returns the category label on exactly one row and None on every other row of the span.

The result is quietly catastrophic. A “Personnel” header merged down four line items lands on the first line and vanishes from the other three, so a group-by on category silently drops 75% of the personnel spend into a null bucket. This guide builds a reconciler that snapshots every merged range, unmerges into a working copy, forward-fills the top-left anchor value across the former span so every row carries its category, flattens column-spanning headers, coerces currency to Decimal, and maps each row to a canonical cost pool.

When to Use This Approach

Reach for this reconciler when all three conditions hold:

  • The workbook is a real funder template, not a clean export. Program officers build budgets by hand in Excel, and merge & center is how humans draw a category band or a “Year 1 / Year 2” super-header. Those merges are presentation, not data — openpyxl faithfully preserves them, and every downstream group-by has to undo them first.
  • The numbers feed a regulated artifact. Reconciled category totals reconcile against the financial-management records required by 2 CFR §200.302, so a category that leaks into a null bucket is a misstatement of allocated cost, not a cosmetic glitch. Consistency of treatment across every award is itself a requirement under 2 CFR §200.403, which is exactly why the mapping to cost pools must be deterministic rather than eyeballed.
  • Money must stay exact. Budget arithmetic in binary float accumulates rounding error, and a subtotal that is off by a cent fails an auditor’s cross-foot. Every currency cell here is coerced through Decimal, never float.

Reading formulas, writing a reconciled workbook back to disk, cross-funder alias resolution, and indirect-cost rate math are explicitly out of scope. Flat CSV emission belongs to Automating Excel to CSV Conversion for Budget Tracking; reconciling one funder’s category names against a canonical vocabulary belongs to Field Mapping & Normalization. This stage runs synchronously on one worksheet at a time so the fill order and audit trail stay deterministic.

Merged-cell reconciliation: unmerge, forward-fill the anchor, then coerce to Decimal On the left, a "Before" grid shows a Personnel category cell merged down two line-item rows, so only the first row carries the label. An unmerge-plus-fill step produces the "After" grid where both rows read Personnel. A coerce step converts the dollar strings into Decimal values, which are handed off as canonical cost pools: personnel salaries as Decimal 80000.00 and personnel fringe as Decimal 18400.00, aligned to 2 CFR 200.403. A pipeline strip below names the five stages: openpyxl.load_workbook with data_only, worksheet.merged_cells.ranges, unmerge plus forward-fill anchor, coerce currency to Decimal, and map to canonical cost pools handed to Field Mapping. Merged-cell reconciliation: unmerge, forward-fill, coerce Every row carries its category anchor before currency becomes Decimal and maps to a cost pool. Before — anchor merged down Personnel anchor A2:A3 Salaries $80,000 Fringe $18,400 unmerge + fill After — anchor forward-filled Personnel Personnel Salaries $80,000 Fringe $18,400 coerce $→Decimal Canonical cost pools personnel · salaries → Decimal('80000.00') personnel · fringe   → Decimal('18400.00') consistent treatment · 2 CFR §200.403 Pipeline stages load_workbook data_only=True merged_cells .ranges snapshot unmerge + forward-fill anchor coerce currency → Decimal map to cost pools → Field Mapping & Normalization

Step-by-Step Implementation

The reference implementation targets Python 3.10+ and openpyxl for both reading and in-memory mutation. Money is handled exclusively with the standard-library decimal module — never float — so cross-foot checks stay exact.

text
openpyxl==3.1.5

One contract to internalize before writing any code: openpyxl preserves merges as metadata, not as data. A merged range stores its value on the top-left anchor cell only; every other cell in the span returns None. Reconciliation means projecting that anchor value back across the span so each row stands alone.

Step 1: Open the workbook and configure the audit logger

Every reconciliation must be replayable from its logs, so configure a standard-library logger (never print) and open the workbook with data_only=True so cell reads return the cached computed value Excel stored, not the underlying formula string. Keep read_only=False: read-only mode does not let you unmerge or write cells back into the working copy.

python
import logging
from decimal import Decimal, InvalidOperation
from pathlib import Path
from typing import Dict, List, Optional, Tuple

from openpyxl import load_workbook
from openpyxl.worksheet.worksheet import Worksheet
from openpyxl.worksheet.cell_range import CellRange

# Configure structured audit logger
AUDIT_LOGGER = logging.getLogger("grant_budget.reconcile")
AUDIT_LOGGER.setLevel(logging.INFO)
_handler = logging.StreamHandler()
_handler.setFormatter(logging.Formatter("%(asctime)s | %(levelname)s | %(message)s"))
AUDIT_LOGGER.addHandler(_handler)


def open_budget_sheet(path: Path, sheet: str) -> Worksheet:
    """Open a funder workbook for cached-value reads with an explicit sheet check."""
    workbook = load_workbook(filename=str(path.resolve()), data_only=True, read_only=False)
    if sheet not in workbook.sheetnames:
        raise KeyError(f"Sheet {sheet!r} absent; workbook has {workbook.sheetnames}")
    worksheet = workbook[sheet]
    AUDIT_LOGGER.info(
        "Opened %s | sheet=%s | dims=%s | merges=%d",
        path.name, sheet, worksheet.dimensions, len(worksheet.merged_cells.ranges),
    )
    return worksheet

The data_only=True flag has one sharp edge: if the workbook has never been opened and saved by a spreadsheet application, no formula results are cached and those cells read back as None. Step 5 treats None currency cells as blanks rather than crashing, and the failure table below explains how to recover the cached values.

Step 2: Enumerate the merged cell ranges

worksheet.merged_cells.ranges is a live set that unmerge_cells mutates in place, so iterating it while unmerging raises RuntimeError: Set changed size during iteration. Snapshot the ranges and their anchor values into a plain list first, sorted top-to-bottom and left-to-right for a deterministic fill order.

python
def snapshot_merges(worksheet: Worksheet) -> List[Tuple[CellRange, object]]:
    """Capture every merged range and its top-left anchor value before any mutation."""
    snapshot: List[Tuple[CellRange, object]] = []
    for rng in sorted(worksheet.merged_cells.ranges, key=lambda r: (r.min_row, r.min_col)):
        anchor_value = worksheet.cell(row=rng.min_row, column=rng.min_col).value
        # Copy the range object so it survives the live-set mutation in Step 3.
        snapshot.append((CellRange(str(rng)), anchor_value))
    AUDIT_LOGGER.info("Captured %d merged ranges for reconciliation", len(snapshot))
    return snapshot

Each CellRange exposes min_row, max_row, min_col, and max_col, which are the inclusive 1-based bounds of the span. Copying via CellRange(str(rng)) detaches the snapshot from the worksheet’s live set so the loop in the next step can safely unmerge.

Step 3: Unmerge and forward-fill the anchor value

Now walk the snapshot: unmerge each range, then stamp its captured anchor value into every cell of the former span. This is the forward-fill — after it runs, a category that was merged down four line items appears on all four rows, so a later group-by cannot drop any of them into a null bucket.

python
def unmerge_and_fill(worksheet: Worksheet, snapshot: List[Tuple[CellRange, object]]) -> None:
    """Unmerge each captured range and project the anchor value across its full span."""
    filled = 0
    for rng, anchor_value in snapshot:
        try:
            worksheet.unmerge_cells(str(rng))
        except KeyError as exc:
            AUDIT_LOGGER.error("Range %s was not merged, skipping: %s", rng, exc)
            continue
        for row in range(rng.min_row, rng.max_row + 1):
            for col in range(rng.min_col, rng.max_col + 1):
                worksheet.cell(row=row, column=col).value = anchor_value
        filled += 1
    AUDIT_LOGGER.info("Forward-filled %d former merge spans", filled)

The + 1 on both range bounds matters: max_row and max_col are inclusive, so omitting it leaves the last row or column of every span unfilled — an off-by-one that reintroduces exactly the null-bucket bug you are trying to kill.

Step 4: Resolve column-spanning headers

Vertical merges carry categories down rows; horizontal merges carry super-headers across columns — a “Year 1” cell merged over its Q1/Q2/Q3/Q4 sub-columns. Because Step 3 already stamped the super-header across every column it spanned, you can now fold the two header rows into one flat key per column.

python
def resolve_headers(worksheet: Worksheet, header_rows: Tuple[int, int]) -> Dict[int, str]:
    """Fold a filled two-tier header into a single flat key per column index."""
    top_row, sub_row = header_rows
    headers: Dict[int, str] = {}
    for col in range(1, worksheet.max_column + 1):
        top = worksheet.cell(row=top_row, column=col).value
        sub = worksheet.cell(row=sub_row, column=col).value
        parts = [str(p).strip().lower().replace(" ", "_") for p in (top, sub) if p is not None]
        if not parts:
            raise ValueError(f"Column {col} has no header text after forward-fill")
        # dict.fromkeys dedups when the super-header equals the sub-header.
        headers[col] = "__".join(dict.fromkeys(parts))
    AUDIT_LOGGER.info("Resolved %d flat column headers", len(headers))
    return headers

A column under “Year 1 → Q1” becomes year_1__q1; a single-tier column where the forward-fill duplicated the same label into both rows collapses back to one token via dict.fromkeys. Raising on an empty header is deliberate — a nameless column downstream is a silent data-loss risk, so fail loud.

Step 5: Coerce currency values to Decimal

Funder cells arrive as a mess: openpyxl hands back native numbers as float, but hand-typed cells arrive as strings like "$80,000" or "(1,200)" accounting negatives. Coerce every one through Decimal, and route float through str() first so you never inherit binary rounding artifacts like Decimal(80000.1) expanding to a 50-digit tail.

python
def to_decimal(raw: object) -> Optional[Decimal]:
    """Coerce a funder currency cell to Decimal; None for blanks, raise on garbage."""
    if raw is None or (isinstance(raw, str) and not raw.strip()):
        return None
    if isinstance(raw, (int, Decimal)):
        return Decimal(raw)
    # str() the float path first: Decimal(0.1) inherits binary noise; Decimal("0.1") does not.
    cleaned = (
        str(raw).strip()
        .replace("$", "").replace(",", "")
        .replace("(", "-").replace(")", "")
    )
    try:
        return Decimal(cleaned)
    except InvalidOperation as exc:
        raise ValueError(f"Non-numeric currency cell {raw!r}") from exc

Blank cells return None so an empty line item is distinguishable from a genuine 0, and a truly non-numeric value (a stray note in an amount column) raises a structured ValueError rather than poisoning a subtotal with a coerced zero.

Step 6: Map rows to canonical cost pools

Finally, translate each funder’s category label into a canonical cost pool. 2 CFR §200.403 requires costs to be treated consistently across awards, which is impossible if “Fringe”, “Fringe Benefits”, and “F&A” scatter into three different buckets. A lookup table pins them down; an unmapped label is quarantined for review, never silently dropped.

python
CANONICAL_POOLS: Dict[str, str] = {
    "salaries": "personnel", "wages": "personnel",
    "fringe": "fringe_benefits", "fringe_benefits": "fringe_benefits",
    "travel": "travel", "equipment": "equipment", "supplies": "supplies",
    "contractual": "contractual", "indirect": "indirect", "f&a": "indirect",
}


def map_cost_pool(category: str) -> str:
    """Map a funder category label to a canonical cost pool, or quarantine it."""
    key = category.strip().lower().replace(" ", "_")
    pool = CANONICAL_POOLS.get(key)
    if pool is None:
        AUDIT_LOGGER.warning("Unmapped category %r routed to quarantine", category)
        return "__unmapped__"
    return pool

Anything landing in __unmapped__ is the handoff contract into Field Mapping & Normalization, where fuzzy and alias-driven resolution lives. This step stays strict and deterministic on purpose.

Verification

Confirm the reconciler behaves deterministically with four checks:

  1. No merges survive. After unmerge_and_fill, assert len(worksheet.merged_cells.ranges) == 0. A non-empty set means a range was skipped and its span is still hollow.
  2. Every row carries its category. Read the category column top to bottom and assert no cell in the data region is None. This is the direct proof the forward-fill closed the null-bucket gap.
  3. Money is exact Decimal. Assert to_decimal("$18,400") == Decimal("18400") and that to_decimal(80000.1) yields Decimal("80000.1") with no binary tail — the guarantee that subtotals cross-foot to the cent for the 2 CFR §200.302 financial record.
  4. Totals reconcile. Sum the forward-filled line amounts per cost pool and assert the total equals the funder’s own subtotal row, which is the check an auditor replays.

A compliant run emits one INFO audit line per stage — open, capture, fill, headers, map — and one WARNING per unmapped category. Ship those logs to a write-once tier so the trail is replayable.

python
ws = open_budget_sheet(Path("foundation_FY26_budget.xlsx"), sheet="Budget")
merges = snapshot_merges(ws)
unmerge_and_fill(ws, merges)
assert len(ws.merged_cells.ranges) == 0
assert to_decimal("(1,200)") == Decimal("-1200")
assert map_cost_pool("Fringe Benefits") == "fringe_benefits"

Common Errors & Fixes

Error Cause Fix
RuntimeError: Set changed size during iteration Iterating worksheet.merged_cells.ranges while unmerge_cells mutates the live set Snapshot the ranges into a list first with snapshot_merges, then unmerge from the copy.
Category column reads None on all but the first row Reading a merged span without unmerging; only the top-left anchor holds the value Run unmerge_and_fill before any group-by; assert the merge set is empty afterward.
Currency cells all read back as None data_only=True but the workbook was never saved by Excel/LibreOffice, so no formula results are cached Open and save the file once in a spreadsheet app to populate the cache, or reopen with data_only=False and evaluate formulas yourself.
Decimal value has a long binary tail Passing a float straight into Decimal(...) Route floats through str() first, as to_decimal does; Decimal("0.1") is exact, Decimal(0.1) is not.
Last row or column of a span still empty range(min, max) omits the inclusive upper bound Use range(rng.min_row, rng.max_row + 1) and the same + 1 on columns.
Whole categories vanish from the output totals A funder label absent from CANONICAL_POOLS was dropped silently map_cost_pool returns __unmapped__ and logs a WARNING; triage those in Field Mapping & Normalization.