How to Create Excel Report Automation with Python

What will we cover in this tutorial?

How to create excel report automation with Python. This will be done on the data similar to this.

The data we work with – 1000 lines like the above – get Excel sheet form link below.

And we will create Excel reports from Python like these ones.

The first sheet we will create in Excel from Python.
The second Excel sheet we create from Python.

Get direct Download link to JuPyter Notebook and Excel sheet used in YouTube video and follow along.

Step 1: Load the data in Pandas DataFrame

Pandas is a fast, powerful, flexible and easy to use open source data analysis and manipulation tool, built on top of the Python programming language.

Pandas has great integration with Excel – both reading and writing Excel files. We will use that in this tutorial.

To read the data simply use the read_excel(…).

import pandas as pd

data = pd.read_excel("SalesData.xlsx", index_col="Date")
print(data.head())

Which will result in output similar to this.

	Sales rep	Item	Price	Quantity	Sale
Date					
2020-05-31	Mia	Markers	4	1	4
2020-02-01	Mia	Desk chair	199	2	398
2020-09-21	Oliver	Table	1099	2	2198
2020-07-15	Charlotte	Desk pad	9	2	18
2020-05-27	Emma	Book	12	1	12

Step 2: Group the data by Sales rep

To identify how much each Sales rep has sold, we will group it and calculate the sum.

repr = data.groupby("Sales rep")
repr_sales = repr['Sale'].sum()

Now that is too simple, right?

The data in rep_sales looks like this.

Sales rep
Charlotte     74599
Emma          65867
Ethan         40970
Liam          66989
Mia           88199
Noah          78575
Oliver        89355
Sophia       103480
William       80400
Name: Sale, dtype: int64

Step 3: Group the data by month, change to month names and sum it

This is a bit more challenging, as we need to group the dates in months and rename the index to month names.

Except if you know how to do it, as you will in a moment.

months = data.groupby(pd.Grouper(freq="M"))
months_sales = months['Sale'].sum()
months_sales.index = months_sales.index.month_name()

You are awesome. See, you use groupby on a Grouper with frequency month (“M”). To use the month name on index and not the last day of month, you reassign the index to use index.month_name().

This creates the following output where you can see the month names are used.

Date
January      69990
February     51847
March        67500
April        58401
May          40319
June         59397
July         64251
August       51571
September    55666
October      50093
November     57458
December     61941
Name: Sale, dtype: int64

Step 4: Create Excel sheet with the data from Step 2 and 3

This is done by creating an Excel writer.

writer = pd.ExcelWriter("SalesReport.xlsx")
repr_sales.to_excel(writer, sheet_name="Sale per rep")
months_sales.to_excel(writer, sheet_name="Sale per month")
writer.close()

This will create an Excel sheet of name SalesReport.xlsx, with two sheets Sale per rep and Sale per month, respectively.

But let’s try to add the two charts.

Step 5: Adding the charts

This is a bit more involved. As you see, the above steps have been quite straight forward and not required any high level Python programming skills (or Excel, except you need to know how to open an Excel sheet).

writer = pd.ExcelWriter("SalesReport.xlsx")
repr_sales.to_excel(writer, sheet_name="Sale per rep")
months_sales.to_excel(writer, sheet_name="Sale per month")

chart = writer.book.add_chart({'type': 'column'})
chart.add_series({
    'values': '=\'Sale per rep\'!$B$2:$B$10',
    'categories': '=\'Sale per rep\'!$A$2:$A$10',
    'name': "Sale"
})
writer.sheets['Sale per rep'].insert_chart("D2", chart)

chart = writer.book.add_chart({'type': 'column'})
chart.add_series({
    'values': '=\'Sale per month\'!$B$2:$B$13',
    'categories': '=\'Sale per month\'!$A$2:$A$13',
    'name': "Sale"
})
writer.sheets['Sale per month'].insert_chart("D2", chart)

writer.close()

This will create the sheets we have been waiting for. The charts are dynamic, meaning, that if you change the values the charts will change accordingly.

First sheet from our results
Second sheet from out results

Final remarks

Remember to see the YouTube video, which explains the material in more depth.

If you like the above content, please share it on a social media or point a friend in this direction.

Leave a Reply