Automate Excel Reports with Python: A Practical Guide Using pandas and openpyxl

Creating an Excel file is only part of automating a report.

The harder work is making sure the dates are valid, the totals mean what readers think they mean, and changes to the source do not quietly corrupt the output.

For this example, a Python script turned public UK bank-holiday data into a formatted workbook.

The build produced 280 records across two worksheets, with checks that rejected a deliberately damaged source.

The situation

This report was built as a practical demonstration using public data. It was not an existing client reporting process, so there is no historical recipient list or measured manual workload.

The intended use was straightforward: give someone planning activity across UK regions a workbook they could filter, inspect and use alongside other planning information.

According to GOV.UK’s bank-holiday data, holiday events are organised into three regional groups: England and Wales, Scotland, and Northern Ireland. Each event includes a title, date and supporting fields.

That structure is useful for software, but it is not a finished spreadsheet report.

A manual approach would involve copying the regional records into Excel, arranging the columns, checking dates and producing annual counts. Each refresh would require someone to repeat or verify those steps.

No manual timing exercise was performed for this demonstration. Assigning an estimated saving would therefore add a claim the work does not support.

Instead, the build had a specific requirement: retrieve the published data, preserve the relevant source information and create a readable Excel workbook from the same process each time.

The refresh was run on demand. No recurring schedule or automatic email delivery was configured.

What I tried first

The first design decision was to keep the report close to the source.

A single table containing every regional event would have been enough to export the data. However, it would leave readers to calculate their own annual counts before answering a basic planning question: how many listed holidays does each region have in each year?

The final workbook therefore used two worksheets.

The summary presented annual counts by region. The detail sheet retained the underlying holiday records so that readers could inspect the dates and notes behind those counts.

A macro, manual template and BI dashboard were not trialled in this build. There would be little value in inventing an unsuccessful first attempt to make the story more dramatic.

Python was chosen because the task combined three distinct operations: retrieving structured data, validating records and applying a consistent workbook layout.

The scope remained small. There was no need for a database, multiple delivery services or a complex reporting application to produce this particular file.

What I built

The implementation used Python to retrieve the source, pandas to prepare the records and openpyxl to create the workbook.

A separate verification step recalculated the summary formulas and rendered the worksheets for inspection.

flowchart TD
    A[GOV.UK bank-holiday JSON] --> B[Retrieve and save source snapshot]
    B --> C[Validate required fields]
    C --> D[pandas: parse dates and check duplicates]
    D --> E[Sort records and derive reporting year]
    E --> F[openpyxl: build formatted workbook]
    F --> G[Reopen and check workbook structure]
    G --> H[Recalculate formulas and inspect rendered sheets]
    H --> I[Local Excel report]

Retrieve the source and preserve a snapshot

The script downloaded the JSON response and saved a local copy before transforming it.

Keeping that snapshot makes the build easier to investigate. If a later refresh produces different counts, the earlier input remains available for comparison.

Without a snapshot, a changed result can be difficult to explain. The difference could come from the source, the transformation or the workbook itself.

The report also recorded when the data was retrieved and included the source address above the detail table.

Those small additions help a reader distinguish a dated snapshot from a live view.

Validate the fields before formatting anything

The script required each event to contain a title, date and notes field.

An empty notes value was acceptable. A missing required field was treated as a structural problem.

This distinction matters. A blank note may be valid source data; an absent column may mean the reporting process no longer understands the input.

The following excerpt comes from the function that ran during the build:

def normalise(data):
    records = []

    for region, block in data.items():
        for event in block["events"]:
            missing = {"title", "date", "notes"} - event.keys()

            if missing:
                raise ValueError(
                    f"Missing required fields: {sorted(missing)}"
                )

            records.append({
                "Region": region,
                "Date": event["date"],
                "Holiday": event["title"],
                "Notes": event["notes"],
            })

    frame = pd.DataFrame(records)

    frame["Date"] = pd.to_datetime(
        frame["Date"],
        format="%Y-%m-%d",
        errors="raise",
    )

    if frame.duplicated(["Region", "Date", "Holiday"]).any():
        raise ValueError("Duplicate region/date/holiday records")

    return frame.sort_values(
        ["Region", "Date"]
    ).reset_index(drop=True)

The duplicate check included the region because the same holiday can legitimately appear in more than one regional calendar.

Removing duplicates by date alone would discard valid records.

Store dates as dates

The source supplied dates in year-month-day order. The script parsed that format explicitly rather than relying on automatic interpretation.

According to the pandas documentation, to_datetime supports a specified format, while errors="raise" causes invalid parsing to raise an exception.

The workbook stored the resulting values as dates and displayed them as dd mmm yyyy.

That separation preserves sorting and date calculations while giving readers an unambiguous UK-friendly display.

Formatting a text value to look like a date would not provide the same behaviour.

Make the workbook usable

The detail sheet contained five columns: region, date, holiday, notes and year.

It used an Excel table with filters, alternating row shading and frozen headings. The region and date columns remained visible when scrolling horizontally.

The summary used COUNTIFS formulas to count matching region and year records from the detail sheet.

This kept the headline figures connected to their supporting rows. The summary did not rely on someone typing totals into a separate table.

The workbook also stated that its counts represented region-specific holiday records, not unique dates across the UK.

That wording prevents an easy misinterpretation: adding the regional counts together does not produce the number of distinct UK holiday dates.

Save and verify the output

According to the openpyxl documentation, workbooks can be saved and subsequently loaded for inspection.

The build reopened the saved file and checked its row count, date values, frozen panes and table definition.

A separate verification stage recalculated the formulas, inspected the summary values and scanned for common formula errors. Both worksheets were then rendered and visually checked.

The file was delivered locally. Email distribution and scheduled refreshes remain separate work, rather than features implied by the existence of a successful export.

What broke

The live source processed successfully. The failure described here was deliberately introduced to check whether the validation worked.

A copy of the downloaded data was modified by renaming the first event’s title field to holiday_name.

The normalisation function was then run against that altered copy.

It stopped with the following message:

Missing required fields: ['title']

This was the intended outcome. The script identified the missing field before creating a report from that damaged input.

The underlying issue was not that the holiday name had disappeared. Its meaning was still recognisable to a person. The problem was that the source no longer matched the structure the code expected.

In a live workflow, the next step would be to confirm whether the renamed field represented an intentional source change. Only then should the mapping be updated and the report rebuilt.

Automatically accepting any vaguely similar field name could hide a more substantial change.

The validation check therefore did not repair the altered source. It made the incompatibility explicit.

No locked-file error, email failure or oversized worksheet occurred during this build. Those remain possible operational concerns, but they are not part of its observed history.

The experiment also exposed an important boundary: testing one renamed field does not prove that every possible source problem is handled. It demonstrates one specific failure path.

The result

The Python build produced a workbook containing 280 holiday records across two worksheets.

The downloaded snapshot covered 2019 to 2028 and included all three regional groups.

The measured runtime included source retrieval, transformation, workbook creation and the initial checks. It excluded the later formula recalculation and visual verification.

It is a single observed run, not a performance benchmark. Network conditions and the execution environment can affect future timings.

The recalculated summary agreed with the regional counts produced during the Python analysis. The formula scan found no matches for the common error values checked.

Here is an excerpt from the actual generated detail sheet:

The demonstrated outcome is a repeatable report build with traceable source records and a tested missing-field check.

It does not establish hours saved, fewer operational mistakes or successful adoption by a reporting team. Those outcomes would require evidence from repeated use.

When not to do this

For someone who only needs to look up the next bank holiday, the GOV.UK website is simpler than maintaining a reporting script.

The same judgement applies to other Excel automation projects.

A shared spreadsheet may be the better choice when the main work involves several people entering updates, discussing exceptions and maintaining a small table together.

A BI dashboard may be more appropriate when readers need an interactive view of centrally managed data, frequent refreshes and consistent measures across departments.

An existing Excel refresh process may also be sufficient when it already retrieves the source reliably and the team can maintain it.

Python becomes more useful when the report involves repeatable transformations, validation rules or formatting requirements that are awkward to manage manually.

There is also an ownership question. Someone needs to understand how the script runs, where its input comes from and what to do when a check fails.

Automating a report without providing that ownership can replace a familiar manual task with an unfamiliar dependency.

For this demonstration, another limitation is worth making explicit: the summary formulas use ranges sized to the generated records. Refreshing the source means rerunning the script. Manually appending rows is not the supported update process.

If you are doing this yourself

1. Define the report’s purpose. Establish who needs it, what decision it supports and whether a workbook is the right output.
2. Preserve the input. Keep a source snapshot and retrieval timestamp so that changes can be investigated.
3. Validate before exporting. Check required fields, date formats and meaningful record keys before applying presentation.
4. Keep values correctly typed. Store dates and numbers as usable spreadsheet values, with formatting applied separately.
5. Check the saved workbook. Verify records, formulas and layout after export, rather than relying on a successful save.
6. Exercise a failure path. Deliberately damage a copy of the source and confirm that the process fails clearly.
7. Separate generation from delivery. Add scheduling, distribution and operational ownership as explicit parts of the workflow.

This build produced a usable Excel report from public data and demonstrated that a renamed required field would stop processing. The next step towards an operational workflow would be to agree the refresh schedule, test delivery and measure the manual process it replaces. Until then, the evidence supports a working report generator with defined checks, rather than a claim about time saved.

Related reading

• Where a report sits inside a wider workflow, see Python automation for real workflows.
• When the source is a spreadsheet rather than clean JSON, start with cleaning messy Excel data in Python.
• To combine several sources before the workbook stage, see automating data workflows with APIs.
• For checks that keep recurring reports trustworthy, see designing data pipelines for reliable reporting.

Reporting that needs to run reliably rather than once? Explore automation services or get in touch.