Every audit starts with the same tedium: two years of statements, one line at a time, into a register. I stopped doing that by hand about three years ago and the workflow has held up.
The key realization is that most utility PDFs are text-layer documents, not scans. If you can select the text in a PDF reader, you can extract it programmatically. pdftotext with the -layout flag preserves the column positions, which matters enormously — it turns the bill into something with predictable structure rather than a wall of words.
From there it is pattern matching. Each utility prints its determinants in a consistent format, so once you have written the extraction rules for one Con Edison EL9 statement you have written them for all of them. The rate lines, the meter reads, the multiplier, the demand figure, the tax lines all come out in the same place every cycle.
Two cautions. First, always reconcile the extracted components back to the printed total on every single statement before you trust any of it. If your parsed line items do not sum to the Current Charges figure the utility printed, your extraction is wrong and everything downstream is wrong with it. Second, scanned statements need OCR and OCR makes errors in exactly the place you care about, which is digits.
What used to take me a day now takes about twenty minutes, and the reconciliation check is stronger than anything I did by hand.
The key realization is that most utility PDFs are text-layer documents, not scans. If you can select the text in a PDF reader, you can extract it programmatically. pdftotext with the -layout flag preserves the column positions, which matters enormously — it turns the bill into something with predictable structure rather than a wall of words.
From there it is pattern matching. Each utility prints its determinants in a consistent format, so once you have written the extraction rules for one Con Edison EL9 statement you have written them for all of them. The rate lines, the meter reads, the multiplier, the demand figure, the tax lines all come out in the same place every cycle.
Two cautions. First, always reconcile the extracted components back to the printed total on every single statement before you trust any of it. If your parsed line items do not sum to the Current Charges figure the utility printed, your extraction is wrong and everything downstream is wrong with it. Second, scanned statements need OCR and OCR makes errors in exactly the place you care about, which is digits.
What used to take me a day now takes about twenty minutes, and the reconciliation check is stronger than anything I did by hand.