Automate a Monthly Excel Report with Python

Every month many finance teams do the same job by hand: open each sales file, copy the rows into one sheet, build a pivot table, format the numbers and save the summary. In this lesson you will write one small Python script that does all of this in a few seconds. You write it once, and from then on you only drop the new month’s file into a folder and run it, or let your computer run it for you on a fixed day.

What you will learn

• How to set up a folder so a script can find each month’s file
• How a script reads every Excel file in a folder and combines them into one table
• How to calculate totals by region and month and check that they are right
• How to write a formatted summary workbook with bold headers, number formats, column widths and frozen headers
• How to run the script by hand, and every month on its own with Task Scheduler on Windows or cron on a Mac

Before you start

You should know the basics of Python and pandas. If not, first do Python for Finance: Your First Steps and pandas: Clean and Analyse Excel Data.

You need Python with pandas and openpyxl on your own computer. Anaconda has both. With plain Python, install them once with pip install pandas openpyxl. Google Colab is not a good fit for this lesson, because the script has to run on your computer, where the files are, at a fixed time.

What we are building

Imagine you work at Sharma Traders. Each month the sales team sends one Excel file with that month’s sales. You keep them all in one folder:

sales_project/
    make_sample_files.py
    monthly_report.py
    monthly_sales/
        sales_2026-04.xlsx
        sales_2026-05.xlsx
        sales_2026-06.xlsx
    reports/
        sales_summary_2026-06.xlsx

• monthly_sales holds the input files. You only ever add files here.
• reports is where the script saves the summary. The script makes this folder by itself.
• monthly_report.py is the script you will write.

Here is what the script does, in order. Read the chart from top to bottom. The loop on the side means “go back and do the same for the next file”.

Why the file names matter

Every file is named sales_ plus the year and month, like sales_2026-04.xlsx. This one rule does two jobs:

1. The script finds the files by this pattern, so other files in the folder (like a draft named notes.xlsx) are left alone.
2. The script reads the month from the file name. Writing the year first and the month as two digits (2026-04, not April 26) means the months also sort in the right order, from oldest to newest.

Step 1: Make the practice files

You may not have three months of sales files at hand, so let us create practice files with a small script. Each file has three columns: Region, Customer and Amount. Here is all the data:

Month	Region	Customer	Amount
2026-04	North	Sharma Traders	42000
2026-04	South	Rao Enterprises	38500
2026-04	West	Gupta Stores	27250
2026-04	North	Kapoor Mart	15800
2026-05	North	Sharma Traders	39900
2026-05	South	Mehta & Sons	51200
2026-05	West	Gupta Stores	30100
2026-05	South	Rao Enterprises	12600
2026-06	North	Kapoor Mart	22300
2026-06	South	Mehta & Sons	47750
2026-06	West	Desai Retail	35900
2026-06	West	Gupta Stores	28400

Make a folder named sales_project, and save this code in it as make_sample_files.py:

from pathlib import Path
import pandas as pd

folder = Path("monthly_sales")
folder.mkdir(exist_ok=True)

data = {
    "2026-04": [("North", "Sharma Traders", 42000), ("South", "Rao Enterprises", 38500),
                ("West", "Gupta Stores", 27250), ("North", "Kapoor Mart", 15800)],
    "2026-05": [("North", "Sharma Traders", 39900), ("South", "Mehta & Sons", 51200),
                ("West", "Gupta Stores", 30100), ("South", "Rao Enterprises", 12600)],
    "2026-06": [("North", "Kapoor Mart", 22300), ("South", "Mehta & Sons", 47750),
                ("West", "Desai Retail", 35900), ("West", "Gupta Stores", 28400)],
}

for month, rows in data.items():
    df = pd.DataFrame(rows, columns=["Region", "Customer", "Amount"])
    df.to_excel(folder / f"sales_{month}.xlsx", index=False)
    print("Created", folder / f"sales_{month}.xlsx")

To run it, open a command window in the sales_project folder. On Windows, open Anaconda Prompt (or Command Prompt) and use cd to go to the folder. On a Mac, open Terminal and do the same. Then type:

python make_sample_files.py

On a Mac, type python3 instead of python if you are not using Anaconda. You should see:

Created monthly_sales/sales_2026-04.xlsx
Created monthly_sales/sales_2026-05.xlsx
Created monthly_sales/sales_2026-06.xlsx

On Windows the folder and file are joined with a backslash, monthly_sales\sales_2026-04.xlsx. That is normal. You now have three real Excel files you can open and look at.

Step 2: The full report script

Save this in the same sales_project folder as monthly_report.py. Do not worry if it looks long, we go through it part by part right after.

from pathlib import Path
import pandas as pd
from openpyxl.styles import Font

BASE = Path(__file__).resolve().parent
INPUT = BASE / "monthly_sales"
OUTPUT = BASE / "reports"

# 1. Find the monthly files
files = sorted(INPUT.glob("sales_*.xlsx"))
if not files:
    raise SystemExit(f"No sales files found in {INPUT}")

# 2. Read each file and remember which month it is
frames = []
for f in files:
    df = pd.read_excel(f)
    df["Month"] = f.stem.replace("sales_", "")
    frames.append(df)
    print(f"Read {f.name}: {len(df)} rows")

# 3. Combine into one table
data = pd.concat(frames, ignore_index=True)
if data[["Region", "Amount"]].isna().any().any():
    raise SystemExit("Some rows have no Region or Amount. Check the column names in each file.")

# 4. Totals by region and month
summary = pd.pivot_table(data, values="Amount", index="Region", columns="Month",
                         aggfunc="sum", fill_value=0, margins=True, margins_name="Total")
by_month = data.groupby("Month")["Amount"].sum().reset_index()

# 5. Check: the report total must match the raw data
assert summary.loc["Total", "Total"] == data["Amount"].sum(), "Totals do not match"

# 6. Write the workbook
OUTPUT.mkdir(exist_ok=True)
out_file = OUTPUT / f"sales_summary_{data['Month'].max()}.xlsx"
with pd.ExcelWriter(out_file, engine="openpyxl") as writer:
    summary.to_excel(writer, sheet_name="Summary")
    by_month.to_excel(writer, sheet_name="By month", index=False)
    data.to_excel(writer, sheet_name="All data", index=False)

    # 7. Format every sheet
    for ws in writer.sheets.values():
        for cell in ws[1]:
            cell.font = Font(bold=True)
        for row in ws.iter_rows(min_row=2):
            for cell in row:
                if isinstance(cell.value, (int, float)):
                    cell.number_format = "#,##0"
        for col in ws.columns:
            width = max(len(str(c.value)) for c in col if c.value is not None)
            ws.column_dimensions[col[0].column_letter].width = width + 4
        ws.freeze_panes = "A2"

print(f"Grand total: {data['Amount'].sum():,}")
print(f"Saved {out_file.name}")

Step 3: How the script works, part by part

The top: tools and folders

• from pathlib import Path brings in Path, Python’s built-in tool for folders and file names. It works the same on Windows and Mac.
• from openpyxl.styles import Font brings in the tool we use to make headers bold.
• BASE = Path(__file__).resolve().parent finds the folder where the script itself is saved. __file__ is the script’s own location.
• INPUT and OUTPUT are the two folders inside it. The / here joins folder names, it does not divide.

Why not just write "monthly_sales"? Because a scheduler usually starts your script from a different folder, and then a plain folder name points to the wrong place. Building every path from BASE means the script always finds its files, wherever it is started from.

Part 1: Find the files

INPUT.glob("sales_*.xlsx") lists every file whose name starts with sales_ and ends with .xlsx. The * is a wildcard, meaning “anything here”, just like in Excel’s Find box. sorted() puts them in order, oldest month first.

If the list is empty, raise SystemExit(...) stops the script with a clear message, instead of a confusing error further down.

Part 2: Read each file

This is a for loop over the files. For each file:

1. pd.read_excel(f) reads it into a small table named df.
2. f.stem is the file name without .xlsx, so sales_2026-04. Removing sales_ leaves 2026-04, which goes into a new Month column on every row.
3. frames.append(df) adds the table to a list.
4. print(...) reports how many rows were read, so you can see every file was picked up.

Part 3: Combine into one table

pd.concat(frames, ignore_index=True) stacks all the tables one below the other, like copying each month’s rows under the last. ignore_index=True renumbers the rows 0, 1, 2 to the end.

The if line is a safety check. If any month’s file has a column spelt differently, for example amount with a small a, pandas makes a new column and leaves Amount blank for those rows. The script stops and tells you, instead of quietly giving a smaller total.

Part 4: The totals

pd.pivot_table builds a table with Region down the side and Month across the top, adding up the Amount. margins=True adds a Total row and column. by_month is a simple list of the total for each month. reset_index() turns the month names back into a normal column, so they print nicely in Excel.

Part 5: Check the total

assert means “this must be true, otherwise stop”. The grand total of the pivot must equal the total of all the rows. Good accountants tick and tie their reports. This line does the same, every time.

Part 6: Write the workbook

• OUTPUT.mkdir(exist_ok=True) creates the reports folder if it is not there yet.
• data['Month'].max() is the latest month, so the report name becomes sales_summary_2026-06.xlsx. Next month it will be sales_summary_2026-07.xlsx, and the June report stays safe.
• pd.ExcelWriter(..., engine="openpyxl") opens the new workbook and each to_excel writes one sheet.

Part 7: Format the sheets

writer.sheets gives us each worksheet, so we can format them before the file is saved:

Code	What it does	Excel equivalent
cell.font = Font(bold=True) on ws[1]	Makes the header row bold	Home > Bold
cell.number_format = "#,##0"	Shows numbers with commas and no decimals	Format Cells > Number
ws.column_dimensions[...].width = width + 4	Sets each column wide enough for its longest value	Double-click the column border
ws.freeze_panes = "A2"	Keeps the header row visible when you scroll	View > Freeze Panes > Freeze Top Row

The isinstance(cell.value, (int, float)) test means “only if this cell holds a number”, so text cells like customer names are left alone. #,##0 is the same custom number format code you would type in Excel. You can swap in any Excel format code here, for example #,##0.00 for two decimal places.

Step 4: Run the script

In the same command window, inside sales_project, type:

python monthly_report.py

You should see:

Read sales_2026-04.xlsx: 4 rows
Read sales_2026-05.xlsx: 4 rows
Read sales_2026-06.xlsx: 4 rows
Grand total: 391,700
Saved sales_summary_2026-06.xlsx

Open reports/sales_summary_2026-06.xlsx. The Summary sheet looks like this:

Region	2026-04	2026-05	2026-06	Total
North	57,800	39,900	22,300	120,000
South	38,500	63,800	47,750	150,050
West	27,250	30,100	64,300	121,650
Total	123,550	133,800	134,350	391,700

The By month sheet has the three monthly totals, and the All data sheet has all 12 rows with their Month column, so anyone can trace a total back to the invoices.

Check one figure by hand: North in April is Sharma Traders 42,000 plus Kapoor Mart 15,800, which is 57,800. It matches.

Next month

When the July file arrives, save it as sales_2026-07.xlsx in monthly_sales and run the same command. Nothing in the script changes. We tried this with a practice July file: the script read four files and saved sales_summary_2026-07.xlsx, with a new 2026-07 column in the Summary.

Step 5: Run it every month on its own

Running one command is already quick. But you can also ask your computer to run it on a fixed day, for example at 9 AM on the 2nd of every month, after the sales team has sent the file. This chart shows the monthly routine once a scheduler is set up.

On Windows: Task Scheduler

First find the full path of your Python. In Anaconda Prompt (or Command Prompt), type where python. Copy the first line, for example C:\Users\Shashi\anaconda3\python.exe.

1. Open the Start menu, type Task Scheduler and open it.
2. In the Actions pane on the right, select Create Basic Task.
3. Give it a name like Monthly sales report and select Next.
4. For the trigger, choose Monthly and select Next.
5. Set the start time to 9:00 AM. In Months, select all months. In Days, choose 2. Select Next.
6. For the action, choose Start a program and select Next.
7. In Program/script, paste the full path of python.exe you copied.
8. In Add arguments, type monthly_report.py.
9. In Start in, type the full path of your sales_project folder, for example C:\Users\Shashi\sales_project.
10. Select Next, check the summary and select Finish.

To test it straight away, find your task in the Task Scheduler Library, right-click it and select Run. A new report should appear in the reports folder.

On a Mac: cron

cron is the scheduler built into macOS. You tell it when to run a command with one line of text.

1. Open Terminal and type which python3 (or which python with Anaconda). Copy the path it shows, for example /opt/anaconda3/bin/python3.
2. Type EDITOR=nano crontab -e and press Return. This opens your list of scheduled jobs in the simple nano editor.
3. Type one line like the one below, with your own paths:

0 9 2 * * /opt/anaconda3/bin/python3 /Users/shashi/sales_project/monthly_report.py >> /Users/shashi/sales_project/report_log.txt 2>&1

4. Press Ctrl + O and Return to save, then Ctrl + X to close nano.
5. Type crontab -l to see your saved job.

The five values at the start say when to run:

Value	Field	Meaning here
0	Minute	At minute 0
9	Hour	At 9 AM (24 hour clock)
2	Day of the month	On the 2nd
*	Month	Every month
*	Day of the week	Any day of the week

The part >> .../report_log.txt 2>&1 saves everything the script prints, including errors, into a log file. Since nobody watches the screen when cron runs, this log is how you see what happened.

Excel by hand versus the script

Monthly job	By hand in Excel	In the script
Collect the files	Open each file	INPUT.glob("sales_*.xlsx")
Join the rows	Copy and paste into one sheet	pd.concat(frames)
Add a month column	Type it next to each block	df["Month"] = ...
Totals by region and month	Insert > PivotTable	pd.pivot_table(...)
Tick the grand total	Compare with a SUM	assert ...
Format numbers and headers	Format Cells, Bold, Freeze Panes	Part 7 of the script
Do it again next month	Repeat every step	Run one command, or let the scheduler run it

Common mistakes

• Running the script inside Jupyter. __file__ exists only when Python runs a saved .py file. In a notebook you get NameError: name '__file__' is not defined. Run it from the command window as shown.
• The report is open in Excel. On Windows, the script cannot replace a workbook that is open, and stops with a PermissionError. Close the file and run again.
• A file with the wrong name. Sales_April.xlsx or sales_2026-4.xlsx breaks the pattern or the month order. Watch the “Read …” lines: every month should be listed.
• Column names that change between months. If one month says Sales Amount instead of Amount, the script stops with “Some rows have no Region or Amount”. Fix the header in that file, or rename the column in the script with df = df.rename(columns={"Sales Amount": "Amount"}) right after read_excel.
• Numbers stored as text. If an amount was typed as text in Excel (often with a green triangle in the cell), it will not add up properly. Fix it in the source file, or add df["Amount"] = pd.to_numeric(df["Amount"]) after reading.
• A scheduler that silently fails. Usually the Python path is wrong, or Start in is empty. Always test with Run in Task Scheduler, or read the log file on a Mac.

Quick recap

• Keep input files in one folder with one naming rule, like sales_2026-04.xlsx.
• The script finds the files with glob, reads each one with read_excel, tags its month and stacks them with concat.
• pivot_table gives totals by region and month, and an assert checks the grand total before anything is saved.
• pd.ExcelWriter with openpyxl writes the sheets, and a short loop adds bold headers, number formats, column widths and frozen headers.
• Schedule it with Task Scheduler on Windows or a cron line on a Mac, and always check the first runs.

Try it yourself

1. Make the practice files and run the report script. Check that the grand total is 391,700.
2. Create a July file with three rows: North, Sharma Traders, 41000. South, Rao Enterprises, 36000. West, Gupta Stores, 29500. Save it as sales_2026-07.xlsx and run the script again. The new report should be sales_summary_2026-07.xlsx with a grand total of 498,200.
3. Change the number format to #,##0.00 and run again. Every amount should now show two decimal places.
4. Add a fourth sheet named By customer with the total for each customer. Hint: data.groupby("Customer")["Amount"].sum().reset_index().

Where to go next

• pandas: Clean and Analyse Excel Data: clean each month’s file (spaces, dates, duplicates, blanks) before it goes into the report.
• Python for Finance: Your First Steps: loops, lists and functions explained from zero.
• Quick start: Create a macro: the Excel way to automate steps inside one workbook.
• Power Platform Automation: automate reports and approvals with Microsoft’s no-code tools.