Automating Weekly Excel Reports With Python and cron

Every Monday at six, somebody opens a database tool, runs a query, pastes the result into a template, saves it and sends it around. The job is boring and has to be exact, which makes it ideal for automation. A Python script can do the whole sequence, and cron can run it while nobody is there. The script is not the hard part. The hard part is the parts around it: where it runs, what it needs, how it fails and how anyone finds out. This post builds a weekly report from the query to the email and the schedule. It is a spoke of automating Excel work without clicking.

The shape of the job

A report script has four stages, and keeping them separate makes each testable.

1. Query. Pull the data for the reporting period from the database.

2. Transform. Summarize and shape it with pandas or SQL.

3. Write. Build the workbook, with formats and totals.

4. Deliver. Email it or copy it to a shared folder.

A function per stage, called from main, is the simplest structure. The script takes the report date as an argument, defaulting to today, so you can rerun last week's report by passing a date.

Query and transform

Let the database do the heavy work and return a result sized for a person:

from datetime import date, timedelta
import pandas as pd
from sqlalchemy import create_engine, text

def fetch(engine, start, end):
    sql = text("""
        SELECT region, category, SUM(quantity * unit_price) AS revenue, COUNT(*) AS lines
        FROM orders
        WHERE order_date >= :start AND order_date < :end
        GROUP BY region, category
        ORDER BY region, category
    """)
    return pd.read_sql(sql, engine, params={"start": start, "end": end})

def week_bounds(today):
    monday = today - timedelta(days=today.weekday())
    return monday - timedelta(days=7), monday       # last Monday to this Monday

The query uses bound parameters, never string formatting, and a half-open range (>= start AND < end) so the boundary day is never counted twice. Credentials come from the environment, not from the file: create_engine(os.environ["REPORT_DB_URL"]). Use a read-only database account for reports (giving Excel users a read-only database account).

Write the workbook

Use pandas to place data and openpyxl or XlsxWriter for formatting. A report with a summary sheet and a detail sheet:

def write_report(df, path):
    tmp = path.with_suffix(".tmp.xlsx")
    with pd.ExcelWriter(tmp, engine="xlsxwriter") as xw:
        pivot = df.pivot_table(index="region", columns="category", values="revenue",
                               aggfunc="sum", margins=True, margins_name="Total").fillna(0)
        pivot.to_excel(xw, sheet_name="Summary")
        df.to_excel(xw, sheet_name="Detail", index=False)

        book = xw.book
        money = book.add_format({"num_format": "#,##0.00"})
        summary = xw.sheets["Summary"]
        summary.set_column(1, pivot.shape[1], 14, money)
        xw.sheets["Detail"].autofilter(0, 0, len(df), len(df.columns) - 1)
        xw.sheets["Detail"].freeze_panes(1, 0)
    tmp.replace(path)                                    # atomic rename

Writing to a temporary file and renaming it means anyone looking at the folder sees either last week's file or the complete new one, never a half written one. Path.replace is atomic on the same filesystem. Name the output with the period, sales_2025-W40.xlsx, so files do not overwrite each other and the folder is a history (formatting generated workbooks covers the sheet details).

Deliver

Email is the common delivery. With the standard library:

import os, smtplib
from email.message import EmailMessage

def send(path, to, subject, body):
    msg = EmailMessage()
    msg["From"], msg["To"], msg["Subject"] = os.environ["REPORT_FROM"], to, subject
    msg.set_content(body)
    msg.add_attachment(path.read_bytes(), maintype="application",
                       subtype="vnd.openxmlformats-officedocument.spreadsheetml.sheet",
                       filename=path.name)
    with smtplib.SMTP(os.environ["SMTP_HOST"], 587) as s:
        s.starttls()
        s.login(os.environ["SMTP_USER"], os.environ["SMTP_PASSWORD"])
        s.send_message(msg)

Keep the body informative: the period, the row count, the total revenue and one line of context, so the recipient can sanity-check without opening the file. For attachments over a few megabytes, put the file on a share and send a link (emailing a scheduled Excel report from a database).

Put it together

import logging, sys
from pathlib import Path

def main(argv):
    today = date.fromisoformat(argv[1]) if len(argv) > 1 else date.today()
    start, end = week_bounds(today)
    out = Path("/srv/reports") / f"sales_{start.isocalendar().year}-W{start.isocalendar().week:02d}.xlsx"

    df = fetch(create_engine(os.environ["REPORT_DB_URL"]), start, end)
    if df.empty:
        raise SystemExit(f"no rows for {start}..{end}; refusing to send an empty report")

    write_report(df, out)
    total = df["revenue"].sum()
    logging.info("wrote %s rows=%d total=%.2f", out.name, len(df), total)
    send(out, os.environ["REPORT_TO"], f"Weekly sales {start}", f"{len(df)} rows, revenue {total:,.2f}")

if __name__ == "__main__":
    logging.basicConfig(filename="/var/log/weekly_report.log", level=logging.INFO,
                        format="%(asctime)s %(levelname)s %(message)s")
    try:
        main(sys.argv)
    except Exception:
        logging.exception("report failed")
        raise

The empty check matters. A quiet week and a broken query both return no rows, and sending a blank report on a broken query is the sort of thing that goes unnoticed for a month. Refusing to send, and failing loudly, is better.

Schedule it with cron

Edit the crontab with crontab -e:

0 6 * * 1  cd /srv/report && /srv/report/.venv/bin/python weekly_report.py >> /var/log/weekly_report.out 2>&1

The five fields are minute, hour, day of month, month and day of week, so 0 6 * * 1 is 06:00 on Mondays (cron expression tool explains any line). Four details cause most scheduled-job failures:

• A minimal environment. cron runs with a short PATH and none of your shell setup. Use the full path to the virtual environment's Python, and set environment variables in the crontab or load them from a protected file.

• The time zone. cron uses the server's time zone, which is often UTC. Check with date, and decide whether 06:00 means local or UTC.

• Relative paths. The working directory is the user's home unless you cd. Use absolute paths everywhere.

• Output. Redirect stdout and stderr to a file, or cron mails them to the local user, where nobody reads them.

On systems with systemd, a timer unit does the same job with better logging and the ability to catch up on missed runs (Persistent=true). Either is fine; choose the one the team already operates.

Prevent overlap and detect failure

Add a lock so a slow run does not collide with the next: wrap the command with flock -n /tmp/weekly_report.lock, which exits if another instance holds the lock. Then, make failure noisy: on exception, send a message to a team channel or mailbox, not just a log line. A dead-man's switch helps too. A monitoring service that expects a ping after each successful run alerts when the ping does not arrive, which catches the failure mode where cron itself did not run.

Keep a copy of every report and a row in a small table: date, rows, total, status. A comparison against the previous week's row count and total catches a query that returns plausible but wrong data, such as a join that doubled the revenue.

Test it

Test each stage with small inputs. week_bounds takes dates and returns dates, so a unit test is three lines. write_report takes a DataFrame and a path, and the test reopens the workbook and checks sheet names, the header and a total. Run the whole script against a staging database before pointing it at production (testing that a generated Excel report is correct).

When a scheduled script is not the answer

People want to explore, not receive. If recipients keep asking for different cuts, give them a dashboard or a pivot on a live connection.

The report is one query. A database view, plus a connection from Excel with refresh on open, needs no script (building a pivot table from a database query).

Nobody owns the server. A cron job on a machine with no owner will outlive its author. Put it in the team's scheduler with documentation.

The data is sensitive. Emailing a workbook copies it to mailboxes you do not control. Share through a controlled location.

See the Python documentation for smtplib and the pandas reference for ExcelWriter. To convert a small result to JSON, use the CSV to JSON converter.