Explore Your Financial Data in QuickBooks

QuickBooks Dashboard Example for Exploring Financial Data with Bold BI

Published On: March 21, 2019
Updated On: August 29, 2023
7 min read

QuickBooks is accounting software offering tools for small businesses, accountants and individuals. QuickBooks doesn’t require its users to be accounting experts, thereby keeping everything simplified and easily accessible. You can track income, expenses, invoiced and accepted payments, sales, sales tax, inventory, and bills with metrics from your QuickBooks account. This blog article will walk through you how to integrate QuickBooks Online data with Bold BI dashboards and create a tracking dashboard.

If you are new to Bold BI Dashboards, sign up for a free 15-day trial on our website. At the end of the registration process, you will reach the following page view.

Before getting into the data fetch process, ensure you have a QuickBooks Online account with your company or client details to analyze.

Getting QuickBooks Online data

The QuickBooks Online Accounting API is a RESTful API through which QuickBooks companies can be accessed.

Its format looks like the following.

https://quickbooks.api.intuit.com/v3/company/<:company_id>/query?query=<:select_query>

**Table 1:** Parameters used in the QuickBooks Online Accounting URI
:company_id	Represents a unique value assigned for each company. This will be shown at top of the quick reference guide accessible through the keyboard shortcut.
:select_query	Represents the query request through Intuit Developer SQL-like query language.

Defining required metrics for dashboard

To track income, expenses, customer receivables and vendor payables, the following metrics will be useful to showcase in the dashboard:

Invoice – Unpaid
Invoice – Unpaid – Due
Sales Receipt – Paid
Invoice – Unpaid – Overdue
Invoice – Paid
Accounts Receivables (A/R) Balance with due days split
Accounts Payables (A/P) Balance with due days split
A/R Balance by customer
A/P Balance by vendor

In the previous list, Invoice and Sales Receipt need to be considered different modes of income.

Connecting to QuickBooks Online

As you are already on the homepage, click the Create Data Source button under the Data Sources icon as highlighted below.
Services view in homepage
Consequently, the supported data connectors get listed like in the following.
Data Connections listing in Bold BI Dashboards
Furthermore, in the Categories drop-down list at the top, choose the category Finance.
Data connections under Finance category
Likewise, choose the QuickBooks Online connector. As a result, the Accounts dialog window opens. Consequently, window shows a list of the QuickBooks accounts in the drop-down that are already configured by you.
Accounts window to connect to QuickBooks Online
Furthermore, choose one from the list and click Continue. Certainly, if you haven’t yet, click Connect a new account and register your QuickBooks Online account to access its data from the Bold BI Dashboards application.
Sign in window for QuickBooks Online account
Information sharing confirmation page for QuickBooks Online
As a result, Create Data Source window opens like the following.
New Data source window of QuickBooks Online

Likewise, for tracking metrics, the following lists the data sources that need to be created and what for.

Datasource Name	Used for
BillData	Calculating accounts payable (A/P) to vendors for the purchases made.
CustomerData	Calculating accounts receivable (A/R) from customers for the goods sold.
InvoiceData	Calculating income obtained through invoices, income overdue and income expected.
PurchasedData	Calculating expenses.
SalesRecData	Calculating income obtained through sales receipts.

Creating a QuickBooks Online data source

Name the data source BillData.
In the URL section, fill the following API endpoint URL (replace the company ID in the placeholder) and click Preview & Connect.
https://quickbooks.api.intuit.com/v3/company/<:company_id>/query?query=select%20%2a%20from%20bill
Search query for Bill data from QuickBooks Online
Therefore, the Choose Table(s) window opens. Furthermore, select the required columns and click Connect.
Schema view of URI resultset
Consequently, the data source window opens in the dashboard designer. Also, drag and drop the table on the left to the table design view. Finally, click Save and Exit on the top right corner menu.
Design view of BillData data source

As a result, the data sources list window opens. Furthermore, click Create Data Source and create the other data sources listed previously. Also, the names and URLs for those data sources are listed in the following table.

Data source	URL
InvoiceData	https://quickbooks.api.intuit.com/v3/company/<:company_id>/query?query=select%20%2a%20from%20invoice
PurchasedData	https://quickbooks.api.intuit.com/v3/company/<:company_id>/query?query=select%20%2a%20from%20purchase
SalesRecData	https://quickbooks.api.intuit.com/v3/company/<:company_id>/query?query=select%20%2a%20from%20salesreceipt
CustomerData	https://quickbooks.api.intuit.com/v3/company/<:company_id>/query?query=select%20%2a%20from%20customer

Creating a dashboard

Click the Home icon on the left to navigate to the homepage.
New Dashboard tile in homepage
Select the New Dashboard tile. As a result, a blank dashboard is created and opened.
A blank dashboard design view
Select the data source icon on the right. As a result, the data source panel expands as shown in the following.
An empty data sources panel
Furthermore, select Use Existing to open the existing data source listing in the same panel.
Existing data sources view
Also, choose the data sources we created in the beginning one at a time and select ADD. Finally, all five data sources get added to the dashboard and listed in the DATA SOURCES panel like in the following.
Data sources listing in data sources panel

Similarly, in the dashboard design view, drag these listed widgets from the toolbox to showcase required metrics.

Metric	Widget Type
Invoice – Unpaid	Card
Overdue Amount	Card
Due Amount	Card
Invoice – Paid	Card
Sales Receipt – Paid	Card
Expenses	Card
A/R Balance (by days past due)	Column Chart
A/P Balance (by days past due)	Column Chart
Customer Balance Overview	Grid
Vendor Balance Overview	Grid

Finally, select Publish in the Save drop-down menu at the top-left to save and publish the dashboard to the server.

Editing a data source

Some of the data sources need to be edited to shape the data before showcasing it in widgets.

Editing BillData data source

To start with, click the edit icon near the data source BillData listed in the DATA SOURCES panel. As a result, the data source design view page opens like in the following.
Editing view of BillData data source
Furthermore, select the fx icon in the toolbar to open the expression designer.

Also, enter the name Due Split and an expression like in the following. Consequently, this expression column is required to show the A/P to vendors split by different ranges of due days to prioritize your focus.

IF(DAYDIFF(TODAY(),[DueDate])>=’0′,’0 or less’,IF(DAYDIFF([DueDate],TODAY()) < 31,’1 ~ 30′,IF(DAYDIFF([DueDate],TODAY()) < 61,’31 ~ 60′,IF(DAYDIFF([DueDate],TODAY()) < 91,’61 ~ 90′,’Over 90′))))

Expression editing view of BillData data source

Furthermore, click Save in the expression designer.
Finally, click Save in the data design page to save the expression update with the data source.

Editing InvoiceData data source

Similarly, click the edit icon near the data source InvoiceData listed in the DATA SOURCES panel.
Likewise, in the data source design view page, select the filter icon in the toolbar to open the Query Filters dialog window.
Furthermore, select + ADD at the top-right corner. Also, from the new row item added below it, choose the DetailType (QueryResponse_Invoice_Line) column.
Query Filters view of InvoiceData data source
Also, in the third drop-down list showing values, check only the SalesItemLineDetail value and uncheck all others. Click Apply.
Hence, click OK in the window to save the filter created.
In addition, select the fx icon in the toolbar, to open the expression designer.

Create four expression columns listed in the following table with their respective reasons. Hence, click Save for each.

Name	Expression	Reason
Paid	[TotalAmt]-[Balance]	To showcase the amount paid by customers from invoices.
Overdue Amount	IF([DueDate]<TODAY(),[Balance],0)	To showcase in the card the overdue amount from invoices as part of the income.
Due Amount	IF([DueDate]>=TODAY(),[Balance],0)	To showcase in the card the due amount from invoices as part of the income.
Due Split	IF(DAYDIFF(TODAY(),[DueDate])>=’0′,’0 or less’,IF(DAYDIFF([DueDate],TODAY()) < 31,’1 ~ 30′,IF(DAYDIFF([DueDate],TODAY()) < 61,’31 ~ 60′,IF(DAYDIFF([DueDate],TODAY()) < 91,’61 ~ 90′,’Over 90′))))	To showcase the A/R from customers split by different ranges of due days to prioritize your focus.

Finally, click Save in the data design page to save the expressions update with the data source.

Editing PurchasedData data source

Further, open the PurchasedData data source design page like we’ve done for the other data sources.
Likewise, click the filter icon in the toolbar to open the Query Filters dialog window.
Similarly, select + ADD at the top-right corner. Certainly, from the new row item added below it, choose the DetailType column.
After that, in the third drop-down list showing values, check only the AccountBasedExpenseLineDetail value and uncheck all others. Hence, click Apply.
Furthermore, click OK in the window to save the filter created.
Finally, click Save in the data design page to save the filter update with the data source.

Editing SalesRecData data source

Further, open the SalesRecData data source design page like we’ve done for the other data sources.
Likewise, click the filter icon in the toolbar to open the Query Filters dialog window.
Similarly, select + ADD at the top-right corner. Certainly, from the new row item added below it, choose the DetailType column.
After that, in the third drop-down list showing values, check only the SalesItemLineDetail value and uncheck all others. Hence, click Apply.
Furthermore, click OK in the window to save the filter created.
Finally, select Save in the data design page, to save the filter update with the data source.

Configuring widgets in a dashboard

Configure the widgets in the dashboard with corresponding data sources as described in the following table.

Widget	Data Configuration
Invoice – Unpaid	Data source: InvoiceData Actual Value: Balance Aggregation Type: Sum
Overdue Amount	Data source: InvoiceData Actual Value: Overdue Amount Aggregation Type: Sum
Due Amount	Data source: InvoiceData Actual Value: Due Amount Aggregation Type: Sum
Invoice – Paid	Data source: InvoiceData Actual Value: Paid Aggregation Type: Sum
Sales Receipt – Paid	Data source: SalesRecData Actual Value: TotalAmt Aggregation Type: Sum
Expenses	Data source: PurchasedData Actual Value: TotalAmt Aggregation Type: Sum
A/R Balance (by days past due)	Data source: InvoiceData Value(s): Balance (Sum) Column(s): Due Split
A/P Balance (by days past due)	Data source: BillData Value(s): Balance (Sum) Column(s): Due Split
Customer Balance Overview	Data source: CustomerData Column: DisplayName Balance (Sum) => with filter criteria set to greater than 0.
Vendor Balance Overview	Data source: BillData Column: name (QueryResponse_Bill_VendorRef) Balance (Max) => with filter criteria set to greater than 0.

To start with, select a widget in the dashboard design area and click the gear icon (Settings) in the top-right corner. As a result, this opens the properties panel on the right.
Widget Properties View in Dashboard Design Window
Further, navigate to the ASSIGN DATA tab, select the data source in the combo box on the top. Also, start configuring the columns in their proper sections as shown in the previous table.
Data Configuration View in Dashboard Design Window
In addition, repeat this process for all the widgets placed in the dashboard.
Hence, save the dashboard. Finally the dashboard is ready.
QuickBooks Online Accounting Dashboard

Sharing the dashboard

Now, share this dashboard with your customers. With their accounts configured to use the Bold BI Dashboard platform, they can start exploring their financial data like this in order to make better decisions. We hope this article helps you build a tracking dashboard with QuickBooks Online data using the Bold BI Dashboard Platform. If you have any questions on this blog, please feel free to post them in the following comment section. Get started with Bold BI by signing up for a free 15-day trial and create more interactive business intelligence dashboards. You can also contact us by submitting your questions through the Bold BI website or, if you already have an account, you can log in to submit your support question.

Rajadurai Chinnadurai

Rajadurai is a seasoned product manager with 10+ years of experience in data visualization and analytics. His expertise in creating intuitive dashboards has helped top-tier organizations harness the power of their data. Through his writing, he shares his passion for data visualization and inspires readers to explore this exciting field.