How to Automate Excel with Python: 5 Easy Steps?

An innovative technique used by companies to perform Data Analysis is linking their data source with a programming language and having it automate their Data Analysis. One such type of automation is Python Excel Automation. Python is the most popularly used programming language today and has limitless applications across many fields.

Its flexible and dynamic capabilities make it the ideal choice when it comes to deployment and maintenance. MS Excel is a popular Microsoft application that operates in the form of a spreadsheet and is used to save and analyze numerical data. It was one of the first tools used by companies all around the world to analyze data to gain insights.

It is also termed a traditional BI (Business Intelligence) tool. By setting up Python Excel Automation, you can transfer data automatically from various sources to multiple destinations seamlessly. This article provides a step-by-step guide to help you automate Excel with Python. It also gives you an overview of Python and Excel to better understand these technologies individually. Read along for more!

Table of Contents

• Prerequisites
• Introduction to Python
• Introduction to MS Excel
• Steps to Set Up Python Excel Automation
  • Step 1: Analyzing the Excel Dataset
  • Step 2: Making Pivot Tables using Pandas
  • Step 3: Designing the Reports using Openpyxl
  • Step 4: Automating the Report with Python
  • Step 5: Scheduling the Python Script
• Conclusion

Prerequisites

• Working knowledge of MS Excel.
• Strong Technical Foundation in Python.
• Knowledge of popular Python Libraries.

Introduction to Python

Python is a high-level programming language that uses an interpreter to process code. It was developed by Guido Von Rossum and was released in 1991. Its seamless code readability and dynamic nature make it an excellent choice when coding. It is used in a wide variety of fields including Machine Learning, Artificial Intelligence, Server-Side Scripting, Data Analytics, and Deep Learning. It was developed by Python Software Foundation and its stable release was on 3 May 2021.

Python is an Interactive, Interpreted, Object-Oriented programming language that incorporates multiple attributes like Exceptions, Modules, Dynamic Typing, Dynamic Binding, Classes, High-level Dynamic Data Types, etc. It can also be used to make system calls to almost all well-known Operating Systems.

Key Features of Python

Python houses a wide range of features that differentiates it from other programming languages. Some of those features are given below:

• It is free and open-source. You can download Python from their official website. It can be downloaded and installed easily. As Python is open-source, users that have a good technical background can make changes to the code as per business use cases and product requirements.
• Python is a beginner-friendly language and hence, most people with basic programming knowledge can easily learn the Python syntax and start coding on it. 
• While using Python, developers do not need to have any information on the System Architecture or manage memory usage manually. The Python Interpreter handles this automatically.
• Python is highly robust and portable. This means that the Python code written on one computer can easily be transferred to another system and can run without any issues.
• Python also supports Object-Oriented Programming that allows users to write readable and reusable code.

With Python Excel Automation, you can use the portable and robust nature of Python in tandem with Excel to improve your Data Analysis efficiently.

Introduction to MS Excel

Microsoft Excel is a spreadsheet program developed by Microsoft for Windows, macOS, Android, and iOS. It was released in 1987 and has been used by multiple calculations, graphic tools, pivot tables, and a macro programming language like Visual Basic for Applications. MS Excel has gained popularity over these years because the data is easy to save and data can be added or removed without any discomfort.

Key Features of MS Excel

MS Excel has many unique features that make it a good choice over other spreadsheet software. Some of those features are given below:

• MS Excel allows you to create headers and footers in the spreadsheet document and allows users to protect their data by providing passwords for their data.
• It supports filtering where you can find the required data in your workbook and replace it with the value you need.
• MS Excel supports Data Sorting, thereby allowing you to sort your data in ascending or descending order.
• It supports multiple variations of formulae, so you can perform all types of calculations easily. It also supports multiple types of Data Visualization and charts.
• You can perform Formula Auditing to form relationships between cells and tables.

With Python Excel Automation, you can use the sorting and robust nature of MS Excel with a powerful programming language like Python to automate reporting seamlessly.

To learn more about MS Excel, click this link.

Steps to Set Up Python Excel Automation

Now that you have a good understanding of Python and MS Excel, it is now time to understand the steps to set up Python Excel Automation. You can follow the below steps to set Python Excel Automation up:

• Step 1: Analyzing the Excel Dataset
• Step 2: Making Pivot Tables using Pandas
• Step 3: Designing the Reports using Openpyxl
• Step 4: Automating the Report with Python
• Step 5: Scheduling the Python Script

Step 1: Analyzing the Excel Dataset

The first step in Python Excel Automation is to analyze the Dataset. The Dataset used in this tutorial is a Sales Dataset. This Dataset is also available on Kaggle. As this Dataset is in .csv format, you need to change it to .xslx format. The data will be used to create the below report after setting up our Python Excel Automation.

Step 2: Making Pivot Tables using Pandas

The next step in Python Excel Automation is to design Pivot Tables. Before doing that, you need to import the following libraries:

import pandas as pd
import openpyxl
from openpyxl import load_workbook
from openpyxl.styles import Font
from openpyxl.chart import BarChart, Reference
import string

Pandas is used to read the Excel file, create the Pivot table and export it to Excel. You can then use the Openpyxl library in Python to write Excel formulas, make charts and spreadsheets in Python.

To read your Excel file, make sure that the file is in the same place where your Python script is located and run the following code in Excel:

excel_file = pd.read_excel('supermarket_sales.xlsx')
excel_file[['Gender', 'Product line', 'Total']]

To create the pivot table, you need to access the excel_file data frame that you created previously. You can use the “.pivot_table()” to create the table. If you want to create a pivot table to show the total money split up for males and females you can run the below code:

report_table = excel_file.pivot_table(index='Gender',columns='Product line',values='Total',aggfunc='sum').round(0)

Finally, to export the Pivot Table, we will use the “.to_excel()” method as shown below:

report_table.to_excel('report_2021.xlsx',sheet_name='Report',startrow=4)

The Excel folder is now exported in the same location as your Python scripts.

Step 3: Designing the Reports using Openpyxl

The next step in Python Excel Automation is to design the reports. To make the report, you need to use the “load_workbook” method, that is imported from Openpyxl and save it using the “.save()” method. This is shown below:

wb = load_workbook('report_2021.xlsx')
sheet = wb['Report']
# cell references (original spreadsheet)
min_column = wb.active.min_column
max_column = wb.active.max_column
min_row = wb.active.min_row
max_row = wb.active.max_row

Python Excel Automation allows you to create Excel charts using Pivot Tables. To create an Excel chart using a Pivot Table, you need to use the Barchart Module and to identify the position of the data and category values, you can use the Reference Module. Both of these were imported before in Step 1. You can write Excel-based formulas in Python, the same way you write them in Excel. An example of this is shown below:

sheet['B7'] = '=SUM(B5:B6)'
sheet['B7'].style = 'Currency

Step 4: Automating the Report with Python

The next step in Python Excel Automation is to automate your report. You can write all the code into one function so that it will be easy to automate the report. That code is shown below:

import pandas as pd
import openpyxl
from openpyxl import load_workbook
from openpyxl.styles import Font
from openpyxl.chart import BarChart, Reference
import string

def automate_excel(file_name):
   """The file name should have the following structure: sales_month.xlsx"""
   # read excel file
   excel_file = pd.read_excel(file_name)
   # make pivot table
   report_table = excel_file.pivot_table(index='Gender', columns='Product line', values='Total', aggfunc='sum').round(0)
   # splitting the month and extension from the file name
   month_and_extension = file_name.split('_')[1]
   # send the report table to excel file
   report_table.to_excel(f'report_{month_and_extension}', sheet_name='Report', startrow=4)
   # loading workbook and selecting sheet
   wb = load_workbook(f'report_{month_and_extension}')
   sheet = wb['Report']
   # cell references (original spreadsheet)
   min_column = wb.active.min_column
   max_column = wb.active.max_column
   min_row = wb.active.min_row
   max_row = wb.active.max_row
   # adding a chart
   barchart = BarChart()
   data = Reference(sheet, min_col=min_column+1, max_col=max_column, min_row=min_row, max_row=max_row) #including headers
   categories = Reference(sheet, min_col=min_column, max_col=min_column, min_row=min_row+1, max_row=max_row) #not including headers
   barchart.add_data(data, titles_from_data=True)
   barchart.set_categories(categories)
   sheet.add_chart(barchart, "B12") #location chart
   barchart.title = 'Sales by Product line'
   barchart.style = 2 #choose the chart style
   # applying formulas
   # first create alphabet list as references for cells
   alphabet = list(string.ascii_uppercase)
   excel_alphabet = alphabet[0:max_column] #note: Python lists start on 0 -> A=0, B=1, C=2. #note2 the [a:b] takes b-a elements
   # sum in columns B-G
   for i in excel_alphabet:
       if i!='A':
           sheet[f'{i}{max_row+1}'] = f'=SUM({i}{min_row+1}:{i}{max_row})'
           sheet[f'{i}{max_row+1}'].style = 'Currency'
   sheet[f'{excel_alphabet[0]}{max_row+1}'] = 'Total'
   # getting month name
   month_name = month_and_extension.split('.')[0]
   # formatting the report
   sheet['A1'] = 'Sales Report'
   sheet['A2'] = month_name.title()
   sheet['A1'].font = Font('Arial', bold=True, size=20)
   sheet['A2'].font = Font('Arial', bold=True, size=10)
   wb.save(f'report_{month_and_extension}')
   return

Step 5: Scheduling the Python Script

The final step in Python Excel Automation is to run the Python script at different schedules as per data requirements. You just need to use the task scheduler or cron on Windows and Mac respectively.

That’s it! You have successfully set up Python Excel Automation in 5 easy steps!

Conclusion

This article gave you a step-by-step guide on setting up Python Excel Automation. It also gave you an overview of both Python and MS Excel along with their features. Overall, Python Excel Automation is an innovative process that you can use to create visual reports on Python just the same way as you would on Excel seamlessly. Organizations can leverage Python Excel Automation to leverage their business tasks according to their needs.

In case you want to integrate data from data sources like Python and MS Excel into your desired Database/destination and seamlessly visualize it in a BI tool of your choice, then Hevo Data is the right choice for you! It will help simplify the ETL and management process of both the data sources and destinations.

Want to take Hevo for a spin? Sign up here for a 14-day free trial and experience the feature-rich Hevo suite first hand.

Share your experience of setting up Python Excel Automation in the comments section below.