Build Excel Automation Tools with Python
Learn how to design Excel automation tools using Python for sales analytics.
Looking to automate Excel tasks using Python?
You’re not alone. Despite the growing popularity of Python, many businesses still rely heavily on Excel.
As a Data Scientist using Python, how can I deploy automation tools relying on Excel?
In this article, we’ll explore how to use Python to build automation tools for sales analytics on Excel.
By leveraging Python’s capabilities, we can overcome Excel’s limitations and provide a solution that’s easy for non-technical colleagues to use.
Excel Sales Data Analytics with Python
How many articles have you read stating that
- “Excel is Dead; long live Python.”
- “Python is the new Excel.”
- “Excel became obsolete.”
However, when you look around you, in your team, or in other departments, you can hardly find other colleagues using Python.
When you mention it, it is seen as a black box that people can’t trust because “it’s too complicated,” “we can’t see formulas,” or “I cannot run it on my computer.”
How can you build tools that are trusted and used?
A first step to promote it would be to allow them to run your scripts on their computers without prior Python knowledge.
Scenario
As a data scientist for a major retail company, your colleagues from the supply chain team are requesting your help analyzing sales data.
For this example, we’ll take a dataset from the Kaggle challenge: Store Item Demand Forecasting Challenge.
I have used this Store Demand Forecasting dataset in another article.
Data Set
- Transactions from 2013–01–01 to 2017–12–31
- 913,000 Sales Transactions
- 50 unique SKU
- 10 Stores
Objective: Your colleagues would like to build a pivot table of monthly sales by item for each store.
Issue: With nearly 1 million records, your colleagues are reaching the limits of Excel.
Therefore, they ask for the support of the Data Scientist to find a magic solution.
Are you familiar with this scenario?
While Excel may need help with large datasets, Python offers powerful alternatives that can handle massive amounts of data with ease.
Its linear programming capabilities can also automate decision-making tasks like budget planning.
The product design approach presented here is similar.
If interested, check out this article
If you prefer watching, have a look at the video version of this article
Excel Automation using Python’s Pandas
Returning to our initial sales analytics project, we want to design a Python script to process sales data.
What is the output format?
The objective is to create a pivot table and export the results in an Excel file.
Your script is performing the following tasks:
- Import initial raw data from Kaggle Challenge (sales_data.csv)
- Date Formatting
- Pivot Table of Sales by Month for each pair Store, Item
- Sorting records by Store and Item
- Saving the final report in an Excel file
However, this “works on your machine”, but your colleagues cannot use the script.
How do you create a tool that a wider audience can use?
Share this script using an executable file
Anybody can run an executable file without prior coding experience.
Objective: Create an executable (.exe) that will run your script and can be used without installing Python on your computer.
New Python Library in your toolbox: Pyinstaller
PyInstaller bundles a Python application and all its dependencies into a single package.
Users can run the packaged app without installing a Python interpreter or modules.
How to install it?
Step 1: Install Pyinstaller
pip install pyinstallerStep 2: Save your Python script in the current directory
Save it in the same directory where you have put your sales records' initial file (sales_data.csv). Name your file: “pivot.py”
Step 3: Create your executable file using Pyinstaller
Enter the following command in your command prompt
pyinstaller --onefile pivot.py
When the process is completed, you will see this message: “Building EXE from EXE-00.toc completed successfully.”
And your (.exe) file will pop in a " Build " folder.
How easy is it to use?
Step 4: Use your executable file
Ask your colleagues to Cut and paste this (.exe) file into a folder with the initial raw data and click to start the script.
They can follow the process thanks to the print messages at each script step.
Impact businesses with your analytics skills
At the end of the process, they can save the sales report in a folder.
This opens a world of opportunities.
Now that you can package Python scrips into an executable file, the possibilities for sharing data insights are infinite.
For a personal project, I used Python to help a friend optimize his small business planning and revenue management.
This required advanced analytics models that he could not build with his limited coding experience.
How could I support him?
I have developed a set of modules to simulate different scenarios, which will help his team optimize their revenue and inventory management.
Using an executable file, they can run multiple scenarios and generate insights to support their decision-making process.
For more information about Business Planning with Python 👇
Next Steps
This simple tool is effective but can be improved with additional features.
How can we boost the user experience?
Sales Data Analysis can be complex, and the requirements may change from one file to another.
This static solution is probably not suitable for all cases:
- What if column names changed?
- What if a specific KPI has been updated?
We need to provide flexibility to the tool.
The tool should adapt to the user, not the contrary.
Data Processing Smart Agent with GPT
Since the recent release of Large Language Models (LLMs) like GPT, we now have a solution to bring more “intelligence” in our tool.
What if the code is generated or corrected by an agent?
My first experiment, the design of a LangChain Agent connected to a TMS, shows the potential of using an Agent to interact with users.
This agent's performance is impressive.
It can answer operational questions autonomously, query a database, and provide advanced coding capabilities to any user.
How can we expand this prototype to more tools?
My goal is to equip a GPT agent with
- Python Scripts to automate data processing tasks
- Context, articles and knowledge about sales analytics or finance
With the GPT feature, you can even productize and deploy any tool on ChatGPT.
You can then replace this executable file with an agent that can perform any data analysis task.
💡 For more details, check the articles below
About Me
Let’s connect on Linkedin and Twitter. I am a Supply Chain Engineer who uses data analytics to improve logistics operations and reduce costs.
For consulting or advice on analytics and sustainable supply chain transformation, feel free to contact me via Logigreen Consulting.
If you are interested in Data Analytics and Supply Chain, look at my website.
💡 Follow me on Medium for more articles related to 🏭 Supply Chain Analytics, 🌳 Sustainability and 🕜 Productivity.
💌 New articles straight in your inbox for free: Newsletter
📘 Your complete guide for Supply Chain Analytics: Analytics Cheat Sheet
References
- Kaggle Dataset, Store Item Demand Forecasting Challenge, Link
