Overview
Spreadsheets are a great tool for personal finance.
Unfortunately, they often aren’t responsive, accessible, or user-friendly when you are on the go.
So, how best to check in on key metrics when you are out and about?
How can one answer questions like:
- Is there enough budget left to dine out tonight?
- Has my paycheck posted this month?
- Is my checking account balance dangerously low?
Last month, our #winning-workflow theme was mobile-friendly dashboards.
I wanted to share a solution I built for my personal-finance dashboard.
The tiller-web-dashboard project leverages Google-Apps-Script web apps to publish a real-time, responsive, mobile-friendly dashboard for your personal-finance spreadsheets. Just identify the metrics that matter to you, link them into the Web Dashboard configuration sheet, and get quick answers on the web wherever you are.
Warnings
- This project is designed for intermediate users and includes only lightweight error checking. We hope it meets your needs out of the box, but further tweaks may be required to get it working in your environment. As a one-off Tiller-Labs release, Tiller offers no warranties or support for this solution.
- Before running the script, you will see a message from Google that says “This app isn’t verified.” This is normal since you will have just created the script. Review the code and make sure you are comfortable with what it is doing— again this script is for intermediate & advanced users. You will need to click an authorization button from Google that says
Go to <your script name> (unsafe)to run the script.
How the Script Works
The script contains two components:
- Google Apps Script (GAS) script that will be embedded in your spreadsheet
-
Dashboard renderer hosted by Tiller (this
htmlwon’t render properly if you click on it— you’ll need to click “View Page Source” to see the code)
The GAS script will be configured as a web app and will respond to calls to its endpoint by sending the contents of your spreadsheet’s Web Dashboard sheet to the dashboard renderer. The dashboard will then leverage Bootstrap, JQuery and Javascript to render the data into a responsive, mobile-friendly dashboard.
We have chosen to deploy the GAS script with minimal formatting responsibility so that it will rarely need to be updated and redeployed. It is built to simply forward the sheet data to the renderer.
Set Up Your Spreadsheet
- Install the sample dashboard configuration sheet (instructions on GitHub)
- Install the script in your spreadsheet (instructions on GitHub)
- Personalize your dashboard (instructions below)
Personalize Your Dashboard
Configure your dashboard to always shows the metrics that matter to you most.
The dashboard is configured via the content of the new sheet named Web Dashboard.
The first row in the Web Dashboard sheet contains your dashboard title.
The second row in the Web Dashboard sheet contains headers recognized by the renderer. The renderer recognizes the following header names:
HeaderValueTypeColorNote
Columns with (row-two) headers not listed above will be ignored by the renderer.
Each subsequent row will contain cells defining a metric block.
Once the script is deployed as a web app, the contents of your dashboard will always be live. Just reload your dashboard URL (the one from the “Deploy as web app” window) and you will see your most up-to-date metrics.
Title
Update the contents of cell A1 to change your dashboard’s title.
Metric Blocks
Metrics will be organized into responsive, mobile-friendly squares.
Header
The header cell is the title of the metric block.
Value
The value cell is the primary value of the metric block. The content will be formatted by the Type cell.
Type
The type cell determines which formatter is applied to the value.
The renderer recognizes the following types:
-
currency- a currency value without trailing digits (e.g. $1,515) -
currency00- a currency value with trailing digits (e.g. $1,515.50) -
date- a date formatted as “m/d/yy” (e.g. 2/14/20) -
integer- an integer (e.g. 5) -
percent- a percent without trailing digits (e.g. 15%) -
text- unformatted text
Color
The color cell determines the color of the metric block.
The following colors are recognized by the renderer: “apricot”, “beige”, “black”, “blue”, “brown”, “cyan”, “darkgray”, “darkblue”, “green”, “lavender”, “lime”, “gray”, “magenta”, “maroon”, “mint”, “navy”, “olive”, “orange”, “pink”, “purple”, “red”, “teal”, “white”, "yellow”.
Using formulas to dynamically set the color of metric blocks can be a powerful tool for calling attention. For example, a formula like the one below can flip a metric from green to red when it falls below a certain value:
=if(B5<200,"red","green")
Note
The note cell behaves like a footer to the metric. It is located at the bottom and appears in a smaller font. It can be used to provide more detailed context for a metric (like the metric’s date range).
Bundled Metric Blocks
With proper formatting, multiple data points can be bundled into a single metric block.
Multi-value
With multi-value bundled metric blocks, multiple values are sequentially displayed, like a vertical list, within a single metric block. Multiple metric rows (in the Web Dashboard) are rendered into a single metric block when subsequent rows have a blank header cell.
Data Tables
With data table bundled metric blocks, a small table can be built within a metric block by setting the value and type cells as data arrays. As with multi-value bundled metric blocks, multiple rows can be added to the data table by leaving the header cell subsequent rows blank.
A data table value should be formatted to look like:
["2020-02-15","Citi Mortgag",-1000]
And may have a corresponding type cell that looks like:
["date","text","currency"]
To build array data like the example above, you may use a formula like:
="["""&text(G10,"YYYY-MM-DD")&""","""&left(H10,12)&""","&I10&"]"
This formula takes a date value from G10 and applies a date formatter. It also clips the description in H10 to just 12 characters.
Examples
Uncategorized Transactions
This example counts uncategorized transactions in the Transactions sheet in the current year.
=countifs(Transactions!C2:C,"",Transactions!A2:A,">="&date(year(today()),1,1),Transactions!A2:A,"<"&date(year(today())+1,1,1))
Current Account Balance
This example performs a lookup on “Wells Fargo Checking” from the Accounts sheet.
=vlookup("Wells Fargo Checking",{Accounts!$H$2:$H,Accounts!$J$2:$J},2,false)
Category Available in Current Month
This example finds the available/remaining budget in the “Clothing” category by doing a two axis lookup in the Yearly Budget sheet— category on the rows axis and budget month on the columns axis.
=offset('Yearly Budget'!$E$7,match("Clothing",'Yearly Budget'!$A$7:$A,0)-1,iferror(match(date(year(today()),month(today()),1),INDIRECT("'Yearly Budget'!E3:3"),0))+1)
FAQ
Can I remove the “Terms of Service” message at the top?
I don’t believe so. I think it is a precaution applied to all web apps by Google.