Docs: Recent Balances

Google Sheets Show & Tell
1

Overview

The Recent Balances template makes charting account balances from the past 30 days quick and easy. It pulls data from the Balance History sheet and renders that data tabularly and graphically based on a number of options in a dropdown:

  • All Accounts
  • Selected Accounts
  • Asset Accounts
  • Liability Accounts
  • (Individual Accounts)

This template is based on feature request from @richard1.

Installation

To install the template in your personal spreadsheet, follow these steps:

  1. Open the template-master spreadsheet.
  2. Right click on the “Recent Balances” tab and select “Copy to / Existing spreadsheet”. At the bottom, enter the URL of your personal spreadsheet.
  3. In your personal spreadsheet, rename the tab from “Copy of Recent Balances” to “Recent Balances”.

Setup

The template relies on the Tiller Balance History and Accounts sheets. They must be installed in your spreadsheet for the template to function. If these sheets are installed, no additional setup is required.

Usage

Mostly, just select an account subset from the green dropdown in A4.

To display a customized set of accounts, unhide first group F:G and check a set of accounts that will display when “Selected Accounts” is chosen in the dropdown.

Unhiding the second group J:AO will reveal the day-by-day balances. (This area feeds the chart.)

Permissions

Feel free to do whatever you want to it. Make it better. Make it your own.

I probably won’t add features, maintain this template or make enhancements, but I encourage you to make improvements and repost the template.

Notes

  • Changes to the list of accounts in your spreadsheet may jumble your checkbox selections feeding the “Selected Accounts” option.
  • Account balance lines in the chart may show as points or have interruptions if there are day-to-day gaps in the balances in your Balance History sheet. :slightly_frowning_face:
2

I was thinking of trying the same thing, glad you beat me to it! Unfortunately, it’s not working for me. So far, I found the query in AR7 wouldn’t expand because the word ‘max’ was entered in AS7. When I deleted AS7, the query in AR7 filled in. The section from J-AO isn’t filling in now. I’m at work, so haven’t had time to figure out where the issue is there, but thought I’d let you know in case you have an idea of what could be going on.

3

Actually, never mind the last post, I have it working out of the box for the first 4 options in the dropdown, but it doesn’t show anything if I select an individual account from the dropdown.

5

Thanks for your patience, @jpfieber. Just took a look at this. I had a similar issue when I installed the sheet in my personal spreadsheet.

The cause was that the column lookups in CA9:CA12 were not regenerating after the template-copy operation. For example, the “Date” lookup in CA9 was pointing to column “B” (which was accurate for the master spreadsheet, but pointed to “Time” in my personal spreadsheet). I resolved the issue by “jostling” the formulas in CA9:CA12 (e.g. by adding a space at the end of each formula to force them to recompute). Can you try this?

If you experience the same issue, it is drag for sharing templates— really degrades the experience. Not sure if it is something I did differently or a change on Google’s end, but I feel like the tooling used to not have this problem.

As for the single-account selections, you caught a bug. There was a missing cell reference in K6. That is fixed in the latest master.

Let me know how your experience is…