One report that runs itself, safely, for a real person

The situation

Three branches of a small academy send their registers as separate spreadsheets every week, dirty in the usual ways, and somebody spends a morning merging them. Build the automation that does it: clean with a printed report, merge, produce the workbook, email it, run every day on a schedule — and fail loudly when the input is missing. Then ship it to a real person and show them the first run.

What you'll be able to show

• Choose automation targets by cost-to-effort ratio
• Clean data with a printed before/after report, never silently
• Schedule a script and prove its failure is visible
• Keep secrets in .env and out of the repository

What you need

• Python 3 with pandas and openpyxl, a .env file
• Windows Task Scheduler (or cron)
• An email account with an app password, and a free JSON API

Tasks

1. 1The hit-list
   List five repetitive computer tasks you or someone near you does. For each: how often, minutes each time, yearly cost in hours. Star the two with the best cost-to-scripting-effort ratio.
   A correct result: A five-row table with yearly hours and two starred rows.
2. 2Three dirty registers, one clean merge
   Fake three branch registers (10 rows each: one city misspelled, one amount as text, one blank row). Write merge_report.py: glob, read, CLEAN with printed before/after counts, concat, write combined.xlsx plus a per-course summary sheet.
   A correct result: combined.xlsx with two sheets, and a printed cleaning report an office could trust.
3. 3Date-stamp, log, schedule
   Date-stamp the output filename. Add logging: one OK line per run with counts; a FAILED line with the reason on exception. Schedule it daily. Confirm from its own log that it fired at the local time you intended.
   A correct result: A log with a dated OK line from a scheduled run.
4. 4Break the input on purpose
   Rename the input folder and let the schedule fire. Confirm the failure is visible in the log AND in an error signal you would actually notice — not just an absent file.
   A correct result: A FAILED log line and the error signal you received.
5. 5Email it, with a fetch that cannot kill it
   Send combined.xlsx to yourself on each run via smtplib, with the app password in .env and .env in .gitignore (verify with git status). Add one fetch from a free JSON API into the email body, with a timeout and a try/except that logs but does not stop the report.
   A correct result: The email received with the attachment; git status shows .env untracked; the fetch failure test logs and the email still goes.
6. 6Ship it to a real person
   Take your starred task that belongs to a real person (a family shop, a colleague, or your own weekly chore). Build it with all four rules — dry-run, report, log with loud failure, .env — schedule it, and show them the first successful run. Write three honest lines in the README about what it saves.
   A correct result: A second automation, scheduled, demonstrated to its owner, with three README lines.

What to hand in

The repo (with .env excluded), the log showing a scheduled OK run and a FAILED run, the received email, and the README lines for the shipped automation.

How it is marked