E-commerce Sellers, True Profit per Product and Missing TCS/TDS Credits with AI
Author : CA Namita Hiranandani
ICAI AI HACKATHON SEASON 6 | AI AND PYTHON | GST AND INCOME TAX
E-commerce Sellers: Find True Profit per Product and Missing TCS/TDS Credits with AI
Author: CA Namita Hiranandani, Chartered Accountant
| AI classifies. Python calculates. The CA decides. | Online sellers book GST TCS and Income Tax TDS as expenses, so they never see their true profit and never check whether these credits reached the GST portal and AIS. This tool reads marketplace settlements and shows, every month, the true profit of each product and every missing tax credit, with the action to take. Only column headings were shared with AI. Every number is calculated offline, and the dashboard is locked with AES-256 before it is shared. |
| Rs 45.45 L | true profit, against Rs 43.87 L booked | Rs 1.58 L | TCS and TDS moved out of expenses | Rs 8,307 | credits at risk, found month by month | 2 of 10 | products losing money in both years |
Results on fictional demo data, FY 2024-25 and FY 2025-26, processed in 0.7 seconds
| Problem | Solution | Result |
| Profit | TCS, TDS and GST on fees booked as expenses | Classified as recoverable credits, not costs | True profit per product, marketplace and year |
| Tax credits | Nobody checks the GST portal and AIS | Monthly reconciliation at the rate of each date | Every missing or unaccepted credit, with amount |
| Action | Findings stay in spreadsheets | Rule-based management summary | Action plan, client note, marketplace emails |
| Privacy | Client data in generic AI tools | Headings only to AI, fully offline | Encrypted dashboard, zero internet requests |
| Prompts used (all 14) | https://claude.ai/artifact/2Q8vZG6jovi61o6W7e6UCY | Master prompt in Appendix A, section prompts in Appendix B |
| Built with | Claude (AI, one time), Python with pandas, cryptography, Chart.js. Open source and offline | |
| Who can use it | CA firms and accountants of e-commerce sellers, every month |
How it works
Figure 1. AI is used once on column headings; Python runs every month; the CA reviews and acts
1. How It Was Built: Prompt to Output
One master prompt built the engine. Short follow-up prompts, sent in the same chat, added each dashboard section. Every output below is real and ties back to the same data.
Step 1. Build the engine
| Prompt Role, Context, Golden rule, Task, Inputs (column headings only), Rules (rate of each date, cost versus credit, due dates, flags), Output, Checks. Full text in Appendix A. |
Result: One Python script. Run it, and it recognises each file by its headings and prints a summary line to verify:
| python "Seller Dashboard Generator.py" "Last 2 year data" --password | Apr 2024 to Mar 2026: 17,187 orders | True profit Rs 4,545,117 (26.2%) | TCS+TDS withheld Rs 158,449 | credits at risk Rs 8,307 | loss-making products 2 | Dashboard written in 0.7 seconds |
Step 2. Find the true profit of each product
| Prompt Table per SKU: units sold, returned, net sales, every cost, true profit and margin. Loss-making products in red at the top. Chart of profit by product. |
Result: K106 Brass Lamp Large loses Rs 59,271 and K108 Wooden Coaster Set loses Rs 12,539 over two years, after fees, shipping and returns.
Figure 2. True profit by product: two products lose money in both years
Step 3. Find the missing TCS credits
| Prompt Table by marketplace and month: TCS expected at the rate of that date, TCS withheld, TCS on the GST portal, status. Flags as red rows at the top. If GSTR-8 is not yet due, show Awaiting and do not count it as at risk. |
Result: 6 of 48 months have issues: old 1% rate after the cut, returns not adjusted, months missing on the portal, under-reported in GSTR-8, a credit not accepted. Rs 7,647 of TCS at risk, plus Rs 660 of TDS from the AIS check.
Figure 3. GST TCS reconciliation, issues listed first
Step 4. Turn findings into action
| Prompt Rule-based text filled with numbers from Python: one-line verdict, decision path, action plan with owner and deadline, client note, and one email per marketplace. |
Result: A verdict a client understands, five questions answered with evidence, and an action plan sorted by amount.
Figure 4. Management Summary: verdict and decision path
Step 5. Run it every month
| Prompt Accept one or more data folders in one command. Combine files of the same type and skip duplicate rows. |
Result: New FY 2026-27 data was added with an overlapping August download. Same script, one more folder:
| python "Seller Dashboard Generator.py" "Last 2 year data" "FY 2026-27" --password | -> Marketplace A settlement: 3 files combined, 478 duplicate rows skipped | -> Marketplace B settlement: 3 files combined, 417 duplicate rows skipped | True profit Rs 5,878,422 (26.5%) | TCS+TDS withheld Rs 187,958 | credits at risk Rs 12,099 |
Months whose return is not yet due show Awaiting in amber, so a CA running it in October sees no false alarms for July to September TDS.
2. The CA Knowledge Inside the Rules
| Tax | Rate applied by transaction date | Base |
| GST on product (demo items) | 12% up to 21 Sep 2025, 5% from 22 Sep 2025 | Price including GST |
| GST TCS, Section 52 CGST Act | 1% up to 9 Jul 2024, 0.5% from 10 Jul 2024 | Net taxable value |
| TDS, Section 194-O (Section 393 of the Income-tax Act 2025 from 1 Apr 2026) | 1% up to 30 Sep 2024, 0.1% from 1 Oct 2024 | Gross taxable value |
| Flag | Meaning |
| Old 1% rate applied | Deducted at the old rate after the rate cut |
| Over-collected | TCS not reversed on returned orders |
| Not in GST portal / Under-reported | Missing from GSTR-8, or reported lower than withheld |
| Pending acceptance | On the portal but not accepted, so not in the electronic cash ledger |
| Not in AIS / Short in AIS / Rate mismatch | TDS missing, lower, or at the wrong rate |
| Awaiting | Return not yet due (GSTR-8 by the 10th; TDS returns by 31 Jul, 31 Oct, 31 Jan, 31 May) |
A tolerance of Rs 10 per marketplace per month ignores rounding differences.
3. Privacy, Security and Verification
| Control | How it works |
| Golden rule | All numbers come from Python. AI only wrote the rules and the wording. No live AI inside the dashboard |
| Data minimisation | Only column headings were shared with AI. No client data leaves the laptop |
| Offline | Single HTML file with charts embedded. Tested: zero network requests |
| Encryption | AES-256-GCM, key from PBKDF2-SHA256 with 250,000 rounds. Password asked at run time and never stored. Tested: no plain-text data in the protected file |
| Accuracy | Python results matched a second calculation engine in the dashboard for every marketplace and year, with zero mismatches. Profit by product equals profit by month. Every planted error was caught |
| Professional judgement | The tool flags and quantifies; the CA reviews and decides. A separate Responsible Use and Disclaimer note goes with it |
4. Benefits and Legal Alignment
| Benefits to CA firms | Legal and professional alignment |
| Recover client money: missing TCS and TDS found and quantified | CGST Act 2017: Section 52 TCS, GSTR-8, acceptance before cash ledger credit |
| Better advice: true profit drives pricing and channel decisions | Income-tax Act 1961 and 2025: Section 194-O TDS, Section 393 from 1 Apr 2026, AIS |
| Speed: two years in under a second, rerun every month | DPDP Act 2023: data minimisation, no client data to external AI |
| New service line: monthly e-commerce profit and credit review | ICAI Code of Ethics: confidentiality; the CA keeps professional judgement |
All demo data is fictional: Kesari Handicrafts Pvt Ltd, Marketplace A and Marketplace B. TCS is not the seller's expense. It is the seller's money.
5. Tools and Libraries Used
| Tool | What it does in this use case |
| Claude (AI assistant by Anthropic) | Used once, on column headings only: classified each settlement column, drafted the tax rules and wrote the Python and dashboard code from 14 prompts |
| Python 3 | Runs the engine on the CA's laptop, offline |
| pandas | Reads the CSV files, combines them, skips duplicate rows, parses dates and calculates profit and reconciliations |
| cryptography | AES-256-GCM encryption of the dashboard for the --password option |
| Python standard library | hashlib (PBKDF2-SHA256 key from the password), getpass (hidden password typing), json, base64, os, sys, time, webbrowser (opens the dashboard) |
| Chart.js 4.4.1 | Charts, embedded inside the HTML file so nothing loads from the internet |
| HTML, CSS and JavaScript | The dashboard, its filters and the second calculation engine used for the accuracy check |
| Browser built-ins | WebCrypto to unlock the protected file; Speech Synthesis for the Audio Briefing |
| Windows Command Prompt and .bat files | One command or a double-click to run each month |
| 7-Zip (optional) | AES-256 encrypted archives for the raw CSV folders |
Setup, one time: install Python from python.org, then run pip install pandas cryptography. All tools except the AI assistant are free and open source.
Appendix A. Master Prompt (Prompt 1)
Given to the AI assistant once. No client data, only column headings.
| ROLE | You are a Python developer working with a Chartered Accountant in India who audits e-commerce sellers. | CONTEXT | Sellers receive monthly settlement reports from 2 marketplaces in different formats. Marketplaces deduct commission, shipping, fees, GST TCS (Sec 52 CGST Act) and Income Tax TDS (Sec 194-O, now Sec 393 of the Income-tax Act 2025). | Sellers book the whole deduction as expense and never check whether TCS/TDS reached the GST portal and AIS. | I am NOT sharing any data, only column headings. | We will build in steps. This prompt builds the engine and a basic dashboard. I will add each dashboard section with follow-up prompts in this same chat. | GOLDEN RULE | All numbers come from Python. AI only writes the rules and the wording. No internet, no live AI inside the dashboard. | TASK | 1. Classify every settlement column as Revenue, Cost, Input Tax Credit, TCS credit or TDS credit. | 2. Calculate TRUE profit per product, per marketplace, per month and per financial year. | 3. Reconcile TCS with the GST portal and TDS with AIS, by marketplace and month. | 4. Produce a basic offline dashboard with the key totals and action points. Filters, tabs and sections will follow in my next prompts. | 5. Optionally password-protect the dashboard before it is shared. | INPUTS (column headings only) | - Marketplace A: Order ID, Order Date (dd-mm-yyyy), Transaction Type, SKU, Quantity, Item Price Incl GST, Commission, Fixed Closing Fee, Shipping Fee, GST on Fees, TCS Withheld, TDS 194O, Net Settlement | - Marketplace B: Ref No, Txn Date (yyyy/mm/dd), Product Code, Units, Sale Value, Return Value, Marketplace Fee, Payment Gateway Charge, Logistics Charge, Promotion Charge, GST on Charges, GST TCS Withheld, IT TDS Withheld, Amount Settled | - SKU Cost Master: SKU, Product Name, Unit Cost | - GST portal TCS credit received: GSTIN of ECO, Tax Period, Net Value, IGST, CGST, SGST, Status | - AIS: Information Source, Month, Amount Paid or Credited, TDS Deducted | RULES | - Use the rate in force on each transaction date: | GST on product: 12% until 21 Sep 2025, then 5% | TCS: 1% until 9 Jul 2024, then 0.5% of net taxable value | TDS: 1% until 30 Sep 2024, then 0.1% of gross taxable value | - TCS, TDS and GST on fees are NOT costs. | - Flag: old rate applied, TCS not reversed on returns, missing on portal, under-reported in GSTR-8, not accepted, missing or short in AIS. | - A month not yet due shows Awaiting, not at risk: GSTR-8 is due by the 10th of the next month; TDS returns by 31 Jul, 31 Oct, 31 Jan and 31 May. | - Tolerance Rs 10 per marketplace per month. | - Financial year is April to March, shown as FY 2024-25. | - Recognise each file from its column headings, and also accept dates saved by Excel. | - Accept one or more data folders in one command (for example last 2 years plus a new year). Combine files of the same type and skip duplicate rows; the latest cost master wins. | OUTPUT | - One Python script using pandas, plus cryptography for the password option (both open source), runs offline. | - Run as: python "Seller Dashboard Generator.py" "folder" ["more folders"] [--password] | - Writes ONE self-contained HTML dashboard: no internet, no external links. | - Keep all calculations in one place so every section added later uses the same numbers. | - A --password option encrypts the data inside the HTML with AES-256. The password is asked at run time and never stored; the browser unlocks the file offline. | CHECKS | - Total profit by product must equal total profit by month. | - Print a summary line in the terminal so I can verify the numbers. | - The HTML must make zero internet requests. |
Appendix B. Section Prompts (Prompts 2 to 14)
Sent in the same chat, one at a time, testing after each. Full text with copy buttons: https://claude.ai/artifact/2Q8vZG6jovi61o6W7e6UCY
| 2 | Layout, filters, KPI cards | 9 | GST TCS Credits vs GST portal, Awaiting rule |
| 3 | P and L with AI classification panel | 10 | Income Tax TDS Credits vs AIS |
| 4 | Trends with rate change dates | 11 | Management Summary, client note, emails |
| 5 | Year Comparison, like-for-like | 12 | What-If Planner, break-even price |
| 6 | Marketplaces: what the seller keeps per Rs 100 | 13 | Ask a Question, Audio Briefing, Export, Print |
| 7 | Products: loss-makers on top | 14 | Checks: Python vs JavaScript, zero mismatches |
| 8 | Returns: flag above 15 percent |
