A small US business kept building a weekly sales report by hand in Excel. Here’s how I automated it with Python and pandas and emailed it with Gmail’s SMTP.

A small business owner I work with was spending about 30 minutes every Monday opening a CSV export from their sales system, summing columns in Excel, and typing the numbers into an email. It’s exactly the kind of boring, repeatable task Python is great at.

In this post I’ll show you how I automated it: read the CSV, build a clean summary, and send it by email. By the end you’ll have a script you can run with one command.

The problem

The sales system exports a CSV with columns date, customer, and amount.
Every week someone needs the total sales, the number of orders, and the top 3 customers.
The result has to land in the owner’s inbox as a short, readable email.

What you need

Python 3.10 or newer
The pandas library
A Gmail account with 2-Step Verification (for the app password)

python3 -m venv venv
source venv/bin/activate
pip install pandas

Step 1: Read the CSV and build the summary

Here is the function I use. It takes the CSV path and returns plain text.

import pandas as pd


def build_summary(path: str) -> str:
    df = pd.read_csv(path, parse_dates=["date"])

    total = df["amount"].sum()
    orders = len(df)
    top = df.groupby("customer")["amount"].sum().nlargest(3)

    lines = [
        f"Orders: {orders}",
        f"Total sales: ${total:,.2f}",
        "",
        "Top 3 customers:",
    ]
    for customer, amount in top.items():
        lines.append(f"  {customer}: ${amount:,.2f}")

    return "\n".join(lines)

The :,.2f format gives you US-style numbers like $12,430.50, which is what American clients expect.

Step 2: Create a Gmail app password

Gmail will reject your normal account password for scripts. I had to create an app password:

Turn on 2-Step Verification in your Google account.
Search for “App passwords” in your account settings.
Create one and copy the 16-character code.

Don’t paste it in your code. Keep it in an environment variable.

Step 3: Send the email

import os
import smtplib
from email.message import EmailMessage


def send_email(body: str) -> None:
    msg = EmailMessage()
    msg["Subject"] = "Weekly Sales Summary"
    msg["From"] = os.environ["SMTP_USER"]
    msg["To"] = os.environ["REPORT_TO"]
    msg.set_content(body)

    with smtplib.SMTP_SSL("smtp.gmail.com", 465) as server:
        server.login(os.environ["SMTP_USER"], os.environ["SMTP_PASSWORD"])
        server.send_message(msg)

Step 4: Put it together

if __name__ == "__main__":
    summary = build_summary("sales.csv")
    send_email(summary)
    print("Report sent.")

Run it with your variables set:

export SMTP_USER="you@gmail.com"
export SMTP_PASSWORD="your-16-char-app-password"
export REPORT_TO="owner@clientbusiness.com"
python report.py

Errors I ran into

smtplib.SMTPAuthenticationError: Username and Password not accepted I used my regular Gmail password. Use the app password instead, and make sure there are no spaces in it.

KeyError: 'amount' The CSV header was Amount with a capital A. Print df.columns to see the exact names, and clean them with df.columns = df.columns.str.strip().str.lower().

Totals look wrong The amounts were exported as text like "$1,200.00". Convert them first:

df["amount"] = df["amount"].astype(str).str.replace(r"[$,]", "", regex=True).astype(float)

What I’d do next

Once this works on your machine, the natural next step is to run it automatically every week on a server. I cover that in a separate post about scheduling scripts on a cheap VPS.

FAQ

Can I send an HTML email or attach a PDF? Yes. EmailMessage supports add_alternative(html, subtype="html") and add_attachment(...).

Is Gmail okay for a business? It’s fine for low volume. For larger volume, a transactional email service is a better fit.

Do I need pandas for something this small? No, the standard csv module works too, but pandas makes grouping and totals much shorter.

How I Automated a Weekly Sales Report From a CSV With Python (and Emailed It to the Client)