AutoReconcile: Bank Reconciliation Tool

User Manual & Product Description

1. Product Overview

AutoReconcile is a lightweight, browser-based utility designed to automate the tedious process of comparing your internal cash book records against your official bank statements.

Designed specifically for small businesses and accountants, this tool runs entirely in your web browser. This means your sensitive financial data never leaves your computer—it is processed locally using JavaScript, ensuring complete data privacy.

Key Features

• Dual CSV Input: dedicated input areas for internal records and bank feeds.

• Smart Parsing: Automatically detects dates, descriptions, reference numbers (Check/Receipt #), and amounts.

• Auto-Matching: Algorithms automatically pair transactions based on exact amounts.

• Variance Reporting: Instantly calculates the difference between your books and the bank.

• Privacy-First: No database, no cloud uploads, no server tracking.

• Print-Ready: Generates a clean, professional report suitable for physical filing or PDF export.

• Philippine Currency Support: Native support for Philippine Peso (₱) formatting.

2. Data Preparation Requirements

To ensure the tool works correctly, your data must be in CSV (Comma Separated Values) format. You can usually export this from Excel, Google Sheets, or your online banking portal.

Required Column Headers

The tool looks for specific keywords in the first row of your data. Ensure your CSV has headers similar to these:

Field

Accepted Header Keywords

Example Data

Date

Date, Day

2023-10-25

Reference

Ref, Reference, Check, Chk, Receipt

CHK-101, RCP-55

Description

Description, Desc, Memo, Details

Payment from Client A

Amount

Amount, Debit, Credit, Value

1500.00, -500.50

Important Formatting Notes

1. Negatives vs. Positives: Ensure withdrawals/payments are negative (e.g., -150.00) and deposits/income are positive (e.g., 5000.00).

2. Date Format: The standard YYYY-MM-DD is recommended, though the tool attempts to read various formats.

3. Clean Data: Remove currency symbols (₱, $) from the CSV data itself (the tool handles them visually, but raw data should be numbers).

3. How to Use the Tool

Step 1: Input Data

1. Open the bank_reconciler.html file in any modern web browser (Chrome, Edge, Firefox, Safari).

2. Left Box (Internal Cash Book): Copy and paste the CSV content from your accounting software or spreadsheet ledger.

3. Right Box (Bank Statement): Copy and paste the CSV content from your downloaded bank statement.

Step 2: Process

1. Click the Reconcile Now button.

2. The screen will scroll down to the generated report.

(Note: If you want to test the tool first, click Load Sample Data to populate the fields with dummy information.)

Step 3: Analyze Results

The report is divided into three sections:

A. Summary Dashboard

• Cash Book Balance: The total sum of all items pasted in the left box.

• Bank Stmt Balance: The total sum of all items pasted in the right box.

• Unreconciled Difference: The variance between the two.

  • Green "Balanced": Difference is ₱0.00.

  • Red "Discrepancy": Indicates items are missing or amounts do not match.

B. Action Items (Unmatched Tables)

• Unmatched in Cash Book (Unpresented): These are items you recorded (like a check you wrote) that have not yet cleared the bank.

  • Action: Verify if the check is still outstanding or if the bank missed it.

• Unmatched in Bank Stmt (Unrecorded): These are items on the bank statement (like bank fees or direct deposits) not yet in your ledger.

  • Action: Add these entries to your accounting software.

C. Reconciled Items (Matched)

Click the "Reconciled Items" bar to expand the list. This shows every transaction that was successfully paired between the two lists based on the amount.

Step 4: Printing / Saving

1. Once you are satisfied with the reconciliation, click the Print Report button in the top right corner.

2. This opens your browser's print dialog.

3. You can print to a physical printer or select "Save as PDF" to archive the reconciliation report digitally.

4. Troubleshooting

Error: "Error Processing Data"

• Cause: The tool could not find the required headers (Date, Desc, Amount) in your pasted text.

• Fix: Ensure the very first line of your pasted text contains the headers (e.g., Date, Description, Amount).

Error: Balances don't match what I expect

• Cause: The tool calculates the balance solely based on the rows you pasted. It does not know your "Opening Balance" unless you include it as a row in the CSV.

• Fix: Add a row at the top of your data: 2023-10-01, -, Opening Balance, 50000.

Issue: Transactions aren't matching even though they look the same

• Cause: The tool uses a "fuzzy" match for amounts with a tolerance of 0.009 to handle rounding errors, but dates and descriptions are ignored for matching—only Amounts are used to pair items.

• Fix: Ensure the amounts are exactly the same. If you recorded 100.00 but the bank recorded 99.99, they will not match automatically.

5. Technical Specifications

• Platform: Web Browser (Desktop recommended).

• Technology: HTML5, TailwindCSS, PapaParse JS Engine.

• Currency Locale: Philippines (en-PH).

• Data Storage: None (Ephemeral / Session-based).