Automated Bank Reconciliation and GSTR-2B Vendor Matching Toolkit (Excel-VBA) for a Modelled Trading SME

Every month a GST-registered trader's accountant performs two matching chores by eye: reconciling the bank statement against the books, and checking every purchase invoice against GSTR-2B — the portal's auto-drafted ITC statement — because since section 16(2)(aa) of the CGST Act took effect, input tax credit is available only for invoices that appear there. Both chores are slow, repetitive and exactly where credits get lost and differences hide.

This advanced project designs, builds and evaluates an Excel-VBA toolkit that automates both matchings for "Vinayaka Traders", a modelled GST-registered trading SME in Bengaluru (fictional, with all practice data generated for the project). The macro workbook imports the bank statement CSV and the Tally Prime bank-ledger export, matches on amount, date window and reference, and produces a proper bank reconciliation statement with an exception list. A second module imports the GSTR-2B export and the purchase-register export, matches on GSTIN, invoice number, taxable value and tax, and classifies every exception — invoice missing in books, invoice missing in 2B, value or tax mismatch — into a vendor follow-up list and an ITC claim worksheet aligned to Table 4 of GSTR-2B.

The evaluation is measurement-driven: on six modelled months (~300 bank lines, ~150 purchase invoices), the toolkit's task time and accuracy are compared against manual matching using paired t-tests, and a self-test macro plants known errors to verify the engine. Written for the BCU B.Com 5.6 Survey Project (Semester 5, 4 credits; 100 marks = report 60 + viva-voce 20 + log book 20).

A registered trading SME's monthly compliance now depends on two matching exercises that are still done by eye. Bank reconciliation is slow because statement lines and ledger entries rarely describe themselves identically, and the manual tick-mark leaves no reusable exception record. ITC matching is legally consequential: credit under section 16(2)(aa) is available only for invoices communicated in GSTR-2B (rule 36(4)), so an uncaught mismatch is either credit lost forever or an irregular claim. Manual matching of 150 invoices a month takes hours, produces inconsistent judgments, and generates no follow-up trail for vendors who file late.

What is needed is a transparent, parameterised, testable automation — built in the spreadsheet environment the accountant already uses — that performs both matchings, documents every exception in a defensible format, reconciles its own logic against Tally Prime's inbuilt BRS, and can be evaluated with a proper statistical comparison rather than anecdote.

Abstract

Bank reconciliation and GSTR-2B matching are monthly, rule-based, high-volume tasks in every registered trading SME — ideal candidates for automation with spreadsheet tools the accountant already owns. This project builds a macro-enabled Excel toolkit that automates both: importing the bank statement and Tally ledger export to produce a reconciliation statement and exception list, and importing the GSTR-2B and purchase-register exports to produce a vendor-wise ITC matching report. The toolkit is evaluated on six modelled months of transactions against manual matching, using paired t-tests on task time and accuracy, with a self-test macro that verifies the matching engine against planted errors. The output is a working, testable automation plus quantified evidence of its benefit.

Introduction

For a trader, two questions decide the health of each month-end: does the bank balance in the books equal the balance the bank reports, and can every rupee of claimed input tax credit be traced to an invoice in GSTR-2B? The GST portal answers the second partially: GSTR-2B is generated on the 14th of the following month as a static, auto-drafted ITC statement, built from suppliers' GSTR-1 filings and import data, and section 16(2)(aa) with rule 36(4) restricts credit to what it shows. The accountant must still compare it, invoice by invoice, with the purchase register in the books — and with Tally Prime exports on one side and portal exports on the other, the comparison is a perfect Excel job.

Existing system

• BRS: the accountant ticks the bank statement against the Tally ledger on screen or on printouts; timing differences (unpresented cheques, uncredited deposits) are listed from memory; one missed line carries forward for months.
• ITC matching: the GSTR-2B export is eyeballed against the purchase register; mismatches become phone calls; credits for invoices the vendor never filed lapse quietly.
• Limitations: hours of scanning, no reusable record of exceptions, no follow-up list, and results that depend on who did the ticking.

Proposed system

• A parameterised matching engine in VBA: exact-amount first, then a date-window pass (± configurable days) and a reference-similarity pass, marking each line auto-matched, partially matched or exception.
• A BRS sheet built from the unmatched items: balance per bank, add uncredited deposits, less unpresented cheques, balance per books — with every adjustment traceable to a line.
• A GSTR-2B matching sheet keyed on GSTIN + invoice number, with value/tax comparison and four exception classes feeding a vendor follow-up list and a claim worksheet mapped to GSTR-2B Table 4 categories.
• A self-test macro that plants known discrepancies and asserts the engine's outputs, plus an evaluation harness with timed runs for the paired-comparison study.

Feasibility

• Technical: Excel 2016+ and VBA only; Tally Prime's educational mode supplies the exports; no installation beyond enabling macros.
• Time: 14 weeks — realistic for the design-build-test-evaluate cycle at advanced level.
• Cost: nil; all data is modelled, so no real firm's figures are needed.
• Ethics: no real GSTINs, account numbers or client data; masked examples only.

In scope

• One modelled trading SME ("Vinayaka Traders") with six months of generated data: ~300 bank statement lines and ~150 purchase invoices per period set.
• Bank reconciliation from statement CSV + Tally bank-ledger export to a BRS sheet and exception list.
• GSTR-2B vs purchase-register matching, exception classification, vendor follow-up list, ITC claim worksheet (Table 4 categories).
• VBA self-test harness and a timed evaluation study (toolkit vs manual) on the modelled data.
• Parameters sheet: date tolerance, amount tolerance, reference-similarity threshold — all user-editable.

Out of scope

• Determining ITC eligibility beyond matching (blocked credits under section 17(5) are flagged only by category, not judged).
• Filing returns, uploading to the GST portal, or parsing the portal JSON — the module consumes the portal's Excel/CSV export.
• Multi-bank, multi-GSTIN or multi-company consolidation; TDS-TCS matching; e-invoice reconciliation.
• A real firm's live data — the study uses modelled data exclusively.

Development model

An incremental build with a verification gate each week — a compact engineering cycle suited to a tool project: each increment must pass its self-test before the next begins.

Evaluation design

For each of six modelled months, the same operator (with a second operator as control) performs manual matching and toolkit matching, timed; accuracy is scored against the known ground truth of the generated data. Paired t-tests (T.TEST type 1) compare mean task times and error counts; a checklist records usability issues. Because the data is generated with known planted discrepancies, accuracy is measured exactly, not estimated.

Hypotheses (α = 0.05)

• H1₀: there is no difference in mean task time between manual and toolkit BRS — paired t-test.
• H2₀: there is no difference in mean task time between manual and toolkit GSTR-2B matching — paired t-test.
• H3₀: there is no difference in accuracy (planted discrepancies detected) between the two methods — paired comparison of detection counts.
• H4₀: the toolkit's BRS output does not differ from Tally Prime's inbuilt BRS — deterministic cross-check (difference must be zero).

Quality practices

Version-labelled imports (statement file name and hash recorded on each sheet), parameters documented on-sheet, all VAT of the engine covered by the self-test macro, and a change log sheet — the practices an examiner expects of an advanced submission.

The toolkit is a two-pipeline workbook with a shared matching core, a parameters sheet and a self-test harness.

flowchart TD
  subgraph Inputs
    BS["Bank statement CSV (portal/bank download)"]
    TL["Tally bank ledger export (CSV)"]
    G2B["GSTR-2B export (GST portal, Excel/CSV)"]
    PR["Tally purchase register export (CSV)"]
  end
  BS --> IMP["Import & normalise (dates, amounts, narration)"]
  TL --> IMP
  G2B --> IMP2["Import & normalise (GSTIN, invoice no, value, tax)"]
  PR --> IMP2
  IMP --> ME{"Matching core"}
  IMP2 --> ME
  PAR["Parameters sheet: date window, amount tolerance, similarity threshold"] --> ME
  ME -->|bank lines| BM["Matched / exception marks"]
  ME -->|invoices| GM["Exception classes 1-4"]
  BM --> BRS["BRS sheet: balance per bank vs per books"]
  BM --> XL["Exception list with reasons"]
  GM --> FL["Vendor follow-up list"]
  GM --> CW["ITC claim worksheet (Table 4 categories)"]
  BRS --> XCK{"Cross-check vs Tally inbuilt BRS"}
  ST["Self-test macro: plants known errors"] --> ME
  ST --> LOG["Test log: expected vs actual"]

Matching-core logic

1. Exact pass: same amount (to the rupee, within tolerance) and plausible date order.
2. Window pass: same amount within the configurable date window (default ±3 days) — covers cheque float and settlement lag.
3. Reference pass: amount close and narration/reference similarity above threshold — covers multi-line settlements.
4. Classification: auto-matched, candidate (needs one click to confirm) or exception, each with the pass that matched it.

Why Dictionary objects

The VBA engine keys transactions in Scripting.Dictionary structures (amount-indexed for the bank pipeline, GSTIN-and-invoice-indexed for the GSTR-2B pipeline), turning lookups from nested scans into hash access. That single design choice is what makes 300-line months run in seconds and keeps the code readable enough for an examiner to audit.

GSTR-2B exception classes

(1) in 2B, not in books — purchase missed or vendor's GSTIN wrong in the books; (2) in books, not in 2B — vendor has not filed or invoice number differs; (3) taxable-value mismatch; (4) tax-rate or tax-amount mismatch. Classes 1–2 feed the follow-up list; classes 3–4 feed both the follow-up list and the claim worksheet's adjustment rows, so the credit finally claimed per Table 4 is defensible under section 16(2)(aa).

• Consume the GST portal's JSON download directly, removing the Excel-export intermediate step.
• Add section 17(5) blocked-credit tagging with a rules sheet, so the claim worksheet separates ineligible categories explicitly.
• Extend matching to e-invoice (IRN) data and TDS-TCS statements for a fuller compliance cockpit.
• Port the engine to Power Query for a no-macro version, and compare maintainability in the report.
• Conduct a small practitioner study: give the toolkit to five local accountants for one cycle and measure adoption and feedback — the natural 'survey' sequel to this build.