mailwal

Build a Sales Report Robot with Python and Polars: Day 1 - Turn a Folder of Excel Files into a PDF Report

Daily Python Projects

Build a Sales Report Robot with Python and Polars: Day 1 - Turn a Folder of Excel Files into a PDF Report

Merge four Excel files, add up the sales and save a PDF report, all in less than a second.

In many offices, someone spends Monday morning on the same job. Each regional office sends last week’s sales as an Excel file. Then that person copies the files into one sheet, makes a pivot table and a chart, and saves everything as a PDF for the manager. This week we build a Python program that does that whole job, using Polars, a fast library for working with tables of data.

Here is what we build today. On the left are four Excel files, one from each region. On the right is the PDF report our program makes from them:

Daily Python Projects is a reader-supported publication. To receive new posts and support my work, consider becoming a free or paid subscriber.

The report shows the revenue of each region as a table and as a chart, and further down the revenue of each product. The program makes it in less than a second.

Projects in this week’s series:

Day 1 (Today): We merge a folder of Excel files with Polars and turn the totals into a PDF report.

Day 2 (Wednesday): The robot sends the report by email, and we schedule it to run by itself every Monday on Windows, Mac and Linux.

View All Projects This Week

Get the data

Please download the Excel files and place them in a “sales” folder in the project directory:

South
7.09KB ∙ XLSX file
East
7.54KB ∙ XLSX file
North
7.42KB ∙ XLSX file
West
7.21KB ∙ XLSX file

And here is how those files look like inside:

The output

Here is how the generated PDF will look like:

Sales Report 2026 10 27
25.3KB ∙ PDF file

What you’ll learn

  • How to read many Excel files and stack them into one table with Polars

  • How to add a column and make totals with group_by, the way a pivot table does it in Excel

  • How to put tables and a chart into a PDF file with fpdf2

The key idea

First the program reads every Excel file in the folder and stacks them into one table called all_tables, with 210 rows. After that, two steps do the real work. We add a Revenue column, and then we add up the units and the revenue for each region:

all_tables = all_tables.with_columns(Revenue=pl.col('Units') * pl.col('Unit Price'))

region_totals = all_tables.group_by('Region').agg(pl.col('Units', 'Revenue').sum())
region_totals = region_totals.sort('Revenue', descending=True)

In Polars, pl.col('Units') means the Units column. So the first line multiplies two columns, and Polars does it for all 210 rows at once. There is no for loop.

Then group_by('Region') puts the rows into one group per region, and agg() says what to do with each group. Here it adds up the units and the revenue. This is exactly what a pivot table does in Excel. If you print region_totals, you see this:

Look at North. It sold the most units, 446, but it comes third in revenue, because it sold only 24 office chairs, our most expensive product. West sold 91. That is why the report keeps the units next to the revenue.

Worth knowing

  • pl.read_excel() needs a second package called fastexcel, which does the actual reading. Without it, Polars stops and tells you to install it.

  • pl.concat() needs the same column names in every file. If one office renames Unit Price to Price, the program stops with “column names don’t match”, so you never get a report with wrong numbers.

  • While a file is open in Excel, Excel keeps a hidden file next to it whose name starts with ~$. That is why the loop skips those names.

Run it

Install the libraries and run main.py (found the script at the bottom of the post):

pip install polars fastexcel matplotlib fpdf2
python main.py

Running main.py will print out each step and will save the report in the output folder as a PDF:

You can also put your own Excel files in the sales folder. They need the columns Date, Region, Product, Units and Unit Price.

Try this

Add a third table to the PDF with the revenue of each day of the week. A small hint: pl.col('Date').dt.strftime('%A') gives you the name of the day. Which day had the most revenue? Reply and tell me what you get.

Solution

The full code is below. Every step in main.py has a comment.

Tomorrow the robot learns to send the PDF by email, and we schedule it so it runs by itself every Monday morning. Day 2 comes with the full code and a video for paid subscribers.

Daily Python Projects is a reader-supported publication. To receive new posts and support my work, consider becoming a free or paid subscriber.

Practice Python on ActiveSkill: solve up to 10 free exercises a day, follow a guided path, and keep improving between project posts - activeskill.dev

© 2026 Ardit Sulce
[email protected]