Balance Tracker

Google Sheets Show & Tell
,
1

What is the goal of your workflow? What problem does it solve, or how does it help you?
This Balance Tracker spreadsheet shows how daily account balances have changed since any given date.

It extracts data from Tiller’s Balance History spreadsheet, sorts it into account columns, and plots individual histories. It can exclude records prior to a specified date, calculate subtotals and figure percentage gain or loss.
How did you come up with the idea for your workflow?
I like to view past fluctuations of my various account balances.
Please describe your workflow. What are the sheets? Does it use any custom scripts or formulas?
Pretty simple. Just one functioning spreadsheet with no scripts, no custom formulas, and only two cells available for manual input. All it does is revamp data taken from the Balance History sheet already present in Tiller feed templates. (I’ve included a dummy Balance History in the workbook, along with a sheet of notes.)

The heart of the spreadsheet is a pivot table entered in cell D6. It places accounts in columns (as many as needed), creates a row for each distinct date, and places balances in the value grid. The balances are averaged by date to ensure that each date appears only once. Dates are filtered according to the date entered in cell A1.

Sparkline plots above account columns show the rise and fall of balances over the selected time period. Note that these plots do not have consistent vertical scaling. These plots can be deleted or copied and pasted according to the number of accounts.

Red and green values below the sparklines show the percentage change in balance over the selected time period.

An inset chart displays a line graph for any one account depending on what column letter is entered in cell A2. For a meaningful display, the chart needs to be edited and customized for each account to set a suitable minimum value for the vertical axis. I couldn’t figure a way to automate that step without a script.

The chart plots data from column C. Columns A and B are subtotals that can be modified to suit by editing cells A8 and B8.
Anything else you’d like people to know?
I’m a new subscriber learning the ropes and pestering the help desk hourly. Racing the clock tonight to dip my toe in the water before the deadline.
Is it ok for others to copy, use, and modify your workflow?
Please do. I welcome any questions, comments, or improvements.
If you said yes above, please make a copy of your workflow and share the copy’s URL:

https://docs.google.com/spreadsheets/d/1ni0UoOjGaEjU98oam2nKGrautjaBVurAWzbhYQGlQ30/template/preview

2

Can someone explain how to get this into my Sheet? I have to be missing something here. Thanks, Blake

3

Blake, when I click on the URL just below the box above, I get a preview with, in the upper right corner, an option to Use Template. Clicking on that opens a copy that can be edited. This is what I’d expect. I hope that works for you as well.

More puzzling to me is that if I click on the title or icon inside the box, I go straight to the workbook (or maybe a copy), bypassing the preview. That doesn’t seem right. Perhaps that works only for me as the owner. I don’t know.

4

Morning after thoughts: I should have sorted in descending order to see current balances up top and perhaps frozen the upper rows. There’s room for at least one more chart, and the charts need titles. Distinctive matching line colors on the charts and their corresponding sparklines would help.

5

I just now clicked on the URL and got a page were I needed to request permission which I did.

Last night when I clicked the URL I got to and then clicked on the Use Template. It pulled up a copy which was fine. My problem was about how to get that copy into my Sheet. I copied it over and ran into what I think were problems copying over the pivot table in D6. I just redid the pivot table but things did not work so I thought I would stop wasting any more time and ask for guidance because it seems like I am missing something simple.

Any guidance?