Scheduling Python Excel Scripts with Cron

A report that runs only when you remember to run it isn't automated. On Linux and macOS, cron is the simplest way to trigger a Python script at a fixed time with no extra infrastructure. The catch is that cron runs your script in a stripped-down environment — minimal PATH, no virtualenv activation, home directory as the working directory — so a script that works in your shell can fail silently under cron. This page covers a report script that survives that environment, the crontab syntax to schedule it, and the safeguards that keep it reliable. Scheduling is the when of the broader Automating Reporting Workflows pipeline: cron fires whatever generator you have built — a multi-sheet dashboard or a filled report template — and hands the finished workbook to a delivery step.

What cron does and doesn't give you

Before writing the schedule, internalize what changes between your shell and cron:

• Cron does not source .bashrc or .profile, so it does not activate your virtualenv or pick up custom PATH entries.
• The working directory is the user's home, not the script's location, so relative paths resolve somewhere you didn't intend.
• stdout and stderr are discarded unless you redirect them, so an uncaught error vanishes without a trace.

Every recommendation below is a direct response to one of these facts: invoke the interpreter by absolute path, use absolute file paths, and log to a known file.

A report script that runs unattended

The script below stands in for your real generator — swap in a dashboard or template build once the plumbing works. It uses absolute paths, creates its own directories, logs to a dated file, and exits non-zero on failure so a monitor can detect it. It is self-contained: if the source CSV is missing it writes a small sample so the run still demonstrates the full path. Save it as /opt/reporting/generate_daily_report.py:

#!/usr/bin/env python3
"""Scheduled Excel report generator: load data, summarize, write .xlsx."""
import sys
import logging
from datetime import datetime
from pathlib import Path

import pandas as pd

# Absolute paths — cron's working directory is not the script's directory.
BASE_DIR = Path("/tmp/reporting_demo")   # use /opt/reporting in production
DATA_DIR = BASE_DIR / "data"
OUTPUT_DIR = BASE_DIR / "output"
LOG_DIR = BASE_DIR / "logs"
for d in (DATA_DIR, OUTPUT_DIR, LOG_DIR):
    d.mkdir(parents=True, exist_ok=True)

log_file = LOG_DIR / f"report_{datetime.now():%Y%m%d}.log"
logging.basicConfig(
    filename=log_file,
    level=logging.INFO,
    format="%(asctime)s | %(levelname)s | %(message)s",
    datefmt="%Y-%m-%d %H:%M:%S",
)

def main():
    logging.info("Starting daily Excel report generation.")
    try:
        source_file = DATA_DIR / "transactions.csv"
        if not source_file.exists():
            # Seed a sample so the run is self-contained for this demo.
            pd.DataFrame({
                "category": ["A", "B", "A", "C", "B"],
                "amount": [100.0, 250.5, 75.25, 40.0, 250.5],
            }).to_csv(source_file, index=False)
            logging.info("Source missing; wrote sample data.")

        df = pd.read_csv(source_file)
        logging.info("Loaded %d records from %s", len(df), source_file)

        summary = df.groupby("category", as_index=False)["amount"].sum()

        output_file = OUTPUT_DIR / f"daily_summary_{datetime.now():%Y%m%d}.xlsx"
        summary.to_excel(output_file, index=False, engine="openpyxl")
        logging.info("Report saved to %s", output_file)
    except Exception:
        logging.exception("Report generation failed.")  # captures traceback
        sys.exit(1)
    logging.info("Execution completed successfully.")

if __name__ == "__main__":
    main()

Make it executable so it can be invoked directly:

chmod +x /opt/reporting/generate_daily_report.py

Reading and writing crontab syntax

A crontab line is five time fields followed by the command:

┌───────── minute        (0–59)
│ ┌─────── hour          (0–23)
│ │ ┌───── day of month  (1–31)
│ │ │ ┌─── month         (1–12)
│ │ │ │ ┌─ day of week   (0–7, where 0 and 7 are both Sunday)
│ │ │ │ │
* * * * *  command to run

A few patterns cover most reporting needs:

0 6 * * *        # every day at 06:00
30 7 * * 1-5     # 07:30 on weekdays (Mon–Fri)
0 9 1 * *        # 09:00 on the first day of every month
0 */4 * * *      # every 4 hours, on the hour
0 22 * * 0       # 22:00 every Sunday

Edit your schedule with crontab -e and add the entry. Invoke the virtualenv's Python by absolute path — this sidesteps the fact that cron never activated the environment — and redirect output to a log so interpreter-level errors (a missing module, a syntax error) are captured before your in-script logging starts:

# Run daily at 06:00 using the venv interpreter directly
0 6 * * * /opt/reporting/venv/bin/python3 /opt/reporting/generate_daily_report.py >> /opt/reporting/logs/cron_stdout.log 2>&1

Calling /opt/reporting/venv/bin/python3 directly is more reliable than wrapping the command in bash -c 'source venv/bin/activate && ...': it guarantees the exact interpreter and installed packages, with no dependency on shell initialization.

Test it the way cron will run it

Bugs from the cron environment won't show up in your normal shell, where the venv is active and you're in the project directory. Reproduce cron's bare environment with env -i before trusting the schedule:

# Run with an empty environment, like cron does
env -i /opt/reporting/venv/bin/python3 /opt/reporting/generate_daily_report.py
echo "exit code: $?"

If it works here, it will work under cron. Then confirm the schedule is installed with crontab -l.

Prevent overlapping runs

If a run can take longer than its interval, cron will start a second copy on top of the first, and the two can fight over the same output file. The simplest guard is flock, which wraps the command in a lock the kernel releases automatically when the process exits:

# -n: fail immediately if the lock is held (skip this run rather than queue it)
0 * * * * /usr/bin/flock -n /tmp/report.lock /opt/reporting/venv/bin/python3 /opt/reporting/generate_daily_report.py >> /opt/reporting/logs/cron_stdout.log 2>&1

flock needs no code changes and is the recommended approach. If you prefer to handle it inside Python, the fcntl module offers flock on a lock file, but the shell wrapper is simpler and just as safe.

Timezones

Cron uses the system timezone, which on a server is often UTC and may not match your reporting window. Check it with timedatectl, then either translate your target time into the system zone or pin a timezone at the top of the crontab so the schedule is unambiguous:

TZ=America/New_York
0 6 * * * /opt/reporting/venv/bin/python3 /opt/reporting/generate_daily_report.py >> /opt/reporting/logs/cron_stdout.log 2>&1

The Windows equivalent

Windows has no cron; use Task Scheduler, scriptable via schtasks. The trigger fields differ but the principle is identical — call the venv's Python by absolute path against the script. The snippet below is the short version; the full Windows Task Scheduler walkthrough covers the .bat logging wrapper and running under a service account. This registers a daily 06:00 task:

schtasks /Create /SC DAILY /ST 06:00 /TN "DailyExcelReport" ^
  /TR "C:\reporting\venv\Scripts\python.exe C:\reporting\generate_daily_report.py"

Use /SC WEEKLY /D MON,TUE,WED,THU,FRI for weekdays, or /SC HOURLY /MO 4 for every four hours. Run an elevated prompt if the task needs to run whether or not the user is logged on (add /RU with a service account and /RL HIGHEST).

Cron and Task Scheduler are both OS timers that launch a fresh process per run. If you would rather keep the timetable inside one long-running Python process — no crontab, no schtasks, and portable across operating systems — reach for an in-process scheduler like APScheduler, which brings its own overlap and timezone handling.

Common failures and fixes

• ModuleNotFoundError under cron, but not in your shell. Cron didn't activate the venv. Invoke /path/to/venv/bin/python3 directly instead of python3.
• FileNotFoundError for a file that exists. A relative path resolved against the home directory. Use absolute paths for every read and write.
• The job appears to run but produces nothing, with no error. Output went to the void. Redirect cron output with >> logfile 2>&1 and keep your in-script logging to a file.
• Two copies clobbering each other. A run outlasted its interval. Wrap the command in flock -n.
• Ran at the wrong time. System timezone differs from your intent. Set TZ= in the crontab or convert your time to the system zone.

Validate after deployment

1. Logs — tail -f /opt/reporting/logs/report_YYYYMMDD.log should show load, transform, and save messages.
2. File — open the generated .xlsx and confirm the expected rows and columns are present.
3. Exit code — a non-zero exit (from sys.exit(1)) is what monitoring keys off; the cron_stdout.log redirect captures any error cron would otherwise mail or discard.

The environment a scheduled job runs in

A script that works in your terminal and fails under cron is almost always an environment problem rather than a code problem. Three differences account for nearly all of it:

# Absolute interpreter, absolute script, absolute output — and capture everything
SMTP_HOST=smtp.example.com
SMTP_USER=reports@example.com

0 6 * * 1-5 /srv/reports/.venv/bin/python /srv/reports/monthly.py >> /srv/reports/logs/cron.log 2>&1

Using the virtual environment's interpreter directly removes any need to activate it — the bin/python inside a venv already knows its own site-packages. Redirecting both streams to a log file is what makes a silent failure diagnosable; without 2>&1 the traceback goes to cron's mail and, on most servers, nowhere at all.

Testing the real thing is worth the two minutes: env -i /srv/reports/.venv/bin/python /srv/reports/monthly.py runs the script with an empty environment and reproduces most cron-only failures immediately.

Overlapping runs and how to prevent them

A job scheduled every fifteen minutes that occasionally takes twenty will eventually run twice at once, and two processes writing one workbook produce a corrupt file. A lock file is the simplest guard:

import fcntl
from pathlib import Path

def single_instance(lock_path="/tmp/monthly_report.lock"):
    handle = open(lock_path, "w")
    try:
        fcntl.flock(handle, fcntl.LOCK_EX | fcntl.LOCK_NB)
    except BlockingIOError:
        raise SystemExit("another run is still in progress — exiting")
    return handle          # keep it open for the life of the process

lock = single_instance()

Keeping the handle referenced matters: the lock is released when the file object is garbage collected, so assigning it to a variable that stays alive is what keeps the guard in force. On Windows, where fcntl is unavailable, the same effect comes from creating a lock file exclusively with open(path, "x") and removing it in a finally block.

Choosing between cron, Task Scheduler and an in-process scheduler

Cron and Windows Task Scheduler start a fresh process per run, which is exactly what you want for a report: a crash cannot poison the next execution, and the schedule survives a reboot. An in-process scheduler such as APScheduler keeps one long-running process and is the better fit when jobs share expensive state — a database connection pool, a warmed cache — or when the schedule itself changes at runtime.

The failure mode differs accordingly. A cron job that fails leaves nothing behind; an APScheduler process that dies takes every future run with it, so it needs a supervisor to restart it. For most Excel reporting, the operating system's scheduler is the simpler and more robust choice.

Make the schedule visible

A job that nobody can see the state of is a job nobody trusts. Two lines at the end of every run — one to a log, one to a small status file — give whoever asks an immediate answer:

import json
from datetime import datetime, timezone
from pathlib import Path

def write_status(name, ok, rows=0, message="", path="state/status.json"):
    Path(path).parent.mkdir(parents=True, exist_ok=True)
    status = json.loads(Path(path).read_text()) if Path(path).exists() else {}
    status[name] = {
        "finished_utc": datetime.now(timezone.utc).isoformat(timespec="seconds"),
        "ok": ok,
        "rows": rows,
        "message": message,
    }
    Path(path).write_text(json.dumps(status, indent=2))

A single JSON file covering every scheduled report answers "did the March run happen?" without opening a log, and it is trivially readable by a dashboard, a monitoring check or a colleague. The key insight is that a scheduler tells you a process exited; only the job itself can say whether it did the work.

Timezones and the hour that repeats

Cron runs on the server's local time, which means a daily 06:00 job runs twice on the day clocks go back and not at all on the day they go forward. For a report where the exact hour matters, running the server in UTC removes the problem entirely; where local time is required, scheduling outside 02:00–03:00 avoids the transition window.

The related trap is a report labelled by "today". A job that starts at 23:55 and finishes at 00:05 computes two different dates depending on where the call appears, so capture the reporting date once at the start of the run and pass it through — never call date.today() twice in one job.

Schedules drift, so measure them

Recording the start and finish time of every run — and how long it took — turns a schedule from an assumption into something observable. A job that has crept from four minutes to nineteen is on its way to overlapping with the next run, and the trend is visible months before the collision. Two timestamps per run in a status file is all the instrumentation this needs.

Schedule the check, not just the job

A second, much smaller scheduled task that reads the status file and alerts when a report has not run turns a silent failure into a notification. Without it, the failure mode nobody catches is the one where the job never started at all.

Presentation comes after the data

Any write replaces what it covers, so formatting, filters, images and charts belong in a single finishing pass that runs after the last value has been written. Splitting the job that way — build the frame, write it, then decorate the finished sheet — is what stops a style disappearing the month someone adds a to_excel call in the middle. It also gives a report one obvious place to change when the house style moves, instead of a dozen scattered blocks that have to be found first.

Frequently asked questions

Why does my script work in the shell but fail under cron with ModuleNotFoundError? Cron does not source .bashrc or .profile, so your virtualenv is never activated and python3 resolves to the system interpreter. Invoke the venv's interpreter by absolute path — /opt/reporting/venv/bin/python3 — instead of relying on PATH.

Do I need to activate the virtualenv in the crontab? No. Calling the venv's Python by absolute path is more reliable than bash -c 'source venv/bin/activate && ...' because it pins the exact interpreter and installed packages with no dependency on shell initialization.

Why does cron run my job at the wrong time? Cron uses the system timezone, which on a server is often UTC. Check it with timedatectl, then either convert your target time to the system zone or set TZ= at the top of the crontab so the schedule is unambiguous.

How do I stop two runs from overlapping if a job runs long? Wrap the command in flock -n /tmp/report.lock. The -n flag makes the second run fail immediately rather than queue, and the kernel releases the lock automatically when the process exits — no code changes needed.

How can I test a job the way cron will actually run it? Reproduce cron's bare environment with env -i /opt/reporting/venv/bin/python3 /opt/reporting/generate_daily_report.py and check the exit code. If it works under an empty environment, it will work under cron.

Conclusion

The four practices that make a cron job reliable all trace back to the same root cause — cron's stripped-down environment. Invoke the virtualenv Python by absolute path, use absolute file paths throughout the script, redirect both stdout and stderr to a log file, and test with env -i before trusting the schedule. Add flock -n for any job that can outlast its interval. Everything else is application logic.

Related

• Up: Automating Reporting Workflows — the full ingest → transform → generate → deliver pipeline this scheduling stage belongs to.
• On Windows: Run a Python Excel Script on Windows Task Scheduler — the same report script, triggered with schtasks instead of cron.
• In-process alternative: Schedule Recurring Excel Reports with APScheduler — a cross-platform scheduler that lives inside a long-running Python process, no OS timer required.
• Then deliver it: Emailing Excel Reports with smtplib — add delivery to the same job so the workbook lands in an inbox the moment it is generated.
• What the job generates: Building Multi-Sheet Excel Dashboards and Generating Excel Reports from Templates — the upstream builders whose output cron fires on a timetable, plus Exporting Excel Reports to PDF for a read-only artifact.