The land of Rows, Row and ColData can be intimidating when dealing with Quickbooks data, but in this tutorial you will learn how to navigate through Quickbooks JSON data to return the data you want. First we will look at a Profit & Loss statement and then the Balance Sheet.

Additional Documentation:
1. Creating custom QuickBooks data sources in Klipfolio
2. Working with JSON data in Klipfolio

Profit & Loss Report (AKA Income Statement)

1. Query for current fiscal year: https://quickbooks.api.intuit.com/v3/company/<comapnyrealm>/reports/ProfitAndLoss?minorversion=5&date_macro=This%20Fiscal%20Year-to-date

*Please replace <companyrealm> with your company id that can be found in Quickbooks.

The Profit & Loss report will return five major groupings; Income, Gross Profit, Expenses, Net Operating Income, and Net Income (Profit). If you want to return the net income for your company, filter the summary objects to only return summary information for Net Income.

@/Rows/Row/Summary[ColData/value='Net Income']/ColData/value

This will return two value records for net income. The first is the name of the records returned, and the second is the value amount. Since we only want to return the second value, you can specify which position/index to return the record on the ColData level:

@/Rows/Row/Summary[ColData/value='Net Income']/ColData[2]/value

Balance Sheet

1. Query for current fiscal year: https://quickbooks.api.intuit.com/v3/company/<comapnyrealm>/reports/BalanceSheet?minorversion=5&date_macro=This%20Fiscal%20Year-to-date

If you aren't an accountant, it is beneficial to look at a balance sheet to understand the structure of this important financial statement.

A balance sheet has two main classes; assets and liabilities & equity. Within these classes you have categories of current and fixed/long-term assets and liabilities as well as owner's/shareholder's equity. At the tertiary level there are specific items for the categories. The items in a balance sheet can continue to be split for further granularity. Every level of grouping (class/category/items) is converted within the QuickBooks JSON data as "Rows".

*Note, the wording of class/category/items is arbitrary and used for example purposes.

For example, if you include bank accounts in your balance sheet and want to return a specific bank account, this would fall in the fourth level under the "Bank Accounts" item under the "Current Assets" categories and under the "Asset" class so you can expect four "Rows" within your JSON path:

Assets -> Current Assets -> Bank Accounts -> Bank Account

@/Rows/Row/Rows/Row/Rows/Row/Rows/Row/ColData/value

To better understand this the Rows can be replaced with the names of the levels:

@/Asset/Row/Current_Assets/Row/Bank_Acconts/Row/Bank_Account/Row/ColData/value

However, this will return all values at this level so we can apply similar filtering as the profit & loss report above:

@/Rows/Row/Rows/Row/Rows/Row/Rows/Row[ColData/value='Piggy Bank']/ColData/value

For further clarity, every "Rows" has a corresponding "Row" and "ColData". ColData stands for column data and this is where the values for each level of the hierarchy reside.

If you want an aggregated summary of a particular grouping instead of particular items from that group, you can filter to return data from the Summary field for that grouping. Below is the syntax to return the aggregated value for total liabilities and equity.

@/Rows/Row/Summary[ColData/value='TOTAL LIABILITIES AND EQUITY']/ColData[2]/value

Hope this helps you get started on building the Quickbooks dashboard you have always wanted!

-Adam