# Automate a recurring Excel report: deterministic Python, AI only for the fuzzy parts

> The pattern practitioners recommend on r/AI_Agents and r/automation: scripts for ingest, transform and validate, an OpenAI or Claude API call only for fields that need interpretation, and a hard stop when totals don't reconcile.

- **Category:** [Excel](https://stepgleam.com/categories/excel)
- **Difficulty:** Advanced
- **Time:** ~90 minutes
- **Tools:** Python + openpyxl, OpenAI API or Claude API, Claude Code, Excel (Microsoft 365)
- **Reliability score:** 73/100
- **Steps:** 6
- **Last updated:** 2026-10-26
- **Published:** 2026-10-16

## Tools used

- [Python + openpyxl](https://openpyxl.readthedocs.io) — Deterministic reading, transforming and writing of .xlsx files
- [OpenAI API or Claude API](https://platform.openai.com/docs) — Called only for fuzzy fields (classification, extraction)
- [Claude Code](https://code.claude.com/docs) — Coding agent that reads, edits and tests the repo
- [Excel (Microsoft 365)](https://support.microsoft.com/en-us/excel) — Where the data and formulas live

## What you'll get

A script that produces the weekly workbook the same way every time, uses an LLM API only for a few fuzzy columns, and refuses to deliver when validation fails.

## Who this is for

Analysts and developers who build the same Excel report every week and are comfortable running a Python script (or having Claude Code write one).

## Before you start

People who automate Excel reports for a living keep saying the same thing: don't let an AI agent drive every step. Use deterministic code for the repeatable parts, call a model through its API only where judgement is needed, and make the run stop when something doesn't add up. This guide assembles their advice from three threads into one pipeline.

## Steps

### Step 1: Split the job into stages before choosing tools

**Tool:** Python + openpyxl

Write down the stages: **ingestion, transformation, calculation, validation, delivery**. One practitioner says to *"decompose this into ingestion, transformation, calculation, validation, and delivery before choosing an agent framework."* Another sketches the pipeline as source data → raw snapshot → normalize → validate/reconcile → update workbook → notify.

**Why this works:** Naming stages shows which ones are deterministic and which genuinely need a model.

[Open step 1](https://stepgleam.com/guides/automate-recurring-excel-reports-with-python-and-an-llm-api#step-1)

### Step 2: Have a coding agent write the script with openpyxl

**Tool:** Claude Code

For batch file work, people recommend Python with openpyxl over driving the Excel UI. One says Claude Code *"can write and run the scripts for you end to end"* and that tables and lookups are *"like 10 lines of code."* Describe the input files, the output layout and the stages, and ask the agent to write and run the script.

**Why this works:** Code does the same thing every week; a UI-driving agent might not.

**Prompt:**

```
Write a Python script using openpyxl that reads every .xlsx in ./input, normalizes the columns listed in SPEC.md, writes ./output/weekly_report.xlsx with the Summary and Detail sheets described there, and prints row counts per stage. Run it on the sample files.
```

[Open step 2](https://stepgleam.com/guides/automate-recurring-excel-reports-with-python-and-an-llm-api#step-2)

### Step 3: Call the model API only for fields that need interpretation

**Tool:** OpenAI API or Claude API

Keep the LLM out of copying formulas, sorting and building known reports. Send it only the genuinely fuzzy part, such as classifying a free-text reason or extracting a field from an unusual document, and ask for structured output. One commenter's recipe: Python pulls the data, extracted text goes to the AI *"with clear prompt"*, it returns structured data, and the script writes it back to Excel.

**Why this works:** Limiting the model to small, checkable outputs keeps errors visible and costs low.

**Prompt:**

```
Classify this support note into exactly one of: billing, delivery, product, other. Reply with JSON {"category": "..."} only. Note: <text>
```

[Open step 3](https://stepgleam.com/guides/automate-recurring-excel-reports-with-python-and-an-llm-api#step-3)

### Step 4: Snapshot inputs and validate totals on every run

**Tool:** Python + openpyxl

Save a copy of the raw inputs for each run, then check row counts and reconcile totals against the source before writing the report. One practitioner says *"every run should snapshot its inputs, validate totals, and fail closed when the expected structure changes."*

**Why this works:** A snapshot lets you reproduce a bad week; validation stops it from going out.

[Open step 4](https://stepgleam.com/guides/automate-recurring-excel-reports-with-python-and-an-llm-api#step-4)

### Step 5: Stop and flag instead of guessing

**Tool:** Python + openpyxl

If a file changes format, expected rows disappear or totals don't reconcile, the script should stop and report the exception. In the words of one commenter, it should not *"let an LLM make a best guess and continue."* Send yourself the error, not a plausible-looking bad spreadsheet.

**Why this works:** Silent failure is worse than no report.

[Open step 5](https://stepgleam.com/guides/automate-recurring-excel-reports-with-python-and-an-llm-api#step-5)

### Step 6: Know when to drive real Excel instead

**Tool:** Excel

openpyxl writes files but doesn't run Excel's calc engine. One user who hit this wall says external links, volatile functions, some array formulas and files under SharePoint/OneDrive sync paths can force you back to *"driving the real app"*. Open the output in Excel and check that formulas calculate before you automate delivery.

**Why this works:** Knowing the library's limits prevents reports that look fine and calculate wrong.

[Open step 6](https://stepgleam.com/guides/automate-recurring-excel-reports-with-python-and-an-llm-api#step-6)

## Pro tips

- Keep the normalized data outside the workbook too. One practitioner advises not making the workbook the only source of truth.
- For agent-driven workbook edits, tools such as xl-cli add plan, dry-run, backup and verify steps around openpyxl.

## Common mistakes

- Letting an agent orchestrate deterministic steps it could get wrong.
- Sending whole sheets to the API when one column needs interpretation.
- Delivering the report when validation failed "just this once".

## FAQ

### Do I need Microsoft Graph?

If the workbooks live in OneDrive or SharePoint and must be edited in place, practitioners suggest the Graph API. For local files, openpyxl is enough.

### Can I do this without code?

Partly. One commenter suggests n8n or make.com to chain steps. The validate-and-stop principle still applies.

---

Canonical guide: https://stepgleam.com/guides/automate-recurring-excel-reports-with-python-and-an-llm-api
Read this guide as Markdown: https://stepgleam.com/guides/automate-recurring-excel-reports-with-python-and-an-llm-api.md