Insights · Water
Moving your water billing off Excel without losing a month
How to prepare accounts, tariffs and meter readings, run a trial billing calculation, and reconcile balances before moving a water scheme from Excel to BillGrid.
Joseph Gitonga · 4 min read
On this page 7 sections
Moving water billing off Excel starts with a checked customer list, agreed balances, correct tariffs and a reliable starting reading for every meter. Test the billing calculation before sending customers their first live bills.
This guide separates a trial calculation from the live cutover. That distinction matters: a trial checks whether the new bills are right; it does not keep opening balances synchronized with your spreadsheet.
Choose the live cutover date first
Choose a date just after a billing cycle closes. Decide which bills and payments remain in the old records, and which will be handled in BillGrid. Keep the old spreadsheet as a read-only archive after the switch.
For a trial, arrange the setup with the BillGrid team before importing. If trial accounts already exist, agree how their balances and readings will be brought up to the live cutover date. Do not create duplicate accounts or assume another upload will replace their balances.
Clean the records you need
| Record | What to check |
|---|---|
| Customer | Name, contact details and which connections belong to them |
| Account | Existing payment reference, service address, zone and route |
| Meter | Physical serial number, units and the date of the starting reading |
| Balance | What is owed or held as credit at the cutover date |
| Deposit | Amount actually held, recorded separately from arrears |
| Tariff | Applicable rate, category, standing charge and authorized levies |
Use one row per metered account. If a customer has two meters, keep those accounts distinguishable. Check shared phone numbers and similar names rather than treating a phone number as proof that two records belong to the same person.
If zones live in cell colours or tab names, give them explicit names. Check that the meter number in the spreadsheet matches the physical meter.
Prepare the import with a checked balance
Download the sample file from Accounts, under Actions, and use its headings. Review the preview before importing, then read the import report for rows that need correction.
Keep existing account numbers where customers already use them to pay. For opening balances, a positive amount means money owed and a negative amount means credit. Have the responsible person sign off the total and its effective date.
Re-importing an existing account does not replace its opening balance. If bills, payments or credits change during the trial, reconcile those movements before going live. Agree any required correction with the BillGrid team and your bookkeeper, then check the statement for every affected account.
Handle existing deposits separately
List the deposits already held and agree how to bring them across before importing accounts. Check any configured deposit charges so existing customers are not charged again. Historical payments must be recorded only once; have the bookkeeper review the opening figures and deposit records together.
Set up tariffs and starting readings
Create the tariffs that apply to your accounts. Check low, typical and high consumption examples, including standing charges, minimum bills and levies. An account-specific tariff may differ from the category default, so review exceptions.
For each meter, confirm the previous reading, its date and the first current reading. Use the current sample or capture sheet and its instructions. Check meters that have been replaced, rolled over or changed units individually.
Do not proceed with a missing or uncertain starting reading. Check unusually large and small consumption figures before they reach a bill.
Run a parallel calculation
Keep Excel as the source of live bills during the trial. Calculate the same period in BillGrid without posting or sending a second set of bills.
- Calculate the trial run and review Needs attention.
- Compare the total with Excel, then check individual accounts across usage levels and customer categories.
- Investigate every unexplained difference, including rounding and tariff choices.
- Void the unposted trial run when the comparison is complete.
Keep a record of which readings and balances belong to the trial. Voiding a trial run does not refresh opening balances or reset the account's reading history.
Reconcile before going live
| Stage | What to confirm |
|---|---|
| Before cutover | Customers know the bill format and payment instructions |
| Cutover date | Final spreadsheet balances are agreed and subsequent movements have an owner |
| Before live billing | Trial-to-live balances, deposits and starting readings are reconciled |
| Billing day | The correct period is calculated, exceptions are reviewed and approved bills are posted |
| Payment review | Receipts are recorded once, including payments for pre-cutover debts |
Keep a list of unresolved differences with a person responsible for each. Do not sign off the cutover merely because the overall totals are close.
Tell customers what changes
A short notice should state the effective date, how bills will arrive and whether payment references change. Confirm all instructions before sending it.
Illustrative notice. "From November, your water bill will come by SMS with a link to view and pay. Our paybill and your account number remain unchanged. Please contact the office if your opening balance differs from your records."
After the first cycle, compare billed charges, receipts, credits and outstanding balances again. See a month of water billing for the ongoing routine, or water billing to discuss your migration.