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
- 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.
Hevo Data, a No-code Data Pipeline helps to load data from any data source such as Databases, SaaS applications, Cloud Storage, SDK,s, and Streaming Services and simplifies the ETL process. It supports MS Excel and Python, along with 150+ data sources (including 30+ free data sources), and is a 3-step process by just selecting the data source, providing valid credentials, and choosing the destination. Hevo not only loads the data onto the desired Data Warehouse/destination but also enriches the data and transforms it into an analysis-ready form without having to write a single line of code.
Its completely automated pipeline offers data to be delivered in real-time without any loss from source to destination. Its fault-tolerant and scalable architecture ensures that the data is handled in a secure, consistent manner with zero data loss and supports different forms of data. The solutions provided are consistent and work with different BI tools as well.
Check out why Hevo is the Best:
- Secure: Hevo has a fault-tolerant architecture that ensures that the data is handled in a secure, consistent manner with zero data loss.
- Schema Management: Hevo takes away the tedious task of schema management & automatically detects the schema of incoming data and maps it to the destination schema.
- Minimal Learning: Hevo, with its simple and interactive UI, is extremely simple for new customers to work on and perform operations.
- Hevo Is Built To Scale: As the number of sources and the volume of your data grows, Hevo scales horizontally, handling millions of records per minute with very little latency.
- Incremental Data Load: Hevo allows the transfer of data that has been modified in real-time. This ensures efficient utilization of bandwidth on both ends.
- Live Support: The Hevo team is available round the clock to extend exceptional support to its customers through chat, email, and support calls.
- Live Monitoring: Hevo allows you to monitor the data flow and check where your data is at a particular point in time.
Simplify your Python Data Analysis with Hevo today! Sign up here for a 14-day free trial!
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
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.
Image Source: towardsdatascience.com
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.