Dashboard for Quickbooks : How to Build from scratch

A very Good Dashboard Software for your Business. Try It!

If you are using Quickbooks then this may be of your interest. This tutorial is developed using this Dashboard Software

We will create the following dashboard from scratch

1. Login to your Quickbooks

2. Create a Custom Transaction Report (remove the left margin and include, account type, class,item, item description, qty, sales price columns)

3. Run the report for a date range

4. Export to Excel

Once your data is available in Excel, connect it via this Dashboard Tool

Once connected, create a blank dashboard and add the following SQL queries

Executive Dashboard

Change the date range in the below SQL, also change [qb_data$] to [sheet1$] or whatever your worksheet name is.

select Format(date,’YYMM’) & ‘ – ‘& Format(date,’MMM’) as Month , -sum(amount)/1000 as Sales
from
[qb_data$]
where “Account Type” = ‘Income’
and date between #12/1/2007# and #01/08/2009#
group by Format(date,’YYMM’) & ‘ – ‘& Format(date,’MMM’)
order by 1 asc

Expense Trend Query

select Format(date,’YYMM’) & ‘ – ‘& Format(date,’MMM’) as Month , sum(amount)/1000 as Expense
from
[qb_data$]
where “Account Type” = ‘Expense’
and date between #12/1/2007# and #01/08/2009#
group by Format(date,’YYMM’) & ‘ – ‘& Format(date,’MMM’)
order by 1 asc

The Sales Growth Query is slightly complex. This chart does a comparison between the current period and the previous period. Now in Excel SQL, there are no analytical functions and hence the use of complex techniques to get the previous and current amount all on same row to get the differentials

Sales Growth

select y.month, (-y.expense + x.expense)*100/(-x.expense) as percentage_change
from
(
select Format(date,’YY’)*1 as year, format(date,’MM’)*1 as id, Format(date,’YYMM’) & ‘ – ‘& Format(date,’MMM’) as Month , sum(amount) as Expense
from
[qb_data$]
where “Account Type” = ‘Income’
and date between #12/1/2007# and #01/08/2009#
group by Format(date,’YY’)*1, format(date,’MM’)*1,Format(date,’YYMM’) & ‘ – ‘& Format(date,’MMM’)
) as x
,
(
select Format(date,’YY’)*1 as year, format(date,’MM’)*1 as id, Format(date,’YYMM’) & ‘ – ‘& Format(date,’MMM’) as Month , sum(amount) as Expense
from
[qb_data$]
where “Account Type” = ‘Income’
and date between #1/1/2007# and #11/08/2009#
group by Format(date,’YY’)*1, format(date,’MM’)*1,Format(date,’YYMM’) & ‘ – ‘& Format(date,’MMM’)
) as y
where
(
(
(x.year=y.year and x.id=y.id-1)
or (x.year=y.year -1 and x.id=12 and y.id=1)
)
)

Expense Growth

select y.month, (y.expense – x.expense)*100/(x.expense) as “Percent change from Previous Month”
from
(
select Format(date,’YY’)*1 as year, format(date,’MM’)*1 as id, Format(date,’YYMM’) & ‘ – ‘& Format(date,’MMM’) as Month , sum(amount) as Expense
from
[qb_data$]
where “Account Type” = ‘Expense’
and date between #12/1/2007# and #01/08/2009#
group by Format(date,’YY’)*1, format(date,’MM’)*1,Format(date,’YYMM’) & ‘ – ‘& Format(date,’MMM’)
) as x
,
(
select Format(date,’YY’)*1 as year, format(date,’MM’)*1 as id, Format(date,’YYMM’) & ‘ – ‘& Format(date,’MMM’) as Month , sum(amount) as Expense
from
[qb_data$]
where “Account Type” = ‘Expense’
and date between #1/1/2007# and #11/08/2009#
group by Format(date,’YY’)*1, format(date,’MM’)*1,Format(date,’YYMM’) & ‘ – ‘& Format(date,’MMM’)
) as y
where
(
(
(x.year=y.year and x.id=y.id-1)
or (x.year=y.year -1 and x.id=12 and y.id=1)
)
)

Operating Profit

select x.month, sum(x.income_amount+x.cogs_amount+x.expense_amount)*100/sum(x.income_amount) as operating_profit
from
(
select Format(date,’YY’)*1 as year, format(date,’MM’)*1 as id, Format(date,’YYMM’) & ‘ – ‘& Format(date,’MMM’) as Month
,switch(”Account Type” = ‘Income’ ,amount ,”Account Type” <> ‘Income’,0 ) as Income_amount
,switch(”Account Type” = ‘Cost of Goods Sold’ ,amount ,”Account Type” <> ‘Cost of Goods Sold’,0 ) as COGS_amount
,switch(”Account Type” = ‘Expense’ ,amount ,”Account Type” <> ‘Expense’,0 ) as Expense_amount
from
[qb_data$]
where “Account Type” in ( ‘Income’,'Cost of Goods Sold’,'Expense’)
and date between #12/1/2007# and #01/08/2009#
) as x
group by x.month

Gross Margin

select x.month, sum(x.income_amount+x.cogs_amount)*100/sum(x.income_amount) as Gross_margin
from
(
select Format(date,’YY’)*1 as year, format(date,’MM’)*1 as id, Format(date,’YYMM’) & ‘ – ‘& Format(date,’MMM’) as Month
,switch(”Account Type” = ‘Income’ ,amount ,”Account Type” <> ‘Income’,0 ) as Income_amount
,switch(”Account Type” = ‘Cost of Goods Sold’ ,amount ,”Account Type” <> ‘Cost of Goods Sold’,0 ) as COGS_amount
from
[qb_data$]
where “Account Type” in ( ‘Income’,'Cost of Goods Sold’)
and date between #12/1/2007# and #01/08/2009#
) as x
group by x.month

Once you have the Custom Transaction Detail report from Quickbooks, it is pretty straight forward to get different metrics. If you notice the main ingredient in all the SQL queries is the ‘Account Type’. Account type could be Income, Expense, COGS or something else. So if you have custom account types, you could embed them here and get your own personal KPIs.

We hope you succeed in your small business and this kind of dashboards are very crucial in monitoring your business health. Please don’t neglect reporting if you are serious about your business.