Case Study 1A - QuickBooks Data Cleaning & Visualization
Using python to clean messy system reports
Project Summary
The purpose of this project is to show how a custom script can be developed to assist in data cleaning for “canned” or standard reports produced by the QuickBooks system. QuickBooks and several other ERPs, such as NetSuite, are known for producing reports that are difficult to manipulate if further analysis is desired.
Using python, we can prepare both the trial balance and journal entry reports from the QuickBooks system. The reports are downloaded in the typical format, with header rows and data not filled in (particularly in the JE) which can take time to edit. The script will automatically reformat these reports so that they are easier to work with and can then be read directly into the Power BI dashboard created for analysis.
Take a look below at how this is done!
QuickBooks reports are used solely as an example of the process. Similar solutions can be built for reports from any system whether it be an ERP system, or another system used by your company.
The cleansed data is then visualized in a Power BI dashboard which showcases:
- Tab 0: Reconciles the journal entry data for the period to the trial balance to ensure data in the analysis is complete.
- Tab 1: Looks at AR activity over the period to analyze new credit extended as well as collections made.
- Tab 2: Like Tab 1, this tab analyzes new short-term credit as well as debt paid during the period.
- Tab 3: Analyzes cash activity during the period.
- Tab 4: Provides insights into revenue earned during the period and identifies key customers.
- Tab 5: A broader look at the journal entry dataset to understand the bigger picture of activity during the period.
If you would like a demo of the process, to see the full functionality of the Power BI dashboard, or to discuss how a similar solution could be built for your organization please get in touch!