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.
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.
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)
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|
Creating a data source using QuickBooks Desktop Enterprise
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 Desktop Enterprise
- Navigate and log in to the QuickBooks website.
- Select Reports from the QuickBooks sidebar.
- Select either the Recommended or the All Reports link.
- Select a Report Type. For example, Profit and Loss.
- Select the Save Customizations button.
- Type a Name for the custom report.
- Select the OK button.
- In QuickBooks, click the Excel button to download and save the report to your local hard drive.
Start the data source creation process
- Open Klipfolio Dashboard in a new web browser window and click the Library link.
- Select the Data Sources tab.
- Click the Create a New Data Source button.
- 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
- 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
- In Klipfolio, click the Check Email Now button.
- When the report arrives, verify the content, and click the Continue button.
- Give the report a name and Save the data source file.
Optional: Schedule the report in Quickbooks
- Click Reports from the QuickBooks sidebar.
- Select the My Custom Reports link.
- Click the row with the Report Name so that row is highlighted.
- Click the Edit link.
- Select Set the email schedule for this group checkbox.
- Copy the
ds+email address to the To field and change the plus
+to an underscore
_. For example, ds_
- Click the Edit Schedule button.
- Select an interval and date range to send the report on a regular basis.
- Click the Save button.
Note: Each time the report is uploaded to Klipfolio, the existing report is replaced.