Python Meets Excel: Automate Monthly Reports Without Opening Excel

Manually updating Excel reports every month is tedious, error-prone, and unnecessary in 2024. With just a few lines of Python code, you can automate the entire process — from importing data to formatting and even emailing your reports — without ever having to open Excel manually.

In this guide, we’ll walk step-by-step through building a Python automation pipeline using pandas and openpyxl. This approach is perfect for generating monthly analytics, finance summaries, or operational metrics that require consistent formatting.

1. Setting Up Your Environment

Before we write any code, let’s install the required libraries:

pip install pandas openpyxl

Why these libraries?

pandas is great for ingesting and transforming tabular data.
openpyxl lets you manipulate Excel files directly, including formatting, styling, and formulas.

We’ll also use Python’s built-in smtplib for sending the reports via email.

2. Loading and Transforming the Data

Here we simulate loading sales data from a CSV and performing a monthly aggregation:

import pandas as pd

# Load raw sales data
sales_df = pd.read_csv('sales_data.csv')

# Convert date column to datetime
sales_df['date'] = pd.to_datetime(sales_df['date'])

# Extract month
sales_df['month'] = sales_df['date'].dt.to_period('M')

# Aggregate sales by region and month
monthly_summary = sales_df.groupby(['region', 'month'])[['revenue']].sum().reset_index()

This gives us an up-to-date monthly revenue breakdown per region. The transformation step is modular, so you can later swap the data source (e.g., database, API).

3. Writing and Formatting the Excel Report

Let’s now take that summary and write it to an Excel file with styling.

from openpyxl import Workbook
from openpyxl.styles import Font, Alignment

# Create a workbook and add a worksheet
wb = Workbook()
ws = wb.active
ws.title = 'Monthly Report'

# Write header row with styling
headers = list(monthly_summary.columns)
for col_num, header in enumerate(headers, 1):
    cell = ws.cell(row=1, column=col_num, value=header)
    cell.font = Font(bold=True)
    cell.alignment = Alignment(horizontal='center')

# Write data rows
for row in monthly_summary.itertuples(index=False):
    ws.append(row)

# Auto-adjust column widths
for col in ws.columns:
    max_length = max(len(str(cell.value)) if cell.value else 0 for cell in col)
    ws.column_dimensions[col[0].column_letter].width = max_length + 2

# Save the Excel file
report_file = 'Monthly_Report.xlsx'
wb.save(report_file)

At this point, you have an Excel file named Monthly_Report.xlsx that’s clean, formatted, and ready to go.

4. Sending the Excel Report via Email

You can automatically email the report to your team using smtplib and email libraries.

import smtplib
from email.message import EmailMessage

# Configure email
msg = EmailMessage()
msg['Subject'] = 'Automated Monthly Sales Report'
msg['From'] = 'reports@company.com'
msg['To'] = 'team@company.com'
msg.set_content('Hi team,\n\nFind attached the latest monthly sales report.\n\nBest,\nAutomation Bot')

# Attach the Excel file
with open(report_file, 'rb') as f:
    file_data = f.read()
    msg.add_attachment(file_data, maintype='application', subtype='vnd.openxmlformats-officedocument.spreadsheetml.sheet', filename=report_file)

# Send the email
with smtplib.SMTP('smtp.yourcompany.com', 587) as smtp:
    smtp.starttls()
    smtp.login('your_email', 'your_password')
    smtp.send_message(msg)

Security tip: Use environment variables or a key vault service instead of hardcoding credentials.

5. Scheduling the Script for Monthly Execution

You can run this script automatically every month using the built-in scheduler appropriate for your OS or cloud platform:

Windows: Use Task Scheduler to run a .bat file that calls your Python script.
macOS/Linux: Use a cron job like 0 9 1 * * /usr/bin/python3 /path/to/script.py
Cloud: Schedule with AWS Lambda + EventBridge or GitHub Actions for monthly execution.

With this step handled, your reports fully automate themselves on the first of every month.

Conclusion: Why This Approach Works

This method is reliable, repeatable, and infinitely customizable. You can update this script to handle multiple tabs, charts, conditional formatting, or even Excel formulas. No mouse-clicks, no manual cross-checking — just consistent, automated results.

Download Sample Code: You can download the full working script from this GitHub repo: github.com/example/python-excel-reporter

Happy automating!

Useful links: