Excel-Based GST IMS Offline Tool for High-Volume Invoices

Step-by-Step Guide to Using the Excel-Based GST IMS Offline Tool for High-Volume Invoices

Executive Summary: Understanding the Invoice Management System (IMS)

The introduction of the Invoice Management System (IMS) on the GST Portal represents a significant shift in how registered taxpayers review, reconcile, and claim Input Tax Credit (ITC). IMS acts as an interactive ledger interface between the supplier’s outward supply reporting (GSTR-1 / GSTR-1A / IFF) and the recipient’s auto-generated ITC statement (GSTR-2B).

Before IMS, buyers had limited interactive control over auto-populated GSTR-2B data. Invoices were either present or missing, leaving taxpayers to manually manage timing differences, amended invoices, or incorrect entries through internal books of account. IMS changes this paradigm by allowing recipients to explicitly Accept, Reject, or Pending (leave pending) every single tax invoice, debit note, and credit note filed by their suppliers before GSTR-2B generation on the 14th of every month.

While the online GST portal interface is sufficient for small businesses processing a few dozen invoices per tax period, it is not built for large enterprises, multi-branch corporations, or high-volume tax practitioners handling thousands of line items monthly. Processing massive datasets through web browsers often leads to session timeouts, display latency, and operational errors.

To address these challenges, the Goods and Services Tax Network (GSTN) released the official Excel-Based GST IMS Offline Tool. This guide provides an end-to-end walkthrough for tax professionals, Chartered Accountants, CFOs, and finance teams to deploy and operate the tool for high-volume invoice processing.

Core Operational Workflow: Online vs. Offline IMS

Understanding when to transition from the online GST portal to the offline Excel workflow is essential for building efficient tax operations.

Operational Parameter	Online GST Portal IMS Interface	Excel-Based GST IMS Offline Tool
Recommended Volume	< 50 Invoices per month	Unlimited (Handles 100,000+ line items)
Processing Mechanism	Individual record clicks / Basic pagination	Bulk macro-driven action assignment
System Dependency	Constant active internet connection required	Offline local execution; internet needed only for API/JSON fetch
Audit Trail Capabilities	Minimal on-screen record	Persistent Excel record with internal comparison formulas
Error Handling	Real-time web error pop-ups	Structured `err.json` error report processing
ERP Reconciliation	Manual side-by-side browser check	Automated VLOOKUP / XLOOKUP / Power Query integration

System Prerequisites and Environment Configuration

To prevent processing crashes, data truncation, or macro execution failures during high-volume operations, ensure your IT environment meets the following hardware and software specifications:

1. System Requirements

Operating System: Windows 10 or Windows 11 (64-bit recommended).
Processor: Intel Core i5 (10th Gen or higher) / AMD Ryzen 5 or higher.
RAM: Minimum 8 GB (16 GB or higher recommended for datasets exceeding 50,000 records).
Storage: Solid State Drive (SSD) with at least 5 GB of free space for handling large JSON files.

2. Software Configuration & Security Settings

Microsoft Excel: Excel 2016, 2019, 2021, or Microsoft 365 (64-bit edition).
VBA Macro Permissions: Macro execution must be explicitly enabled.
1. Open Excel $\rightarrow$ Go to File $\rightarrow$ Options $\rightarrow$ Trust Center $\rightarrow$ Trust Center Settings.
2. Select Macro Settings $\rightarrow$ Choose Enable VBA macros.
3. Check the box for Trust access to the VBA project object model.
File Unblocking: When downloading the macro-enabled utility (.xlsm) from the GST Portal, right-click the downloaded ZIP/Excel file, select Properties, check the Unblock option at the bottom of the General tab, and click Apply.

Step 1: Downloading & Deploying the IMS Offline Utility

To begin, download the official setup package directly from the government source:

Navigate to the official GST Portal (www.gst.gov.in).
Do not log in yet; navigate to Downloads $\rightarrow$ Offline Tools $\rightarrow$ IMS Offline Tool.
Click Download. A compressed .zip folder will be saved to your local machine.
Extract the contents of the ZIP folder using 7-Zip or WinRAR into a dedicated workspace directory on your SSD (e.g., C:\GST_IMS_WorkSpace\).
Verify that the extracted contents contain the primary macro file: GST_IMS_Offline_Utility.xlsm.

Step 2: Fetching High-Volume IMS Invoices from the GST Portal

To process records offline, you must export the supplier-filed invoice database from the GST Portal using JSON files:

Log in to the GST Portal using your tax credentials.
Navigate to Services $\rightarrow$ Returns $\rightarrow$ Invoice Management System (IMS) Dashboard.
Select the relevant Financial Year (FY) and Return Period (Month).
Click on Download IMS Data Excel/JSON.
If your invoice volume exceeds 500 records, click Generate JSON File.
The system will queue your request. For high-volume databases (50,000+ records), the generation process takes between 5 and 20 minutes.
Once generated, download the file (.zip containing one or more .json files) and save it to your local directory: C:\GST_IMS_WorkSpace\Downloads_JSON\.

Step 3: Importing JSON Data into the Excel Offline Utility

With the raw JSON data downloaded, load it into the macro-enabled spreadsheet environment:

Open GST_IMS_Offline_Utility.xlsm in Microsoft Excel.
Click Enable Content on the top yellow security banner to allow VBA scripts to run.
On the Open / Main Menu sheet, enter your GSTIN and select the applicable Financial Year and Tax Period.
Click the Import Modules / Open JSON button.
A file browser popup will appear. Navigate to C:\GST_IMS_WorkSpace\Downloads_JSON\, select the downloaded JSON file, and click Open.
The macro will parse the JSON structure and populate data across the functional worksheets:
- B2B: Normal Business-to-Business Invoices.
- DE: Deemed Exports Invoices.
- SEZWP: Supplies to SEZ with Payment of Tax.
- SEZWOP: Supplies to SEZ without Payment of Tax.
- CDNR: Credit and Debit Notes for Registered Receivers.

Step 4: Structuring High-Volume Invoices for ERP Matching

For businesses managing high transaction volumes, manual review cell-by-cell is inefficient. Instead, combine the IMS data in Excel with your internal Accounts Payable (AP) register extracted from your ERP (such as Tally Prime, SAP, Oracle, or Microsoft Dynamics).

Recommended VLOOKUP / XLOOKUP Matching Logic

Create an internal operational working sheet named ERP_Reconciliation within or alongside the IMS utility workbook. Map your ERP AP ledger against the imported IMS data using composite key joins:

\text{Composite Key} = \text{Supplier GSTIN} \ \& \ \text{Invoice Number}

Use Excel formulas to evaluate matches:

Where:

Column A contains the Supplier GSTIN.
Column B contains the Document/Invoice Number.
ERP Column C contains the ERP Taxable Amount or Total Value.

Automated Decision Matrix for Action Assignment

By comparing your internal ERP register with the IMS dataset imported from the portal, you can automatically apply the appropriate IMS action status (Accept, Reject, or Pending) across thousands of rows using dynamic IF formulas:

Excel Automation Rule Formula Example:

Place the following dynamic formula in the Action column of your IMS worksheet to assign bulk decisions automatically:

Step 5: Applying Actions in the IMS Offline Tool Sheets

Once your reconciliation logic determines the appropriate decisions for all transactions, update the action statuses within the offline tool’s worksheets (B2B, CDNR, SEZ, etc.):

Navigate to the target sheet (e.g., B2B).
Locate the Action dropdown column on the far right of the data table.
The column accepts three explicit string values:
- ACCEPT: Validates the document. The tax credit will flow into your auto-generated GSTR-2B for the current tax period.
- REJECT: Rejects the supplier’s invoice. The credit will be excluded from your GSTR-2B. Use this for incorrect GSTINs, duplicate filings, or fraudulent claims.
- PENDING: Leaves the document open without taking an explicit action. The invoice stays in the IMS system and rolls forward to future tax periods until acted upon.
Bulk Action Assignment: Copy the assigned status value (e.g., ACCEPT) and paste it down the entire column for verified records. Excel macros within the tool can handle tens of thousands of rows simultaneously.
No Action Taken: If an invoice is left untouched, the system treats it as No Action Taken, which defaults to Accepted upon GSTR-2B generation.

Important: If you intend to defer an ITC claim for a disputed or unverified invoice, you must explicitly set its status to PENDING. Leaving it as “No Action Taken” will cause the tax credit to auto-populate into your eligible GSTR-2B.

Step 6: Exporting the Action JSON File

After reviewing and applying decisions across all relevant sub-sheets (B2B, CDNR, SEZ):

Return to the Main Menu tab of the Excel Offline Utility.
Click Validate Sheet to run the tool’s built-in validation macros.
If structural errors exist (such as missing compulsory fields, incorrect date formats, or invalid action syntax), the sheet highlights the affected cells in red and generates an error list. Correct these errors before proceeding.
Once validation succeeds, click Generate JSON File to Upload.
Choose a destination folder (e.g., C:\GST_IMS_WorkSpace\Uploads_JSON\).
The utility will generate a compressed, properly formatted output file named similar to: IMS_Action_Payload_27AAAAA0000A1Z5_102026.json.

Step 7: Uploading Action Payload to the GST Portal & Managing Errors

Log in to the official GST Portal.
Navigate to Services $\rightarrow$ Returns $\rightarrow$ Invoice Management System (IMS) Dashboard.
Select the relevant Financial Year and Return Period.
Click Upload JSON / Import Action Payload.
Browse and select your generated payload file: IMS_Action_Payload_...json.
Click Upload.

Resolving Upload Errors with the Offline Tool

Status: Processed: All actions were accepted and updated on the live IMS ledger.
Status: Processed with Errors: Most records were updated, but a subset failed due to validation mismatches (e.g., the supplier modified or deleted an invoice while you were compiling your offline data).
1. Click Download Error JSON.
2. Open GST_IMS_Offline_Utility.xlsm.
3. Go to Main Menu $\rightarrow$ Click Import Error JSON.
4. Select the downloaded error payload.
5. Navigating to the data sheets will display a dedicated Error Column explaining why specific transactions failed.
6. Correct the status for those specific records, re-generate the payload JSON, and upload it again to the portal.

Advanced Technical Nuances: Handling Complex Tax Scenarios

When operating at scale, enterprise tax teams encounter complex scenarios that require precise handling within the IMS framework:

1. Handling Credit Notes (CDNR) & Impact on GSTR-2B

Credit Notes issued by suppliers reduce your available Input Tax Credit. Their treatment in IMS requires special attention:

Rejecting a Credit Note: If you Reject a Credit Note in IMS, the reduction in ITC will not be applied to your GSTR-2B. Consequently, your available ITC will not be reduced for that tax period.
System Safeguard: To prevent tax evasion, rejecting a credit note automatically increases the supplier’s output tax liability in their next return cycle. Ensure any rejection of a credit note is backed by documented commercial disputes, non-receipt of original materials, or incorrect invoicing details.

2. Multi-Branch Reconciliation for Large Enterprises (Branch Transfers / ISD)

Enterprises operating with distinct Goods and Services Tax Identification Numbers (GSTINs) under a single PAN across multiple states must aggregate IMS processing systematically

Best Practices for Enterprise Tax Teams & CA Firms

Maintain Strict Processing Timelines: Supplier filing deadlines fall on the 11th (GSTR-1) and 13th (IFF) of each month. Complete your ERP reconciliation and run the IMS offline export between the 11th and 13th of every month to ensure all accepted records flow into your final GSTR-2B generated on the 14th.
Establish a Dedicated IMS Workspace Directory: Maintain a structured local repository organized by Financial Year, Month, and GSTIN. Storing input JSON files, reconciled Excel workbooks, and generated payload files ensures a complete audit trail for GST audits and annual returns (GSTR-9/9C).
Audit “No Action Taken” Invoices: While “No Action Taken” defaults to “Accepted” during GSTR-2B generation, do not rely on passive acceptance for unverified supplier records. Run macro checks to flag any invoices not matched in your AP ledger, and set their status to PENDING to prevent premature or eligible-credit claims on unverified purchases.
Coordinate Discrepancies with Suppliers Early: Leverage the automated variance outputs generated during your ERP vs. IMS reconciliation to communicate discrepancies (such as missing invoices, rate errors, or incorrect GSTIN assignments) to suppliers before they finalize their return filings.

Summary & Key Takeaways

The Excel-based GST IMS Offline Tool provides tax teams and enterprises with the capacity and flexibility required to manage high-volume invoice reconciliations efficiently. By shifting heavy data processing off the web portal and into an automated offline spreadsheet workflow, finance teams can process large datasets securely, minimize compliance risks, and ensure accurate Input Tax Credit management every month.

Need Professional Guidance on Enterprise GST Reconciliation?

Managing complex GST compliance, high-volume ITC reconciliations, and IMS implementations across multi-branch organizations requires dedicated technical and legal tax expertise.

For customized advisory services, automated tax workflow builds, or external audit assistance, contact our corporate advisory team at CleverCoins.