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.
💌 New articles straight in your inbox for free: Newsletter
📘 Boost your Productivity with Data Analytics: Productivity Cheat Sheet
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. Generative AI: GPT for Process Optimization
3. Advancing to Automated Data Extraction from ERPsI. Automating 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
- 3 categories of costs: Investments, Rental, Purchasing
Objective: 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.
💡 Follow me on Medium for more articles related to 🏭 Supply Chain Analytics, 🌳 Sustainability and 🕜 Productivity.
II. 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
1. 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)
2. 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.
III. 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, it took 2 weeks full time (1 headcount) to perform these tasks 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 be shared with your colleagues to run the script without Python.
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 quite impressive; the GPT-powered agent can automate any task.
What if we want to automate accounting tasks with GPT?
With the new feature of ChatGPT, “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,
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.
