Logan's Analytics Bootcamp
Dashboard › Phase 4: Python for Report Automation
M4.6

Automating the End-to-End Report Pipeline

⏱ 7 hours📅 Weeks 25–26❓ 4 quiz questions
Phase 4 goal

Write Python that reads messy program data, transforms it with pandas, and generates finished Excel and Word deliverables (the same kind of document automation your coworker builds), reliably and repeatably.

Environment note: Use your organization's approved Python distribution if one exists. Some environments require specific installers, package mirrors, or a pre-approved package list. Packages used: pandas, openpyxl, python-docx, matplotlib (and optionally docxtpl). Work only with the fictional dataset while learning.

🎯 Learning objectives

Tick each one when you can actually do it.

💡 Key concepts

  • Project layout:
    status_reporter/
├── config.toml            # paths, thresholds, reporting period
├── requirements.txt
├── run_reports.py         # entry point (argparse: --period 2026-09 --program PRG-002)
├── reporter/
│   ├── load.py            # read + clean (Module 4.2)
│   ├── evm.py             # calculations (Module 4.3)
│   ├── charts.py          # matplotlib figures
│   ├── excel_out.py       # openpyxl (Module 4.4)
│   └── word_out.py        # python-docx (Module 4.5)
├── templates/
└── output/2026-09/
  • Config, not hard-coding: tomllib (standard library in Python 3.11+) to read config.toml.
  • Logging: logging module writing to output/run.log (what ran, row counts, warnings).
  • Validation gates: Stop or warn when totals don't reconcile (e.g., sum of program BAC vs. portfolio BAC), reporting periods are missing, or there are orphaned keys. A report that's wrong but looks polished is worse than no report.
  • Reading from the same source as Power BI: Point Python at the same SharePoint-synced folder (OneDrive sync) or the cleaned CSV, so all outputs share one source of truth.
  • Running it: Manually, Windows Task Scheduler, or Power Automate Desktop (if licensed and approved) calling a script. Write outputs to a OneDrive-synced SharePoint folder → a cloud flow (When a file is created in a folder) notifies reviewers or starts an approval.
  • Reading your coworker's code: Look for the entry point (if __name__ == "__main__":), follow the imports, and find where data is read, transformed, and written. Use these skills to maintain and extend shared scripts.

🛠 Hands-on exercise: Refactor into a Package

⬇ Practice data for this module
D1 programsProgram dimension (12 rows)CSVXLSX
D7 risksRisk register (80 rows)CSVXLSX

Take your scripts from 4.2–4.5 and reorganize them into the layout above. Add argparse options --period and --program (default: all), logging, and a config file with thresholds (CPI amber/red, slip days).

📦 Mini-project: Portfolio Report Run

One command (python run_reports.py --period 2026-09) produces:

  • 12 program status reports (.docx) with charts and tables
  • 1 portfolio roll-up Excel workbook (Summary, EVM, Budget, Milestones, Risks) with formatting and tables
  • 1 portfolio executive summary (.docx) listing programs with red CPI/SPI, slips > 30 days, and top risks
  • A run.log and a validation_report.csv Runtime target: under 1 minute. Then set up a Power Automate flow that emails you when Portfolio_Summary_2026-09.docx appears in the SharePoint output folder.

🧠 Self-check quiz

Answer in your own words, then check. Grade yourself honestly; your score is saved.

Q4.6.1
Why put thresholds and paths in a config file?
Write at least a few words to unlock.
Q4.6.2
What is a validation gate and why does it matter in program reporting?
Write at least a few words to unlock.
Q4.6.3
What does if __name__ == "__main__": do?
Write at least a few words to unlock.
Q4.6.4
How can a Python-generated report kick off a Power Automate flow?
Write at least a few words to unlock.
← M4.5 Word Documents with python-docxM5.1 AI Governance and Data Security for Gov/Defense Analytics →