It’s almost a given that small to mid-sized businesses (SMBs) use QuickBooks to handle their accounting. The combination of easy-to-use and power is hard to beat.
For some businesses, the built-in reports are all the information they ever need. For others, additional analysis is needed to do things like aggregate based on item type, location, address, or customer. One of our early users wanted to see the total amount of each product type they shipped to each state. Nothing crazy, but not something that QuickBooks just gives you.
Doing this sort of analysis in Excel is certainly possible. Generally, it involves some copying and pasting followed by a pivot table. To see a great how-to for doing it with Excel, check out this blog by David Ringstrom: Analyze QuickBooks Data with Excel Pivot Tables
Let’s take a look at an example using TRANSDATA® Platform. We’ll be working with the Profit and Loss Statement Detail report. The Excel export you get out of QuickBooks is frequently not ideal for analysis. There are extra columns and rows that are really there to make it look nice to a person.
Here is the QuickBooks export data as it appears:
Our first task then is to tidy up the data to make it more analysis-friendly. In this case, we will simply get rid of the rows which contain subheadings. This is easily done with a simple Filter Node in TRANSDATA® to check if the Type field is empty since subheading rows have no Type
Here are the results of that filter:
It looks a lot better, though we do end up with an extra column called “1”, but that’s not really an issue. Since we’re going to aggregate this data, it won’t show up in the end result. If you really hate it, you can always remove it (hint – Field Order Node).
Now that our data is cleaned up, let’s try a simple aggregation using an Aggregate Node. We’ll combine records based on the Type field and sum the Amount field like so:
Here are the results showing the totals for each Type:
Much more useful already, isn’t it?
Now let’s try aggregating the data using multiple fields. We’ll combine based unique combinations of Type, Name, and Class while still summing the Amount:
Here are the results of this new aggregation:
This is the kind of valuable business information that helps to quickly make decisions. At a glance, we get the amount from each account.
Here is the completed model:
Just 6 nodes. It’s available for download on our website. Try it out with our free version and your own data, or use it as a starting point to build your own QuickBooks analysis model.