import pandas as pd

File Paths (Change as per your location)

gstr1_file = "GSTR1.xlsx" gstr3b_file = "GSTR3B.xlsx" gstr2b_file = "GSTR2B.xlsx" gstr9_file = "GSTR9.xlsx"

output_file = "GST_Reconciliation_Report.xlsx"

-----------------------------

1. Read Excel Files

-----------------------------

gstr1 = pd.read_excel(gstr1_file) gstr3b = pd.read_excel(gstr3b_file) gstr2b = pd.read_excel(gstr2b_file) gstr9 = pd.read_excel(gstr9_file)

-----------------------------

2. Standardize Columns

-----------------------------

tax_columns = ['Taxable Value', 'IGST', 'CGST', 'SGST']

for df in [gstr1, gstr2b]: for col in tax_columns: df[col] = pd.to_numeric(df[col], errors='coerce').fillna(0)

-----------------------------

3. GSTR-1 Summary (Outward)

-----------------------------

gstr1_summary = gstr1[tax_columns].sum() gstr1_summary.name = "GSTR-1"

-----------------------------

4. GSTR-3B Outward (3.1)

-----------------------------

gstr3b_outward = gstr3b[gstr3b['Section'] == '3.1(a)'][tax_columns].sum() gstr3b_outward.name = "GSTR-3B Outward"

-----------------------------

5. GSTR-3B ITC (4A)

-----------------------------

gstr3b_itc = gstr3b[gstr3b['Section'] == '4(A)'][tax_columns].sum() gstr3b_itc.name = "GSTR-3B ITC"

-----------------------------

6. GSTR-2B Summary (ITC)

-----------------------------

gstr2b_summary = gstr2b[tax_columns].sum() gstr2b_summary.name = "GSTR-2B"

-----------------------------

7. GSTR-9 Outward & ITC

-----------------------------

gstr9_outward = gstr9[gstr9['Particulars'] == 'Outward'][tax_columns].sum() gstr9_outward.name = "GSTR-9 Outward"

gstr9_itc = gstr9[gstr9['Particulars'] == 'ITC'][tax_columns].sum() gstr9_itc.name = "GSTR-9 ITC"

-----------------------------

8. Create Reconciliation Tables

-----------------------------

outward_recon = pd.concat([ gstr1_summary, gstr3b_outward, gstr9_outwa_