Python for Chartered Accountants: Automation, Insights and Data Analysis

Why Python belongs in a CA’s toolkit

Python can turn a repeatable accounting procedure into a reusable script: read files, validate them, apply rules and produce an exception report. Start with a task you already understand manually. Excel remains useful for review; Python handles repeatable preparation and calculations.

Python is a programming language. A library is a ready-made collection of functions—such as pandas for working with tables. Ordinary Python automation follows explicit rules; it does not need AI. AI can help draft code or explain results, but the CA must approve the logic and evidence.

Eight practical applications

Choose a small set of libraries

• pandas: read CSV/Excel, filter rows, group transactions and join datasets.
• openpyxl: read and write modern Excel workbooks. Test carefully when retaining complex workbook features.
• pathlib and logging: built-in tools for file paths and run logs.
• Matplotlib or Plotly: create charts after reconciling the underlying figures.
• pdfplumber: extract text and tables from suitable PDFs. Scans need OCR and independent checks of extracted figures.
• Streamlit: an optional next step for an internal upload-and-review interface with appropriate access controls.

Install only what a project needs. Begin with pandas and openpyxl; add visualisation or PDF tools later.

Set up your first project on Windows

1. Install Python from its official website and use an editor such as VS Code. Create a folder called ca-python.
2. Open a terminal in that folder and run the commands below. Calling the environment’s interpreter directly avoids activation-policy changes.
3. Create an input folder containing working copies of your Excel exports. Keep source originals separately.
4. Save the example as sales_review.py, then run it. The script creates an output folder.

py -m venv .venv
.venv\Scripts\python -m pip install pandas openpyxl
.venv\Scripts\python sales_review.py

On macOS/Linux, use python3 -m venv .venv and .venv/bin/python. An approved local environment is preferable for confidential client data.

Worked example: consolidate sales and identify review candidates

This starter script expects each workbook’s first sheet to have headers in row 1: Customer, InvoiceNo, Date, Amount. Use ISO date text such as 2026-04-05 or genuine Excel dates, numeric INR amounts without currency symbols, and invoice IDs stored as text. Remove title rows and subtotals in a controlled preparation copy. All files must cover the intended scope without accidental overlap.

The output contains consolidated transactions, customer totals, possible duplicates and control totals. It stops on missing data or an existing output file. This is an educational template: map and validate it for your actual exports before professional use.

from pathlib import Path
import pandas as pd

# Input: one sheet per file, row 1 headers, INR amounts.
# Required columns: Customer, InvoiceNo, Date, Amount.
source = Path("input")
files = sorted(p for p in source.glob("*.xlsx")
               if not p.name.startswith("~$"))
if not files:
    raise ValueError("No input workbooks found")
frames = []
for file in files:
    df = pd.read_excel(file, dtype={"Customer": "string",
                                   "InvoiceNo": "string"})
    required = {"Customer", "InvoiceNo", "Date", "Amount"}
    if not required.issubset(df.columns):
        raise ValueError(f"Missing columns in {file.name}")
    if df[list(required)].isna().any().any():
        raise ValueError(f"Blank required values in {file.name}")
    for col in ["Customer", "InvoiceNo"]:
        df[col] = df[col].str.strip()
        if df[col].eq("").any():
            raise ValueError(f"Empty identifiers in {file.name}")
    df["Date"] = pd.to_datetime(df["Date"],
                                format="%Y-%m-%d", errors="raise")
    df["Amount"] = pd.to_numeric(df["Amount"], errors="raise")
    if not df["Amount"].map(lambda x: float("-inf") < x < float("inf")).all():
        raise ValueError(f"Non-finite amounts in {file.name}")
    df["SourceFile"] = file.name
    frames.append(df)

data = pd.concat(frames, ignore_index=True)
# Same customer/invoice/amount is a REVIEW candidate, not proof.
flags = data.duplicated(["Customer", "InvoiceNo", "Amount"],
                        keep=False)
summary = data.groupby("Customer", as_index=False)["Amount"].sum()
controls = pd.DataFrame({
    "Metric": ["Files", "Rows", "Amount", "Flagged rows"],
    "Value": [len(files), len(data), data["Amount"].sum(), flags.sum()]
})
out = Path("output")
out.mkdir(exist_ok=True)
target = out / "sales_review.xlsx"
if target.exists():
    raise FileExistsError("Archive the previous output before rerunning")
with pd.ExcelWriter(target, engine="openpyxl") as writer:
    data.to_excel(writer, sheet_name="Consolidated", index=False)
    summary.to_excel(writer, sheet_name="CustomerSummary", index=False)
    data.loc[flags].to_excel(writer, sheet_name="ReviewCandidates", index=False)
    controls.to_excel(writer, sheet_name="Controls", index=False)
print(f"Created {target}")

Illustrative check: File A contains C001 / INV001 / ₹10,000 and C002 / INV002 / ₹20,000. File B repeats the C001 row and contains C003 / INV003 / ₹5,000. Expected output: 4 rows, ₹45,000 total and 2 flagged rows. Do not remove the repeated row automatically: first establish whether it is a duplicate or a valid separate transaction.

The duplicate key is intentionally simple. Production logic may need entity, financial year, voucher type, currency and invoice date. Preserve leading zeros at export; converting an already numeric identifier to text cannot restore lost zeros. Use integer paise or decimal arithmetic where exact monetary comparisons are required.

Turn data into management insights

1. Reconcile first: agree the period, source reports, row counts and gross/net basis. Link sales totals to the relevant books and explain differences.
2. Calculate consistently: monthly net sales, gross margin = gross profit ÷ net sales × 100, expense-to-sales ratios and overdue balances. Show “not applicable” when the denominator is zero.
3. Find the drivers: segment changes by customer, product or branch. Separate rate, volume, mix and one-off effects where the data supports it.
4. Write an evidence-based action: state the observation, possible explanation, evidence needed, owner and review date.

Illustrative insight: Revenue rises from ₹40 lakh to ₹50 lakh while gross profit remains ₹12 lakh. Gross margin falls from 30% to 24%, a decline of 6 percentage points. Investigate purchase cost, discounts and sales mix; the figures alone do not establish the cause.

Build reconciliations without hiding exceptions

Standardise identifiers on copies, preserve original values, then check key uniqueness before joining. In pandas, an outer merge with indicator=True separates left-only, right-only and matched records; validate="one_to_one" can reject duplicate keys when that relationship is expected. Reject blank keys first. For genuine many-to-one relationships, define and reconcile the aggregation explicitly.

Compare each amount field within a documented tolerance. Keep exact matches, amount differences, unmatched rows and ambiguous candidates in separate review queues. Fuzzy invoice matching is a suggestion requiring approval. Bank and GST reconciliations also need treatment of reversals, credit notes, timing differences and amendments specific to the records.

Use AI to help write and review Python

Act as a Python assistant for a CA. Using the anonymised schema and sample rows below, write a pandas script to [task]. Require explicit column mapping, reject missing or invalid values, preserve source filenames and row references, reconcile input/output counts and totals, and export exceptions separately. Never delete source files, overwrite originals or silently drop duplicates. Explain assumptions, dependencies and how to test the result against a small manually checked dataset. Ask about ambiguous accounting rules before implementing them.

Run suggested code on dummy data first. Review file operations, network requests and package requirements before execution. Give an AI tool only information authorised under the client engagement and the firm’s data policy. Keep professional conclusions tied to underlying records.

Controls before routine use

• Retain source exports, extraction date, entity, period, currency and approved mapping.
• Test blank fields, invalid dates, credit notes, duplicated keys and zero denominators; investigate every rejected row.
• Reconcile counts and totals at each transformation. Preserve nil-balance ledger accounts when relevant to audit scope.
• Log the script version, parameters, source references, run time and reviewer approval.
• Schedule only after supervised runs are stable. Use Windows Task Scheduler or an approved scheduler with explicit paths, failure alerts and protection against overlapping runs.
• Review all outputs before client delivery, accounting imports or filing. An analytical exception is a lead for investigation.

A four-week learning plan

1. Week 1: learn variables, lists, loops, functions and file paths. Read one anonymised CSV and check totals.
2. Week 2: learn pandas filtering, grouping, dates and Excel export. Reproduce a manually checked monthly summary.
3. Week 3: build a small reconciliation and review unmatched records and duplicate-key behaviour.
4. Week 4: add logs, a control sheet, documented assumptions and an independent review. Automate one recurring task.

Official references and related guides

• Python Packaging: installing packages in a virtual environment
• pandas: reading and writing data
• pandas: joins, indicators and validation
• GST reconciliation using AI
• Tally productivity using AI
• Create a CFO dashboard using AI

Technical references checked on 23 October 2026. This guide covers workflow design and programming; statutory eligibility and reporting decisions require a separate review of applicable requirements.