Content authored by practitioners with experience at Amazon India, Intuit QuickBooks, and the Tata Group. Meet the team →
Teams moving bank reconciliation from an Excel workbook to software often lose matching rules, mappings or the original dates of opening items, so ageing and controls break after go-live.
Carry across rules, mappings and dated opening items, leave spreadsheet workarounds behind, and prove equivalence with one parallel month-end close.
Inventory of lookup keys, tolerance rules, narration clean-up formulas, account-to-ledger map and cheque ageing logic; opening items imported with original dates and tagged as brought forward.
A signed migration record showing each workbook element, its software setting, and a parallel-run difference list classed as workbook error, tool error or timing.
When you move bank reconciliation from Excel to software, carry across three things: the matching rules your team really uses, the ledger and account mappings, and the opening reconciling items with their original dates. Leave helper columns and colour codes behind. Then run the workbook and the new tool in parallel for one month-end close and explain every difference.
If you are still deciding whether to move, read when manual reconciliation tops out. If you are staying in Excel for now, the narration parsing recipe keeps the workbook manageable.
Which workbook elements become configuration?
Open the workbook and list every element that changes a result:
- Lookup keys: VLOOKUP or XLOOKUP on cheque number, UTR or invoice number. These become matching rules.
- Tolerance rules: for example, accept a date difference of 2 working days or an amount difference of ₹1 for rounding. These become configured tolerances.
- Narration clean-up formulas: MID, SEARCH or TEXTSPLIT used to pull a UTR or VPA out of a narration. These become parsing rules.
- Account-to-ledger map: which bank account goes to which ledger in Tally, SAP or your ERP.
- Cheque ageing logic: the formula that flags a cheque older than three months, the validity period the RBI set from 1 April 2012.
Anything that only formats, highlights or hides rows is not logic and does not need to move. Once the tool is live, every rule change should be logged with user and date, the standard the proviso to Rule 3(1) of the Companies (Accounts) Rules, 2014 has applied to accounting software since 1 April 2023.
How do you load opening items without distorting ageing?
Pick a cut-over date, usually a month-end with a signed reconciliation. Export every open item as at that date with its original date, amount, reference and reason. Check that their net total equals the difference on the signed reconciliation. Then import them tagged as brought forward. For example, if the reconciliation signed at 31 March shows a difference of ₹4,18,650, the 62 brought-forward items, say outstanding cheques of ₹5,26,150 less deposits not yet credited of ₹1,07,500, must net to exactly ₹4,18,650 before go-live on 1 April. Keep the retired workbook with the books: Section 128(5) of the Companies Act, 2013 requires books and vouchers to be kept for eight financial years. If the tool dates them on the import day, a cheque already 80 days old will look new, and the stale flag will be missed.
How do you prove the software matches the workbook?
| Workbook element | Where it goes | Who signs it off | Test that proves equivalence |
|---|---|---|---|
| Lookup keys | Matching rules | Preparer | Same matches as the workbook on last month |
| Tolerance rules | Configured tolerances | Finance controller | Same items within tolerance |
| Narration formulas | Parsing rules | Preparer | Same references extracted from 50 sample lines |
| Account-to-ledger map | Account master | Finance controller | Every account mapped, none duplicated |
| Cheque ageing logic | Ageing and stale rules | Preparer | Same stale list at cut-over |
| Opening items | Brought-forward import | Finance controller | Total equals last signed reconciliation |
What does a worked parallel run look like?
In an illustrative month, the account has 940 statement lines and 62 brought-forward items. The workbook and the tool are run on the same files. The tool’s result differs from the workbook on 18 items. Each is classified:
- 7 workbook errors: two duplicate ticks, four lookups that matched the wrong invoice because amounts repeated, and one formula that skipped a row.
- 4 tool errors: a narration format from one bank not yet parsed, covering credits of ₹86,200 in all. The rule is fixed and the month rerun.
- 7 timing items: credits after the cut-off treated differently because the workbook used voucher date and the tool used value date. The team agrees one basis.
Only when all 18 are explained, and the tool’s open items equal the workbook’s corrected open items, should the workbook be retired. The days 1 to 5 runbook shows where the new tool fits in the close, and the implementation guide covers the wider plan.
How Terra Insight fits
TransactIG delivers bank-to-books reconciliation set up through configuration and industry presets, with an exception queue and audit-ready exports. Bring last month’s workbook and statements to a representative-sample fit benchmark and compare the results line by line. See the bank reconciliation software page.