Building Client-Side Multi-Page Bank Statement Reconciliation & BIFF8 .XLS Export in Vanilla JS
Converting financial documents directly in the browser removes a huge privacy bottleneck for bookkeepers and developers—sensitive bank statements and expense receipts never need to touch a third-party server unless the u
Converting financial documents directly in the browser removes a huge privacy bottleneck for bookkeepers and developers—sensitive bank statements and expense receipts never need to touch a third-party server unless the user explicitly opts into cloud extraction. While building JSONtoSheet, we ran into three stubborn engineering problems that most naive PDF-to-spreadsheet scripts miss: multi-page coordinate drift, floating-point balance drift, and legacy accounting software compatibility.
Here is how we solved each one in vanilla JavaScript.
1. Digital PDF Text Coordinates vs. Multi-Page Canvas Scaling
Bank statements generally arrive in two very different forms:
-
Digital-native PDFs containing selectable text runs with exact
(x, y)transform matrices. - Scanned or flattened PDFs where each page is a raster image that needs OCR or vision extraction.
For digital PDFs, reading raw text strings in stream order breaks immediately on multi-column layouts. Instead, we group text items by baseline y coordinate within a tolerance band (typically 2.5pt–3.5pt) and sort each row left-to-right by x. Column boundaries—Date, Description, Debit, Credit, Running Balance—are inferred from header anchor positions on page 1 and carried across subsequent pages while ignoring repeating page headers and footer disclaimers.
For scanned multi-page statements, the trap is naive canvas downscaling. Many browser scripts cap page height or aggressively shrink DPI to stay under memory limits, which blurs or crops the bottom summary block where the ending balance lives. Instead, we use a progressive canvas scale loop: render each page at an initial target scale (e.g., 2.0x for 144+ DPI legibility), serialize to JPEG at step-down quality if a per-page byte budget is exceeded, and preserve the full page height so bottom totals and carryover lines are never truncated. You can see representative multi-page edge cases in our PDF bank statement to Excel workflow and the public json-to-excel-test-fixtures repository.
2. Integer-Cent Arithmetic for Balance Reconciliation
A table of extracted transactions is only trustworthy if the math reconciles. Unfortunately, IEEE-754 floating-point math produces subtle drift across hundreds of rows (0.1 + 0.2 !== 0.3), causing false reconciliation mismatches.
Before running any ledger check, every parsed currency string (accounting for parentheses like (142.50) or trailing CR/DR markers) is normalized into integer cents:
function parseToCents(raw) {
if (!raw) return 0;
const trimmed = String(raw).trim();
const isNegative = /^\(.*\)$/.test(trimmed) || /^-/.test(trimmed) || /DR$/i.test(trimmed);
const digitsOnly = trimmed.replace(/[^0-9.]/g, '');
if (!digitsOnly) return 0;
const cents = Math.round(parseFloat(digitsOnly) * 100);
return isNegative ? -cents : cents;
}
function reconcileStatement(openingCents, rows, expectedEndingCents) {
let runningCents = openingCents;
for (const row of rows) {
runningCents += row.creditCents - row.debitCents;
row.computedBalanceCents = runningCents;
}
return {
computedEndingCents: runningCents,
deltaCents: expectedEndingCents - runningCents,
reconciled: runningCents === expectedEndingCents,
};
}
Using integer cents guarantees exact arithmetic across 50+ page statements. When deltaCents !== 0, the UI immediately flags the exact row where computedBalanceCents diverges from the statement's printed running balance. The same integer-cent check powers subtotal verification in our receipt to Excel parser for thermal expense slips.
3. Generating Legacy BIFF8 .xls Alongside .xlsx in Memory
Modern SpreadsheetML (.xlsx) is straightforward to build in browser memory as a ZIP archive of XML parts. However, many legacy ERP importers, older QuickBooks Desktop bridges, and regional tax portals still expect binary BIFF8 .xls wrapped in an OLE2 Compound File Binary container.
To support both without server round-trips, our client-side exporter shares one normalized workbook model (sheets[], typed cells, column widths, and number formats like #,##0.00) and forks only at serialization:
-
.xlsxpath: emits shared strings, worksheet XML (t="n"for numeric cents divided by100, ISO dates, UTF-8 text), and minimal styles into an in-memory store-only ZIP. -
BIFF8
.xlspath: allocates a 512-byte sector-aligned OLE2 FAT table with aWorkbookstream containingBOF,CODEPAGE(UTF-16LE),FONT,XF,LABELSST/NUMBERrecords, andEOF.
Keeping all parsing, integer-cent reconciliation, and binary serialization in client-side JS means sensitive statements stay local while still producing audit-ready workbooks.
Originally published by Dev.to WebDev. Aggregated on AIWithGhost for educational purposes — full credit and traffic to the original publisher.