Data Automation & Cleaning Intermediate

Automated GST Reconciliation (GSTR-2B vs Purchase Register CSV) in Python

Reconcile thousands of supplier invoices between GSTR-2B and ERP Purchase Register in Python Pandas with fuzzy invoice matching and tolerance checks.

#pandas#numpy

Problem Statement

CA firms and finance teams spend days matching GSTR-2B portal exports against Tally/SAP purchase registers to claim accurate Input Tax Credit (ITC). This script categorizes Exact Matches, Value Mismatches, and Missing in 2B in seconds.

Formula / Core Logic

Normalizes GSTIN + stripped alphanumeric Invoice Number as composite join key and flags tax differences > ₹2.00.

Installation

pip install pandas numpy

gst-reconciliation-gstr2b-vs-purchase-register-python.py

import pandas as pd
import numpy as np

def reconcile_gstr2b(df_2b, df_pr, tolerance=2.0):
    for d in (df_2b, df_pr):
        d['Join_Key'] = d['GSTIN'].str.strip().str.upper() + '_' + d['Invoice_No'].astype(str).str.replace(r'[^A-Za-z0-9]', '', regex=True).str.upper()
        
    merged = pd.merge(df_pr, df_2b, on='Join_Key', how='outer', suffixes=('_PR', '_2B'), indicator=True)
    diff = (merged['Tax_Amount_PR'].fillna(0) - merged['Tax_Amount_2B'].fillna(0)).abs()
    
    merged['Recon_Status'] = np.select([
        (merged['_merge'] == 'both') & (diff <= tolerance),
        (merged['_merge'] == 'both') & (diff > tolerance),
        merged['_merge'] == 'left_only',
        merged['_merge'] == 'right_only'
    ], ['EXACT_MATCH', 'TAX_MISMATCH', 'MISSING_IN_2B', 'MISSING_IN_BOOKS'], default='CHECK')
    return merged

Get the Complete 100+ Python Quant & Automation Bundle

Instant lifetime access to all production-ready scripts, valuation models, and trading bots ($19).

Buy Full Bundle on Gumroad ($19) →

Frequently Asked Questions

Q: Why strip special characters like `/` and `-` from Invoice Numbers?

Suppliers often upload `INV/2026/01` while bookkeepers enter `INV-2026-01`. Stripping punctuation prevents false mismatches.