Excel Automation: Generate Your Monthly Report in Five Minutes with Python

Monthly reporting is one of the biggest headaches for SME owners and clerical staff. Every month-end, you pull together piles of orders, invoices and sales records, manually add up the numbers, sort them, and build a report — hundreds of rows of copy & paste, and one wrong cell throws off the whole month. This tutorial shows you how to use Python and two free libraries to shrink the whole process down to five minutes, with zero manual work and zero mistakes.

Why use Python for Excel?

The Excel files you work with every day can be handled entirely by code. Python, together with pandas (a data-handling powerhouse) and openpyxl (which reads and writes .xlsx files directly), lets you:

  • Read automatically — load a whole .xlsx file at once, no cell-by-cell checking
  • Calculate automatically — totals, averages and grouped statistics in one line of code
  • Export automatically — write out a clean summary report

Step 1: Install the two libraries

Assuming you already have Python installed (see the previous beginner tutorial if not), open Command Prompt and run:

pip install pandas openpyxl

That's it — nothing else to configure.

Step 2: What are we processing?

Say you have a sales.xlsx with this month's sales records, with columns for date, product, client and amount. You want to know:

  • What's this month's total revenue?
  • How much did each product bring in?
  • Who's the biggest client?

Step 3: Write the script

Create a new file called monthly_report.py and paste in the code below:

import pandas as pd # Read the sales records df = pd.read_excel("sales.xlsx") # This month's total revenue total = df["Amount"].sum() print(f"Total revenue: ${total:,.0f}") # Revenue by product by_product = df.groupby("Product")["Amount"].sum().sort_values(ascending=False) print(by_product) # Top client top_client = df.groupby("Client")["Amount"].sum().idxmax() print(f"Biggest client: {top_client}") # Write the results to a new Excel file with pd.ExcelWriter("monthly_summary.xlsx") as w: by_product.to_frame("Revenue").to_excel(w, sheet_name="Product Stats") df.groupby("Client")["Amount"].sum().to_frame("Revenue").to_excel(w, sheet_name="Client Stats")

Line by line

  • pd.read_excel("sales.xlsx") — loads the whole file into a DataFrame (like a live spreadsheet).
  • df["Amount"].sum() — adds up every number in the "Amount" column.
  • groupby("Product") — groups rows by product, then applies sum() to each group.
  • sort_values(ascending=False) — sorts from largest to smallest so you instantly see the top seller.
  • idxmax() — returns the name of the client with the largest total.
  • ExcelWriter — writes all the results into a new file, with separate worksheets.

Step 4: Run it

Put sales.xlsx in the same folder as the script, then run:

python monthly_report.py

Within seconds you'll have a monthly_summary.xlsx containing both the product and client statistics sheets. Next month, drop in a new file and run it again.

Bonus: Run it automatically every month

Want to be even lazier? Schedule the script to run on the 1st of every month. On Windows, use Task Scheduler with a monthly trigger — your report will be ready before you even walk into the office.

Summary

From several hours of manual work to five minutes of code, the key isn't whether you can write code — it's whether you take the first step. With this approach you can go further: auto-merge Excel files from a dozen branches, auto-generate charts, auto-email the boss. Digital transformation doesn't have to happen all at once — starting with a single report is enough.