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

Article originally published on: Medium.

If you are Data Analyst and want to access a large amount of unstructured data, you have the desire to impact and you are obsessed with automating repetitive tasks: go to your Finance Department.

I will share in this article a solution based on my experience extracting data from very unstructured Excel Files to perform operational and financial audits.

💌 New articles straight in your inbox for free: Newsletter

1. Scenario

Problem Statement

You are working as Data Analyst for a major Logistics Company and your colleagues from the Finance Team request your support to build 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

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.

This report will be used by finance to analyze the costs for the last 3 years, and understand the trends. Your tool will help to get visibility in a single report, without using extra resources to perform it manually.

2. Your Solution

You will design a simple python script that will perform:

1. Open every Excel Report located in a folder
2. Processing and cleaning data
3. Build the monthly report following the format presented above
4. Merge the monthly report with the global data frame
5. Saving final results in an Excel file

Import Monthly Excel Reports and Process Data

Few important points here:

• header parameter of pandas to only takes 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 very linked to the report I processed, I share the code for reference here (Link).

3. Share this tool

Now that you’ve built your tool you would like to share it with Finance teams. For your reference, it was taking 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?

About Me

Let’s connect on Linkedin and Twitter, I am a Supply Chain Engineer that is using data analytics to improve logistics operations and reduce costs.