GST ITC Reconciliation Automation - Excel VBA Solution Developer
Problem
This prompt teaches CAs to build an Excel VBA tool that automatically matches GSTR-2A/2B with purchase registers, identifies ITC mismatches, flags ineligible credits u/s 17(5), and generates reconciliation reports. Eliminates 5-8 hours of manual matching per client per month by automating the entire reconciliation workflow with one-click execution.
Prompt Input
⚠️ NO ACTUAL DATA NEEDED: This teaches you to BUILD the tool. Test with dummy data only. --- Guide me to build a VBA-based GST ITC reconciliation tool with this setup: **USE CASE:** Reconciliation Type: GSTR-2B vs Purchase Register Monthly reconciliation for [X] clients Data volume: Approximately [500/1000/2000] invoices per month **MY DATA STRUCTURE:** Purchase Register (Excel) has columns: 1. Invoice Date 2. Invoice Number 3. Supplier GSTIN 4. Supplier Name 5. Taxable Value 6. IGST Amount 7. CGST Amount 8. SGST Amount 9. Total Invoice Value GSTR-2B (Downloaded from GST Portal) has columns: 1. GSTIN of Supplier 2. Invoice Number 3. Invoice Date 4. Taxable Value 5. Integrated Tax 6. Central Tax 7. State Tax **MATCHING LOGIC NEEDED:** - Match by: GSTIN + Invoice Number + Invoice Date - Tolerance: Allow ±₹[10] difference in values **REPORTS NEEDED:** 1. Summary: Total ITC as per Books vs 2B, Mismatch amount 2. Invoices in Books but not in 2B (supplier didn't upload) 3. Invoices in 2B but not in Books (missing entries) 4. Value mismatches (same invoice, different amounts) **MY SKILL LEVEL:** Excel: [Intermediate] VBA: [Can record macros, need complete code] **WHAT I NEED:** - Complete VBA code with comments (copy-paste ready) - Step-by-step: Where to paste code, how to set up sheets - One button to run entire reconciliation - Excel template structure (Input sheet, Output sheet layout)
Prompt Output
Complete Excel VBA solution package: (1) Excel template structure - 4 sheets: Purchase_Register (paste your data), GSTR_2B (paste portal download), Reconciliation_Output (auto-generated), Settings (parameters). (2) Complete VBA code module with detailed comments explaining each section: Sub ReconcileGST_ITC() - Main macro that executes reconciliation, Functions for data cleaning, matching logic, mismatch identification. (3) Step-by-step implementation guide: Open Excel → Alt+F11 → Insert Module → Paste code → Close VBA editor → Insert button on sheet → Assign macro → Test with sample data. (4) Button creation instructions with screenshots description. (5) Sample dummy data (10-15 rows) for testing. (6) Error handling for common issues (blank cells, wrong formats). (7) Output format: Color-coded reports (green=matched, red=mismatch, yellow=missing). (8) How to customize for your specific column names. Total implementation time: 30-45 minutes.