Utility Bill Data Extraction: Fields, Meters and Periods

Utility bill data extraction means pulling the fields a bookkeeper needs off each bill: provider, account number, service address, service period, statement date, due date, current charges, previous balance and amount due. Post current charges, not amount due, split service periods that cross month-end, and allocate multi-meter bills by location before the data reaches the books.

Which fields matter in utility bill data extraction?

The fields that matter in utility bill data extraction are the ones that identify the account, date the service and give the charge for that service. That is nine fields: provider, account number, service address, service period, statement date, due date, current charges, previous balance and amount due. Usage figures and meter numbers come next when a firm needs them.

Utility bills look alike from one provider to the next, but the labels vary. One bill says "Billing period", another says "Service from" and "Service to", a third prints only "Read dates". The job is to map each label to the same nine fields every time.

The sample below is a fictional electric bill for a fictional client, Brightwater Bakery LLC, from a fictional provider, Northgate Electric Cooperative. Every value is an example.

On this sample, current charges and amount due match, because the last bill was paid in full. That will not always be true, and the difference is the most common extraction error, covered two sections down.

A utility bill is an invoice: it asks for payment for service already supplied. The general rules for reading invoice header fields, and the validation checks that catch misreads, are covered in validation rules for extracted invoice data. Two of those checks are worth repeating in utility form here: the service period end date should fall on or before the statement date, and the due date should fall after it.

Layout of the extracted rows matters once there are many bills. A sheet with one row per bill works for single-meter accounts, and one row per meter works for the multi-meter case below. The trade-off is laid out in one row per invoice or one row per line.

Utility bill service periods that cross month-end

Utility bill service periods rarely line up with calendar months. Meters are read on a cycle, so a bill can cover March 18 to April 16. When the books close monthly, a bookkeeper either splits the charge between the two months by days of service or records it in one month and accrues the missing piece at month-end.

Worked split: March 18 to April 16

The Brightwater sample bill covers 30 days of service and $412.80 of current charges (example values). A day-based split works like this:

1. Count the days in each month. March 18 to March 31 is 14 days. April 1 to April 16 is 16 days. Together they make the 30 days printed on the bill.
2. Work out the daily rate: $412.80 divided by 30 days is $13.76 a day.
3. Multiply: March gets 14 days at $13.76, which is $192.64. April gets 16 days at $13.76, which is $220.16.
4. Check the sum: $192.64 plus $220.16 is $412.80, the bill's current charges.

Use the day count printed on the bill when there is one. Providers count read dates differently, and some bills treat the first or last date as a read day rather than a service day. If the bill says 29 days, split over 29.

A day-based split assumes even usage across the period. It ignores the fixed monthly customer charge, which some firms prefer to assign to one month. For most bookkeeping clients the difference is small, but the method should be the same every month.

Accruing instead of splitting

The other approach waits for the bill. At the end of March, the March 18 to 31 service has been used but not yet billed. The bookkeeper accrues an estimate, often based on the prior bill's daily rate, and reverses it when the April bill arrives. Under accrual accounting this keeps March's expense in March.

The tax timing of an expense is a separate question from monthly bookkeeping. IRS Publication 538 (revised January 2022) says that under the accrual method, when an expense is for services provided to you or your use of property, "economic performance occurs as the property or services are provided or the property is used." Under the cash method it says expenses are generally deducted "in the tax year in which you pay the expenses." Which method applies to a client, and how a year-end bill is treated, is the preparer's call.

Current charges vs amount due on a utility bill: which number to post?

Current charges are the number to post as the utility expense for a service period, not amount due. Amount due is what the customer owes today. It folds in any unpaid previous balance, late fees, credits and payments received. Posting amount due counts last month's unpaid bill twice and books fees as if they were usage.

Here is the Brightwater sample again, this time on a month when the March bill went unpaid (example values):

If a bookkeeper posts $806.77 as April's electricity, the $388.15 that was already recorded from the earlier bill lands in the books a second time. The $5.82 late charge also gets mixed into usage cost, when many charts of accounts keep fees in their own account.

The safe rule is to extract the lines, not just the bottom number. Previous balance, payments, adjustments, late charges and current charges should each come off the bill as their own field. The check is arithmetic: previous balance, minus payments received, plus fees and adjustments, plus current charges, should equal amount due. On the sample, $388.15 minus $0.00, plus $5.82, plus $412.80, gives $806.77. A row that does not foot points to a misread field.

Payment matching follows the same split. The payment that clears this bill may be $806.77 in one go. Against the books, that payment settles two bills and a fee. A receipt vs invoice comparison explains why the bill and the payment record are separate documents that prove different things.

The same bill often reaches a firm twice: once as the emailed PDF and once as a download from the provider's site. Account number, statement date and amount due are the fields that identify a repeat, as described in which invoice fields identify a duplicate invoice.

Multi-meter and multi-location utility bill accounts

A multi-meter utility bill account covers several meters or sites on one statement, with one account number and one amount due. Each meter or site usually gets its own block of usage and charges. A bookkeeper extracts one row per meter, assigns each to its location or rental unit, and allocates any account-level charges by a fixed basis.

Commercial clients, landlords and farms are the usual cases. A client with a shop and a warehouse may get one electric bill with two service addresses. A landlord with a fourplex may get one water bill for a master meter, or one electric bill with a house meter for common areas and separate meters per unit.

Here is a fictional three-meter bill for a fictional landlord, Okafor Properties LLC, covering one building (example values):

In this example the account-level charge is split evenly across meters. Splitting by usage is the other common basis. Pick one and use it every month, and note it where the next bookkeeper will see it.

Meter blocks can also cross pages. A long bill may list meter one on page two and meter three on page four, with a summary on page one. Extract from the meter pages, then check that the meter totals add up to the summary's current charges. A missing page shows up as a gap.

Tenant-paid meters need a flag. If a tenant pays a unit's meter directly, that meter may still appear on the landlord's account summary at zero, or not at all. Confirm with the client which meters are theirs before posting.

Utility bill usage data: kWh, therms and gallons

Utility bill usage data is the quantity of service used in the period, printed next to the dollar charges. Electric bills give kilowatthours, natural gas bills give therms or cubic feet, and heating oil and propane deliveries give gallons. A firm needs these figures when it allocates cost by usage, compares periods, or reports on energy use.

The units are set by the fuel. The U.S. Energy Information Administration says "Electric utilities and power plants generally measure electricity in kilowatthours (kWh)." Its page on British thermal units explains that "heating oil is measured in gallons and natural gas is measured in cubic feet," and gives conversion factors including "1 therm=100,000 Btu" and "1 kilowatthour=3,412 Btu."

For extraction, three habits keep usage data clean:

• Store the unit with the number. "3,096" means nothing without "kWh". A gas bill in hundreds of cubic feet and one in therms are not comparable until converted.
• Keep usage separate from charges. Usage goes in its own column, not in the description field, so it can be added and compared.
• Match usage to the same service period as the dollars. If a bill covers 30 days and the next covers 33, compare daily averages rather than bill totals.

Most bookkeeping engagements need only the dollars. Usage matters for the multi-meter allocations above, for clients who rebill tenants, and for engagements that ask for energy figures. Ask before extracting it from every bill.

Utility bill extraction mistakes: estimated reads, budget billing and deposits

Utility bill extraction mistakes often come from bills where the amount does not reflect actual usage or expense. Three cases cause most of them: estimated meter reads, budget billing, and deposits. In each, the bill shows a real number that misleads if it is posted as the period's usage cost without a second look.

Estimated reads. When a meter is not read, the provider estimates. Pennsylvania's utility regulator, in its consumer guide Your Rights & Responsibilities (written for residential customers in that state), says an estimate "is based on your previous usage through previous meter readings and may be adjusted for existing weather conditions." It adds that after several estimated months, "your actual bill may be increased or decreased according to the usage indicated by the actual meter reading." For the books, that means a large catch-up bill can follow a run of estimates. Extract the read type and flag estimated bills.

Budget billing. Under budget billing, the same guide says, the customer has bills "averaged throughout the year," and "You will be billed for approximately the same amount each month even though your usage may vary from season to season." At the end of the budget year there may be a settle-up amount. A budget bill shows the averaged amount due, and many also show the actual charges for the period. Extract both when they are printed, and confirm with the client which figure the books should carry.

Deposits. A new account may carry a security deposit on the first bill. The Pennsylvania guide says a utility may hold a deposit until a payment history is established and "must refund a deposit, along with interest earned" once the customer is no longer subject to it. A refundable deposit is money held, not a charge for service, so it should not be extracted as usage cost. When it comes back, it often appears as a credit on a later bill.

Other lines deserve the same care. One-time connection fees, equipment charges, and credits for overpayment all sit inside current charges or next to it. Extract them as separate lines if the bill shows them separately.

Lost utility bills are among the easier records to replace, since providers keep past statements in the customer's online account. The wider process for rebuilding missing expense records is covered in lost receipts for business expenses.

Utility bills are not among the document types listed on the Schema CPA homepage, so the field checks in this post are ones the firm runs on its own extraction.