Report Automation

About this project

A pandas + openpyxl pipeline that turns a raw sales-transactions CSV into a formatted Excel workbook: headline totals, a category breakdown, a city breakdown, and — the part most CSV-to-Excel scripts skip — an explicit, row-by-row list of every transaction the report refused to trust, and exactly why.

The naive version of this script is two lines: read the CSV, sum a column. That runs, and it prints a number, and the number is wrong the moment the file contains any of the problems a real sales export accumulates within a month of someone editing it by hand — a duplicated transaction ID, a return recorded as a negative quantity, a blank or unparseable amount, a stray forward-dated row. None of those raise an exception, so nothing tells you the total is wrong, and a crash gets investigated while a polished-looking wrong number gets forwarded to a manager. This project's validation pass runs every row through seven named checks before anything is summed, excludes a failing row from every total, and writes it — in full, with the reason — to its own "Data Issues" sheet, so the report is auditable rather than just believed.

What you will learn

• Validate real-world messy input with named, specific checks instead of trusting it
• Never silently drop a bad row — record every exclusion with a reason, in an auditable place
• Write real openpyxl formatting: currency number formats, autofilter, frozen headers, conditional rules
• Reopen and read back the file you just wrote to confirm it, instead of trusting that saving didn't throw
• Repoint a report script at a different CSV schema by editing one dictionary, not rewriting the pipeline

Before you start

• Python basics — functions, dictionaries, and reading a CSV with pandas
• Python 3.10+, plus pandas and openpyxl — one pip command

New to Python? The free Python course covers everything this project assumes.

Features

• Seven named validation checks — duplicate IDs, bad quantities, unparseable amounts, out-of-period or invalid dates
• Every excluded row is written to a "Data Issues" sheet with its reason, never silently dropped
• Real openpyxl styling: currency number formats, autofilter, frozen header row, styled headers
• A genuine conditional-formatting rule (not a fixed colour) flagging any sale below a margin threshold
• The workbook is reopened and read back after writing, with results printed, to confirm it is actually correct
• One dictionary at the top of the file remaps the whole pipeline onto a differently-named CSV schema

Technology stack

Track: Data & Analytics · Level: Mini project

What is included

• report_automation.py — the full pipeline, commented throughout
• sample_sales_data.csv — 100 realistic rows, 11 deliberately broken across every validation category
• A README on all seven checks, what triggers each one in the sample, and what deliberately does not

Delivered as a 20 KB ZIP. SHA-256 published under Changelog so you can verify it.

System requirements

Operating system

Any

Runtime

Python 3.10 or newer. pip install pandas openpyxl

Licence

Frequently asked

Changelog