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.
Tools used
- Python + openpyxlDeterministic reading, transforming and writing of .xlsx files
- OpenAI API or Claude APICalled only for fuzzy fields (classification, extraction)
- Claude CodeCoding agent that reads, edits and tests the repo
- Excel (Microsoft 365)Where the data and formulas live
Step navigator
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.
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.
Step-by-step instructions
Split the job into stages before choosing tools
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.
Where this comes from: u/IncreaseNegative4614 and u/matt88dev in r/AI_Agents: "AI Agents for Excel".
Why this works
Naming stages shows which ones are deterministic and which genuinely need a model.
Have a coding agent write the script with openpyxl
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.
Where this comes from: u/AICodeSmith in r/automation: "Best way to automate simple tasks in word/excel?".
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.
Why this works
Code does the same thing every week; a UI-driving agent might not.
Call the model API only for fields that need interpretation
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.
Where this comes from: u/Much_Pomegranate6272 in r/AiForSmallBusiness: "Is there an AI agent that can handle Excel + online data enrichment?".
Classify this support note into exactly one of: billing, delivery, product, other. Reply with JSON {"category": "..."} only. Note: <text>Why this works
Limiting the model to small, checkable outputs keeps errors visible and costs low.
Snapshot inputs and validate totals on every run
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."
Where this comes from: u/IncreaseNegative4614 in r/AI_Agents: "AI Agents for Excel".
Why this works
A snapshot lets you reproduce a bad week; validation stops it from going out.
Stop and flag instead of guessing
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.
Where this comes from: u/matt88dev in r/AI_Agents: "AI Agents for Excel".
Why this works
Silent failure is worse than no report.
Know when to drive real Excel instead
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.
Where this comes from: u/Deep_Ad1959 in r/automation: "Best way to automate simple tasks in word/excel?".
Why this works
Knowing the library's limits prevents reports that look fine and calculate wrong.
End of guide · Reviewed Oct 26, 2026
Pro tips2
- 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 mistakes3
- 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".
FAQ2
Do I need Microsoft Graph?
Can I do this without code?
Related field guides.
ExcelClean a messy workbook: have Copilot flag issues, then decide yourself
Use Copilot as an auditor rather than an autopilot. Ask it to flag anything that needs manual review on its own sheet, then fix the data with Excel features you control.
ExcelWrite complex Excel formulas with Copilot, then prove they work
How r/excel users actually use Copilot for formulas: describe each field, take the draft as a starting point, test it on awkward rows, and check every reference before filling down.