How do I connect Klipfolio and QuickBooks Online?

You can set up a connection between Klipfolio and QuickBooks to show your financial data on a dashboard. Use the steps in this article to learn how set up that connection.

Note: Klipfolio does not connect to QuickBooks Desktop version.

What is Klipfolio?

Klipfolio integrates with many cloud services and tools to create beautiful data visualizations.

How do I get started with QuickBooks Online?

You have the following options for visualizing your data on a dashboard in Klipfolio:

Try the Klip Gallery Use the Connector Gallery
The Klip Gallery allows you to track your key metrics using pre-built Klips for some of the most popular services and data sources. The Connector Gallery helps you get your data fast with pre-built queries. Use this article as a guide to build your own QuickBooks data source.

 

You'll need to:

  • Authenticate with QuickBooks Online to create a handshake connection between your Klipfolio and QuickBooks accounts.
  • Then, if you want to write a QuickBooks Online query entirely from scratch, and for tips on how to work with QuickBooks data, jump down to the Advanced section below.

Authenticate with QuickBooks Online

  • Click Connect to QuickBooks.
  • Enter your QuickBooks login credentials.
  • Click Next Step.
    Note: You now have an OAuth Token for your Connected Account. You can manage your tokens in the Account section under Connected Accounts.

Note: If you have trouble connecting, you may not have enabled access to third party applications in your Quickbooks account. Follow these steps to enable your connection.

Find your QuickBooks Online Company ID

To get the right data from QuickBooks, you'll need to provide your QuickBooks Company ID and use it in Klipfolio.

To find your company ID,

  1. Open a new tab and log in to QuickBooks.
  2. In QuickBooks, select one of your QuickBooks Companies.

    You are now in your chosen company's QuickBooks homepage.
  3. Press [Ctrl + Alt + ?] and a dialog box will pop up with the company ID in it.
    There are two types of Company IDs:
    • If your Company ID has three letters at the end, copy everything except the last three letters into Klipfolio.
    • If your Company ID entirely consists of numbers, copy the entire ID, paste it into Klipfolio and remove spaces.

Optional: Where to find your Fiscal Year Start Month

To add certain QuickBooks Klips from the Klip Gallery, you will need to enter the same fiscal year start of month that you have set up in your QuickBooks Online account.

To find your fiscal year start month in QuickBooks, navigate back to your company homepage.

Click on your Company Name at the top right corner of your screen.
Under Settings, select Company Settings.
In the Settings sidebar, select Advanced.
At First Month of fiscal year, copy the first three letters of your start month.
In Klipfolio, enter the first three letters of your start month. For example, Feb.

Click Add Klip.

Advanced: Build a data source yourself

Klipfolio connects to hundreds of services in the cloud that have a REST API. If you take the time to learn how to get data into Klipfolio using APIs, your possibilities are endless. Here's a quick and simple overview video on APIs and Klipfolio:

  Klipfolio API's 101

If APIs are a bit too tricky for you, enlist a data analyst or developer to help you.

If you want to edit a QuickBooks data source that you already created, check out How to edit or reconfigure a data source, and get step-by-step instructions.

QuickBooks Online quick reference table

API Documentation QuickBooks API documentation

Authentication Method

OAuth Token Authentication
Response Format JSON

API Limits

Refer to QuickBooks API limits

By default, QuickBooks returns a maximum of 100 records per query. To retrieve more records, set the maxResults parameter to 1000 or, to retrieve more than 1000 records, pagination is required.

Pagination Method

By default, QuickBooks returns a maximum of 100 records per query. This can be raised to 1000 by setting the maxResults parameter. Refer to Data Sources with Multiple Pages for details on how to handle pagination.

Date Parameters

start_date=yyyy-MM-dd
end_date=yyyy-MM-dd

Sample Queries

https://quickbooks.api.intuit.com/v3/company/companyID/reports/ProfitAndLoss

QuickBooks Report Response Structure

To use QuickBooks data in a Klip 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.

To select data for use in a Klip 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:

@/Rows/Row/ColData[3]/value

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

<blank>
Income
Expenses
Net Income

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

[count(/Columns/Column/ColTitle[.='Expenses']/preceding::ColTitle)+1]

This calculation breaks down as follows:

/Columns/Column/ColTitle[.='Expenses']/preceding::ColTitle
returns
Income, <blank> (the columns preceding Expenses)

count(/Columns/Column/ColTitle[.='Expenses']/preceding::ColTitle)
returns
2 (the number of items returned by the above)

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

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

@/Rows/Row/ColData[count(/Columns/Column/ColTitle[.='Expenses']/preceding::ColTitle)+1]/value

Related Links