How do I connect Klipfolio with QuickBooks (Online and Enterprise)?

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.

What is Klipfolio?

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

How do I get started with QuickBooks?

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.

Which version of QuickBooks do you have?

If you have a QuickBooks Online account, you will be able to pull data into Klipfolio using their API.

If you have a QuickBooks Desktop Enterprise account, you will need to use Klipfolio's email attachment connector to bring your data into Klipfolio.

Select which QuickBooks account you use:

QuickBooks Online

You can choose to connect with QuickBooks Online using the Connector Gallery or the Klip Gallery in Klipfolio.

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.

Prerequisite: Find your QuickBooks 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. Your Company ID is everything except the last three digits.

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

QuickBooks Desktop Enterprise

You can choose to connect with QuickBooks Enterprise using the Email Attachment Core Connector in the Connector Gallery in Klipfolio.

QuickBooks Enterprise quick reference table

Data Source Type Use Email Attachments as a data source
File format supported: Excel

Step-by-Step

Creating a data source using QuickBooks

To create a data source using QuickBooks, you need to accomplish the following tasks:

  • Create a custom report.
  • Send a report.
  • Optional: Schedule a report.

Create a custom report in QuickBooks

  1. Navigate and log in to the QuickBooks website.
  2. Select Reports from the QuickBooks sidebar.
  3. Select either the Recommended or the All Reports link.

  1. Select a Report Type. For example, Profit and Loss.
  2. Select the Save Customizations button.
  3. Type a Name for the custom report.
  4. Select the OK button.
  5. In QuickBooks, click the Excel button to download and save the report to your local hard drive.

Start the data source creation process

  1. Open Klipfolio Dashboard in a new web browser window and click the Library link.
  2. Select the Data Sources tab.
  3. Click the Create a New Data Source button.
  4. Select the Email Attachment option. Take note of the email address that starts with ds+. You might want to leave this window open so you can receive the emailed report.

Send an email with the Excel attachment

  1. From your mail account, send the Excel version of the report as an attachment to the ds+ email address created earlier in Klipfolio.

Receive the Excel attachment

  1. In Klipfolio, click the Check Email Now button.

  2. When the report arrives, verify the content, and click the Continue button.
  3. Give the report a name and Save the data source file.

Optional: Schedule the report in Quickbooks

  1. Click Reports from the QuickBooks sidebar.
  2. Select the My Custom Reports link.
  3. Click the row with the Report Name so that row is highlighted.
  4. Click the Edit link.
  5. Select Set the email schedule for this group checkbox.
  6. Copy the ds+ email address to the To field and change the plus + to an underscore _ .  For example, ds_

  7. Click the Edit Schedule button.
  8. Select an interval and date range to send the report on a regular basis.
  9. Click the Save button.
    Note: Each time the report is uploaded to Klipfolio, the existing report is replaced.

Related Links