Fuels Electricity Extract

Versions

• v2 # Changelog ## v2 - The test workbooks under `lib/testdata/` ship base64-encoded (`*.xlsx.b64`); the tests decode them into a temporary directory. ## v1 - Initial release: `lib/extract.py` conforms rows from a client's fuels-and-electricity activity workbook to the `FuelsAndElectricityBill` contract. Required fields (`siteId`, `type`, `billingDate`, `periodStart`, `periodEnd`, `quantityType`, `unit`, `totalValue`) are never defaulted — a row missing one is parked in `issues` with a `missing` list and the `parked_row` found so far. Optional fields (`spendAmount`, `spendCurrency`, `billingIdentifier`, `supplierName`, `supplierReference`, `percentageApportioned`, `percentageRenewable`) are omitted, never guessed, when absent from the source. - `lib/vocabulary.py` mirrors `FUEL_TYPES`/`UNITS` verbatim from `fuelsAndElectricityBill.ts` (the authoritative TypeScript contract) and provides `nearest_candidates` for triage suggestions — a source value outside either closed enum is parked with `typeCandidates`/ `unitCandidates` (kept separate so a human can tell which field a suggestion is for), never coerced. - **Unit/type are never assumed except as a documented fallback, and a real source column always wins over it.** Only `Electricity` → `kWh`/ `Electricity Grid` and `Natural gas` → `Natural Gas` are sheet-level fallbacks (the first is brief-mandated; both are used only when the sheet has no Fuel Type/Unit column of its own, or a row's cell in it is blank). Every sheet — including `Electricity` — always looks for its own Fuel Type/Unit column first; if present and populated, that value is used even when it disagrees with the fallback (e.g. an `Electricity` sheet genuinely metered in m³ emits `m³`, not `kWh` — contradicting a source fact would be the same ~11x corruption risk as inventing one). When a fallback value IS used for a given row, `type`/`unit` are listed in that bill's `derived`. - **A populated sheet never silently vanishes, and an unrecognised sheet never floods `issues` either.** A sheet with a **strong anchor column** (Period End or Consumption specifically - a Unit or Fuel Type match alone is not enough, since a lookup/reference tab plausibly has one of those without being a bill table) parks each incomplete row individually, with `parked_row` carrying the row's raw, non-blank source cells (keyed by their original headers, coerced through a total `_json_safe` - see below) alongside the fields this module identified — the LLM triage step needs the actual values to reason from. A sheet with no strong anchor at all (a guidance/notes/lookup tab) parks as exactly ONE sheet-level issue (`"row": null`, naming the sheet and the columns actually found) instead of one per row — a workbook mixing a real data tab with a guidance tab and an emission-factor lookup tab headed `Fuel | Unit | Factor` (a coincidental Unit-column match, without the earlier strong-anchor requirement) previously produced 19+ near-identical issues that buried the genuine ones. A sheet that has real cell content but yields zero rows at this module's assumed header row (e.g. exactly enough real rows that the header consumes the last one) also surfaces as one issue — UNLESS the header row it read already contains a strong anchor, in which case the sheet is a genuinely unfilled template (headers present, no data entered yet) and correctly yields nothing rather than a misdiagnosed "header may be on a different row" issue. - A sheet that raises while being read as a table (corrupt region, unexpected format) surfaces as a single issue (`missing: ["sheet-unreadable"]`) instead of silently contributing nothing. - Text dates are read UK day-first (`"03/04/2025"` → 3 April) **except** when the string is ISO-leading (`"2025-03-04"`, `"2025/03/04"`, `"2025.03.04"`) — the most common machine-written format — which is read in its own unambiguous year-month-day order instead; blanket day-first parsing would silently swap month/day on an ISO date. pandas' own ambiguity warning (e.g. for `"04/13/2025"`, where day-first would need a nonexistent 13th month) is captured via `warnings.catch_warnings(record= True)` and never silenced — if it fires, the row parks rather than guessing. (`simplefilter("error", ...)` does NOT work for this specific warning: pandas raises it from Cython internals where turning it into an exception doesn't propagate normally - it becomes an unraisable exception printed to stderr, and the guessed value is returned regardless. Fixed to use `record=True`, which doesn't depend on exception propagation.) - **A date cell that does not name a full DAY parks rather than resolving to one.** `"2025"`, `"Mar-25"`, `"Mar 2025"`, `"Jan"`, `"2025-03"` are all parked. A date parser asked for a date always returns a whole one, filling in whatever the text omits — `"2025"` silently became 2025-01-01 and `"Mar-25"` became 0001-03-25, each emitted as a COMPLETE, committable bill with zero issues raised. The `"2025"` case is the dangerous one: it does not look bogus, and it asserts a whole year's consumption as a single day on 1 January. Completeness is detected by parsing the text against two defaults that differ in year, month AND day: only a text that pins all three itself resolves identically both times. Every format that does name a full day is unaffected (`31 December 2025`, `Dec 31, 2025`, `31-Dec-2025`, `3 April 2025`, `1/2/2025`, `31/12/25`, `03/04/2025`, `2025-03-04`, `2025.03.04`, `2025-03-04 00:00:00`). - **A bill's site is resolved per ROW, from the source's own `Site` column.** `extract_bills(..., site_ids_by_name=...)` / `--sites` takes a mapping of the workbook's own site names to site resource ids, which the agent builds by querying the `site` resource type BEFORE extracting. A row whose `Site` cell matches nothing in that mapping is **parked** with `missing: ["siteId"]`, a `siteCandidates` list and an explanatory `note` — never stamped with a default id. Previously a single `site_id` argument was written onto every row while the row's own site text reached only `parked_row`, so a three-site sheet came back as three bills on one site with zero issues: this blueprint's design-spec failure mode 2 ("every Odysea bill attributed to DUK's site") reproduced inside the code meant to prevent it. `site_id` / `--site-id` remains the explicit answer for rows that name no site at all (a sheet with no `Site` column, e.g. DUK's annual totals) and never overrides a site a row names. Name matching normalises case and whitespace only — nothing fuzzier, since a near-match would be a guess about which site a bill belongs to. - The CLI takes flags rather than positional ids: `python lib/extract.py <workbook> [--sites <json-or-path>] [--site-id <id>] [--sheets a,b,c]`. At least one of `--sites`/`--site-id` is required. - **`periodStart`'s calendar-month convention is a known, disclosed approximation.** It is applied to every row with a `Period End`, including a row that is really quarterly or annual, so such a row gets a one-month period — and that period is what `Integrations.Process.SiteEmissions` resolves emissions factors against. Neither recognised source shape records a period _start_ to read instead. It is named in every bill's `derived` so triage can correct it, and pinned by a test so it stays a known approximation rather than an accident. - `billingIdentifier` is populated from the source's meter number (`Meter no. (if known)`) for a metered bill — without it, two meters at one site billed in the same period would collide onto the same `reference`. It is never synthesised when there's no meter number, and it is never ALSO emitted as `supplierReference`: a meter number identifies a bill, not the supplier's reference for the customer, and reusing it forged one Partner per meter downstream (`streamingUpserts`' `addSupplier` upserts Partners on `reference`), turning one supplier with 12 meters into 12 suppliers. This extractor emits no `supplierReference` at all, so the projector falls back to `supplierName`. When that absence causes two OTHER rows (no meter on either side) to collide onto an identical `reference` anyway, **both are parked, not emitted** — never silently merged by Task 11's commit-time upsert. Parked with `missing: ["billingIdentifier"]` and a `note` explaining the collision; never resolved by synthesising an identifier (the ruling against that stands). - `reference` (`siteId|type|billingDate|billingIdentifier`) is stamped onto every emitted bill so re-committing the same workbook updates existing records instead of duplicating them. - Every emitted bill carries a `derived` list naming which of its own fields were computed or sheet-defaulted rather than read for that specific row — `periodStart`/`billingDate` (always, on bill-table sheets), and `type`/`unit` (only when a sheet-level fallback was used for that row, not when a real column supplied the value). `derived` is NOT part of the `FuelsAndElectricityBill` schema (the resource type sets `additionalProperties: false`) — Task 11's commit skill must strip it before calling `qb.commit_ingest`. See SKILL.md. - `_json_safe` (used when building a parked row's raw-cell dump) is total — it handles `datetime`/`date`/`time`/`timedelta`/`Decimal`/`bytes` explicitly and falls back to `str(value)` for anything else, rather than letting an unrecognised type reach `json.dumps`. An Excel time-formatted column (e.g. a cosmetic "Read time" field) previously crashed the whole run at the very end (`main()`'s `json.dumps(result)`) with no bills.json written at all, even though every row had parked correctly up to that point — losing an entire extraction over one unrelated column. - A fully blank row inside a bill table (trailing padding, common in real workbooks) is skipped rather than parked as a spurious issue. - Fixtures under `lib/testdata/` (`duk.xlsx`, `odysea.xlsx`) reproduce the shapes of the two real source workbooks without their data, since the originals were not available to construct this skill. Additional review-round edge cases (unit-column handling, unrecognised/non-tabular sheets, UK/ISO dates, colliding meters and meterless duplicates, blank padding rows, unreadable sheets, misplaced header rows vs. unfilled templates, time-formatted cells) are covered by small workbooks built on the fly in `lib/test_extract.py` rather than further committed fixtures. - Behaviour note: always looking up a sheet's own Fuel Type/Unit column (rather than only when there's no sheet-level fallback) means an `Electricity` sheet with a near-miss `Type` column — e.g. literally `"Electricity"` rather than the closed enum's `"Electricity Grid"` — now parks those rows (unrecognised type, with `typeCandidates`) instead of silently emitting them under the `Electricity Grid` fallback as earlier drafts of this skill did. This is the correct direction (a real column saying something different from the fallback must never be silently overridden) but is a visible behaviour change worth calling out explicitly, since it means workbooks with loosely-worded Type columns will see more parked rows than a purely sheet-name-driven default would have produced. 9/22/2026
• v1 # Changelog ## v1 - Initial release: `lib/extract.py` conforms rows from a client's fuels-and-electricity activity workbook to the `FuelsAndElectricityBill` contract. Required fields (`siteId`, `type`, `billingDate`, `periodStart`, `periodEnd`, `quantityType`, `unit`, `totalValue`) are never defaulted — a row missing one is parked in `issues` with a `missing` list and the `parked_row` found so far. Optional fields (`spendAmount`, `spendCurrency`, `billingIdentifier`, `supplierName`, `supplierReference`, `percentageApportioned`, `percentageRenewable`) are omitted, never guessed, when absent from the source. - `lib/vocabulary.py` mirrors `FUEL_TYPES`/`UNITS` verbatim from `fuelsAndElectricityBill.ts` (the authoritative TypeScript contract) and provides `nearest_candidates` for triage suggestions — a source value outside either closed enum is parked with `typeCandidates`/ `unitCandidates` (kept separate so a human can tell which field a suggestion is for), never coerced. - **Unit/type are never assumed except as a documented fallback, and a real source column always wins over it.** Only `Electricity` → `kWh`/ `Electricity Grid` and `Natural gas` → `Natural Gas` are sheet-level fallbacks (the first is brief-mandated; both are used only when the sheet has no Fuel Type/Unit column of its own, or a row's cell in it is blank). Every sheet — including `Electricity` — always looks for its own Fuel Type/Unit column first; if present and populated, that value is used even when it disagrees with the fallback (e.g. an `Electricity` sheet genuinely metered in m³ emits `m³`, not `kWh` — contradicting a source fact would be the same ~11x corruption risk as inventing one). When a fallback value IS used for a given row, `type`/`unit` are listed in that bill's `derived`. - **A populated sheet never silently vanishes, and an unrecognised sheet never floods `issues` either.** A sheet with a **strong anchor column** (Period End or Consumption specifically - a Unit or Fuel Type match alone is not enough, since a lookup/reference tab plausibly has one of those without being a bill table) parks each incomplete row individually, with `parked_row` carrying the row's raw, non-blank source cells (keyed by their original headers, coerced through a total `_json_safe` - see below) alongside the fields this module identified — the LLM triage step needs the actual values to reason from. A sheet with no strong anchor at all (a guidance/notes/lookup tab) parks as exactly ONE sheet-level issue (`"row": null`, naming the sheet and the columns actually found) instead of one per row — a workbook mixing a real data tab with a guidance tab and an emission-factor lookup tab headed `Fuel | Unit | Factor` (a coincidental Unit-column match, without the earlier strong-anchor requirement) previously produced 19+ near-identical issues that buried the genuine ones. A sheet that has real cell content but yields zero rows at this module's assumed header row (e.g. exactly enough real rows that the header consumes the last one) also surfaces as one issue — UNLESS the header row it read already contains a strong anchor, in which case the sheet is a genuinely unfilled template (headers present, no data entered yet) and correctly yields nothing rather than a misdiagnosed "header may be on a different row" issue. - A sheet that raises while being read as a table (corrupt region, unexpected format) surfaces as a single issue (`missing: ["sheet-unreadable"]`) instead of silently contributing nothing. - Text dates are read UK day-first (`"03/04/2025"` → 3 April) **except** when the string is ISO-leading (`"2025-03-04"`, `"2025/03/04"`, `"2025.03.04"`) — the most common machine-written format — which is read in its own unambiguous year-month-day order instead; blanket day-first parsing would silently swap month/day on an ISO date. pandas' own ambiguity warning (e.g. for `"04/13/2025"`, where day-first would need a nonexistent 13th month) is captured via `warnings.catch_warnings(record= True)` and never silenced — if it fires, the row parks rather than guessing. (`simplefilter("error", ...)` does NOT work for this specific warning: pandas raises it from Cython internals where turning it into an exception doesn't propagate normally - it becomes an unraisable exception printed to stderr, and the guessed value is returned regardless. Fixed to use `record=True`, which doesn't depend on exception propagation.) - **A date cell that does not name a full DAY parks rather than resolving to one.** `"2025"`, `"Mar-25"`, `"Mar 2025"`, `"Jan"`, `"2025-03"` are all parked. A date parser asked for a date always returns a whole one, filling in whatever the text omits — `"2025"` silently became 2025-01-01 and `"Mar-25"` became 0001-03-25, each emitted as a COMPLETE, committable bill with zero issues raised. The `"2025"` case is the dangerous one: it does not look bogus, and it asserts a whole year's consumption as a single day on 1 January. Completeness is detected by parsing the text against two defaults that differ in year, month AND day: only a text that pins all three itself resolves identically both times. Every format that does name a full day is unaffected (`31 December 2025`, `Dec 31, 2025`, `31-Dec-2025`, `3 April 2025`, `1/2/2025`, `31/12/25`, `03/04/2025`, `2025-03-04`, `2025.03.04`, `2025-03-04 00:00:00`). - **A bill's site is resolved per ROW, from the source's own `Site` column.** `extract_bills(..., site_ids_by_name=...)` / `--sites` takes a mapping of the workbook's own site names to site resource ids, which the agent builds by querying the `site` resource type BEFORE extracting. A row whose `Site` cell matches nothing in that mapping is **parked** with `missing: ["siteId"]`, a `siteCandidates` list and an explanatory `note` — never stamped with a default id. Previously a single `site_id` argument was written onto every row while the row's own site text reached only `parked_row`, so a three-site sheet came back as three bills on one site with zero issues: this blueprint's design-spec failure mode 2 ("every Odysea bill attributed to DUK's site") reproduced inside the code meant to prevent it. `site_id` / `--site-id` remains the explicit answer for rows that name no site at all (a sheet with no `Site` column, e.g. DUK's annual totals) and never overrides a site a row names. Name matching normalises case and whitespace only — nothing fuzzier, since a near-match would be a guess about which site a bill belongs to. - The CLI takes flags rather than positional ids: `python lib/extract.py <workbook> [--sites <json-or-path>] [--site-id <id>] [--sheets a,b,c]`. At least one of `--sites`/`--site-id` is required. - **`periodStart`'s calendar-month convention is a known, disclosed approximation.** It is applied to every row with a `Period End`, including a row that is really quarterly or annual, so such a row gets a one-month period — and that period is what `Integrations.Process.SiteEmissions` resolves emissions factors against. Neither recognised source shape records a period _start_ to read instead. It is named in every bill's `derived` so triage can correct it, and pinned by a test so it stays a known approximation rather than an accident. - `billingIdentifier` is populated from the source's meter number (`Meter no. (if known)`) for a metered bill — without it, two meters at one site billed in the same period would collide onto the same `reference`. It is never synthesised when there's no meter number, and it is never ALSO emitted as `supplierReference`: a meter number identifies a bill, not the supplier's reference for the customer, and reusing it forged one Partner per meter downstream (`streamingUpserts`' `addSupplier` upserts Partners on `reference`), turning one supplier with 12 meters into 12 suppliers. This extractor emits no `supplierReference` at all, so the projector falls back to `supplierName`. When that absence causes two OTHER rows (no meter on either side) to collide onto an identical `reference` anyway, **both are parked, not emitted** — never silently merged by Task 11's commit-time upsert. Parked with `missing: ["billingIdentifier"]` and a `note` explaining the collision; never resolved by synthesising an identifier (the ruling against that stands). - `reference` (`siteId|type|billingDate|billingIdentifier`) is stamped onto every emitted bill so re-committing the same workbook updates existing records instead of duplicating them. - Every emitted bill carries a `derived` list naming which of its own fields were computed or sheet-defaulted rather than read for that specific row — `periodStart`/`billingDate` (always, on bill-table sheets), and `type`/`unit` (only when a sheet-level fallback was used for that row, not when a real column supplied the value). `derived` is NOT part of the `FuelsAndElectricityBill` schema (the resource type sets `additionalProperties: false`) — Task 11's commit skill must strip it before calling `qb.commit_ingest`. See SKILL.md. - `_json_safe` (used when building a parked row's raw-cell dump) is total — it handles `datetime`/`date`/`time`/`timedelta`/`Decimal`/`bytes` explicitly and falls back to `str(value)` for anything else, rather than letting an unrecognised type reach `json.dumps`. An Excel time-formatted column (e.g. a cosmetic "Read time" field) previously crashed the whole run at the very end (`main()`'s `json.dumps(result)`) with no bills.json written at all, even though every row had parked correctly up to that point — losing an entire extraction over one unrelated column. - A fully blank row inside a bill table (trailing padding, common in real workbooks) is skipped rather than parked as a spurious issue. - Fixtures under `lib/testdata/` (`duk.xlsx`, `odysea.xlsx`) reproduce the shapes of the two real source workbooks without their data, since the originals were not available to construct this skill. Additional review-round edge cases (unit-column handling, unrecognised/non-tabular sheets, UK/ISO dates, colliding meters and meterless duplicates, blank padding rows, unreadable sheets, misplaced header rows vs. unfilled templates, time-formatted cells) are covered by small workbooks built on the fly in `lib/test_extract.py` rather than further committed fixtures. - Behaviour note: always looking up a sheet's own Fuel Type/Unit column (rather than only when there's no sheet-level fallback) means an `Electricity` sheet with a near-miss `Type` column — e.g. literally `"Electricity"` rather than the closed enum's `"Electricity Grid"` — now parks those rows (unrecognised type, with `typeCandidates`) instead of silently emitting them under the `Electricity Grid` fallback as earlier drafts of this skill did. This is the correct direction (a real column saying something different from the fallback must never be silently overridden) but is a visible behaviour change worth calling out explicitly, since it means workbooks with loosely-worded Type columns will see more parked rows than a purely sheet-name-driven default would have produced. 8/11/2026