<?xml version="1.0" encoding="UTF-8"?>
<rss version="2.0" xmlns:atom="http://www.w3.org/2005/Atom" xmlns:dc="http://purl.org/dc/elements/1.1/">
  <channel>
    <title>DEV Community: YuMir</title>
    <description>The latest articles on DEV Community by YuMir (@yumir1688).</description>
    <link>https://dev.to/yumir1688</link>
    <image>
      <url>https://media2.dev.to/dynamic/image/width=90,height=90,fit=cover,gravity=auto,format=auto/https:%2F%2Fdev-to-uploads.s3.us-east-2.amazonaws.com%2Fuploads%2Fuser%2Fprofile_image%2F4257619%2F831da484-f75f-4767-9537-c3c331a7abae.png</url>
      <title>DEV Community: YuMir</title>
      <link>https://dev.to/yumir1688</link>
    </image>
    <atom:link rel="self" type="application/rss+xml" href="https://dev.to/feed/yumir1688"/>
    <language>en</language>
    <item>
      <title>Building Client-Side Multi-Page Bank Statement Reconciliation &amp; BIFF8 .XLS Export in Vanilla JS</title>
      <dc:creator>YuMir</dc:creator>
      <pubDate>Sat, 21 Nov 2026 16:20:44 +0000</pubDate>
      <link>https://dev.to/yumir1688/building-client-side-multi-page-bank-statement-reconciliation-biff8-xls-export-in-vanilla-js-4008</link>
      <guid>https://dev.to/yumir1688/building-client-side-multi-page-bank-statement-reconciliation-biff8-xls-export-in-vanilla-js-4008</guid>
      <description>&lt;p&gt;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 &lt;a href="https://jsontosheet.com/" rel="noopener noreferrer"&gt;JSONtoSheet&lt;/a&gt;, 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.&lt;/p&gt;

&lt;p&gt;Here is how we solved each one in vanilla JavaScript.&lt;/p&gt;

&lt;h2&gt;
  
  
  1. Digital PDF Text Coordinates vs. Multi-Page Canvas Scaling
&lt;/h2&gt;

&lt;p&gt;Bank statements generally arrive in two very different forms:&lt;/p&gt;

&lt;ol&gt;
&lt;li&gt;
&lt;strong&gt;Digital-native PDFs&lt;/strong&gt; containing selectable text runs with exact &lt;code&gt;(x, y)&lt;/code&gt; transform matrices.&lt;/li&gt;
&lt;li&gt;
&lt;strong&gt;Scanned or flattened PDFs&lt;/strong&gt; where each page is a raster image that needs OCR or vision extraction.&lt;/li&gt;
&lt;/ol&gt;

&lt;p&gt;For digital PDFs, reading raw text strings in stream order breaks immediately on multi-column layouts. Instead, we group text items by baseline &lt;code&gt;y&lt;/code&gt; coordinate within a tolerance band (typically &lt;code&gt;2.5pt–3.5pt&lt;/code&gt;) and sort each row left-to-right by &lt;code&gt;x&lt;/code&gt;. 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.&lt;/p&gt;

&lt;p&gt;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 &lt;strong&gt;bottom summary block&lt;/strong&gt; where the ending balance lives. Instead, we use a &lt;strong&gt;progressive canvas scale loop&lt;/strong&gt;: render each page at an initial target scale (e.g., &lt;code&gt;2.0x&lt;/code&gt; 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 &lt;a href="https://jsontosheet.com/pdf-bank-statement-to-excel" rel="noopener noreferrer"&gt;PDF bank statement to Excel&lt;/a&gt; workflow and the public &lt;a href="https://github.com/YuMir1688/json-to-excel-test-fixtures" rel="noopener noreferrer"&gt;json-to-excel-test-fixtures&lt;/a&gt; repository.&lt;/p&gt;

&lt;h2&gt;
  
  
  2. Integer-Cent Arithmetic for Balance Reconciliation
&lt;/h2&gt;

&lt;p&gt;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 (&lt;code&gt;0.1 + 0.2 !== 0.3&lt;/code&gt;), causing false reconciliation mismatches.&lt;/p&gt;

&lt;p&gt;Before running any ledger check, every parsed currency string (accounting for parentheses like &lt;code&gt;(142.50)&lt;/code&gt; or trailing &lt;code&gt;CR&lt;/code&gt;/&lt;code&gt;DR&lt;/code&gt; markers) is normalized into &lt;strong&gt;integer cents&lt;/strong&gt;:&lt;br&gt;
&lt;/p&gt;

&lt;div class="highlight js-code-highlight"&gt;
&lt;pre class="highlight javascript"&gt;&lt;code&gt;&lt;span class="kd"&gt;function&lt;/span&gt; &lt;span class="nf"&gt;parseToCents&lt;/span&gt;&lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="n"&gt;raw&lt;/span&gt;&lt;span class="p"&gt;)&lt;/span&gt; &lt;span class="p"&gt;{&lt;/span&gt;
  &lt;span class="k"&gt;if&lt;/span&gt; &lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="o"&gt;!&lt;/span&gt;&lt;span class="n"&gt;raw&lt;/span&gt;&lt;span class="p"&gt;)&lt;/span&gt; &lt;span class="k"&gt;return&lt;/span&gt; &lt;span class="mi"&gt;0&lt;/span&gt;&lt;span class="p"&gt;;&lt;/span&gt;
  &lt;span class="kd"&gt;const&lt;/span&gt; &lt;span class="n"&gt;trimmed&lt;/span&gt; &lt;span class="o"&gt;=&lt;/span&gt; &lt;span class="nb"&gt;String&lt;/span&gt;&lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="n"&gt;raw&lt;/span&gt;&lt;span class="p"&gt;)&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="nf"&gt;trim&lt;/span&gt;&lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="p"&gt;)&lt;/span&gt;&lt;span class="p"&gt;;&lt;/span&gt;
  &lt;span class="kd"&gt;const&lt;/span&gt; &lt;span class="n"&gt;isNegative&lt;/span&gt; &lt;span class="o"&gt;=&lt;/span&gt; &lt;span class="o"&gt;/^&lt;/span&gt;\&lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="o"&gt;*&lt;/span&gt;\&lt;span class="p"&gt;)&lt;/span&gt;$&lt;span class="o"&gt;/&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="nf"&gt;test&lt;/span&gt;&lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="n"&gt;trimmed&lt;/span&gt;&lt;span class="p"&gt;)&lt;/span&gt; &lt;span class="o"&gt;||&lt;/span&gt; &lt;span class="o"&gt;/^-/&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="nf"&gt;test&lt;/span&gt;&lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="n"&gt;trimmed&lt;/span&gt;&lt;span class="p"&gt;)&lt;/span&gt; &lt;span class="o"&gt;||&lt;/span&gt; &lt;span class="o"&gt;/&lt;/span&gt;&lt;span class="no"&gt;DR&lt;/span&gt;$&lt;span class="o"&gt;/&lt;/span&gt;&lt;span class="n"&gt;i&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="nf"&gt;test&lt;/span&gt;&lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="n"&gt;trimmed&lt;/span&gt;&lt;span class="p"&gt;)&lt;/span&gt;&lt;span class="p"&gt;;&lt;/span&gt;
  &lt;span class="kd"&gt;const&lt;/span&gt; &lt;span class="n"&gt;digitsOnly&lt;/span&gt; &lt;span class="o"&gt;=&lt;/span&gt; &lt;span class="n"&gt;trimmed&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="nf"&gt;replace&lt;/span&gt;&lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="o"&gt;/&lt;/span&gt;&lt;span class="p"&gt;[&lt;/span&gt;&lt;span class="o"&gt;^&lt;/span&gt;&lt;span class="mi"&gt;0&lt;/span&gt;&lt;span class="o"&gt;-&lt;/span&gt;&lt;span class="mi"&gt;9&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="p"&gt;]&lt;/span&gt;&lt;span class="o"&gt;/&lt;/span&gt;&lt;span class="n"&gt;g&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt; &lt;span class="s1"&gt;''&lt;/span&gt;&lt;span class="p"&gt;)&lt;/span&gt;&lt;span class="p"&gt;;&lt;/span&gt;
  &lt;span class="k"&gt;if&lt;/span&gt; &lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="o"&gt;!&lt;/span&gt;&lt;span class="n"&gt;digitsOnly&lt;/span&gt;&lt;span class="p"&gt;)&lt;/span&gt; &lt;span class="k"&gt;return&lt;/span&gt; &lt;span class="mi"&gt;0&lt;/span&gt;&lt;span class="p"&gt;;&lt;/span&gt;
  &lt;span class="kd"&gt;const&lt;/span&gt; &lt;span class="n"&gt;cents&lt;/span&gt; &lt;span class="o"&gt;=&lt;/span&gt; &lt;span class="nb"&gt;Math&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="nf"&gt;round&lt;/span&gt;&lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="nb"&gt;parseFloat&lt;/span&gt;&lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="n"&gt;digitsOnly&lt;/span&gt;&lt;span class="p"&gt;)&lt;/span&gt; &lt;span class="o"&gt;*&lt;/span&gt; &lt;span class="mi"&gt;100&lt;/span&gt;&lt;span class="p"&gt;)&lt;/span&gt;&lt;span class="p"&gt;;&lt;/span&gt;
  &lt;span class="k"&gt;return&lt;/span&gt; &lt;span class="n"&gt;isNegative&lt;/span&gt; &lt;span class="o"&gt;?&lt;/span&gt; &lt;span class="o"&gt;-&lt;/span&gt;&lt;span class="n"&gt;cents&lt;/span&gt; &lt;span class="p"&gt;:&lt;/span&gt; &lt;span class="n"&gt;cents&lt;/span&gt;&lt;span class="p"&gt;;&lt;/span&gt;
&lt;span class="p"&gt;}&lt;/span&gt;

&lt;span class="kd"&gt;function&lt;/span&gt; &lt;span class="nf"&gt;reconcileStatement&lt;/span&gt;&lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="n"&gt;openingCents&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt; &lt;span class="n"&gt;rows&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt; &lt;span class="n"&gt;expectedEndingCents&lt;/span&gt;&lt;span class="p"&gt;)&lt;/span&gt; &lt;span class="p"&gt;{&lt;/span&gt;
  &lt;span class="kd"&gt;let&lt;/span&gt; &lt;span class="n"&gt;runningCents&lt;/span&gt; &lt;span class="o"&gt;=&lt;/span&gt; &lt;span class="n"&gt;openingCents&lt;/span&gt;&lt;span class="p"&gt;;&lt;/span&gt;
  &lt;span class="k"&gt;for&lt;/span&gt; &lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="kd"&gt;const&lt;/span&gt; &lt;span class="n"&gt;row&lt;/span&gt; &lt;span class="k"&gt;of&lt;/span&gt; &lt;span class="n"&gt;rows&lt;/span&gt;&lt;span class="p"&gt;)&lt;/span&gt; &lt;span class="p"&gt;{&lt;/span&gt;
    &lt;span class="n"&gt;runningCents&lt;/span&gt; &lt;span class="o"&gt;+=&lt;/span&gt; &lt;span class="n"&gt;row&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="n"&gt;creditCents&lt;/span&gt; &lt;span class="o"&gt;-&lt;/span&gt; &lt;span class="n"&gt;row&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="n"&gt;debitCents&lt;/span&gt;&lt;span class="p"&gt;;&lt;/span&gt;
    &lt;span class="n"&gt;row&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="n"&gt;computedBalanceCents&lt;/span&gt; &lt;span class="o"&gt;=&lt;/span&gt; &lt;span class="n"&gt;runningCents&lt;/span&gt;&lt;span class="p"&gt;;&lt;/span&gt;
  &lt;span class="p"&gt;}&lt;/span&gt;
  &lt;span class="k"&gt;return&lt;/span&gt; &lt;span class="p"&gt;{&lt;/span&gt;
    &lt;span class="n"&gt;computedEndingCents&lt;/span&gt;&lt;span class="p"&gt;:&lt;/span&gt; &lt;span class="n"&gt;runningCents&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt;
    &lt;span class="n"&gt;deltaCents&lt;/span&gt;&lt;span class="p"&gt;:&lt;/span&gt; &lt;span class="n"&gt;expectedEndingCents&lt;/span&gt; &lt;span class="o"&gt;-&lt;/span&gt; &lt;span class="n"&gt;runningCents&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt;
    &lt;span class="n"&gt;reconciled&lt;/span&gt;&lt;span class="p"&gt;:&lt;/span&gt; &lt;span class="n"&gt;runningCents&lt;/span&gt; &lt;span class="o"&gt;===&lt;/span&gt; &lt;span class="n"&gt;expectedEndingCents&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt;
  &lt;span class="p"&gt;}&lt;/span&gt;&lt;span class="p"&gt;;&lt;/span&gt;
&lt;span class="p"&gt;}&lt;/span&gt;
&lt;/code&gt;&lt;/pre&gt;

&lt;/div&gt;



&lt;p&gt;Using integer cents guarantees exact arithmetic across 50+ page statements. When &lt;code&gt;deltaCents !== 0&lt;/code&gt;, the UI immediately flags the exact row where &lt;code&gt;computedBalanceCents&lt;/code&gt; diverges from the statement's printed running balance. The same integer-cent check powers subtotal verification in our &lt;a href="https://jsontosheet.com/receipt-to-excel" rel="noopener noreferrer"&gt;receipt to Excel&lt;/a&gt; parser for thermal expense slips.&lt;/p&gt;

&lt;h2&gt;
  
  
  3. Generating Legacy BIFF8 &lt;code&gt;.xls&lt;/code&gt; Alongside &lt;code&gt;.xlsx&lt;/code&gt; in Memory
&lt;/h2&gt;

&lt;p&gt;Modern &lt;code&gt;SpreadsheetML&lt;/code&gt; (&lt;code&gt;.xlsx&lt;/code&gt;) 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 &lt;strong&gt;binary BIFF8 &lt;code&gt;.xls&lt;/code&gt;&lt;/strong&gt; wrapped in an OLE2 Compound File Binary container.&lt;/p&gt;

&lt;p&gt;To support both without server round-trips, our client-side exporter shares one normalized workbook model (&lt;code&gt;sheets[]&lt;/code&gt;, typed cells, column widths, and number formats like &lt;code&gt;#,##0.00&lt;/code&gt;) and forks only at serialization:&lt;/p&gt;

&lt;ul&gt;
&lt;li&gt;
&lt;strong&gt;&lt;code&gt;.xlsx&lt;/code&gt; path&lt;/strong&gt;: emits shared strings, worksheet XML (&lt;code&gt;t="n"&lt;/code&gt; for numeric cents divided by &lt;code&gt;100&lt;/code&gt;, ISO dates, UTF-8 text), and minimal styles into an in-memory store-only ZIP.&lt;/li&gt;
&lt;li&gt;
&lt;strong&gt;BIFF8 &lt;code&gt;.xls&lt;/code&gt; path&lt;/strong&gt;: allocates a 512-byte sector-aligned OLE2 FAT table with a &lt;code&gt;Workbook&lt;/code&gt; stream containing &lt;code&gt;BOF&lt;/code&gt;, &lt;code&gt;CODEPAGE&lt;/code&gt; (UTF-16LE), &lt;code&gt;FONT&lt;/code&gt;, &lt;code&gt;XF&lt;/code&gt;, &lt;code&gt;LABELSST&lt;/code&gt;/&lt;code&gt;NUMBER&lt;/code&gt; records, and &lt;code&gt;EOF&lt;/code&gt;.&lt;/li&gt;
&lt;/ul&gt;

&lt;p&gt;Keeping all parsing, integer-cent reconciliation, and binary serialization in client-side JS means sensitive statements stay local while still producing audit-ready workbooks.&lt;/p&gt;

</description>
      <category>javascript</category>
      <category>webdev</category>
      <category>pdf</category>
      <category>beginners</category>
    </item>
  </channel>
</rss>
