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
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
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:
- 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
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?