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,
- Open a new tab and log in to QuickBooks.
- In QuickBooks, select one of your QuickBooks Companies.
You are now in your chosen company's QuickBooks homepage.
- 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.
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:
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|
|OAuth Token Authentication|
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.
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.
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:
- Header describes the report (for example, report name, start and end dates of report period)
- Columns describes the data contained in each column of the report (column title and type)
- 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:
- Expand the Columns element until the ColTitle objects are visible and identify the column required. This example will use column 3, Expenses.
- 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
- 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
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
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)
3 (the number of the Expenses column)