Automate a Weekly Report with Python and AI

By the end of this guide you will have one script that reads last week's rows out of your CSV files, calculates every number itself, refuses to run if the data looks empty or out of date, asks a model for three short paragraphs of commentary, and writes a finished report.md to disk. On a laptop with the packages already installed it takes about forty minutes to assemble and under ten seconds to run. After that, one line in your crontab turns Monday morning into something that happened while you were asleep.

The idea that makes it reliable is a split you should hold onto: Python owns the arithmetic, the model owns the sentences. A language model is a text predictor. Ask it to add a column of figures and it will produce something that looks like a sum, confidently, and occasionally be wrong by enough to embarrass you in front of your team. Ask it to turn {"revenue": 18420.5, "change_pct": -6.2} into "Revenue slipped to 18,420.50, about six percent below last week" and it is doing exactly what it is good at.

This is one guide in Automating Repetitive Tasks with Python and AI, which covers the general read-decide-write-schedule shape. Here you are applying that shape to a recurring report, where the accuracy stakes are higher than usual because someone will make a decision based on what your script says.

Prerequisites

You need Python 3.10 or newer inside a virtual environment — a private folder of packages that belongs to this project alone. If you have not made one, follow Create a Python Virtual Environment for AI first, then come back.

python -m venv .venv
source .venv/bin/activate        # Windows: .venv\Scripts\activate
pip install "pandas>=2.2" "openai>=1.40" "python-dotenv>=1.0"
pip freeze > requirements.txt

You also need an OpenAI API key. Put it in a file called .env beside your script:

OPENAI_API_KEY=sk-your-key-here

Then add that file to .gitignore before you commit anything, because a key in a public repository gets found and spent by strangers:

echo ".env" >> .gitignore

Finally, you need data. This guide assumes two files in a data/ folder: orders.csv with columns order_date, product and amount, and signups.csv with a signup_date column. Your real column names will differ — change them in one place at the top of the script and everything downstream follows. If your exports arrive with stray whitespace, blank rows or inconsistent dates, run them through Cleaning CSV Data with Pandas for AI before you wire them in here.

Step 1 — Pull just this week's rows

Almost every reporting bug traces back to a fuzzy window. "Last week" has to mean one exact range of dates, applied identically to every file, and you need the previous week too so you can show change. Write one loader that takes a filename, a date column and a start and end, and reuse it for both files.

from datetime import date, timedelta
from pathlib import Path

import pandas as pd

DATA_DIR = Path("data")


def load_window(filename: str, date_col: str, start: date, end: date) -> pd.DataFrame:
    """Read one CSV and keep only the rows dated on or after start and before end."""
    frame = pd.read_csv(DATA_DIR / filename, parse_dates=[date_col])
    in_window = (frame[date_col] >= pd.Timestamp(start)) & (frame[date_col] < pd.Timestamp(end))
    return frame.loc[in_window].copy()


if __name__ == "__main__":
    end = date.today()
    start = end - timedelta(days=7)
    prev_start = start - timedelta(days=7)

    orders = load_window("orders.csv", "order_date", start, end)
    prev_orders = load_window("orders.csv", "order_date", prev_start, start)
    signups = load_window("signups.csv", "signup_date", start, end)

    print(f"{len(orders)} orders, {len(signups)} signups, {start} to {end}")

Two details are doing real work here. parse_dates=["order_date"] converts that column from text into real timestamps as the file is read, so date comparisons behave; without it you are comparing strings and the filter silently returns nonsense. And the window is half-open — greater than or equal to start, strictly less than end — so a row dated exactly on the boundary lands in one week only, never both.

The whole script is a pipeline: files in on the left, a finished report out on the right, with a single API call in the middle. Here is the shape you are building toward.

Step 2 — Compute every number in Python

This is the step people skip, and it is the step that makes the report trustworthy. Take the two filtered frames and produce one flat dictionary of plain Python values: integers, floats and strings, nothing clever. That dictionary is the single source of truth for both the tables you render and the prose the model writes.

def build_facts(orders, prev_orders, signups, start: date, end: date) -> dict:
    """Turn the filtered frames into one flat dictionary of finished numbers."""
    revenue = float(orders["amount"].sum())
    prev_revenue = float(prev_orders["amount"].sum())

    if prev_revenue > 0:
        change_pct = round((revenue - prev_revenue) / prev_revenue * 100, 1)
    else:
        change_pct = None  # nothing to compare against

    by_product = orders.groupby("product")["amount"].sum().sort_values(ascending=False)

    return {
        "week_start": start.isoformat(),
        "week_end": end.isoformat(),
        "order_count": int(len(orders)),
        "revenue": round(revenue, 2),
        "prev_revenue": round(prev_revenue, 2),
        "revenue_change_pct": change_pct,
        "average_order_value": round(revenue / len(orders), 2) if len(orders) else 0.0,
        "new_signups": int(len(signups)),
        "top_products": [
            {"product": str(name), "revenue": round(float(total), 2)}
            for name, total in by_product.head(3).items()
        ],
    }

Notice the float() and int() calls. pandas returns its own numeric types, and those do not convert cleanly to JSON, which is how you will hand the figures to the model in Step 4. Casting here saves you a confusing TypeError later. The change_pct branch matters too: a first-ever run has no previous week, and dividing by zero would end the script before it ever printed anything useful.

Keep the dictionary small. Three top products is plenty; twenty would bloat the prompt, cost more tokens, and give the model more chances to fixate on something trivial. If you want to see what that costs in practice, Estimate OpenAI API Costs with Python shows how to price a call before you send it.

Step 3 — Refuse to send a broken report

An automated report that quietly publishes zeros is worse than no report, because people believe it. The most common failure is not a crash — it is an upstream export that did not run, leaving you with a stale or empty file that pandas reads perfectly happily. So put a guard between loading and generating, and make it fail loudly.

Two checks cover nearly every real incident. First, does the window contain a plausible number of rows? Second, is the newest row in the whole file recent enough that the export must have run? Both are cheap, and both run before you spend a cent on the API.

class ReportBlocked(Exception):
    """Raised when the input data is too empty or too old to report on."""


def check_inputs(window_rows: int, newest_row: pd.Timestamp | None,
                 today: date, min_rows: int = 5, max_age_days: int = 2) -> None:
    if window_rows < min_rows:
        raise ReportBlocked(f"only {window_rows} rows in the window (expected at least {min_rows})")
    if newest_row is None or pd.isna(newest_row):
        raise ReportBlocked("no usable dates in the source file")
    age_days = (today - newest_row.date()).days
    if age_days > max_age_days:
        raise ReportBlocked(f"newest row is {age_days} days old — the export looks stale")


if __name__ == "__main__":
    all_orders = pd.read_csv(DATA_DIR / "orders.csv", parse_dates=["order_date"])
    try:
        check_inputs(len(orders), all_orders["order_date"].max(), date.today())
    except ReportBlocked as problem:
        print(f"Report skipped: {problem}")
        raise SystemExit(1)

Exiting with status 1 is deliberate. Schedulers treat a non-zero exit as a failed run, so a blocked report shows up in your logs or your monitoring instead of vanishing. During your first few weeks, tune min_rows to something just below your quietest real week — set it too high and a genuinely slow week gets blocked, too low and an empty export slips through.

Step 4 — Ask the model for prose, never for maths

Now the fun part, and the part where a strict prompt pays for itself. You send the facts dictionary as JSON and ask for a fixed three-paragraph structure. The system prompt — the standing instruction that applies before the user message — carries all the rules, so the request itself stays a single line of data.

import json
import os

from dotenv import load_dotenv
from openai import OpenAI

load_dotenv()  # reads .env, which is listed in .gitignore
client = OpenAI(api_key=os.getenv("OPENAI_API_KEY"))

SYSTEM_PROMPT = """You write the commentary section of an internal weekly business report.

You receive a JSON object of figures that have already been calculated for you.

Rules:
- Never calculate, estimate, round or invent a number. Only repeat values present in the JSON.
- If a value is null, say the comparison is unavailable rather than guessing.
- Write exactly three short paragraphs, in this order:
  1. The headline result for the week.
  2. What moved, and which products drove it.
  3. One specific thing to watch next week.
- Plain sentences only: no greeting, no sign-off, no bullet points, no headings.
"""


def write_narrative(facts: dict) -> str:
    response = client.chat.completions.create(
        model="gpt-4o-mini",
        temperature=0.3,
        messages=[
            {"role": "system", "content": SYSTEM_PROMPT},
            {"role": "user", "content": json.dumps(facts, indent=2)},
        ],
    )
    return response.choices[0].message.content.strip()

temperature=0.3 keeps the wording steady from week to week — a report that reads the same way every Monday is easier to skim than one that reinvents its voice. What Is Temperature in an LLM API? explains the dial in full. The numbered structure in the system prompt is the other half of that consistency; for more patterns like it, see Write System Prompts that Control Output Format.

One habit worth building: read the narrative once a month with the JSON open beside it and check that every figure in the prose appears in the dictionary. Models are good at following the "never invent a number" rule and not perfect at it. Because your tables come from the dictionary directly, a drifting sentence is visible immediately — the prose and the table will disagree.

Step 5 — Render the file and put it on a schedule

The report is a template with holes in it. Python's built-in string.Template is enough; you do not need a templating library for one file. Fill the numbers from the facts dictionary and the paragraphs from the model, write to disk with a dated filename, and you have an archive as a side effect.

from pathlib import Path
from string import Template

REPORT = Template("""# Weekly report: $week_start to $week_end

| Metric | This week | Last week |
| --- | --- | --- |
| Revenue (USD) | $revenue | $prev_revenue |
| Orders | $order_count | - |
| Average order (USD) | $average_order_value | - |
| New signups | $new_signups | - |

Change vs last week: $revenue_change_pct percent

## Commentary

$narrative
""")


def render_report(facts: dict, narrative: str) -> Path:
    fields = dict(facts)
    if fields["revenue_change_pct"] is None:
        fields["revenue_change_pct"] = "no comparable"  # first run, nothing to compare
    out_dir = Path("reports")
    out_dir.mkdir(exist_ok=True)
    path = out_dir / f"report-{facts['week_end']}.md"
    path.write_text(REPORT.substitute(**fields, narrative=narrative), encoding="utf-8")
    return path

string.Template treats every $ as the start of a placeholder, which is why the currency is written as "USD" in a header rather than as a symbol. If you want a literal dollar sign in the output, write $$. To produce HTML instead of markdown, change the template body and the file extension — the rest of the script does not care. If the report needs to land in an inbox rather than a folder, Generate Email Newsletters with Python and AI covers the sending half.

The last piece is the schedule. On macOS or Linux, run crontab -e and add one line that runs at 07:00 every Monday, using the virtual environment's Python by absolute path and appending everything to a log:

0 7 * * 1 /home/you/reports/.venv/bin/python /home/you/reports/weekly_report.py >> /home/you/reports/run.log 2>&1

Absolute paths are not optional. cron starts with almost no environment, so a bare python or a relative data/orders.csv will fail in ways that are painful to debug. Either use full paths everywhere or begin the script with os.chdir(Path(__file__).parent). On Windows, Task Scheduler does the same job with a weekly trigger pointing at .venv\Scripts\python.exe. If the data is reachable from the internet and you would rather not keep a machine awake, Schedule Python AI Jobs with GitHub Actions runs the same script on a hosted timer and keeps a log of every execution.

Compute it or generate it

When you add a new section to the report, decide which side of the line it falls on before you write any code. The rule of thumb: anything a spreadsheet formula could produce belongs in pandas, and anything requiring a sentence belongs to the model.

Troubleshooting

• KeyError: 'order_date' — the column name in your CSV is not what the script expects, often because of a trailing space or a byte-order mark from Excel. Print list(frame.columns) to see the real names, then add frame.columns = frame.columns.str.strip() right after read_csv.
• TypeError: '>=' not supported between instances of 'str' and 'Timestamp' — you forgot parse_dates, so the date column is still text. Add parse_dates=["order_date"] to read_csv, or convert afterwards with pd.to_datetime.
• ValueError: Invalid placeholder in string: line 4, col 18 — a bare $ in the template that is not a valid placeholder name. Double it to $$ for a literal dollar sign, or rename the placeholder to letters and underscores only.
• TypeError: Object of type int64 is not JSON serializable — a pandas number slipped into the facts dictionary. Wrap it in int() or float() in build_facts; that is exactly what those casts in Step 2 are preventing.

If a traceback still looks impenetrable, Read a Python Traceback in Five Minutes shows how to find the one line that matters. Errors coming back from the API rather than from your own code — a rejected key or a rate limit — are covered in Fix the 401 Unauthorized Error in OpenAI Python and Fix the 429 Rate-Limit Error in Python.

When to use this vs. alternatives

• Versus a dashboard tool. A dashboard is better when people want to explore the numbers themselves and slice them different ways. This script is better when the same five figures matter every week and someone has to say what they mean — a dashboard will never write "signups held up despite the revenue dip".
• Versus writing the report by hand. Keep writing by hand while the format is still changing; you learn what belongs in it by drafting a few. Automate once the structure has settled and you find yourself copying last week's document as a starting point.
• Versus giving the model the raw CSV. Tempting, and fine for a one-off exploration, but not for a recurring report. Long files eat tokens, arithmetic done inside the model is unverifiable, and you lose the guard that stops a stale export from becoming a published number.

Once this is running, the same skeleton — load, compute, guard, generate, render, schedule — carries straight over to other weekly chores. Rename and Sort Files with Python and AI applies it to a messy downloads folder, and Python Script to Automate Email Sorting applies it to an inbox. Back to Automating Repetitive Tasks with Python and AI.

Related guides

• Cleaning CSV Data with Pandas for AI — fix messy exports before they reach your report.
• Write System Prompts that Control Output Format — make the commentary come back in the same shape every week.
• Schedule Python AI Jobs with GitHub Actions — run the script on a timer without leaving a laptop on.
• Estimate OpenAI API Costs with Python — price a weekly run before you commit to it.
• Rename and Sort Files with Python and AI — the same automation skeleton applied to a folder of files.