Guide

Advanced Data Transformation And CleaningDeep dive

Refresh an Excel Report from a Database on a Schedule

Keep a workbook current without anyone asking: an incremental query with a watermark, an atomic write nobody can catch half-finished, a freshness stamp readers can see, and a check that the refresh actually ran.

An extract is only useful while it is current, and the failure mode of a scheduled refresh is not a crash — it is silence. The job stops being triggered, or fails at 06:00 into a log nobody reads, and the workbook on the shared drive keeps being opened and quoted for another three weeks. Nobody notices, because a stale file looks exactly like a fresh one.

This guide covers the four things that make a scheduled refresh trustworthy: querying incrementally so the run stays inside its window, writing the file atomically so nobody can open it half-written, stamping it so readers can see how old it is, and checking its age so a refresh that stops running gets noticed. It is part of Moving Data Between Excel and Databases.

Prerequisites

Bash

pip install pandas openpyxl sqlalchemy

You also need the export itself working as a one-off first; Export SQL Query Results to Excel with Python covers the query, the sheets and the formatting this guide schedules.

Step 1: Query only what changed

A full rebuild is the right default while it is fast enough — it holds no state, so it cannot drift. When the full pull stops fitting the window, switch to a watermark: the highest updated_at the last successful run saw.

Python

import json
from pathlib import Path

import pandas as pd
from sqlalchemy import create_engine, text

engine = create_engine(os.environ["REPORT_DB_URL"], pool_pre_ping=True)
STATE = Path("state/refresh.json")

CHANGED = text("""
    SELECT order_id, region, order_date, amount, updated_at
    FROM orders
    WHERE updated_at > :since
    ORDER BY updated_at
""")


def read_state():
    if STATE.is_file():
        return json.loads(STATE.read_text())
    return {"watermark": "1970-01-01T00:00:00", "rows": 0}


def fetch_changes(since):
    with engine.connect() as conn:
        return pd.read_sql(CHANGED, conn, params={"since": since},
                           parse_dates=["order_date", "updated_at"])

Two rules keep a watermark honest. Use a strictly greater-than comparison against the maximum value you actually received, not now() — clock differences between the database and the job are exactly how rows go missing. And make sure the source column is updated on every write, including deletes-as-flags; a watermark over a column that some updates skip silently drops those rows forever.

Because rows can be updated as well as inserted, the merge has to be a replace rather than an append:

Python

def merge(cached, changes, key="order_id"):
    if cached is None or cached.empty:
        return changes
    keep = cached[~cached[key].isin(changes[key])]
    return (pd.concat([keep, changes], ignore_index=True)
              .sort_values(["region", "order_date"])
              .reset_index(drop=True))

Step 2: Write the file atomically

A reader who opens the workbook while the job is halfway through writing it gets a corrupt file or a partial one. Write to a temporary name in the same directory and swap:

Python

import os
from pathlib import Path


def publish(df, target, build):
    """build(df, path) writes the workbook; this makes the swap atomic."""
    target = Path(target)
    tmp = target.with_name(f".{target.stem}.tmp{target.suffix}")
    build(df, tmp)
    os.replace(tmp, target)          # atomic within one filesystem
    return target

os.replace is atomic on POSIX and on Windows: readers see either the old file or the new one, never a partial write. The temporary file must be in the same directory as the target — a rename across filesystems is a copy, and a copy is not atomic. Prefixing the temporary name with a dot keeps it out of the way of anyone browsing the folder mid-run.

On Windows there is one extra case: if a colleague has the workbook open in Excel, the replacement raises PermissionError because Excel holds a lock on the target. Decide deliberately which behaviour you want:

Python

def publish_or_park(df, target, build, retries=3, wait=20):
    for attempt in range(1, retries + 1):
        try:
            return publish(df, target, build)
        except PermissionError:
            if attempt == retries:
                parked = Path(target).with_name(
                    f"{Path(target).stem}-{pd.Timestamp.now():%Y%m%d-%H%M}.xlsx")
                build(df, parked)
                raise RuntimeError(f"{target} is locked; wrote {parked} instead")
            time.sleep(wait)

Parking the output under a dated name means a locked file delays publication rather than losing the run's work — and the error names both files, so whoever is holding the lock knows what to do. Handle Permission Denied When Writing Excel in Python covers the lock behaviour in more detail.

Step 3: Stamp the workbook so readers can see its age

The cheapest reliability feature in reporting is a visible timestamp:

Python

from openpyxl.styles import Font


def build_workbook(df, path):
    summary = df.groupby("region", as_index=False)["amount"].sum()
    with pd.ExcelWriter(path, engine="openpyxl",
                        datetime_format="yyyy-mm-dd") as writer:
        summary.to_excel(writer, sheet_name="Summary", index=False, startrow=2)
        df.to_excel(writer, sheet_name="Detail", index=False)

        ws = writer.sheets["Summary"]
        ws["A1"] = f"Generated {pd.Timestamp.now():%Y-%m-%d %H:%M} · {len(df):,} rows"
        ws["A1"].font = Font(italic=True, color="5B6780")
        ws.freeze_panes = "A4"

startrow=2 leaves room for the stamp above the table. Put it on the first sheet, above the fold, not in a footer or a hidden metadata sheet: the point is that a reader who has had the file open for a fortnight sees the date without looking for it.

Step 4: Record state only after success

The watermark must advance only when the file has actually been published — otherwise a failure between the query and the write skips those rows on the next run:

Python

def refresh(target="reports/orders.xlsx"):
    state = read_state()
    changes = fetch_changes(state["watermark"])

    if changes.empty:
        log.info("no changes since %s", state["watermark"])
        return 0

    cached = load_cached_extract()
    merged = merge(cached, changes)
    publish(merged, target, build_workbook)
    save_cached_extract(merged)

    STATE.parent.mkdir(parents=True, exist_ok=True)
    STATE.write_text(json.dumps({
        "watermark": changes["updated_at"].max().isoformat(),
        "rows": len(merged),
        "published_at": pd.Timestamp.now().isoformat(timespec="seconds"),
    }, indent=2))
    return len(changes)

The ordering is the whole point: query, merge, publish, then record. Written the other way round — state first — a crash during the write advances the watermark past rows that were never published, and those rows are gone from every future run. The same argument applies to the cached extract, which is why it is saved after the publish rather than before.

Step 5: Alert on staleness, not just on failure

A failing job raises an error someone can route. A job that stops being triggered — a disabled task, a deleted crontab, a decommissioned server — raises nothing at all. The only signal is the file's age, so check that separately from the job itself:

Python

from datetime import datetime, timedelta


def check_freshness(path, max_age=timedelta(hours=26)):
    path = Path(path)
    if not path.exists():
        return f"{path} does not exist — the refresh has never succeeded"
    age = datetime.now() - datetime.fromtimestamp(path.stat().st_mtime)
    if age > max_age:
        return f"{path} is {age.total_seconds() / 3600:.1f}h old (limit {max_age})"
    return None

Run that from a different schedule than the refresh — a separate cron entry, a monitoring system, anything that is not the job being watched. The allowance of 26 hours for a daily job is deliberate slack: it tolerates a late run without alerting, and still fires long before anyone would quote day-old numbers as current. Wire the message into whatever the rest of your error handling and logging already uses.

Common pitfalls and gotchas

Symptom	Cause	Fix
Rows missing from the extract	Watermark set from `now()` rather than the data	Use `max(updated_at)` of the rows received
Duplicate rows after a refresh	Changes appended instead of replaced by key	Drop matching keys from the cache, then concat
Corrupt file for a reader	The workbook was written in place	Write to a temp name, then `os.replace`
`PermissionError` on Windows	Excel holds a lock on the open file	Retry, then park under a dated name
Numbers quoted weeks later as current	No visible timestamp	Stamp the summary sheet above the table
Silent stop, nobody noticed	Only the job's exit code was monitored	Alert on the output file's age
Refresh grows slower each week	Full rebuild over a growing table	Move to the watermark query
Timezone drift in the watermark	Naive local times on both sides	Store UTC, compare UTC

Performance and scale notes

Incremental refresh trades a simple job for a stateful one, so make the switch only when the numbers justify it. A full extract that takes 40 seconds is not worth complicating; one taking twenty minutes and growing is. When you do switch, keep a periodic full rebuild — weekly is common — so that any rows a bad watermark missed get corrected rather than accumulating.

Two indexes decide the cost: one on the watermark column for the incremental query, and one on the key used by the merge. Without the first, every refresh scans the whole table and the incremental version is slower than the full one it replaced.

Conclusion

A scheduled refresh is a small amount of code and four decisions. Query with a watermark taken from the data, not the clock. Publish atomically so a reader never opens a half-written file, and decide in advance what a locked file should do. Stamp the workbook where readers will see it. Record the new state only after the publish succeeds, and monitor the file's age separately, because the failure that actually happens is the job quietly not running at all.

Frequently asked questions

What happens if someone has the file open when the refresh runs? On Windows the replacement fails with a permission error, because Excel holds a lock. Write to a temporary name and replace, catch the error, and either retry or publish under a dated filename so a lock never blocks the whole run.

Full refresh or incremental? Full while it is fast enough — it has no state to get wrong. Move to an incremental watermark query when the full pull stops fitting in the window, and keep a periodic full rebuild to correct any drift.

How do readers know the numbers are current? Put a generated-at timestamp and the source period in a visible cell on the first sheet. A file without one is treated as current forever.

How do I get alerted when the refresh silently stops? Monitor the output file's age rather than the job's exit code. A job that never runs produces no failure — only a file that quietly gets older.

Up to the parent guide:

Moving Data Between Excel and Databases — the export and load this schedules.

Related guides:

Export SQL Query Results to Excel with Python — the query and formatting run on each cycle.
Schedule Recurring Excel Reports with APScheduler — the trigger side of the same job.
Handle Permission Denied When Writing Excel in Python — what to do when a reader has the file open.
Validate an Excel Report Before Sending It — checks worth running between the build and the swap.