iib Reconciliation

Developed by: Zaid Saad Alaloosy

A lightweight, specialized tool designed to simplify the process of matching and clearing accounting transactions. The application identifies offsetting entries where debit and credit amounts cancel each other out and displays only the transactions that remain unreconciled.

Features

• Excel/CSV Data Import: Upload your accounting data directly from Excel (.xlsx, .xls) or CSV files
• Automated Matching: Detects one-to-one or multiple offsetting entries (Debit vs. Credit) with the same amount
• Unreconciled Report: Generates a clean, filtered report showing only transactions that are not fully matched
• Data Export: Save the reconciliation results back into Excel for further review or reporting
• User-Friendly Workflow: Minimal steps—import, reconcile, and export

How to Use

Step 1: Open the Application

1. Double-click on index.html to open the iib Reconciliation application in your web browser
2. The application will load with a clean, professional interface

Step 2: Import Your Data

1. Click on the upload area or drag and drop your Excel/CSV files
2. Supported formats: .xlsx, .xls, .csv
3. You can upload multiple files at once
4. The application will automatically detect and load your data

Step 3: Configure Column Mapping

1. Select which column contains Debit amounts
2. Select which column contains Credit amounts
3. Optionally select a Description column for better reporting
4. The application will try to auto-detect common column names
5. Preview your data to ensure correct mapping

Step 4: Process Reconciliation

1. Click the "Process Reconciliation" button
2. The application will:
   • Parse all transaction amounts
   • Group transactions by amount
   • Match debits with credits of the same value
   • Identify unreconciled transactions

Step 5: Review Results

1. View the summary showing:
   • Total transactions processed
   • Number of matched transactions
   • Number of unreconciled transactions
2. Use the filter buttons to view:
   • Show All: All transactions
   • Show Matched: Only matched transactions (highlighted in green)
   • Show Unreconciled: Only unmatched transactions (highlighted in red)

Step 6: Export Results

1. Click "Export Results" to download an Excel file
2. The export will contain the currently filtered view
3. Files are saved with descriptive names:
   • reconciliation_all_transactions.xlsx
   • reconciliation_matched_transactions.xlsx
   • reconciliation_unreconciled_transactions.xlsx

Sample Data

A sample CSV file (sample_data.csv) is included to help you test the application. This file contains:

• Various debit and credit transactions
• Some perfectly matched pairs
• Some unreconciled transactions
• Different transaction types (payments, expenses, revenue)

Data Format Requirements

CSV Files

• First row should contain column headers
• Amounts should be numeric (currency symbols will be automatically removed)
• Use separate columns for debits and credits, or use positive/negative values in a single amount column

Excel Files

• First row should contain column headers
• Data should be in the first worksheet
• Amounts should be in numeric format

Example Data Structure

Description,Debit,Credit,Reference
Office Supplies,150.00,,INV-001
Client Payment,,150.00,PAY-001
Utility Bill,75.50,,UTIL-002

Matching Algorithm

The application uses a simple but effective matching algorithm:

1. Amount Grouping: Groups all transactions by their absolute amount value
2. Type Separation: Within each amount group, separates debits from credits
3. One-to-One Matching: Pairs debits with credits of the same amount
4. Match Groups: Assigns unique group IDs to matched pairs for tracking
5. Unreconciled Identification: Any transaction without a matching counterpart is marked as unreconciled

Browser Compatibility

• Chrome (recommended)
• Firefox
• Safari
• Edge
• Internet Explorer 11+

Technical Requirements

• Modern web browser with JavaScript enabled
• No server or internet connection required (runs locally)
• File size limit depends on browser memory (typically handles thousands of transactions)

Troubleshooting

File Upload Issues

• Ensure files are in supported formats (.xlsx, .xls, .csv)
• Check that files are not corrupted or password-protected
• Try uploading one file at a time if multiple files cause issues

Matching Issues

• Verify that debit and credit columns are correctly selected
• Check for formatting issues in amount columns (extra spaces, currency symbols)
• Ensure amounts are numeric values

Export Issues

• Make sure your browser allows file downloads
• Check that you have sufficient disk space
• Try a different browser if export fails

Support

This application is designed to be intuitive and self-explanatory. For best results:

• Use clean, well-formatted data
• Ensure consistent number formatting
• Review column mappings before processing
• Test with the provided sample data first

---

Note: This application processes data locally in your browser. No data is sent to external servers, ensuring complete privacy and security of your financial information.