Utility bill data validation: reporting months, duplicates, credits and HST

A utility bill looks like a fact. It is closer to a claim: the utility's statement of what a meter did and what is owed, produced by a billing system with its own calendar, estimates, adjustments and corrections. Most of the time the claim is right. When it is wrong, or merely ambiguous, and it is loaded into a benchmark without checking, the error compounds into intensities, emissions, M&V baselines and regulatory filings.

This article lists the validation checks VE-MAP applies to every bill and Green Button record, in the order they run, with the reason for each.

Check 1: did the bill read correctly?

Bill PDFs are read with layout-specific rules where the text layer allows, and with image reading where it does not. The extracted line items are reconciled to the printed total; a bill whose parts do not add up to its total is flagged for review rather than trusted. Multi-meter invoices are split per meter. Consumption is stored as the metered quantity, not an adjusted or loss-factored figure, so it matches what Green Button reports for the same meter.

Check 2: is it a duplicate?

The same bill arrives twice more often than anyone admits: uploaded from two mailboxes, re-issued by the utility, or scanned and also downloaded. The platform hashes each document and flags copies within the same commodity. Re-issued bills for the same period are shown side by side so the reviewer chooses which one supersedes.

Check 3: does it match the meter?

Where a Green Button record exists for the same account, bill and meter data are compared over their common date window, bill by bill, and the difference in quantity and in cost is reported. Small differences are normal (different rounding, different period edges). Large quantity differences point to a parsing error or a wrong meter; large cost-only differences usually mean the bill includes arrears or a credit the meter record does not, which is the next check.

Check 4: credits, arrears and estimated reads

• Credits. A bill can show a negative total after a deposit refund, a rate correction or a one-time adjustment. Treated naively, a credit month shows real consumption at negative cost. The platform stores the signed amount, separates current charges from balance forward, and, where the total is negative with real usage, estimates the period cost from the meter's typical rate and marks it as estimated.
• Arrears and payments. Amount due includes prior balances and payments received. Current charges are what the month cost. Reports use current charges and keep amount due for reference.
• Estimated reads. An estimated bill followed by a true-up creates a false saving then a false loss. Reading type is stored per bill and printed in the M&V table so the reviewer can discount the pair. See weather normalization, CUSUM and RETScreen M&V.
• Multi-month catch-up bills. One invoice covering several months is split into its printed monthly rows when the sub-totals reconcile to the invoice, otherwise prorated by days.

Check 5: which month is it?

Meters are read on cycles, not on calendar months, so a bill for May 14 to June 11 is neither a May bill nor a June bill by nature. Two conventions are common: label by end date (it becomes June) or by midpoint (it becomes May). Both produce surprises. The platform uses one explicit rule for every source:

1. If the billing period starts before the 15th, the bill belongs to that month.
2. If it starts on or after the 15th, it belongs to the next month.
3. An invoice longer than 45 days is prorated by days across the months it covers.
4. The same rule labels Green Button billing periods and RETScreen imports, so the Overview, the Bills Manager, the monthly review and the M&V charts all agree.

Check 6: is HST in or out?

Ontario bills carry HST at 13 percent, and whether it is a real cost depends on the owner. A GST/HST registrant operating a commercial building generally recovers it through input tax credits; a residential condominium corporation or a landlord of long-term residential rentals generally does not, because residential rent is exempt. The right treatment is therefore a portfolio setting, not a global one. The platform stores the HST line separately for every bill and Green Button record and applies a per-portfolio toggle at read time: with HST included, cost intensities reflect the cash cost; with HST excluded, they reflect the pre-tax cost. Because the toggle is applied when data is read, historical months follow it automatically.

Check 7: is anything missing?

Each building has an expected set of meters per commodity. When a reporting month has no record for a meter, a missing-bills banner names the building, commodity and month. Missing months are the most common reason an ENERGY STAR score is blank and an EWRB filing, due July 1 in Ontario, is questioned, as discussed in Ontario EWRB reporting. Green Button records, where the utility provides them under the standard the Green Button Alliance maintains, fill many of these gaps automatically, because the feed does not depend on a PDF being forwarded.

Fixing, with a trail

A flagged bill is fixed once, in a dialog that pre-fills the extracted values, and the correction is marked and noted. The original document stays attached. Bulk corrections that follow a discovered parsing rule are applied by the platform with a record of what changed. The point is that a number in a board report can be traced to a bill, and a corrected number can be traced to the person and reason.

FAQ

Is this the same as checking the bill against the tariff?

No. Tariff validation recomputes the charges from the published rates and flags overcharges. The checks above make the bill usable as data. They are complementary; the data checks come first.

Why not just use Green Button and ignore the PDFs?

Green Button covers electricity and gas where the utility has implemented it, and its cost figures are the utility's statement values, which can include arrears. Water, district energy and propane arrive only as bills. And the PDF is the document your auditor will ask for.

Can we change the reporting month rule?

The rule is applied portfolio-wide so every view agrees. If your organization reports on a different convention, tell us on the contact page; the data model stores both period dates, so the label is a presentation choice. The overall data path is described in energy data management for building portfolios.