Chapter 7: Case Study - Automating a Monthly Sales Report

Congratulations on making it this far! It's time to put all your skills together to tackle a real-world scenario. In this chapter, we will build a single Python script that automates the entire process of creating a professional monthly sales report.

The Goal

Our objective is to write a script that takes a raw CSV (Comma-Separated Values) file of sales data, processes it, and generates a polished, multi-sheet Excel report complete with a summary, formatted data, and a chart for visualization.

The Raw Data: sales_data.csv

First, you'll need some raw data. Create a file named sales_data.csv and paste the following content into it. This is a common format for exporting data from databases or other systems.

Product,Category,Price,Units Sold
Laptop,Electronics,1200,15
Mouse,Electronics,25,150
Keyboard,Electronics,75,80
Monitor,Electronics,300,45
Webcam,Electronics,50,100
Desk Chair,Furniture,250,30
Office Desk,Furniture,400,20
Pen Holder,Office Supplies,10,200
Notebook,Office Supplies,5,300
Stapler,Office Supplies,12,75

Because we are working with a .csv file, we will need Python's built-in csv library to read it.

The Script: generate_sales_report.py

This will be our most comprehensive script yet. We'll build it step-by-step. Create a new file named generate_sales_report.py.

Step 1: Reading the CSV and Setting up the Workbook

First, we'll read the data from the CSV file and set up a new Excel workbook with a "Sales Data" sheet.

import csv
from openpyxl import Workbook
from openpyxl.styles import Font, PatternFill
from openpyxl.chart import BarChart, Reference

# Create a new workbook
wb = Workbook()
ws = wb.active
ws.title = "Sales Data"

# Read data from the CSV file
with open('sales_data.csv', 'r') as f:
    reader = csv.reader(f)
    for row in reader:
        ws.append(row) # append() adds a row of data

print("Step 1: CSV data loaded into the 'Sales Data' sheet.")

Step 2: Processing and Formatting the Data

Now, we'll loop through the data we just loaded, add a "Total Revenue" column with a formula, and apply the styling skills we learned in Chapter 4.

# (Add this code to the end of your script)

# Add a header for the new column
revenue_header = ws.cell(row=1, column=5)
revenue_header.value = "Total Revenue"

# Style the entire header row
header_font = Font(bold=True, color='FFFFFF')
header_fill = PatternFill(start_color='4F81BD', end_color='4F81BD', fill_type='solid')
for cell in ws[1]:
    cell.font = header_font
    cell.fill = header_fill

# Add formulas and format cells
for row_num in range(2, ws.max_row + 1):
    price_cell = ws.cell(row=row_num, column=3)
    units_cell = ws.cell(row=row_num, column=4)

    # Format currency
    price_cell.number_format = '$#,##0.00'

    # Add the revenue formula
    revenue_formula = f"={price_cell.coordinate}*{units_cell.coordinate}"
    revenue_cell = ws.cell(row=row_num, column=5)
    revenue_cell.value = revenue_formula
    revenue_cell.number_format = '$#,##0.00'

print("Step 2: Formulas and formatting applied.")

Step 3: Creating a Summary Sheet with a Chart

Finally, let's create a new sheet for a summary chart that visualizes the total revenue for each product.

# (Add this code to the end of your script)

# Create a new sheet for the chart
ws_chart = wb.create_sheet("Sales Chart")

# Create the Bar Chart
chart = BarChart()
chart.title = "Total Revenue by Product"
chart.x_axis.title = "Product"
chart.y_axis.title = "Revenue ($)"

# Select the data for the chart
data = Reference(ws, min_col=5, min_row=2, max_row=ws.max_row)
labels = Reference(ws, min_col=1, min_row=2, max_row=ws.max_row)

chart.add_data(data)
chart.set_categories(labels)
chart.legend = None # We don't need a legend for one series

# Add the chart to the new sheet
ws_chart.add_chart(chart, "A1")

print("Step 3: Summary chart created.")

# Save the final report
wb.save("monthly_sales_report.xlsx")
print("Automation complete! 'monthly_sales_report.xlsx' is ready.")

Run the complete script. In a few seconds, you'll have a multi-sheet, fully-formatted report with data and a chart, all generated from a simple CSV file.

What You've Accomplished

You have successfully built a complete automation workflow. This case study demonstrates how the skills from previous chapters—reading data, looping, styling, formulas, and charting—come together to create a powerful and practical solution. You are now equipped with the knowledge to start automating your own Excel tasks.