Backfill Excel Reports for Past Dates with Python

Sooner or later a daily report needs running for dates in the past: the job was down for a week, a bug in last month's calculation has been fixed, or a new report needs a year of history before anyone trusts it. If the script reads the clock to decide what to report, backfilling means editing the code or faking the system date. Built the right way from the start, it is a loop over dates. This page, part of Scheduling Python Excel Scripts with Cron, turns a daily report into one that can be run for any date or range, safely and repeatably.

Prerequisites

pip install pandas openpyxl

The inputs folder has one sales file per business day of September 2026, named by date — except the 17th, which is missing, as happens when an upstream export fails.

Step 1 — Report for an as-of date, never "now"

The single change that makes backfilling possible: the report function takes the date it reports on as a parameter, and every date-dependent choice — which input to read, what to filter, what to call the output, what the title says — comes from that parameter:

from datetime import date
from pathlib import Path
import pandas as pd

OUT = Path("reports"); OUT.mkdir(exist_ok=True)

def output_path(as_of):
    return OUT / f"daily_sales_{as_of:%Y-%m-%d}.xlsx"

def generate_report(as_of: date) -> Path:
    source = Path("inputs") / f"sales_{as_of:%Y-%m-%d}.xlsx"
    if not source.exists():
        raise FileNotFoundError(f"no input for {as_of}: {source}")
    df = pd.read_excel(source)
    summary = df.assign(Share=df["Revenue"] / df["Revenue"].sum())
    target = output_path(as_of)
    with pd.ExcelWriter(target, engine="openpyxl") as writer:
        summary.to_excel(writer, sheet_name="Sales", index=False, startrow=2)
        writer.sheets["Sales"]["A1"] = f"Daily sales — {as_of:%d %B %Y}"
    return target

print(generate_report(date(2026, 9, 3)).name)

Nothing in the function asks what day it is. The scheduled run passes today's date; a backfill passes others. Naming the output after the as-of date, not the date it was produced, means rerunning 3 September replaces the 3 September report rather than creating a file dated today — the most common backfill bug, which Orchestrate Excel Reports with Apache Airflow calls the logical date.

Step 2 — Accept a date or a range on the command line

One entry point serves both uses: no arguments means "today" for the scheduler, --date means one past day, and --from/--to means a range. argparse converts the strings to dates with date.fromisoformat:

import argparse

def parse_args(argv=None):
    p = argparse.ArgumentParser(description="Daily sales report")
    p.add_argument("--date", type=date.fromisoformat, help="report for one day (YYYY-MM-DD)")
    p.add_argument("--from", dest="start", type=date.fromisoformat, help="first day of a backfill")
    p.add_argument("--to", dest="end", type=date.fromisoformat, help="last day of a backfill")
    p.add_argument("--force", action="store_true", help="replace reports that already exist")
    p.add_argument("--send", action="store_true", help="deliver the reports (off by default)")
    args = p.parse_args(argv)
    if (args.start is None) != (args.end is None):
        p.error("--from and --to go together")
    return args

args = parse_args(["--from", "2026-09-14", "--to", "2026-09-18"])
print(args.start, args.end, args.force, args.send)

Delivery is off unless --send is given. The scheduled job passes it explicitly; a backfill leaves it off, so regenerating a month of reports does not email thirty old ones to the finance team. The two ways of calling the script then look like this:

# the scheduled run: today's report, delivered
python daily_sales.py --send
# a backfill: September regenerated, nothing sent
python daily_sales.py --from 2026-09-01 --to 2026-09-30

Keeping one entry point matters: a separate backfill script drifts from the scheduled one, and the history it produces stops matching what the live job would have made.

Step 3 — Build the list of dates

A daily business report needs business days; a month-end report needs month ends. Let pandas build the list, so weekends — and, with a custom calendar, holidays — are skipped the same way the scheduler skips them:

def dates_to_run(args, today=date(2026, 10, 1)):
    if args.start:
        return [d.date() for d in pd.bdate_range(args.start, args.end)]
    return [args.date or today]

print([f"{d:%a %d}" for d in dates_to_run(args)])
print([f"{d:%Y-%m-%d}" for d in pd.date_range("2026-01-01", "2026-12-31", freq="BME").date][:3], "…  (month ends)")

bdate_range gives Monday to Friday; freq="BME" gives the last weekday of each month. For holiday-aware business days, pass freq=pd.offsets.CustomBusinessDay(holidays=...), as in Run a Report on the Last Business Day of the Month.

Step 4 — Run the range: skip, force and keep going

Loop over the dates, skipping any whose report already exists unless --force was given, and catching failures so one bad day does not stop the rest. Collect the outcome of every date:

def backfill(argv):
    args = parse_args(argv)
    results = []
    for d in dates_to_run(args):
        if output_path(d).exists() and not args.force:
            results.append((d, "skipped", "already exists"))
            continue
        try:
            generate_report(d)
            results.append((d, "made", ""))
        except Exception as exc:
            results.append((d, "failed", f"{type(exc).__name__}: {exc}"))
    return pd.DataFrame(results, columns=["date", "status", "detail"])

summary = backfill(["--from", "2026-09-01", "--to", "2026-09-30"])
print(summary["status"].value_counts().to_dict())
print(summary[summary["status"] != "made"].to_string(index=False))

Twenty-two business days: twenty made, one skipped because 3 September was produced in Step 1, and one failure — the 17th has no input. The failure is recorded with its reason and the backfill carries on. Skipping existing reports makes the backfill restartable: if it is interrupted after ten days, running the same command again picks up where it stopped.

Step 5 — Rerun only what failed

Once the missing input for the 17th has been restored, rerun just the failed dates. The summary already lists them; pass each one as --date:

failed = summary.loc[summary["status"] == "failed", "date"].tolist()
pd.DataFrame({"Region": ["North", "South", "East", "West"], "Revenue": [15100, 9800, 17250, 11900]}).to_excel(
    "inputs/sales_2026-09-17.xlsx", index=False)

retry = pd.concat([backfill(["--date", str(d)]) for d in failed], ignore_index=True)
print(retry.to_string(index=False))
print(len(list(OUT.glob("daily_sales_2026-09-*.xlsx"))), "September reports on disk")

All 22 September reports now exist. Save each backfill's summary to a file — summary.to_csv(f"backfill_{start}_{end}.csv") — so there is a record of what was regenerated, when, and what failed.

Point-in-time inputs

A backfill is only as correct as its inputs. Reports that read one file per day, as here, are easy: the input for 3 September is the file for 3 September. Reports that read a current table — today's customer list, today's price list, a database table updated in place — will silently use today's reference data for every past date, and produce history that never existed. Before backfilling such a report, decide what "as of 3 September" means for each input: filter a history table by its effective dates, read an archived snapshot, or accept and document that reference data is current. Keeping a dated copy of every input on every scheduled run is the cheapest way to make future backfills exact.

Common pitfalls

• Calling date.today() inside the logic. Pass the as-of date in; only the entry point reads the clock.
• Naming output by run date. Name it by the as-of date so reruns replace, not duplicate.
• Stopping at the first failure. Catch per date and summarise at the end.
• Overwriting by default. Skip existing reports unless --force, so restarts are safe.
• Emailing during a backfill. Keep delivery off unless explicitly requested.
• Current reference data for past dates. Use snapshots or effective-dated history.

Performance notes

Dates are independent, so a long backfill can run several at once with concurrent.futures.ProcessPoolExecutor, as in Process Multiple Excel Files in Parallel with Python — but watch the load on shared databases, and take a per-date lock if a scheduled run could overlap.

Conclusion

Make the report a function of an as-of date and derive every date, filter and file name from it. Give the script --date, --from/--to, --force and an explicit --send, build the date list with bdate_range or a month-end frequency, and loop over it skipping existing outputs and catching failures per date. Rerun only the failures once fixed, keep a summary of every backfill, and make sure past dates read past inputs.

Frequently asked questions

How do I rerun a Python report for a past date? Give the report an as-of date parameter and derive every date, file name and filter from it, instead of calling datetime.now() or date.today() inside the logic. Then running it for 3 September is just running it with --date 2026-09-03.

How do I backfill a report for every business day in a range? Build the list of dates with pandas.bdate_range(start, end) — or a holiday-aware calendar — and call the report function for each date, collecting successes and failures, rather than looping in a shell script.

Should a backfill overwrite reports that already exist? Not by default. Skip dates whose output already exists, so an interrupted backfill can simply be restarted, and provide a --force option for when the point of the backfill is to replace reports produced by buggy code.

How do I stop a backfill emailing thirty old reports? Separate generating a report from delivering it, and make delivery a switch. A backfill runs with delivery off, or delivery to a test address, and a person decides which regenerated reports to send.

Related

• Up one level: Scheduling Python Excel Scripts with Cron — running reports on a schedule.
• Prevent Overlapping Runs of an Excel Report Job — per-date locks for backfills.
• Make Excel Output Reproducible — rerun, get the same file.
• Build a Command-Line Tool for Excel Reports with argparse — argparse in depth.