Streamlining GST ITC Reconciliation: A ChatGPT-powered solution
Author: CA Poorva Chube
Overview
Managing Input Tax Credit (ITC) reconciliation under GST has become a critical compliance task for businesses in India. Manual matching of GSTR-2B with Books of Accounts is time-consuming, error-prone, and exposes organizations to financial and regulatory risks. Leveraging automation, VBA macro based excel tool streamlines the entire GST ITC reconciliation process—delivering efficiency, accuracy, and actionable insights for clients.
Business Challenge
- Complexity: Monthly ITC matching across thousands of invoices, multiple vendors, and varying data formats. This requires manual data cleaning and matching which is time consuming.
- Compliance: Ensuring alignment with GST law to avoid ITC loss, penalties, and notices.
- Manual Burden: Traditional Excel-based reconciliation is slow, resource-intensive, and susceptible to manual errors.
- Follow-up with clients: Extracting the mismatches list and drafting email to client is time-consuming as it has to be customized as per client’s requirements
Solution: Automated GSTR-2B vs. Books Reconciliation Tool along with email draft
A custom-developed VBA macro embedded in Excel that fully automates GST ITC reconciliation, including:
| Feature | Description |
| Data Import |
- Import GSTR-2B downloaded from GST portal
- Consolidates data from multiple GSTR-2B sheets (B2B, B2BA, CDNR, CDNRA)
- Credit notes are automatically imported as entries with negative values
- Import data from ITC register extracted from Tally
- Cleans the data and rearranges the columns before importing the dedicated excel sheet in the tool
| Dynamic Mapping | Auto-maps columns and standardizes taxable value, GSTIN, and invoice details |
| Exclusion Logic | Filters out RCM and ineligible ITC with clear logging in a dedicated sheet. |
| Comprehensive Output | Generates summary sheets: Summary, OnlyIn2B, OnlyInBooks, Mismatch |
| Party-wise Analysis | Dedicated output sheet for summary of party-wise ITC differences for targeted action |
| Preserves Data Structure | Retains original formats for seamless review and audit |
| Automated Email Draft | Creates a professional Word draft with the reconciliation summary for clients |
| Audit Trail | Logs all exclusions and differences for compliance documentation |
How It Works: Step-by-Step
1. Import Data:
The tool reads raw GSTR-2B (all annexures) and Books data, auto-detects column headers, and merges sources.
2. Apply Filters:
-Excludes ineligible (RCM and ITC not available) invoices in GSTR-2B file, maintaining an audit log.
-Determines and imports credit notes correctly and fetches the values as negative figures
3. Reconciliation Automation:
- Matches invoices on GSTIN, invoice number, date, and taxable value.
- Classifies invoices into matched, only-in-2B, only-in-Books, and mismatch categories.
- Computes summary at both total and party-wise level.
4. Reporting:
- Generates Excel sheets for each reconciliation result.
- Inserts reconciliation summary into a Word draft with a formal advisory note.
5. Client Advisory:
- The generated email provides ITC difference analysis, actionable insights, and compliance recommendations.
Snapshots:
- Tools’ interface
- Outputs generated:
- Excel sheets for Party-wise reconciliation result along with invoice-level reasoning for differences in separate sheets
- Draft of email is generated along with party-wise difference summary table:
Key Benefits
- Time Saving: Reduces reconciliation time from days to minutes.
- Accuracy: Eliminates manual errors with automated, rule-based matching.
- Audit-Ready: Transparent logs and output formats as per audit and GST requirements.
- Professional Communication: Ready-to-send client communication which can be further customized
- Scalable: Handles large datasets and scalable for enterprises and SMEs alike.
Technical Stack: Powering the Automation
- ChatGPT: For VBA script generation
- Microsoft Excel: Base platform for tool – for data manipulation and automation
- Microsoft Word: Email draft generation based on automated reconciliation
Future Enhancements:
The Automated GST ITC Reconciliation tool is designed with scalability and adaptability in mind. Potential future enhancements include:
| Enhancement | Description |
- Auto-Email Dispatch
| Integration with Outlook or Gmail to directly send reconciliation summaries and advisories to clients from within Excel. |
- Automate data extraction and import from accounting softwares
| Currently, data as per books can be imported only from ‘ITC register’ extracted from Tally Prime. | The process of data extraction and import can be automated further and extended to integration with other accounting packages and ERPs. |
- AI-Based Anomaly Detection
| Incorporation of AI/ML models to flag unusual transactions, potential fraud, or ITC ineligibility trends for proactive action. |
- PAN/GSTIN Validation API Integration
| Automated validation of vendor GSTINs and PANs through government APIs to reduce manual data errors. |
Conclusion:
The tool is generated to ease the GST ITC reconciliation process by use of macro-embedded excel based tool. Currently, it meets the basic requirements of the Chartered accountants relating to GST ITC reconciliation along with drafting of email. Further enhancements will make it more scalable and customizable. The goal is to simplify the reconciliation process along with the further follow-up through user-friendly offline tool.
