Automate Accounting Tasks using Python
Build Solutions to Automate Repetitive Tasks for Financial Audits and Share them with Your Finance Colleagues to Improve their Productivity.
Are you looking to automate repetitive accounting tasks and improve your productivity?
In this article, we’ll explore how to build a Python solution that automatically extracts data from unstructured Excel files, processes it and compiles it into a single report for financial audits.
By following our steps, you can design an efficient tool to help your finance team analyze costs and identify trends over time, saving valuable time and resources.
Plus, we’ll show you how to share your Python script with colleagues without any prior experience with Python.
I. Automating Accounting Tasks with Python
1. Problem Statement: Extracting Data from Unstructured Excel Files
2. Objective: Building a Tool to Automate Data Extraction and Reporting
II. Building an Automated Data Extraction Solution with Python
1. Import Monthly Excel Reports and Process Data
2. Format columns and perform the calculations
III. Next Steps to Scale Your Solution
1. Sharing Your Tool with Finance Teams
2. Automatically create a PowerPoint to present the results
3. Generative AI: Boost your tool with GPT
3. Automate Data Extraction from SAP using VBAAutomating Accounting Tasks with Python
Problem Statement: Extracting Data from Unstructured Excel Files
You are working as a Data Analyst for a major Logistics Company.
Your colleagues from the finance team request your support in building a model to predict the P&L of warehouse operations.
You need to extract information from monthly reports built by accounting teams listing all the detailed costs by category
- 20 Warehouses included in your study
- Audit of the last 36 Months
- 720 Excel Files in Total
- 60 Item Costs to track
- Three categories of costs: Investments, Rental, Purchasing
Building a Tool to Automate Data Extraction and Reporting
Your objective is to build a tool that will automatically extract data from each of these 720 Excel files, format it and combine everything in one report.
Finance will use this report to analyze the costs for the last 3 years and understand the trends.
Your tool will help get visibility in a single report without using extra resources to perform it manually.
🏫 Discover 70+ case studies using Python to automate manual tasks 🤖 and support business optimization 🏪 in this Cheat Sheet
Building an Automated Data Extraction Solution with Python
You will design a simple Python script that will perform:
- Open every Excel Report located in a folder
- Processing and cleaning data
- Build the monthly report following the format presented above
- Merge the monthly report with the global data frame
- Saving final results in an Excel file
Import Monthly Excel Reports and Process Data
There are a few important points here:
- header parameter of pandas to only take the 5th (very useful with Excel files)
- Fill nan with 0 to perform calculations on numeric values
- Trim columns name: very useful in the situation where people are doing manual input (‘Unit Cost’ and ‘Unit Cost ‘ look the same for your users)
Format columns and perform the calculations
This part is linked to the report I processed; I share the code for reference here (Link).
💡 Follow me on Medium for more articles related to 🏭 Supply Chain Analytics, 🌳 Sustainability and 🕜 Productivity.
Next Steps to Scale Your Solution
Sharing Your Tool with Finance Teams
Now that you’ve built your tool, you want to share it with Finance teams.
For your reference, performing these tasks full-time (1 headcount) took 2 weeks in my previous company.
Could you imagine the impact you would have if you could implement this simple tool?
If you want to know more,
If you follow the steps explained in this article, you will have an executable file (.exe) ready to share with your colleagues so they can run the script without Python.
Do you want to generate reports based on the data processed?
Automate PowerPoint Slide Creation with Python
Let us imagine that you need to prepare, every week, PowerPoint presentations based on the results generated by this excel file.
How can you automate this process?
With Python, we can automatically generate the PowerPoint with visuals and comments.
- Automate the Excel file generation using the tool presented in this article.
- Extract results from this Excel document using PowerPoint and create visuals with comments.
- Automatically put the visuals and insights in a PowerPoint presentation.
What is the the final result?
You can generate a deck with slides like the example below
For more information on how to implement this workflow, check this article.
Have you heard about Generative AI?
Generative AI: GPT for Process Optimization
I started experimenting with designing a LangChain Agent connected to a TMS following the adoption of large language models (LLMs),
The outputs are pretty impressive; the GPT-powered agent can automate any task.
What if we want to automate accounting tasks with GPT?
With ChatGPT's new feature, “GPTs,” we can create an agent equipped with documentation, Python scripts, and data.
In this example, we can create an agent that interacts with users to create reports.
- We provide a core module with the Python script presented in this article
- We add prompts for the context and user interaction management
- We use GPT intelligence to improve the outputs
For more details,
How can you collect data automatically from systems?
Advancing to Automated Data Extraction from ERPs
Before building the report, you need to collect the data from your ERP.
If you are using SAP, you may be interested in this series of articles about ERP Automation.
About Me
Let’s connect on Linkedin and Twitter, I am a Supply Chain Engineer using 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 interested in Data Analytics and Supply Chain, look at my website.
💌 New articles straight in your inbox for free: Newsletter
📘 Boost your Productivity with Data Analytics: Productivity Cheat Sheet
