Balance History Tracker - Excel

Microsoft Excel Show & Tell
1

Overview

This is a port of my Google Sheets version of Balance History Tracker for Excel.
The functionality is the same.
I’ve always wanted to redo the Balance History sheet to track by day (if you run Tiller, you can often get multiple updates by day) and then by account/institution, etc. I then saw a post about tracking balances and it pushed me to see if I can finish it up.

image
image955×429 21.7 KB

Installation

  1. You will download my shared sheet, which has several solutions. You will be dealing with the “Balance History Tracker” sheet.
  2. Download the [Transaction Tracker.xlsx] (Microsoft OneDrive - Access files anywhere. Create docs with free Office Online.).
  3. Follow [these instructions ](How to move a new worksheet into your Tiller-powered Excel workbook | Tiller Help Center nto-your-tiller-powered-excel-workbook) to copy the downloaded template into your Excel workbook and to connect the formula references to your local workbook data.

Usage

All filters are optional, and the combinable (but if you select all criteria, it’s possible that nothing will return based on the conditons).

Notes

This uses the last balance update of the day.

Permissions

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

2

This solution appears to be just what I’m looking for. I added the sheet to my workbook and updated the links to point to my workbook. However, I get several #Ref errors in the top filters. Were these filters created using ‘Data Validation’ lists, or references? Can you point me in a direction to resolve the linkage issue?
Thanks. Appears you’ve done a really good job with this sheet. I hope I can use it.
Kip

3

Thanks for your post. It is possible I messed up the filters, iirc I used named ranges. If you unhide the columns to the right, after the raw data should be the lookups for the drop down. When I get home I’ll take a look at it.

4

No worries. I’m sort of new to Tiller. I know the sheets have hidden tables, named ranges and what-not. I just need know how to access the data you used in the top filtering section of your sheet. When I imported the sheet into my workbook and tried to re-target the references, things broke. I’d appreciate whatever you can tell me to help me fix the filters.

Thanks for your time.

Kip

5

That data is just referencing the existing data using a filter and range. If you unhide the columns, and scroll to the right, you should see a few columns with the lookup information.

6

I’ve tried unhiding the columns and I get nothing.

~WRD0005.jpg

7

Stand by, I’m making progress.

~WRD0005.jpg

8

I have 90% of it figured out and working. Thanks for your help. Your worksheet works like a charm. Well done.

Kip

~WRD0005.jpg

9

Thanks, glad to be of no help. :slight_smile: Let me know if you think of suggestions to make it better.

1 Like
10

A Set of check boxes that limit the data retrieval to some time frame, like, last 10 days, last 30 days, or all results within the period chosen. I want to see the recent trends of certain account balances within different time frames, within the selected period.

Kip

~WRD0005.jpg

11

There is a date filter with a drop down for selected dynamic dates as well as letting you pick custom dates.

12

Hey @yossiea!

Thanks so much for building this in Google Sheets and porting it to Excel. I really like how this is a relatively simple template, but it fills an important need.

I had some trouble getting it to work in my sheet. Eventually, I figured out that the range you had in the FILTER() in L13 (i.e. “BalanceHistory[Date]:BalanceHistory[Class]”) was returning garbled and incomplete data because my Balance History column order did not match yours. I solved this by changing the formula in L13 to this (essentially creating a custom range with HSTACK()):

=IFERROR(FILTER(HSTACK(BalanceHistory[Date],BalanceHistory[Time],BalanceHistory[Account],BalanceHistory[Account '#],BalanceHistory[Account ID],BalanceHistory[Balance ID],BalanceHistory[Institution],BalanceHistory[Balance],BalanceHistory[Month],BalanceHistory[Week],BalanceHistory[Type],BalanceHistory[Class]),(IF(B5<>"",BalanceHistory[Class]=$B$5,1=1))*(BalanceHistory[Date]>=O2)*(BalanceHistory[Date]<=O3)*(IF(B6<>"",BalanceHistory[Type]=$B$6,1=1))*(IF(D5<>"",BalanceHistory[Institution]=$D$5,1=1))*(IF(D6<>"",BalanceHistory[Account]=$D$6,1=1))),"")

The results looked reasonable after making this change.

I’m not sure I understand what is going on in A13 (particularly the reference to W:W) but you might be able to simplify this section using a spill range (e.g. referencing L13#).

Hope these suggestions make sense.

As part of the Tiller Builder Rewards Program , the Tiller team is excited to award you $100. Thanks so much for sharing and documenting this.

:trophy:

13

Thanks! I will edit and push the hstack version shortly. I also fixed the date filter and made minor cosmetic changes. The thinking behind the A filter is that I am trying to get the last record returned per date/account, in case someone gets multiple during the day. The W is just a column that has data and the format is consistent.

14

I just pushed a major upgrade that fixed a bug that happens when there were too many balances in one day. I also added a sort by date feature.

15

Hi, thanks for the workboo! I’m having trouble getting this file to work on its own before moving it to my file. After making some column order changes and copying over my transaction and balance history data, I got the Transaction Tracker sheet to work but the Balance History Tracker only works with Assets but not Liabilities. Can you please help?

16

Just found this template. Absolutely perfect for what I was looking to figure out myself.
Thank you for coming up with this.
Have you thought about adding a graph that would show the Balance over time for each Account?
I tried adding one, but it is not straight forward as laying over a graph on the data or a pivot graph.
Bob

1 Like