Automating & delivering reports
Logging, testing & packaging
Logs, retries, pre-send checks, pytest, config files and shipping a script to someone else.
Start with the full guides
The long-form treatment of logging, testing & packaging, with the reasoning the shorter pages assume.
- GuideError Handling and Logging in Excel AutomationMake scheduled Excel jobs survivable — structured logging, retries with backoff, atomic writes, permission and lock errors, output validation, exit codes and alerts that mean something.
- GuideTesting and Packaging Excel Automation ScriptsTurn a working report script into something a colleague can run: separate the transform from the I/O, test the workbook you actually wrote, add a command line, move settings into a config file, and ship it.
Step-by-step how-tos
One task each, with code you can copy, run and adapt.
- How-toDetect a Scheduled Report That Did Not RunCatch the failure that error handling cannot: a report job that never started. Check each report's output against its schedule — the last time it should have run plus a grace period — and send heartbeat pings to an external monitor that alerts when a ping is late, with start, success and failure signals and a freshness sheet for humans.
- How-toIgnore Excel Lock Files When Processing a FolderStop a folder-processing job from crashing on the files Excel and LibreOffice leave next to open workbooks: recognise ~$ owner files, .~lock files and temporary save files, skip half-copied workbooks that are not yet valid zip files, notice when a workbook is open in someone's Excel, and report what was skipped and why.
- How-toLog Python Excel Script Output to a FileReplace print() with a logging setup that survives a scheduled run — file and console handlers, rotation, what to record at each stage, and capturing tracebacks and warnings.
- How-toMake an Excel Report Job Safe to RerunDesign a scheduled Excel report job so that running it again — after a crash, a fix or by accident — gives the same result and no duplicate side effects: take the period as a parameter instead of reading the clock, name outputs by period, write files atomically, replace a period's database rows in one transaction, record sends in a ledger, and recover cleanly from a crash half-way through.
- How-toPrevent Formula Injection in Excel Exports from PythonUser-supplied text that starts with "=" becomes a live formula when an export writes it to Excel: openpyxl, xlsxwriter and pandas all do this by default. Find which writers turn strings into formulas, store data as text in .xlsx files, neutralise dangerous prefixes in CSV exports, and test that no data cell ever becomes a formula.
- How-toPrevent Overlapping Runs of an Excel Report JobStop a scheduled report from starting while the previous run is still writing the workbook. Take an operating-system lock at the start of the job, skip or wait when another run holds it, record who holds it, use one lock per report so unrelated jobs still run in parallel, and choose a different lock when jobs run on more than one machine.
- How-toQuarantine Bad Excel Files in a Batch JobKeep one broken workbook from stopping a batch job that processes a folder of Excel files: claim each file, process it in isolation, move failures to a dated quarantine folder with a JSON note explaining what went wrong, keep going with the rest, summarise the run, and let people fix and resubmit files by moving them back.
- How-toRead Untrusted Excel Files Safely in PythonAccepting .xlsx uploads from customers or the web? Check what the file really is, reject decompression bombs before parsing, make sure openpyxl uses defusedxml, never trust cached formula results, spot macros and external links, and parse in a separate process with memory and time limits.
- How-toRetry a Failed Excel Report Job in PythonRetry the failures worth retrying — exponential backoff with jitter, a decorator you can reuse, per-step retry boundaries, idempotent writes and a cap that ends in a real alert.
- How-toSend an Alert When an Excel Report Job FailsMake a failing Excel report job tell someone — a top-level wrapper that turns any exception into an alert with the error, host and traceback, notification channels that cannot hide the original failure, one alert per failure streak plus reminders and a recovery notice, and a heartbeat check for jobs that never ran at all.
- How-toSet Timeouts on Steps of an Excel Report JobStop a scheduled Excel report job from hanging forever: give every network call, SMTP connection and subprocess an explicit timeout, run a step that has no timeout option of its own in a child process that can be killed, and put an overall limit on the whole job in the scheduler — with each timeout tested against a deliberately slow server and command.
- How-toSkip a Report Run When the Input File Has Not ChangedStop a scheduled Excel report from rebuilding and resending the same numbers — fingerprint the input workbook so re-saves without real changes are ignored, check file metadata first to avoid reading large files, record the fingerprint only after a successful run, skip files that are still being written, and alert when the input goes stale.
- How-toTrack Run History and Step Timings of a Report JobRecord every run of a scheduled Excel report job and how long each step took in a small SQLite table, using a context manager around each step; then turn the history into a run-history workbook that shows failures, duration trends and the step that is slowing down, and flags runs much slower than usual before they hit a deadline.
- How-toValidate an Excel Report Before Sending ItThe last gate before delivery — check the file exists and is a real workbook, the sheets and rows are there, totals are plausible, and this run agrees with the last one.
- How-toWrite Excel Files Atomically in PythonNever leave a half-written workbook behind: why saving straight over report.xlsx can corrupt it when a script crashes or a reader opens it mid-write, the write-to-temp-then-os.replace pattern as a reusable context manager for openpyxl, pandas and XlsxWriter, validating before the swap, keeping permissions, and what changes on Windows, network shares and sync folders.
- How-toBlock Large or Sensitive Excel Files with a Pre-commit HookKeep real data out of an Excel automation repository: a pre-commit hook in Python that checks every staged spreadsheet before the commit is made — rejecting files over a size limit, Office lock files and workbooks whose cells contain email addresses, card numbers or National Insurance numbers — with an allowlist for synthetic test fixtures, wired up with the pre-commit framework and tested in a scratch repository.
- How-toBuild a Command-Line Tool for Excel Reports with argparseReplace the constants at the top of your report script with real arguments: paths, month, dry-run and verbose flags, subcommands, and exit codes a scheduler can act on.
- How-toCompare Excel Output to a Golden File in TestsTest a whole generated workbook at once by comparing it with an approved "golden" snapshot — not the .xlsx bytes, which change on every save, but a readable text description of every cell's value, number format and style. Includes masking volatile cells, a clear diff when something changes and a --update-golden option for intended changes.
- How-toCreate Excel Test Fixtures in Memory for pytestStop committing sample spreadsheets to the test folder. Build each test workbook in memory with openpyxl — including the awkward shapes real exports have, such as title rows above the header, dates and numbers stored as text, error cells, formulas without results and formatted empty rows — and run one test across all of them with pytest.mark.parametrize.
- How-toDiff Excel Files in Git with a textconv DriverMake git diff show what changed inside a workbook instead of "Binary files differ": a small Python converter that prints every cell as sheet, address, value and formula, a .gitattributes entry and a diff driver that use it, readable diffs of templates and golden files in pull requests, and the limits of the approach.
- How-toGenerate Realistic Test Data for Excel Reports with FakerBuild believable, reproducible test workbooks for Excel report code with Faker and pandas: seeded generators, locale-specific names and addresses, a custom provider for your own codes, related sheets with valid foreign keys, deliberate messiness such as blanks, stray spaces and text dates, unique values, and a pytest fixture that writes the file.
- How-toKeep Excel Report Settings in a Config FileMove paths, recipients, thresholds and column mappings out of the code: a layered loader with defaults, a TOML or JSON file, environment variables for secrets, and validation that fails at startup.
- How-toMake Excel Output Reproducible in PythonRun the same report twice on the same inputs and get byte-identical workbooks. Find what changes between runs — the created and modified timestamps inside the file, zip entry dates, unsorted inputs, sets, random numbers and the clock — fix each one for xlsxwriter and openpyxl, and prove it with a hash check in a test.
- How-toPackage a Python Excel Script as an EXE with PyInstallerShip a report script to someone with no Python: a one-file build, the hidden imports pandas and openpyxl need, keeping the config and template outside the bundle, and how to find your own files at runtime.
- How-toSet Up an Excel Automation Project with uvTurn a loose report script into a reproducible project with uv: a pinned Python version, dependencies and a lock file, a console command for the report, a test that runs with one command, locked installs on servers and in CI, and a scheduler entry that always runs the exact environment that was tested.
- How-toStore Credentials for Excel Report Scripts SafelyGet database passwords, SMTP logins and API keys out of report scripts: environment variables with a clear error when one is missing, a .env file for development only, the operating system keyring on desktops, a cloud secret manager for servers, identities that need no secret at all, and logs that never print a password.
- How-toStore Excel Files in Git with Git LFSKeep report templates, reference workbooks and golden test files in a git repository without bloating it: track .xlsx and .xlsm with Git LFS so git stores a small pointer and the workbook lives on the LFS server, see what is actually committed, keep readable diffs with a textconv driver, lock binary files so two people cannot edit the same template, and move existing workbooks into LFS.
- How-toTest Excel Cleaning Code with HypothesisLet Hypothesis find the inputs your Excel code breaks on: property-based tests for a text-cleaning function, a round-trip property for writing and reading workbooks that uncovers vanishing empty strings, control characters returned as _x001F_ and floats cut to 15 significant digits, DataFrame strategies for cleaning steps, and settings for CI.
- How-toTest Excel Code Against Several Library Versions with noxFind out before your users do whether your Excel automation still works with the next pandas or openpyxl release, and still works with the oldest versions you claim to support: a nox session matrix over Python and pandas versions, uv for fast environments, warnings turned into errors, and a lowest-versions session that checks the minimums in pyproject.toml.
- How-toTest Excel Output with pytestAssert on the workbook your script actually wrote: build fixtures in tmp_path, check sheets, headers, totals and number formats, and compare floats without false failures.
- How-toTest That Excel Charts Were Written CorrectlyWrite pytest tests for the charts a report script produces: since openpyxl does not load charts back, read them straight from the chart XML inside the .xlsx — chart type, title, series names, value and category ranges, axis settings and which sheet and cell each chart sits on — compare them with expectations or a stored snapshot, and catch the regressions that leave a chart empty, pointing at the wrong column, or with hidden axes.
- How-toLint and Type-Check an Excel Automation ProjectCatch Excel-script bugs before they run — ruff for the mistakes that hide in report code (mutable defaults, inplace pandas calls, bare except, naive timestamps) and mypy with pandas-stubs and types-openpyxl for the ones that crash at runtime, such as read_excel returning a dict, wb.active possibly being None and cell values of mixed type. Every message shown was produced by the real tools.
- How-toPMT, NPV, IRR and XIRR Excel Functions in PythonReproduce Excel's financial functions in Python with numpy-financial: loan payments with PMT, FV, NPER and RATE, project values with NPV and IRR, and dated cash flows with XNPV and XIRR. Every result here matches the spreadsheet — including the NPV difference that catches almost everyone.
- How-toCache API Responses When Building Excel ReportsStop a report script from calling the same API hundreds of times while you develop it or rerun it: a transparent HTTP cache with requests-cache and expiry rules per endpoint, a hand-rolled file cache for responses that are not plain GETs, caching only successful responses, forcing a refresh for the production run, and recording in the workbook how old the cached data was.
- How-toHandle Microsoft Graph Throttling in Excel AutomationKeep Excel Online automation running when Microsoft Graph pushes back — honour Retry-After on 429 and 503 responses, back off with jitter on 504s, never blindly retry non-idempotent writes, run one worker per workbook, and handle throttled requests inside JSON batches. Includes a retry client you can test without a network.
- How-toBuild a Month-End Close Checklist TrackerTurn a month-end close checklist into a tracker that updates itself: express each task's deadline as a working day relative to month end, convert those to real dates with a holiday calendar, read the team's status updates, flag overdue tasks and tasks blocked by unfinished dependencies, measure progress against plan day by day, and write a colour-coded Excel tracker.
- How-toQueue and Retry Failed Report EmailsStop losing report emails when the mail server has a bad moment: write each message to a small SQLite outbox instead of sending it inline, deliver from the outbox with per-recipient status, retry temporary SMTP failures with backoff, mark permanent rejections as failed instead of retrying them forever, and never send the same report twice when the job is rerun.
- How-toTest Report Emails with a Local SMTP ServerCheck what your report job actually sends — recipients, subject, body and the attached workbook — without emailing anyone. Run a throwaway SMTP server with aiosmtpd, point the job at it through configuration, capture every message, and assert on it in pytest, including the Bcc recipients that never appear in the headers.
- How-toCheck a PDF Export for Cut-Off ColumnsCatch the PDF exports nobody wants to receive before they are sent: detect columns that spill onto extra pages, numbers printed as ### because a column is too narrow, and text shrunk below a readable size by fit-to-page, by reading the PDF back with pdfplumber, then fix the page setup with openpyxl and confirm the check passes.
- How-toName, Version and Archive Generated Excel ReportsStop overwriting report_final_v2.xlsx: a sortable naming scheme for generated Excel reports, a content fingerprint that ignores the timestamps Excel files carry so unchanged reruns are skipped, numbered versions with a manifest, a retention rule that archives old periods and keeps final versions, and the same ideas on Amazon S3 with object versioning.
- How-toBackfill Excel Reports for Past Dates with PythonRegenerate a daily or monthly report for a range of past dates — after a bug fix, a missed week or a new report that needs history. Make the report take an as-of date instead of reading the clock, give it a date-range command line, skip what already exists, keep going past failures, rerun only what failed, and never email anyone during a backfill.
- How-toOrchestrate Excel Reports with PrefectTurn a report script into a Prefect flow: tasks for extract, build and publish, automatic retries for the flaky steps, a report date as a parameter so reruns and backfills are one command, failure hooks that alert someone, and a cron schedule served by a long-running process or a work pool — all testable locally.
- How-toRun a Python Excel Report in DockerContainerise a reporting job: a small Dockerfile, pinned dependencies, timezone and locale, mounted volumes for input and output, secrets, and scheduling the container.
- How-toRun an Excel Report as a Kubernetes CronJobSchedule a containerised Python Excel report on a Kubernetes cluster: a CronJob manifest with a time zone, no overlapping runs, a deadline for late starts, retries and a hard time limit, credentials from a Secret, output to object storage, clean-up of finished jobs, a strict schema check before applying, and manual test runs.
- How-toWait for Upstream Data Before Running a ReportStop a scheduled Excel report from running on yesterday's data: before building, check that every input is ready — an export file dated today and finished, a marker file, a database load-status row — poll with growing intervals until a deadline, and then either run, or send a clear "data is late" notice instead of a wrong report. Tested with a simulated clock.
- How-toProfile a Slow Excel Report Script in PythonFind out where a slow report actually spends its time before changing anything. Time each stage with a small context manager, drill into the slowest one with cProfile and pstats, read the output correctly, and fix the usual culprits — per-cell styling in openpyxl, slow reads, row-by-row writes — measuring the gain each time.
- How-toTest win32com and xlwings Code Without ExcelUnit-test Python code that drives Excel through win32com or xlwings on machines without Excel — Linux CI runners, Macs, containers: keep the Excel calls in a thin layer, pass the application in, replace it with small fakes that record what was done, check that workbooks are closed and Excel quit even after errors, stub the win32com import for older code, and keep real-Excel tests for Windows only.
Follow it through a track
Prefer an ordered route? These tracks cover logging, testing & packaging along the way, in the order that builds on itself.
Related topics
Subjects that keep turning up on the same pages as this one, most overlap first.
Logging, testing & packaging is part of Automating & delivering reports.