How to Automate Weekly Reports with Python and AI (Complete 2026 Tutorial)

Every Monday morning, millions of professionals face the same painful ritual: opening last week's spreadsheets, copying numbers into a template, writing a narrative summary, formatting charts, and emailing a report — all before their second coffee. If you've been stuck in this loop, this tutorial is your way out. Learning to automate weekly reports with Python is one of the highest-ROI skills you can add to your professional toolkit right now, and with modern AI APIs, you can generate the narrative analysis automatically too.

In this step-by-step guide, I'll walk you through building a complete automated reporting pipeline from scratch. By the end, you'll have a working Python script that collects data, calculates KPIs, generates AI-powered written insights via GPT-4, assembles a polished Excel or HTML report, emails it to your stakeholders, and runs itself every week without you touching it.

Let's build something genuinely useful.

The Manual Reporting Problem (And Why Python Fixes It)

Before we write a single line of code, let's be honest about what manual weekly reporting actually costs you.

Typical manual reporting workflow:

45–90 minutes pulling data from multiple sources
20–30 minutes calculating KPIs and sanity-checking numbers
30–60 minutes writing the narrative summary and commentary
15–20 minutes formatting the report and creating charts
10 minutes emailing it to the right people
Total: 2–4 hours per week, every week

That's 100–200 hours per year on a single report. And because it's manual, it's also error-prone — a miscopied cell, a wrong date range, an outdated formula. Stakeholders lose trust, and you lose your Friday afternoon.

The automated alternative:

Initial setup: 3–5 hours (you're doing that now)
Weekly execution: 0 minutes (it runs itself)
Total ongoing time: ~0 hours per week

The Python automation tutorial approach I'll show you handles a realistic business reporting scenario: a weekly sales performance report that reads transactional data, computes KPIs, asks GPT-4 to write the executive summary, builds a formatted Excel file, and emails it every Monday morning.

Step 1: Setting Up Your Environment

First, let's get your environment ready. I'm assuming you have Python 3.9+ installed. If you're on an older version, upgrade — several libraries we're using have 3.9+ dependencies.

Install Required Libraries

python

1pip install pandas openpyxl openai schedule python-dotenv jinja2 sendgrid

Here's what each package does in our pipeline:

Library	Role
`pandas`	Data loading, cleaning, and KPI calculation
`openpyxl`	Building and formatting Excel reports
`openai`	Connecting to GPT-4 for AI-generated narrative
`schedule`	Running the script on a weekly cron-like schedule
`python-dotenv`	Keeping API keys out of your code
`jinja2`	Templating HTML reports (optional alternative to Excel)
`sendgrid`	Reliable transactional email delivery

Project Structure

Set up your project folder like this:

Prompt

weekly-report-bot/
├── .env                  # API keys (never commit this)
├── main.py               # Entry point / scheduler
├── data_collector.py     # Data loading and KPI logic
├── ai_analyst.py         # OpenAI integration
├── report_builder.py     # Excel/HTML report assembly
├── email_sender.py       # Email delivery
├── templates/
│   └── report.html       # Optional HTML template
└── data/
    └── sales_data.csv    # Your input data

Configure Your `.env` File

bash

1OPENAI_API_KEY=sk-your-openai-api-key-here
2SENDGRID_API_KEY=SG.your-sendgrid-api-key-here
3REPORT_RECIPIENTS=boss@company.com,team@company.com
4FROM_EMAIL=reports@yourcompany.com

Step 2: Data Collection and KPI Calculation with Pandas

This is where your report gets its raw material. In a real-world scenario, you might pull from a database, an API, or a folder of CSV exports. For this tutorial, we'll read from a CSV file — but the pandas logic is identical regardless of source.

Loading and Cleaning Your Data

python

1# data_collector.py
2import pandas as pd
3from datetime import datetime, timedelta
4
5def load_weekly_data(filepath: str) -> pd.DataFrame:
6    """Load sales data and filter to the past 7 days."""
7    df = pd.read_csv(filepath, parse_dates=["date"])
8
9    # Filter to last 7 days
10    cutoff = datetime.now() - timedelta(days=7)
11    df = df[df["date"] >= cutoff].copy()
12
13    # Clean up: strip whitespace, drop nulls in key columns
14    df["region"] = df["region"].str.strip()
15    df.dropna(subset=["revenue", "units_sold", "salesperson"], inplace=True)
16
17    return df
18
19def calculate_kpis(df: pd.DataFrame) -> dict:
20    """Compute the KPIs we'll feature in the report."""
21    total_revenue = df["revenue"].sum()
22    total_units = df["units_sold"].sum()
23    avg_deal_size = total_revenue / len(df) if len(df) > 0 else 0
24    top_salesperson = df.groupby("salesperson")["revenue"].sum().idxmax()
25    top_region = df.groupby("region")["revenue"].sum().idxmax()
26
27    # Week-over-week change (assumes 'prev_week_revenue' column exists)
28    prev_revenue = df["prev_week_revenue"].sum() if "prev_week_revenue" in df.columns else None
29    wow_change = ((total_revenue - prev_revenue) / prev_revenue * 100) if prev_revenue else None
30
31    return {
32        "total_revenue": round(total_revenue, 2),
33        "total_units": int(total_units),
34        "avg_deal_size": round(avg_deal_size, 2),
35        "top_salesperson": top_salesperson,
36        "top_region": top_region,
37        "wow_change_pct": round(wow_change, 1) if wow_change is not None else "N/A",
38        "num_deals": len(df),
39        "week_ending": datetime.now().strftime("%B %d, %Y"),
40    }

Pro Tip: Reading from Multiple Sources

If your data lives in Excel files from different departments, swap the read_csv call for:

python

1# Read multiple Excel files and combine
2import glob
3
4all_files = glob.glob("data/weekly_exports/*.xlsx")
5df = pd.concat([pd.read_excel(f) for f in all_files], ignore_index=True)

Pandas handles the heavy lifting regardless of how messy the source files are.

Step 3: AI-Powered Insights with the OpenAI API

This is the part that genuinely impresses stakeholders. Instead of you writing "Revenue was up 12% week-over-week, driven primarily by the Northeast region…", GPT-4 writes it for you — and does it consistently well every single week.

Building the AI Analyst Module

python

1# ai_analyst.py
2import os
3from openai import OpenAI
4from dotenv import load_dotenv
5
6load_dotenv()
7client = OpenAI(api_key=os.getenv("OPENAI_API_KEY"))
8
9def generate_executive_summary(kpis: dict, top_performers: list) -> str:
10    """Send KPI data to GPT-4 and get a polished executive summary back."""
11
12    # Format the data clearly for the prompt
13    kpi_text = f"""
14    Week Ending: {kpis['week_ending']}
15    Total Revenue: ${kpis['total_revenue']:,.2f}
16    Total Units Sold: {kpis['total_units']}
17    Average Deal Size: ${kpis['avg_deal_size']:,.2f}
18    Number of Deals Closed: {kpis['num_deals']}
19    Week-over-Week Change: {kpis['wow_change_pct']}%
20    Top Salesperson: {kpis['top_salesperson']}
21    Top Region: {kpis['top_region']}
22    Top 3 Performers: {', '.join(top_performers)}
23    """
24
25    prompt = f"""You are a senior business analyst writing a weekly sales report executive summary.
26    
27    Based on the following KPI data, write a concise 3-paragraph executive summary for senior leadership.
28    
29    Paragraph 1: Overall performance headline and revenue result
30    Paragraph 2: Key drivers, top performers, and regional highlights  
31    Paragraph 3: One forward-looking observation or recommendation
32    
33    Keep the tone professional but direct. Avoid filler phrases like "It is worth noting."
34    Use specific numbers from the data. Total length: 150-200 words.
35    
36    KPI DATA:
37    {kpi_text}
38    """
39
40    response = client.chat.completions.create(
41        model="gpt-4o",
42        messages=[
43            {"role": "system", "content": "You are a precise, data-driven business analyst."},
44            {"role": "user", "content": prompt}
45        ],
46        temperature=0.4,  # Lower temp = more consistent, factual output
47        max_tokens=400
48    )
49
50    return response.choices[0].message.content.strip()

Why Temperature 0.4?

I've tested this extensively. At temperature=0.7 (the default), GPT-4 gets creative — great for brainstorming, but you'll occasionally get hallucinated numbers or overly casual phrasing in what should be a professional report. At 0.4, you get consistent, professional output that still reads naturally.

For automated data reporting where accuracy is paramount, keep temperature between 0.2 and 0.5.

Step 4: Building the Excel Report with openpyxl

Now we assemble everything into a formatted report your stakeholders can actually open and use. Excel is still the lingua franca of business reporting, so let's build a clean .xlsx file.

python

1# report_builder.py
2import openpyxl
3from openpyxl.styles import Font, PatternFill, Alignment, Border, Side
4from openpyxl.utils import get_column_letter
5from datetime import datetime
6
7def build_excel_report(kpis: dict, df, summary_text: str, output_path: str) -> str:
8    """Build a formatted Excel report and return the file path."""
9    wb = openpyxl.Workbook()
10    ws = wb.active
11    ws.title = "Weekly Sales Report"
12
13    # --- Header ---
14    header_fill = PatternFill("solid", fgColor="1F4E79")
15    header_font = Font(color="FFFFFF", bold=True, size=14)
16
17    ws.merge_cells("A1:F1")
18    ws["A1"] = f"Weekly Sales Report — {kpis['week_ending']}"
19    ws["A1"].font = header_font
20    ws["A1"].fill = header_fill
21    ws["A1"].alignment = Alignment(horizontal="center", vertical="center")
22    ws.row_dimensions[1].height = 30
23
24    # --- KPI Summary Section ---
25    ws["A3"] = "KEY METRICS"
26    ws["A3"].font = Font(bold=True, size=11, color="1F4E79")
27
28    kpi_rows = [
29        ("Total Revenue", f"${kpis['total_revenue']:,.2f}"),
30        ("Total Units Sold", f"{kpis['total_units']:,}"),
31        ("Average Deal Size", f"${kpis['avg_deal_size']:,.2f}"),
32        ("Deals Closed", str(kpis['num_deals'])),
33        ("Week-over-Week Change", f"{kpis['wow_change_pct']}%"),
34        ("Top Salesperson", kpis['top_salesperson']),
35        ("Top Region", kpis['top_region']),
36    ]
37
38    label_fill = PatternFill("solid", fgColor="D6E4F0")
39    for i, (label, value) in enumerate(kpi_rows, start=4):
40        ws[f"A{i}"] = label
41        ws[f"B{i}"] = value
42        ws[f"A{i}"].fill = label_fill
43        ws[f"A{i}"].font = Font(bold=True)
44
45    # --- AI Executive Summary ---
46    summary_row = 4 + len(kpi_rows) + 2
47    ws[f"A{summary_row}"] = "EXECUTIVE SUMMARY (AI-GENERATED)"
48    ws[f"A{summary_row}"].font = Font(bold=True, size=11, color="1F4E79")
49
50    ws.merge_cells(f"A{summary_row + 1}:F{summary_row + 6}")
51    ws[f"A{summary_row + 1}"] = summary_text
52    ws[f"A{summary_row + 1}"].alignment = Alignment(wrap_text=True, vertical="top")
53
54    # --- Detail Data Tab ---
55    ws_detail = wb.create_sheet("Raw Data")
56    if not df.empty:
57        headers = list(df.columns)
58        for col_idx, header in enumerate(headers, 1):
59            cell = ws_detail.cell(row=1, column=col_idx, value=header)
60            cell.font = Font(bold=True)
61            cell.fill = PatternFill("solid", fgColor="1F4E79")
62            cell.font = Font(bold=True, color="FFFFFF")
63
64        for row_idx, row in enumerate(df.itertuples(index=False), start=2):
65            for col_idx, value in enumerate(row, start=1):
66                ws_detail.cell(row=row_idx, column=col_idx, value=value)
67
68    # Auto-size columns
69    for sheet in [ws, ws_detail]:
70        for col in sheet.columns:
71            max_length = max((len(str(cell.value)) for cell in col if cell.value), default=10)
72            sheet.column_dimensions[get_column_letter(col[0].column)].width = min(max_length + 4, 50)
73
74    wb.save(output_path)
75    return output_path

For teams that prefer HTML emails with embedded reports, see our guide on Excel report automation for more openpyxl formatting techniques and chart generation.

Step 5: Email Delivery

A report nobody receives isn't a report — it's a file sitting in a folder. Let's wire up delivery. I'll use SendGrid here because it handles deliverability reliably, but I'll also show the smtplib fallback if you prefer to avoid a third-party dependency.

Option A: SendGrid (Recommended for Production)

python

1# email_sender.py
2import os
3import base64
4from sendgrid import SendGridAPIClient
5from sendgrid.helpers.mail import Mail, Attachment, FileContent, FileName, FileType, Disposition
6from dotenv import load_dotenv
7
8load_dotenv()
9
10def send_report_email(report_path: str, kpis: dict, summary: str):
11    """Email the report to all configured recipients."""
12    recipients = os.getenv("REPORT_RECIPIENTS", "").split(",")
13
14    # Read and encode the attachment
15    with open(report_path, "rb") as f:
16        encoded = base64.b64encode(f.read()).decode()
17
18    message = Mail(
19        from_email=os.getenv("FROM_EMAIL"),
20        to_emails=recipients,
21        subject=f"📊 Weekly Sales Report — {kpis['week_ending']}",
22        html_content=f"""
23        <h2>Weekly Sales Report</h2>
24        <p>{summary.replace(chr(10), '<br>')}</p>
25        <hr>
26        <p><strong>Total Revenue:</strong> ${kpis['total_revenue']:,.2f} 
27           ({kpis['wow_change_pct']}% vs last week)</p>
28        <p>Full report attached. This report was generated automatically.</p>
29        """
30    )
31
32    attachment = Attachment(
33        FileContent(encoded),
34        FileName(f"weekly_report_{kpis['week_ending'].replace(' ', '_')}.xlsx"),
35        FileType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"),
36        Disposition("attachment")
37    )
38    message.attachment = attachment
39
40    sg = SendGridAPIClient(os.getenv("SENDGRID_API_KEY"))
41    response = sg.send(message)
42    print(f"Email sent. Status: {response.status_code}")

Option B: smtplib (No External Dependencies)

For those building automated email reports with Gmail or a corporate SMTP server:

python

1import smtplib
2from email.mime.multipart import MIMEMultipart
3from email.mime.base import MIMEBase
4from email.mime.text import MIMEText
5from email import encoders
6
7def send_via_smtp(report_path: str, recipients: list, subject: str, body: str):
8    smtp_host = "smtp.gmail.com"
9    smtp_port = 587
10    username = os.getenv("SMTP_USER")
11    password = os.getenv("SMTP_PASSWORD")  # Use an App Password, not your main password
12
13    msg = MIMEMultipart()
14    msg["From"] = username
15    msg["To"] = ", ".join(recipients)
16    msg["Subject"] = subject
17    msg.attach(MIMEText(body, "html"))
18
19    with open(report_path, "rb") as f:
20        part = MIMEBase("application", "octet-stream")
21        part.set_payload(f.read())
22        encoders.encode_base64(part)
23        part.add_header("Content-Disposition", f'attachment; filename="report.xlsx"')
24        msg.attach(part)
25
26    with smtplib.SMTP(smtp_host, smtp_port) as server:
27        server.starttls()
28        server.login(username, password)
29        server.sendmail(username, recipients, msg.as_string())

Step 6: Scheduling the Script

The final piece is making this actually run without you. You have two solid options.

Option A: Python `schedule` Library (Cross-Platform)

python

1# main.py
2import schedule
3import time
4import os
5from dotenv import load_dotenv
6from data_collector import load_weekly_data, calculate_kpis
7from ai_analyst import generate_executive_summary
8from report_builder import build_excel_report
9from email_sender import send_report_email
10
11load_dotenv()
12
13def run_weekly_report():
14    print("Starting weekly report generation...")
15
16    # Step 1: Load and compute data
17    df = load_weekly_data("data/sales_data.csv")
18    kpis = calculate_kpis(df)
19
20    # Step 2: Get top performers list
21    top_performers = (
22        df.groupby("salesperson")["revenue"]
23        .sum()
24        .nlargest(3)
25        .index.tolist()
26    )
27
28    # Step 3: Generate AI narrative
29    print("Generating AI executive summary...")
30    summary = generate_executive_summary(kpis, top_performers)
31
32    # Step 4: Build the Excel report
33    output_path = f"output/weekly_report_{kpis['week_ending'].replace(' ', '_')}.xlsx"
34    os.makedirs("output", exist_ok=True)
35    build_excel_report(kpis, df, summary, output_path)
36    print(f"Report saved: {output_path}")
37
38    # Step 5: Email it out
39    send_report_email(output_path, kpis, summary)
40    print("Weekly report pipeline complete.")
41
42# Schedule for every Monday at 7:00 AM
43schedule.every().monday.at("07:00").do(run_weekly_report)
44
45if __name__ == "__main__":
46    print("Report scheduler running. Waiting for Monday 7:00 AM...")
47    # Uncomment the next line to run immediately for testing:
48    # run_weekly_report()
49    while True:
50        schedule.run_pending()
51        time.sleep(60)

Option B: Windows Task Scheduler or Linux Cron

For production deployments, you're better off with the OS scheduler rather than a long-running Python process. Check out our detailed walkthrough on scheduling Python scripts for both Windows Task Scheduler and Linux cron configurations.

Quick Linux cron example (runs at 7 AM every Monday):

bash

1# Run: crontab -e
20 7 * * 1 /usr/bin/python3 /home/youruser/weekly-report-bot/main.py >> /var/log/weekly_report.log 2>&1

Common Errors and How to Fix Them

Even clean code hits snags in production. Here are the errors you'll most likely encounter:

Error: `openai.RateLimitError`

Cause: You're hitting OpenAI's API rate limits, usually in testing when running the script multiple times quickly.
Fix: Add exponential backoff:

python

1import time
2
3for attempt in range(3):
4    try:
5        response = client.chat.completions.create(...)
6        break
7    except openai.RateLimitError:
8        time.sleep(2 ** attempt)  # Wait 1, 2, 4 seconds

Error: `KeyError` in `calculate_kpis()`

Cause: Your CSV column names don't match what the code expects (case sensitivity, spaces, etc.).
Fix: Add this after loading your DataFrame:

python

1print(df.columns.tolist())  # Always print columns during development
2df.columns = df.columns.str.strip().str.lower().str.replace(" ", "_")

Error: `smtplib.SMTPAuthenticationError`

Cause: Gmail requires an App Password (not your regular password) when 2FA is enabled.
Fix: Go to your Google Account → Security → App passwords → Generate one for "Mail."

Error: Report file is 0 bytes or corrupted

Cause: The script crashed mid-write, leaving a partial file.
Fix: Write to a temp file and rename on success:

python

1import tempfile, shutil
2with tempfile.NamedTemporaryFile(suffix=".xlsx", delete=False) as tmp:
3    wb.save(tmp.name)
4shutil.move(tmp.name, output_path)

Error: `schedule` runs the job but nothing happens

Cause: The while True loop is blocking on a different thread or the script exited early.
Fix: Test your function directly first (run_weekly_report() without scheduling), then add the scheduler once the pipeline works end-to-end.

Before vs. After: The Real Impact

Task	Manual Process	Automated Process
Data collection	45–90 min	0 min
KPI calculation	20–30 min	0 min
Writing narrative	30–60 min	~15 sec (GPT-4)
Formatting report	15–20 min	0 min
Emailing stakeholders	10 min	0 min
Total weekly time	2–4 hours	0 min ongoing
Error rate	Human-variable	Consistent
Delivery reliability	Dependent on you	Scheduled, automatic
Scalability	Doesn't scale	Runs for 1 or 100 reports

The one-time investment of 3–5 hours building this pipeline pays for itself in the first two weeks.

Conclusion

You now have a complete, production-ready system to automate weekly reports with Python and AI. The pipeline handles everything: pulling data with pandas, calculating KPIs, generating a polished executive summary with GPT-4, building a formatted Excel file with openpyxl, delivering it via email, and running itself every Monday without any manual intervention.

The real power here isn't just the time savings — it's consistency. Your stakeholders get the same quality report every week, on time, without depending on your schedule or energy level. Python automation plus AI is genuinely one of the most impactful combinations available to business professionals in 2026.

Start with the core pipeline, get it working on your actual data, then layer in the AI narrative and scheduling. Ship working code first, then improve. That's the only approach that actually sticks.

Frequently Asked Questions

Do I need to know Python well to build this automated reporting system?
You don't need to be an expert, but you should be comfortable with basic Python syntax, running scripts from the command line, and reading error messages. If you can write a for-loop and import a library, you have enough foundation to work through this tutorial. The code is heavily commented to help you understand each step.

How much does the OpenAI API cost for weekly AI report generation?
For a weekly report, you're sending roughly 300–400 tokens and receiving 200–400 tokens back. At current GPT-4o pricing (approximately $0.005 per 1K input tokens, $0.015 per 1K output tokens), each report run costs less than $0.01. Even running daily reports for a full year costs under $5 in API usage.

Can I use this pipeline with data from a SQL database instead of CSV files?
Absolutely — and it's a small change. Replace the pd.read_csv() call with pd.read_sql() and pass in a SQLAlchemy connection string. For example: df = pd.read_sql("SELECT * FROM sales WHERE date >= NOW() - INTERVAL 7 DAY", con=engine). Everything downstream stays identical.

What if I want to send the report as an HTML email instead of an Excel attachment?
Use Jinja2 to render an HTML template with your KPI data and summary text, then pass that rendered HTML directly as the email body in your SendGrid or smtplib call. HTML emails can look just as polished as Excel reports and are easier for stakeholders to read on mobile. The report.html template file in our project structure is set up for exactly this purpose.

How do I handle reports that need data from multiple departments with different file formats?
Load each source separately with the appropriate pandas reader (read_csv, read_excel, read_json), normalize the column names to a common schema, then use pd.concat() to merge them. You can also use pd.merge() for relational joins if you need to cross-reference data between sources — for example, matching sales transactions to your product catalog.

How to Automate Weekly Reports with Python and AI (Complete 2026 Tutorial)

Let's build something genuinely useful.

The Manual Reporting Problem (And Why Python Fixes It)

Before we write a single line of code, let's be honest about what manual weekly reporting actually costs you.

Typical manual reporting workflow:

45–90 minutes pulling data from multiple sources
20–30 minutes calculating KPIs and sanity-checking numbers
30–60 minutes writing the narrative summary and commentary
15–20 minutes formatting the report and creating charts
10 minutes emailing it to the right people
Total: 2–4 hours per week, every week

The automated alternative:

Initial setup: 3–5 hours (you're doing that now)
Weekly execution: 0 minutes (it runs itself)
Total ongoing time: ~0 hours per week

Step 1: Setting Up Your Environment

First, let's get your environment ready. I'm assuming you have Python 3.9+ installed. If you're on an older version, upgrade — several libraries we're using have 3.9+ dependencies.

Install Required Libraries

python

1pip install pandas openpyxl openai schedule python-dotenv jinja2 sendgrid

Here's what each package does in our pipeline:

Library	Role
`pandas`	Data loading, cleaning, and KPI calculation
`openpyxl`	Building and formatting Excel reports
`openai`	Connecting to GPT-4 for AI-generated narrative
`schedule`	Running the script on a weekly cron-like schedule
`python-dotenv`	Keeping API keys out of your code
`jinja2`	Templating HTML reports (optional alternative to Excel)
`sendgrid`	Reliable transactional email delivery

Project Structure

Set up your project folder like this:

Prompt

weekly-report-bot/
├── .env                  # API keys (never commit this)
├── main.py               # Entry point / scheduler
├── data_collector.py     # Data loading and KPI logic
├── ai_analyst.py         # OpenAI integration
├── report_builder.py     # Excel/HTML report assembly
├── email_sender.py       # Email delivery
├── templates/
│   └── report.html       # Optional HTML template
└── data/
    └── sales_data.csv    # Your input data

Configure Your `.env` File

bash

1OPENAI_API_KEY=sk-your-openai-api-key-here
2SENDGRID_API_KEY=SG.your-sendgrid-api-key-here
3REPORT_RECIPIENTS=boss@company.com,team@company.com
4FROM_EMAIL=reports@yourcompany.com

Step 2: Data Collection and KPI Calculation with Pandas

Loading and Cleaning Your Data

python

1# data_collector.py
2import pandas as pd
3from datetime import datetime, timedelta
4
5def load_weekly_data(filepath: str) -> pd.DataFrame:
6    """Load sales data and filter to the past 7 days."""
7    df = pd.read_csv(filepath, parse_dates=["date"])
8
9    # Filter to last 7 days
10    cutoff = datetime.now() - timedelta(days=7)
11    df = df[df["date"] >= cutoff].copy()
12
13    # Clean up: strip whitespace, drop nulls in key columns
14    df["region"] = df["region"].str.strip()
15    df.dropna(subset=["revenue", "units_sold", "salesperson"], inplace=True)
16
17    return df
18
19def calculate_kpis(df: pd.DataFrame) -> dict:
20    """Compute the KPIs we'll feature in the report."""
21    total_revenue = df["revenue"].sum()
22    total_units = df["units_sold"].sum()
23    avg_deal_size = total_revenue / len(df) if len(df) > 0 else 0
24    top_salesperson = df.groupby("salesperson")["revenue"].sum().idxmax()
25    top_region = df.groupby("region")["revenue"].sum().idxmax()
26
27    # Week-over-week change (assumes 'prev_week_revenue' column exists)
28    prev_revenue = df["prev_week_revenue"].sum() if "prev_week_revenue" in df.columns else None
29    wow_change = ((total_revenue - prev_revenue) / prev_revenue * 100) if prev_revenue else None
30
31    return {
32        "total_revenue": round(total_revenue, 2),
33        "total_units": int(total_units),
34        "avg_deal_size": round(avg_deal_size, 2),
35        "top_salesperson": top_salesperson,
36        "top_region": top_region,
37        "wow_change_pct": round(wow_change, 1) if wow_change is not None else "N/A",
38        "num_deals": len(df),
39        "week_ending": datetime.now().strftime("%B %d, %Y"),
40    }

Pro Tip: Reading from Multiple Sources

If your data lives in Excel files from different departments, swap the read_csv call for:

python

1# Read multiple Excel files and combine
2import glob
3
4all_files = glob.glob("data/weekly_exports/*.xlsx")
5df = pd.concat([pd.read_excel(f) for f in all_files], ignore_index=True)

Pandas handles the heavy lifting regardless of how messy the source files are.

Step 3: AI-Powered Insights with the OpenAI API

Building the AI Analyst Module

python

1# ai_analyst.py
2import os
3from openai import OpenAI
4from dotenv import load_dotenv
5
6load_dotenv()
7client = OpenAI(api_key=os.getenv("OPENAI_API_KEY"))
8
9def generate_executive_summary(kpis: dict, top_performers: list) -> str:
10    """Send KPI data to GPT-4 and get a polished executive summary back."""
11
12    # Format the data clearly for the prompt
13    kpi_text = f"""
14    Week Ending: {kpis['week_ending']}
15    Total Revenue: ${kpis['total_revenue']:,.2f}
16    Total Units Sold: {kpis['total_units']}
17    Average Deal Size: ${kpis['avg_deal_size']:,.2f}
18    Number of Deals Closed: {kpis['num_deals']}
19    Week-over-Week Change: {kpis['wow_change_pct']}%
20    Top Salesperson: {kpis['top_salesperson']}
21    Top Region: {kpis['top_region']}
22    Top 3 Performers: {', '.join(top_performers)}
23    """
24
25    prompt = f"""You are a senior business analyst writing a weekly sales report executive summary.
26    
27    Based on the following KPI data, write a concise 3-paragraph executive summary for senior leadership.
28    
29    Paragraph 1: Overall performance headline and revenue result
30    Paragraph 2: Key drivers, top performers, and regional highlights  
31    Paragraph 3: One forward-looking observation or recommendation
32    
33    Keep the tone professional but direct. Avoid filler phrases like "It is worth noting."
34    Use specific numbers from the data. Total length: 150-200 words.
35    
36    KPI DATA:
37    {kpi_text}
38    """
39
40    response = client.chat.completions.create(
41        model="gpt-4o",
42        messages=[
43            {"role": "system", "content": "You are a precise, data-driven business analyst."},
44            {"role": "user", "content": prompt}
45        ],
46        temperature=0.4,  # Lower temp = more consistent, factual output
47        max_tokens=400
48    )
49
50    return response.choices[0].message.content.strip()

Why Temperature 0.4?

For automated data reporting where accuracy is paramount, keep temperature between 0.2 and 0.5.

Step 4: Building the Excel Report with openpyxl

Now we assemble everything into a formatted report your stakeholders can actually open and use. Excel is still the lingua franca of business reporting, so let's build a clean .xlsx file.

python

1# report_builder.py
2import openpyxl
3from openpyxl.styles import Font, PatternFill, Alignment, Border, Side
4from openpyxl.utils import get_column_letter
5from datetime import datetime
6
7def build_excel_report(kpis: dict, df, summary_text: str, output_path: str) -> str:
8    """Build a formatted Excel report and return the file path."""
9    wb = openpyxl.Workbook()
10    ws = wb.active
11    ws.title = "Weekly Sales Report"
12
13    # --- Header ---
14    header_fill = PatternFill("solid", fgColor="1F4E79")
15    header_font = Font(color="FFFFFF", bold=True, size=14)
16
17    ws.merge_cells("A1:F1")
18    ws["A1"] = f"Weekly Sales Report — {kpis['week_ending']}"
19    ws["A1"].font = header_font
20    ws["A1"].fill = header_fill
21    ws["A1"].alignment = Alignment(horizontal="center", vertical="center")
22    ws.row_dimensions[1].height = 30
23
24    # --- KPI Summary Section ---
25    ws["A3"] = "KEY METRICS"
26    ws["A3"].font = Font(bold=True, size=11, color="1F4E79")
27
28    kpi_rows = [
29        ("Total Revenue", f"${kpis['total_revenue']:,.2f}"),
30        ("Total Units Sold", f"{kpis['total_units']:,}"),
31        ("Average Deal Size", f"${kpis['avg_deal_size']:,.2f}"),
32        ("Deals Closed", str(kpis['num_deals'])),
33        ("Week-over-Week Change", f"{kpis['wow_change_pct']}%"),
34        ("Top Salesperson", kpis['top_salesperson']),
35        ("Top Region", kpis['top_region']),
36    ]
37
38    label_fill = PatternFill("solid", fgColor="D6E4F0")
39    for i, (label, value) in enumerate(kpi_rows, start=4):
40        ws[f"A{i}"] = label
41        ws[f"B{i}"] = value
42        ws[f"A{i}"].fill = label_fill
43        ws[f"A{i}"].font = Font(bold=True)
44
45    # --- AI Executive Summary ---
46    summary_row = 4 + len(kpi_rows) + 2
47    ws[f"A{summary_row}"] = "EXECUTIVE SUMMARY (AI-GENERATED)"
48    ws[f"A{summary_row}"].font = Font(bold=True, size=11, color="1F4E79")
49
50    ws.merge_cells(f"A{summary_row + 1}:F{summary_row + 6}")
51    ws[f"A{summary_row + 1}"] = summary_text
52    ws[f"A{summary_row + 1}"].alignment = Alignment(wrap_text=True, vertical="top")
53
54    # --- Detail Data Tab ---
55    ws_detail = wb.create_sheet("Raw Data")
56    if not df.empty:
57        headers = list(df.columns)
58        for col_idx, header in enumerate(headers, 1):
59            cell = ws_detail.cell(row=1, column=col_idx, value=header)
60            cell.font = Font(bold=True)
61            cell.fill = PatternFill("solid", fgColor="1F4E79")
62            cell.font = Font(bold=True, color="FFFFFF")
63
64        for row_idx, row in enumerate(df.itertuples(index=False), start=2):
65            for col_idx, value in enumerate(row, start=1):
66                ws_detail.cell(row=row_idx, column=col_idx, value=value)
67
68    # Auto-size columns
69    for sheet in [ws, ws_detail]:
70        for col in sheet.columns:
71            max_length = max((len(str(cell.value)) for cell in col if cell.value), default=10)
72            sheet.column_dimensions[get_column_letter(col[0].column)].width = min(max_length + 4, 50)
73
74    wb.save(output_path)
75    return output_path

For teams that prefer HTML emails with embedded reports, see our guide on Excel report automation for more openpyxl formatting techniques and chart generation.

Step 5: Email Delivery

Option A: SendGrid (Recommended for Production)

python

1# email_sender.py
2import os
3import base64
4from sendgrid import SendGridAPIClient
5from sendgrid.helpers.mail import Mail, Attachment, FileContent, FileName, FileType, Disposition
6from dotenv import load_dotenv
7
8load_dotenv()
9
10def send_report_email(report_path: str, kpis: dict, summary: str):
11    """Email the report to all configured recipients."""
12    recipients = os.getenv("REPORT_RECIPIENTS", "").split(",")
13
14    # Read and encode the attachment
15    with open(report_path, "rb") as f:
16        encoded = base64.b64encode(f.read()).decode()
17
18    message = Mail(
19        from_email=os.getenv("FROM_EMAIL"),
20        to_emails=recipients,
21        subject=f"📊 Weekly Sales Report — {kpis['week_ending']}",
22        html_content=f"""
23        <h2>Weekly Sales Report</h2>
24        <p>{summary.replace(chr(10), '<br>')}</p>
25        <hr>
26        <p><strong>Total Revenue:</strong> ${kpis['total_revenue']:,.2f} 
27           ({kpis['wow_change_pct']}% vs last week)</p>
28        <p>Full report attached. This report was generated automatically.</p>
29        """
30    )
31
32    attachment = Attachment(
33        FileContent(encoded),
34        FileName(f"weekly_report_{kpis['week_ending'].replace(' ', '_')}.xlsx"),
35        FileType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"),
36        Disposition("attachment")
37    )
38    message.attachment = attachment
39
40    sg = SendGridAPIClient(os.getenv("SENDGRID_API_KEY"))
41    response = sg.send(message)
42    print(f"Email sent. Status: {response.status_code}")

Option B: smtplib (No External Dependencies)

For those building automated email reports with Gmail or a corporate SMTP server:

python

1import smtplib
2from email.mime.multipart import MIMEMultipart
3from email.mime.base import MIMEBase
4from email.mime.text import MIMEText
5from email import encoders
6
7def send_via_smtp(report_path: str, recipients: list, subject: str, body: str):
8    smtp_host = "smtp.gmail.com"
9    smtp_port = 587
10    username = os.getenv("SMTP_USER")
11    password = os.getenv("SMTP_PASSWORD")  # Use an App Password, not your main password
12
13    msg = MIMEMultipart()
14    msg["From"] = username
15    msg["To"] = ", ".join(recipients)
16    msg["Subject"] = subject
17    msg.attach(MIMEText(body, "html"))
18
19    with open(report_path, "rb") as f:
20        part = MIMEBase("application", "octet-stream")
21        part.set_payload(f.read())
22        encoders.encode_base64(part)
23        part.add_header("Content-Disposition", f'attachment; filename="report.xlsx"')
24        msg.attach(part)
25
26    with smtplib.SMTP(smtp_host, smtp_port) as server:
27        server.starttls()
28        server.login(username, password)
29        server.sendmail(username, recipients, msg.as_string())

Step 6: Scheduling the Script

The final piece is making this actually run without you. You have two solid options.

Option A: Python `schedule` Library (Cross-Platform)

python

1# main.py
2import schedule
3import time
4import os
5from dotenv import load_dotenv
6from data_collector import load_weekly_data, calculate_kpis
7from ai_analyst import generate_executive_summary
8from report_builder import build_excel_report
9from email_sender import send_report_email
10
11load_dotenv()
12
13def run_weekly_report():
14    print("Starting weekly report generation...")
15
16    # Step 1: Load and compute data
17    df = load_weekly_data("data/sales_data.csv")
18    kpis = calculate_kpis(df)
19
20    # Step 2: Get top performers list
21    top_performers = (
22        df.groupby("salesperson")["revenue"]
23        .sum()
24        .nlargest(3)
25        .index.tolist()
26    )
27
28    # Step 3: Generate AI narrative
29    print("Generating AI executive summary...")
30    summary = generate_executive_summary(kpis, top_performers)
31
32    # Step 4: Build the Excel report
33    output_path = f"output/weekly_report_{kpis['week_ending'].replace(' ', '_')}.xlsx"
34    os.makedirs("output", exist_ok=True)
35    build_excel_report(kpis, df, summary, output_path)
36    print(f"Report saved: {output_path}")
37
38    # Step 5: Email it out
39    send_report_email(output_path, kpis, summary)
40    print("Weekly report pipeline complete.")
41
42# Schedule for every Monday at 7:00 AM
43schedule.every().monday.at("07:00").do(run_weekly_report)
44
45if __name__ == "__main__":
46    print("Report scheduler running. Waiting for Monday 7:00 AM...")
47    # Uncomment the next line to run immediately for testing:
48    # run_weekly_report()
49    while True:
50        schedule.run_pending()
51        time.sleep(60)

Option B: Windows Task Scheduler or Linux Cron

Quick Linux cron example (runs at 7 AM every Monday):

bash

1# Run: crontab -e
20 7 * * 1 /usr/bin/python3 /home/youruser/weekly-report-bot/main.py >> /var/log/weekly_report.log 2>&1

Common Errors and How to Fix Them

Even clean code hits snags in production. Here are the errors you'll most likely encounter:

Error: `openai.RateLimitError`

Cause: You're hitting OpenAI's API rate limits, usually in testing when running the script multiple times quickly.
Fix: Add exponential backoff:

python

1import time
2
3for attempt in range(3):
4    try:
5        response = client.chat.completions.create(...)
6        break
7    except openai.RateLimitError:
8        time.sleep(2 ** attempt)  # Wait 1, 2, 4 seconds

Error: `KeyError` in `calculate_kpis()`

Cause: Your CSV column names don't match what the code expects (case sensitivity, spaces, etc.).
Fix: Add this after loading your DataFrame:

python

1print(df.columns.tolist())  # Always print columns during development
2df.columns = df.columns.str.strip().str.lower().str.replace(" ", "_")

Error: `smtplib.SMTPAuthenticationError`

Cause: Gmail requires an App Password (not your regular password) when 2FA is enabled.
Fix: Go to your Google Account → Security → App passwords → Generate one for "Mail."

Error: Report file is 0 bytes or corrupted

Cause: The script crashed mid-write, leaving a partial file.
Fix: Write to a temp file and rename on success:

python

1import tempfile, shutil
2with tempfile.NamedTemporaryFile(suffix=".xlsx", delete=False) as tmp:
3    wb.save(tmp.name)
4shutil.move(tmp.name, output_path)

Error: `schedule` runs the job but nothing happens

Before vs. After: The Real Impact

Task	Manual Process	Automated Process
Data collection	45–90 min	0 min
KPI calculation	20–30 min	0 min
Writing narrative	30–60 min	~15 sec (GPT-4)
Formatting report	15–20 min	0 min
Emailing stakeholders	10 min	0 min
Total weekly time	2–4 hours	0 min ongoing
Error rate	Human-variable	Consistent
Delivery reliability	Dependent on you	Scheduled, automatic
Scalability	Doesn't scale	Runs for 1 or 100 reports

The one-time investment of 3–5 hours building this pipeline pays for itself in the first two weeks.

Conclusion

Start with the core pipeline, get it working on your actual data, then layer in the AI narrative and scheduling. Ship working code first, then improve. That's the only approach that actually sticks.

How to Automate Weekly Reports with Python and AI (Complete 2026 Tutorial)

The Manual Reporting Problem (And Why Python Fixes It)

Step 1: Setting Up Your Environment

Install Required Libraries

Project Structure

Configure Your .env File

Step 2: Data Collection and KPI Calculation with Pandas

Loading and Cleaning Your Data

Pro Tip: Reading from Multiple Sources

Step 3: AI-Powered Insights with the OpenAI API

Building the AI Analyst Module

Why Temperature 0.4?

Step 4: Building the Excel Report with openpyxl

Step 5: Email Delivery

Option A: SendGrid (Recommended for Production)

Option B: smtplib (No External Dependencies)

Step 6: Scheduling the Script

Option A: Python schedule Library (Cross-Platform)

Option B: Windows Task Scheduler or Linux Cron

Common Errors and How to Fix Them

Error: openai.RateLimitError

Error: KeyError in calculate_kpis()

Error: smtplib.SMTPAuthenticationError

Error: Report file is 0 bytes or corrupted

Error: schedule runs the job but nothing happens

Before vs. After: The Real Impact

Conclusion

Frequently Asked Questions

Share this article

How to Automate Weekly Reports with Python and AI (Complete 2026 Tutorial)

The Manual Reporting Problem (And Why Python Fixes It)

Step 1: Setting Up Your Environment

Install Required Libraries

Project Structure

Configure Your .env File

Step 2: Data Collection and KPI Calculation with Pandas

Loading and Cleaning Your Data

Pro Tip: Reading from Multiple Sources

Step 3: AI-Powered Insights with the OpenAI API

Building the AI Analyst Module

Why Temperature 0.4?

Step 4: Building the Excel Report with openpyxl

Step 5: Email Delivery

Option A: SendGrid (Recommended for Production)

Option B: smtplib (No External Dependencies)

Step 6: Scheduling the Script

Option A: Python schedule Library (Cross-Platform)

Option B: Windows Task Scheduler or Linux Cron

Common Errors and How to Fix Them

Error: openai.RateLimitError

Error: KeyError in calculate_kpis()

Error: smtplib.SMTPAuthenticationError

Error: Report file is 0 bytes or corrupted

Error: schedule runs the job but nothing happens

Before vs. After: The Real Impact

Conclusion

Frequently Asked Questions

Share this article

Configure Your `.env` File

Option A: Python `schedule` Library (Cross-Platform)

Error: `openai.RateLimitError`

Error: `KeyError` in `calculate_kpis()`

Error: `smtplib.SMTPAuthenticationError`

Error: `schedule` runs the job but nothing happens

Configure Your `.env` File

Option A: Python `schedule` Library (Cross-Platform)

Error: `openai.RateLimitError`

Error: `KeyError` in `calculate_kpis()`

Error: `smtplib.SMTPAuthenticationError`

Error: `schedule` runs the job but nothing happens