🏆 Personal Finance Dashboard using Power BI

Google Sheets Show & Tell
1

Overview

This is a Power BI Dashboard created using the Tiller Spreadsheet (Google Sheets) to get quick insights on your Net Worth, Balances, Expenses, Bill Payments, etc.,

image
image1468×806 57.4 KB

image

image
image1468×807 66.9 KB

image
image1467×806 34.2 KB

Power BI Dashboard

What is the goal of your workflow? What problem does it solve?

  • To help analyze the spending pattern each month and compare it with the previous months.
  • Get a high level overview on the balances and investment performance
  • Find answers based on the data using natural language

How did you come up with the idea for your workflow?

With all the Tiller data available across multiple tabs in the Spreadsheet, it gets hard to switch between these tabs to have a bird’s eye view of what’s going on with our finances. And a lot of times, the questions that we need answers for might vary and we have to apply some filters manually in the sheets to get the data we need.
So, I wanted to create a Dashboard that can give me these insights at a glance and also able to give answers to me just using natural language using AI.

Installation

Here is the sample Tiller Spreadsheet and the Power BI report that you would need to set up the dashboard.

Download and install Power BI Desktop in your PC (currently available for Windows OS only, but not on Mac or Linux).

Once you install the Power BI Desktop app, download the “.PBIX” file and open it.

Are there dependent sheets?

This Dashboard relies on the sheets “Transactions”, “Categories”, “Accounts” & “Bill Payment Tracker”.

The first 3 sheets would be available for everyone by default with the Foundation Template.
“Bill Payment Tracker” is optional, but I would recommend to install this from the Tiller Community Solutions, if not done already.

Setup

Here’s the video which gives you a demo of the dashboard and also details the step-by-step process of the setup. This should hopefully make it easier to follow.

Configuring the Tiller Spreadsheet

Transactions Sheet:

In the Transactions Sheet, add the below columns, if you don’t have these already.

You can add the missing columns at the right end. Update the below formulas in the header row.

  • Tags - Add a column with the name as “Tags” and leave it blank, if you’re not using it already

  • Type
    =arrayformula(if(row(D1:D)=1,"Type",iferror(vlookup(D1:D,{indirect("Categories!$"&SUBSTITUTE(ADDRESS(1,MATCH("Category",Categories!$1:$1,0),4),1,"")&"$2:$"&SUBSTITUTE(ADDRESS(1,MATCH("Category",Categories!$1:$1,0),4),1,"")),indirect("Categories!$"&SUBSTITUTE(ADDRESS(1,MATCH("Type",Categories!$1:$1,0),4),1,"")&"$2:$"&SUBSTITUTE(ADDRESS(1,MATCH("Type",Categories!$1:$1,0),4),1,""))},2,FALSE),"")))

  • Group
    =arrayformula(if(row(D1:D)=1,"Group",iferror(vlookup(D1:D,{indirect("Categories!$"&SUBSTITUTE(ADDRESS(1,MATCH("Category",Categories!$1:$1,0),4),1,"")&"$2:$"&SUBSTITUTE(ADDRESS(1,MATCH("Category",Categories!$1:$1,0),4),1,"")),indirect("Categories!$"&SUBSTITUTE(ADDRESS(1,MATCH("Group",Categories!$1:$1,0),4),1,"")&"$2:$"&SUBSTITUTE(ADDRESS(1,MATCH("Group",Categories!$1:$1,0),4),1,""))},2,FALSE),"")))

  • Hide From Reports
    =arrayformula(if(row(D1:D)=1,"Hide From Reports",iferror(vlookup(D1:D,{indirect("Categories!$"&SUBSTITUTE(ADDRESS(1,MATCH("Category",Categories!$1:$1,0),4),1,"")&"$2:$"&SUBSTITUTE(ADDRESS(1,MATCH("Category",Categories!$1:$1,0),4),1,"")),indirect("Categories!$"&SUBSTITUTE(ADDRESS(1,MATCH("Hide From Reports",Categories!$1:$1,0),4),1,"")&"$2:$"&SUBSTITUTE(ADDRESS(1,MATCH("Hide From Reports",Categories!$1:$1,0),4),1,""))},2,FALSE),"")))

You might already have Tags column, as some of the templates use that column extensively. This Dashboard works even if you have multiple values separated by comma in the column Tags.

The other columns “Type”, “Group” & “Hide From Reports” are also added into the Transactions sheet, to make it easier for reporting.

Power BI Sheet:

Copy the Power BI tab from the sample sheet into your actual Tiller spreadsheet.
To copy, right click on the tab name and select “Copy to”->“Existing spreadsheet” and select your actual spreadsheet.

Open your Tiller spreadsheet and rename the sheet name from “Copy of Power BI” to “Power BI”.

Navigate to “Data"➝"Named ranges” from the menu. Delete the 3 named ranges that begin with ‘Power BI’ and re-create them with the below names.
To delete a named range, click on the edit icon next to the range name and then choose delete icon.

  • Select the range “A1:C20” and click on “Add a range”. Name it as “InvestmentPerformance” without any quotes
  • Similarly select the range “E1:I50” and create a named range as “LatestBalances”
  • Select “K1:N25” and create named range as “BillsDueInAWeek”

Replace the sample data that is available in columns “A-C” and update it with your own data, if available. Else, leave the headers and delete the data in the rest of the rows

Accounts Sheet:

The Accounts sheet is hidden by default. If you click on the hamburger menu in the Tabs section (bottom left), you will see the Accounts sheet is greyed out. Click on it, to open the tab.

I would recommend you to update the Group for each Account that you had linked to Tiller, to avoid showing up as Ungrouped Assets and Liabilities. (Ex: Savings, Credit, Loan, Retirement, Investment)

Bill Payment Tracker Sheet:

In order to see the bills due in a week or past due in the Notifications, I would recommend you to install the “Bill Payment Tracker” template from the Tiller Community Solutions and add your Bills, if not done already.

But, this is optional and the Power BI report will still work even if you don’t have this.

Copy the link to your Google Sheets and keep it handy

Configuring the Power BI report

Update the source to your Tiller Spreadsheet

  • Open the Power BI report using the .PBIX file shared above. Navigate to “Home” tab in the top ribbon and select “Transform data”. This will open up the Power Query Editor

  • Select the “Transactions” query on the left and click on the step named as “Source” in the Applied Steps on the right pane and replace the URL with the link to your Google Sheets that you just copied earlier

  • Since the data source is changed, you should authorize the access to read the Google Sheets for your Power BI report. To do this, click on “Edit credentials” button and select “Sign in”.

  • Once the access is granted, return to the Power BI Desktop app and click on “Continue” button

  • Repeat the update of the URL for all the queries under the “Master Queries” group

  • If you have Tags column or any other columns other than “Category”, “Group” and Months in the Categories sheet, add it in the step named “Removed Columns” in the query “Budget by Categories”, along with the columns “Type” and “Hide From Reports”

  • Once done, click on “Close & Apply” from the top ribbon. This will take you to the Report view, where you need to select “Apply changes” button the top

Customize Bills & Savings with your own Groups/Categories

At this point, most of the report should be good enough, except for the “Bills” & “Savings” on the top, where you might see (Blank).

To fix it, update the formulas to match your own custom Categories

  • Expand the Measures Table and update the formula for all the measures under “Common” folder with the Groups or Categories that you wish to consider for each type of data

    • For example, you might have the Bills under the Group as “Bills”. But, in the sample sheet we have it under “Living”
    • So, you would need to replace Transactions[Group] = “Living” with Transactions[Group] = “Bills”

  • Please note that whatever Groups or Categories that you are considering for Bills & Savings, those need to be excluded in the Expenses

  • There are 4 measures created to get Expenses & Budgets. The same filters need to be applied in all 4 places mentioned below

    • Budget➝"Budget Allocated"
    • Budget➝"Expenses - Current Month"
    • Common➝"Expenses"
    • “Expenses - Delinked”

  • Also, update the filters to exclude the Groups/Categories in your expense charts

    • Open the “Summary” page and select the “Top 5 Expense Categories” chart
    • Open the Filters Pane and exclude the groups that you have for “Bills” & “Savings”, instead of “Living” & “Savings”
    • Similarly, open the “Expense Trend” page and select on one of the charts to update the filter for the Group same as the previous step

Update your local currency (if it isn’t USD)

If your local currency is not USD, then you can update the currency of the Amount fields in the report as follows:

  • Click on the measure name and select the dropdown next to the dollar($) sign in the top ribbon to choose your local currency
  • Also, update the no: of decimal places field from “Auto” to “0” (zero), if it is set to Auto
  • Repeat this step for the below listed Amount fields.
    • Budget Allocated
    • Expenses - Current Month
    • Bills
    • Expenses
    • Income
    • Savings
    • Expenses - Delinked
    • Transactions➝Amount
    • Transactions➝Positive Amount
    • Investment Performance➝Invested
    • Investment Performance➝Returns
    • Latest Balances➝Actual Amount

Usage

Summary Page:

  • This gives an overview of the Cash Flow, Top 5 Expense Categories, Balances and Investment Performance.
  • It also shows the budget left, based on the budget that you’ve assigned in your Categories sheet
  • If you hold Ctrl and click on the bell icon on the top left, you will be able to see the below
    • No: of uncategorized transactions
    • Bills due in a week or past due

Expense Trend Page:

  • This shows your spending pattern by Group/Category/Tag/Account for the last 3-6 months.
  • You can also filter and highlight the visuals using these options

Query Page:

  • For getting answers from your data, you can use this page, where you can ask questions in natural language and get the result either as a single value/table/charts

  • Here are some examples you can try:

    • Sum of amount in transactions using Chase Freedom with date between 14th Apr 2023 and 13th May 2023
    • Savings by calendar month year

Permissions

Is it ok for others to copy, use, and modify your workflow?

Absolutely… Feel free to try it out and let me know if you face any issues or if you have any ideas to get better insights

Notes

This Power BI Dashboard currently works just in Windows OS (not on Mac or Linux). But, if you have a Microsoft 365 Work Account, you can create this report directly in the Power BI service, which is a cloud based solution.

FAQ

  • What should I do if I just want to consider just specific categories as “Bills” or “Savings”, but not Groups?
    In this case, replace Transactions[Group] = “Living” with Transactions[Category] = “Utilities”
    If you have multiple Categories as Bills, you can add a pipe symbol and provide multiple categories in the filter.
    Ex: Transactions[Category] = “Utilities” || Transactions[Category] = “Subscriptions”

  • My data doesn’t get refreshed. What should I do?
    Navigate to Home tab and click on “Refresh” button to refresh the dataset

2

I love this I guess you have to be a computer wiz to use this lol it is very hard to do but I love it and wish I could use it

3

@jd965448 can you pin point which part of the instructions were looking complicated for you.
I can probably help you out in explaining it with a little more details.

Also, I was planning on create a video walkthrough of the setup, so that you can follow along and replicate the same for your Tiller sheet. (Perhaps by next week)

1 Like
4

All of it lol Frist time working with spreadsheets

5

Video would be great

1 Like
6

This is awesome work and a nice write up! Thank you! I got to take some time and make it for excel!

1 Like
7

This is very nice and a great tool. Thanks for introducing it to us with such excellent documentation!

1 Like
8

So unfortunate it doesn’t have a MAC application. But it saved me a few hours because I would definitely try to install! Nice work!

1 Like
9

Thanks, @ppraju12, for demonstrating the power of Power BI.

We were impressed by the ease of setup and connecting to the Google Sheets spreadsheet as a datasource and also the quality of the dashboard widgets. Also, your instructions were thorough and easy to follow. It is a bummer that setup requires a Windows PC (as many of our team are on Macs), though it sounds like it may be able to run in a Mac environment within a MSFT365 account once it is configured (but we weren’t successful setting this up).

We noticed Power BI can be run on an Android device. If you could post a screenshot of what the reporting output looks like on a mobile platform, we (and others!) would be very interested.

As part of the Tiller Builder Rewards Program, the Tiller team is excited to award you $100. There are still some cross-platform limitations and using the paid version of Power BI may be required to get the full value, but we are grateful that you’ve prototyped this integration and documented your implementation so thoroughly.

:trophy:

1 Like
10

@ppraju12 Great write up on the instructions.

I was able to pretty much get this set up without a hitch.

A couple of things where I ran into issues though are as follows:
The part

It was at this point where you kinda lost me. I think additional syntax documentation may be needed. I’m also not sure if this may have resulted from an error that I received after the previous part. It revealed some type of error in the budget by categories part of the query.

I am trying to grasp the syntax of the changes needed for what you are saying

but I don’t think I have it quite right. I was however able to use the Transactions[Category] = “Utilities” as listed in the FAQ section. My question here is how do I use multiple categories that are bills? Or is this limited to a single group or category?

My personal layout of categories and groups might need adjusting if so but just something that I’ll need to work on.

Other than the one spot where I got hung up, this looks great and maybe attributed to my first time using Power BI.

1 Like
11

@ppraju12 ,

Thank you this is awesome. I managed to get it working.

I had to make a modification to the Budget by Categories table to remove some columns which I had in there that were not accounted for, but other than that, it’s working like a charm.

I need to make the adjustments for budget and savings still. Thank you for sharing.

1 Like
12

@bentyre1 Thanks for your feedback.

Yeah, I understand. It can be confusing with the syntax.

If you have multiple Categories as Bills, you can add a pipe symbol twice “||” and provide multiple categories in the filter.
Ex: Transactions[Category] = “Utilities” || Transactions[Category] = “Subscriptions”

And you probably don’t have to adjust your existing groups or categories…

Give this a try and let me know if it works

1 Like
13

@kylemichaelseaver
To be honest, I haven’t used the Excel template till now, as I have been using Google Sheets for over 2 yrs now and have quite a few customizations to match my needs.

But, I will try to set this up with the Sample Data Sheet for Excel and post a separate topic.

14

@tom

Thanks for the reward!!

Here’s my take on your questions.

Is Power BI Desktop only available for Windows?

  • Technically, yes… But, you can connect to a Virtual Machine with Windows OS from your Mac and install Power BI Desktop there
  • As an alternate way, you can import the dataset into Power BI service (Web based app) and create reports.
    But, you would require a Pro License, if you are using your personal Microsoft account (Live or Hotmail). However, you don’t need a Pro License if you use a Work Account with an Azure Active Directory.

Can we view Power BI reports on Mobile?

  • Certainly, but you would need either a Pro License for your Personal Microsoft account or a Work Account with AAD like I mentioned above.
  • Since it is my personal data, I haven’t published my report into the Power BI service of my Work account, despite having a Pro license there. And I don’t have Pro License for my personal account, so I’m currently using it just on my PC.
  • Regarding the mobile layout, we can view it same as the desktop layout on mobile or we can design a separate layout for mobile experience with the same visuals.
  • I will publish the report with the same data show you the possibility of customizing a mobile layout
  • And I’m refreshing the dataset manually everytime before viewing the report.
    On the Power BI service, we have options to schedule the dataset refresh automatically on a desired frequency.

My apologies if I’m being technical over here. But, just wanted to let you know the possibilities

I will be creating a video walk through for the setup and mention all these possibilities as well at the end of the recording. Will try to get that across by next weekend.

1 Like
15

That did work, I have only identified two current categories as bills, but will add more later.

Thank you.

Is there a way to show 0 instead of (Blank) for the ones that don’t have values yet this month. Such as my income currently? I haven’t gotten paid yet but the Blank to me seems like something is broken.

Also, any insight on the error that I received in the Budget by Categories Query

The error is listed as:
DataFormat.Error: We couldn’t parse the input provided as a Date value.
Details:
Track

There are several rows that the month column has an error listed with that same error.

16

@bentyre1

For displaying the value as 0 instead of (Blank), you would have to update the formula for all the measures under “Common” folder of “Measures Table” to add “+ 0” at the end

image

And regarding the error for “Budget by Categories” query, looks like you might have Tags or any other columns other than “Category”, “Group” and Months in the Categories sheet, you would have to add those in the step mentioned below.

17

Hey everyone, I just updated the video with the demo and setup of the dashboard.
I’m hoping everyone would be able to set this up with the help of this video.

@jd965448, give it a shot by referring the video and let me know if you find anything confusing

@tom, At the end of the video, I’ve mentioned couple of additional things that you can setup to get the most out of Power BI.

  • Accessing this dashboard via web or mobile
  • Setting up a different mobile layout for the dashboard
  • Auto-refresh of the dataset

Hope you find it useful!! :slight_smile:

18

How do you access video

19

I’ve included this at the beginning of the Setup section in the post.

But, here’s the link Personal Finance Dashboard for Tiller

20

This tutorial is terrific. Thanks for creating it. I have a question. I’m stuck on the part where you paste the formula for “Type.” Does that formula work for Excel? Or only for Google Sheets? The reason I ask is that I get an error message, and after I hit “ok” Excel directs me to the “Indirect” formula. I’m trying to troubleshoot the formula, but it’s pretty involved and there’s a lot of formulas I don’t recognize. Any help is appreciated. Hope this makes sense. Thanks again!
Capture

1 Like