Automate Excel Reports with Python and AI

Python with AI Tutorial · Chapter 45 of 48

To automate Excel reports with Python and AI you let pandas do the calculations, openpyxl build a formatted workbook with a chart, and an AI API write the executive summary from the numbers you calculated. The result is a monthly report that takes one command instead of an afternoon, and a summary that is grounded in your own figures rather than guesswork.

Table of Contents

The pipeline

The workflow has four stages, and each one is a function you can test on its own: load the raw transactions, aggregate them with pandas, write a formatted workbook with openpyxl, then send the aggregated numbers (never the raw customer rows) to the model and paste its summary back into the workbook. Install the libraries first: pip install pandas openpyxl anthropic.

Step 1: sample sales data

So that every example runs as-is, this script creates the source file. In real life this is an export from your accounting or CRM system.

import pandas as pd

rows = [
    ("2026-08-03", "North", "Laptop", 4, 1100), ("2026-08-04", "South", "Monitor", 10, 250),
    ("2026-08-06", "East", "Keyboard", 25, 50),  ("2026-08-10", "North", "Monitor", 6, 250),
    ("2026-08-12", "West", "Laptop", 3, 1100),   ("2026-08-14", "South", "Laptop", 5, 1100),
    ("2026-08-18", "East", "Laptop", 2, 1100),   ("2026-08-21", "West", "Keyboard", 40, 50),
    ("2026-08-25", "North", "Keyboard", 30, 50), ("2026-08-28", "South", "Monitor", 8, 250),
]
df = pd.DataFrame(rows, columns=["Date", "Region", "Product", "Units", "UnitPrice"])
df["Revenue"] = df["Units"] * df["UnitPrice"]
df.to_excel("sales_aug_2026.xlsx", index=False)
print("rows:", len(df), "| revenue:", df["Revenue"].sum())

Output:
rows: 10 | revenue: 26150

Step 2: aggregate with pandas

Two small tables and a handful of KPIs are all a monthly report needs. Everything downstream, including the AI summary, is built from these objects.

import pandas as pd

df = pd.read_excel("sales_aug_2026.xlsx")
by_region = (df.groupby("Region", as_index=False)["Revenue"].sum()
               .sort_values("Revenue", ascending=False))
by_product = df.groupby("Product", as_index=False).agg(Units=("Units", "sum"), Revenue=("Revenue", "sum"))
kpis = {
    "Total revenue": int(df["Revenue"].sum()),
    "Units sold": int(df["Units"].sum()),
    "Orders": len(df),
    "Top region": by_region.iloc[0]["Region"],
}
print(by_region.to_string(index=False))
print(kpis)

Output:
Region  Revenue
 South    10000
 North     7400
  West     5300
  East     3450
{'Total revenue': 26150, 'Units sold': 133, 'Orders': 10, 'Top region': 'South'}

Step 3: build the workbook with openpyxl

pandas can save a DataFrame, but openpyxl gives you fonts, number formats, column widths and native Excel charts. This writes a Summary sheet with the KPIs, the region table and a bar chart, plus a Data sheet with the raw rows. Run it in the same session as step 2.

from openpyxl import Workbook
from openpyxl.chart import BarChart, Reference
from openpyxl.styles import Font
from openpyxl.utils.dataframe import dataframe_to_rows

wb = Workbook()
ws = wb.active
ws.title = "Summary"
ws["A1"] = "Monthly Sales Report - August 2026"
ws["A1"].font = Font(bold=True, size=14)
row = 3
for key, value in kpis.items():
    ws.cell(row=row, column=1, value=key).font = Font(bold=True)
    ws.cell(row=row, column=2, value=value)
    row += 1
start = row + 1
for r_idx, r in enumerate(dataframe_to_rows(by_region, index=False, header=True), start=start):
    for c_idx, value in enumerate(r, start=1):
        ws.cell(row=r_idx, column=c_idx, value=value)
end = start + len(by_region)
for cell in ws[start]:
    cell.font = Font(bold=True)
for r in range(start + 1, end + 1):
    ws.cell(row=r, column=2).number_format = "#,##0"
chart = BarChart()
chart.title = "Revenue by region"
chart.add_data(Reference(ws, min_col=2, min_row=start, max_row=end), titles_from_data=True)
chart.set_categories(Reference(ws, min_col=1, min_row=start + 1, max_row=end))
ws.add_chart(chart, "D3")
ws.column_dimensions["A"].width = 22
ws2 = wb.create_sheet("Data")
for r in dataframe_to_rows(df, index=False, header=True):
    ws2.append(r)
wb.save("Monthly_Report_Aug_2026.xlsx")
print("saved; summary sheet has", ws.max_row, "rows, chart at D3")

Output:
saved; summary sheet has 12 rows, chart at D3

Try it with AI

Ask for the formatting you would otherwise click through by hand.

Here is my openpyxl code that writes a Summary sheet: [paste step 3]. Add a light grey fill and thin borders to the region table header, format the Revenue column as currency with no decimals, freeze the first row of the Data sheet, and add a second sheet called Products with the by_product DataFrame. Return the full updated code.

Step 4: let the AI write the executive summary

The model receives a compact fact sheet built from kpis, by_region and by_product, with a system prompt that forbids numbers it was not given. The reply is written into a merged, wrapped cell under the tables and the workbook is saved again. The API key comes from the ANTHROPIC_API_KEY environment variable, exactly as in chapter 43.

import anthropic
from openpyxl import load_workbook
from openpyxl.styles import Alignment, Font

facts = (
    "Month: August 2026\n"
    f"KPIs: {kpis}\n"
    f"Revenue by region:\n{by_region.to_string(index=False)}\n"
    f"Units and revenue by product:\n{by_product.to_string(index=False)}"
)
client = anthropic.Anthropic()
msg = client.messages.create(
    model="claude-sonnet-5", max_tokens=400,
    system=("You write executive summaries for a sales director. Use only the numbers "
            "provided, never invent figures or causes, three to four sentences, plain text."),
    messages=[{"role": "user", "content": facts}],
)
summary = msg.content[0].text.strip()

wb = load_workbook("Monthly_Report_Aug_2026.xlsx")
ws = wb["Summary"]
ws["A15"] = "Executive summary"
ws["A15"].font = Font(bold=True)
ws.merge_cells("A16:H22")
ws["A16"] = summary
ws["A16"].alignment = Alignment(wrap_text=True, vertical="top")
wb.save("Monthly_Report_Aug_2026.xlsx")
print(summary)

Output:
August 2026 revenue reached 26,150 from 10 orders and 133 units. South was the strongest region at 10,000, followed by North at 7,400, while East trailed at 3,450. Laptops generated 15,400 of revenue, more than monitors (6,000) and keyboards (4,750) combined.

Step 5: check the summary against the numbers

Models occasionally round wrongly or borrow a figure from a previous month. A five-line check flags any number in the summary that does not appear in the fact sheet, so you can reject the text before it reaches a director.

import re

def unknown_numbers(summary: str, facts: str) -> list[str]:
    clean_facts = facts.replace(",", "")
    found = re.findall(r"\d[\d,]*", summary)
    return [n for n in found if n.replace(",", "") not in clean_facts]

print(unknown_numbers("Revenue reached 26,150 across 133 units; South led with 10,000.", facts))
print(unknown_numbers("Revenue grew 12% to 27,000.", facts))

Output:
[]
['12', '27,000']

Warning: send the model aggregates, not the raw data sheet. Customer names, emails and invoice numbers have no place in a prompt, and the aggregated tables are all the summary needs. Keep the API key in an environment variable and the spending limit set.

Step 6: run it every month

Wrap the steps in a function that takes the month, so the same script serves every period, and start it from Windows Task Scheduler or cron on the first working day. A command-line argument keeps it simple.

import sys

month = sys.argv[1] if len(sys.argv) > 1 else "2026-08"
source = f"sales_{month}.xlsx"
target = f"Monthly_Report_{month}.xlsx"
print("building", target, "from", source)
# build_report(source, target)   # steps 2 to 5 wrapped in one function

Output:
building Monthly_Report_2026-08.xlsx from sales_2026-08.xlsx

Name the exports consistently, log the token usage from each run, and email the workbook with smtplib or drop it in a shared folder. With the check from step 5 in place, a failed validation can stop the script and alert you instead of sending a wrong number.

Try it with AI

Turn the six steps into one reusable module.

Combine these Python snippets into a single script with a function build_report(source_path, month) that returns the output path: [paste steps 2 to 5]. Add logging, raise an exception if unknown_numbers() returns anything, and make the AI provider switchable between anthropic and openai with an environment variable. Python 3.12, pandas, openpyxl.

Why not let the AI write the whole report?

It is tempting to upload the raw sheet and ask for "a monthly report". Resist it. Arithmetic in pandas is deterministic, auditable and free; arithmetic in a language model is probabilistic and costs tokens. The division of labour in this chapter puts every number under your control and gives the model the one job it is genuinely good at: turning a table into two or three sentences a busy director will read. If the totals are wrong, you look in your code, not in a prompt.

The same split keeps the report stable month after month. Column names, sheet layout and chart position never change unless you change them, so the finance team can build formulas on top of the workbook. Only the summary paragraph varies, and even that is checked before it is written. Treat the model as a writer with a strict fact sheet, and the report stays trustworthy.

Common mistakes

Letting the model calculate totals from raw rows. Do the maths in pandas and hand over results only.
Writing the summary into a single narrow cell without wrap_text, so it looks like one endless line.
Saving with pandas to_excel after openpyxl formatting, which overwrites the chart and styles.
Forgetting that dataframe_to_rows includes an index column unless you pass index=False.
Trusting the summary text without a numeric check.

Exercise

Add a Products sheet to the workbook that contains by_product with bold headers, and extend the fact sheet so the summary can mention the best-selling product by units.

Show answer

from openpyxl import load_workbook
from openpyxl.styles import Font
from openpyxl.utils.dataframe import dataframe_to_rows

wb = load_workbook("Monthly_Report_Aug_2026.xlsx")
ws3 = wb.create_sheet("Products")
for r in dataframe_to_rows(by_product, index=False, header=True):
    ws3.append(r)
for cell in ws3[1]:
    cell.font = Font(bold=True)
wb.save("Monthly_Report_Aug_2026.xlsx")

best = by_product.sort_values("Units", ascending=False).iloc[0]
facts += f"\nBest-selling product by units: {best['Product']} ({best['Units']} units)"
print(facts.splitlines()[-1])

Output:
Best-selling product by units: Keyboard (95 units)

Related chapters

FAQ

Can Python automate Excel reports without opening Excel?

Yes. pandas and openpyxl read and write .xlsx files directly, including charts and formatting, so the script runs on a server or a scheduled task with no Excel installation.

Is it safe to send company sales data to an AI API?

Send aggregated figures only, never customer-level rows, and check your provider’s data policy. Both OpenAI and Anthropic offer API terms under which prompts are not used for training.

How much does the AI summary cost per report?

A fact sheet of a few hundred tokens and a 100-word reply costs a fraction of a cent on current mid-tier models, so even daily reports are negligible next to the time saved.

Working with spreadsheets too? Ready-made Excel, Google Sheets and Power BI templates are at NextGenTemplates.com.

Chapter 45 of 48 · Python with AI: all 48 chapters