How to Work with QuickBooks Data


Once you've created your QuickBooks data source in Klipfolio (as described in How to connect Klipfolio and QuickBooks Online), you'll see that the data structure is more complex than tabular (XLS/CSV) data or even most hierarchical (JSON/XML) data. This article gives a high-level overview of this QuickBooks data structure and describes how to access the data in your QuickBooks data source according to column name.

This article assumes you are familiar with building basic formulas. 

QuickBooks Report Response Structure

Working with QuickBooks data requires an understanding of the structure of a QuickBooks Report Response as well as how to work with XML/JSON data sources. A QuickBooks Report Response has three sections:

  1. Header describes the report (for example, report name, start and end dates of report period)
  2. Columns describes the data contained in each column of the report (column title and type)
  3. Rows contains the report data, there are 2 types:
  • Section describes a sub-report (contains these same three report sections)
  • Data contains report data by column

The structure of the Columns and Rows sections depends on the QuickBooks report. The following description is based on a Customer Income report.


Access QuickBooks Data by Column 

To access QuickBooks data for use in a formula, you must identify the column to use:

  1. Expand the Columns element until the ColTitle objects are visible and identify the column required. This example will use column 3, Expenses.



  1. Expand the Rows element until the ColData object is visible and select the first value.



The formula bar will display @/Rows/Row/ColData/value. Evaluating this object will list all /Rows/Row/ColData/value elements.

  1. To select data from a specific column, you must specify the column number; for example, to select the value in column 3, you must specify 3 as the index (typed between square brackets) into ColData:



This hard-coded approach is sufficient if you know the data in column 3 will never change. However, if this cannot be guaranteed, a more robust solution is to calculate the column number based on the title of the column you need. In this example, the QuickBook Response columns are

Net Income

Calculate the column number of the Expenses column by counting the columns preceding 'Expenses' and adding 1:


This calculation breaks down as follows:

Income, <blank> (the columns preceding Expenses)

2 (the number of items returned by the above)

count(/Columns/Column/ColTitle[.='Expenses']/preceding::ColTitle) +1 
3 (the number of the Expenses column)

  1. Use this column number calculation as the index (typed between square brackets) into ColData:



  • 0
    Shima Beigzadeh

    Excellent article Janice. Thank you so much for elaborating QB Structure so clearly with details :) 


  • 0
    Isaac de la Fuente

    Thanks for the great article.  Anyone have references to more info about working with QBO data in Klipfolio?

  • 0
    Janice Janczyn

    Hi Isaac,

    Klipfolio uses the QuickBooks API as defined by QuickBooks so you should refer to their API documentation. If you have more specific questions about working with your QuickBooks data, please email support@klipfolio.com and we can help guide you.


Please sign in to leave a comment.