Template: Balances-Only Importer

Overview

The Balances-Only Importer template is designed for those who want to include only account balances for certain accounts, not transactions. This is especially useful for retirement or investment accounts that generate tons of transactions when all we want are the balances to feed reports like Balances and Net Worth.

Tiller Money Feeds does not currently have this functionality, but you can vote for it in this feature request. It’s been out there for a long time, so don’t hold your breath.

I should also mention that three common workarounds work pretty well and can be found in this discussion. I’m not crazy about those options because of the large number of investment accounts and I don’t want to mess around with the Transactions sheet.

So I’ve been using this method…

Connect the Balances-Only Importer spreadsheet to your investment accounts using Tiller Money Feeds and turn on Autofill. This basically sets up an additional Tiller Spreadsheet with updated balances. It also fills a Transactions sheet, but we ignore that and all other sheets. We only need the Balance-Only Importer sheet.

Setup time: 5-10 minutes
Recurring workflow: 1-2 minutes

Installation

  1. Open the Balances-Only Importer template and choose File > Make a Copy > and name it such that you’ll know it contains your balances-only accounts (e.g. “Investment Balances”).

  2. Go to Extensions > Tiller Money Feeds > Launch, then choose Link Sheet. The sidebar displays your accounts. Select those for which you want balances only, then choose Confirm. This runs the initial feed of balances and transactions. (If the hidden Transactions sheet appears, you can ignore or hide it again; we won’t use it.)

  3. Select Accounts from the Dropdown - Choose which accounts you want to track balances-only from the dropdown list in column B.

  4. Add the GID - In cell L2, input the template sheet’s GID. You can find this in the URL as a 10-12 digit number after …gid=

  5. Choose a Period such as Last Month, from the dropdown.

  6. Copy the Generated Rows - Follow the Workflow instructions in the column “I” header area. The hyperlink will automatically highlight the rows to copy if you entered the GID in step 5 above.

  7. Open Your Primary Tiller Spreadsheet, then go to the Balance History sheet and insert the number of blank rows indicated in the workflow instructions.

Click for a PRO TIP to append a large number of rows and values to an existing table
  • Scroll to the bottom of the sheet, select the last row, then choose right-click > Insert 1 row below. With the new blank row highlighted, choose Edit > Paste special > Values only. The sheet will automatically expand for the required number of pasted rows.
  1. Paste as Values Only - Go to Edit > Paste Special > Values Only to paste the balances to the blank rows.

  2. Add Accounts to the Accounts Sheet - If this is the first time adding these accounts to your primary Tiller spreadsheet, go to the Accounts sheet, use the dropdowns in column B to add them, and add a Group label such as “Investments.” Another way to find new and ungrouped accounts is to open the Balances sheet to look for “Ungrouped Assets.” OR…

  3. If this is NOT your first time adding these accounts, unlink them with these instructions to no longer download their transactions and balances.

Usage

When you want to update your balances, such as monthly or weekly, follow these steps:

  1. Open the Balances-Only Importer - Open the Tiller spreadsheet you set up which contains the Balances-Only Importer.

  2. Select a Period to retrieve the desired balance history:

  • Last Update returns only the latest balance for each account at the time you run the workflow.
  • Last Week and Last Month returns the daily balances for the time period selected.
  • Custom allows you to enter any date range, retrieving daily balances for that range.
  1. Copy the Balances Records - Select and copy the resulting records.

  2. Paste into Primary Tiller Spreadsheet - Open your primary Tiller spreadsheet. In the Balance History sheet, insert blank rows and paste the copied balances using Paste Special > Values Only to ensure proper formatting.

  3. Repeat as Needed - You can repeat this process at your chosen frequency (weekly, monthly, etc.) to keep your account balances current.

Permissions

You are welcome to copy, use, and modify this template to fit your needs. Please note that a few updated versions may be released periodically as improvements are made.

Notes

  • This sheet has a hidden section with formulas. Do not delete or insert rows. Instead, insert or delete “cells” and shift.

Versions

Version Released Description
version 1.1 9/20/2024 initial release, latest balance only
version 2.1 9/29/2024 added template “Daily Balances-Only Importer” with Period selection
version 2.2 10/23/2024 converged templates into one “Balances-Only Importer” with Period selection including Last Balance
version 2.3 2/1/2025 simplified process to reduce setup time

FAQ

I don’t see a Balance History sheet?

This sheet may be hidden. Go to View > Hidden Sheets > Show Balance History.

Why don’t I see my account in the Select Accounts dropdown?

Ensure that you have filled out the green section of the Accounts sheet. That is the source of the dropdown values.

I already have a separate Tiller spreadsheet with my investment accounts; can I use that file instead of creating a new one?

Yes, you can. Simply copy the template into your existing spreadsheet and choose the accounts that you want balances-only.

Hey Brett, giving this a try and it’s easy use :slight_smile:

What do you do when the Date is not descending sort?
image

I don’t think the sort order matters, and when the next money feed fills in the balance history sheet, it sorts the sheet again anyway. To confirm your screenshot, the 9/19/24 date is for a different account than those showing 9/24/24, correct? And 9/19/24 is the latest balance for that account?

Okay, just let the next fill sort it.

Correct to both.

BTW, I changed cell H2 to a HYPERLINK to select the range with a click :slight_smile:
It does mean adding a cell containing the sheet’s GID.

image

=LET(numRows, COUNT($D$6:$D), firstCell, "C6",
lastCol, IFERROR(SUBSTITUTE(ADDRESS(1, MATCH("ACCOUNTS (Filter ""Hide"")", 5:5, 0) - 2, 4), "1", ""), ""),
lastCell, lastCol&numRows+5, gid, $K$2,
HYPERLINK("#gid="&gid&"range="&firstCell&":"&lastCell, "1. Highlight the "&numRows&" rows below ("&firstCell&":"&lastCell&")")
)
1 Like

This is cool, well-documented and well designed, @brett. Thanks for sharing.

2 Likes

UPDATE 10/23/2024
The added functionality described below has been converged into one template and the instructions at the top have been incorporated.

I added an alternative version of the Balances-Only Importer template, which allows you to return daily balances from a date range, instead of only the current balance. This feature offers two key benefits:

  1. Historical Balance Import: When you set it up for the first time, you can pull in several months of historical balances using the custom date range, allowing you to see trends in your Net Worth report right away.

  2. Granularity: While you may choose to run this workflow monthly, it still enables “weekly” granularity in the Net Worth report.

Additional Setup Instructions
Since this template generates many more rows of data to paste into your Balance History sheet, follow these extra steps to improve usability:

  1. Add a File “Smart Chip”: In cell B3, insert a smart chip for your primary Tiller spreadsheet. This serves as a hyperlink to the file where you’ll paste the balances and automatically updates the file name in the workflow instructions.

  2. Add the GID: In cell R3, input the template sheet’s GID. You can find this in the URL as a 10-12 digit number after …gid=.


    This will create a hyperlink in Step 1 of the workflow, automatically highlighting the rows to copy.

PRO TIP: When pasting a large number of rows (let’s say 24) into another sheet, rather than highlighting and inserting 24 rows, insert a single row at the bottom of the sheet, then paste values. It will automatically expand the sheet. Or, use the ADD rows button at the bottom of the sheet.


Then sort descending on the Date column, or wait for the next Money Feed to sort the Balance History sheet automatically.

A big shout-out :clap: to @Mark.S for providing the Query formula that returns the results!

2 Likes

Hey Brett, I was playing around with the Daily Balances-Only Importer sheet some and I created a Last Update “Period” recognized by the QUERY, which will only list the Last Update balances for the selected accounts.

In case you want to take a peek:
Balances-Only Importer v1.2 - EDIT

I made a few other tweaks here and there, like fixed some #REF errors on the Balance History sheet, along with unique Balance IDs.

1 Like

Sweet! You read my mind: “Why not update the new template option to pull the latest balances too, then delete the original template?” but I thought I’d break your query, so thanks for doing that. I will delete the first sheet (v1.2) and rename your edited sheet to “Balances-Only Importer” v2.2 and update the instructions tomorrow.

…now if only someone will actually use it. :laughing:

1 Like

Maybe do some testing and try to break it, as I’ve only done a bit of testing myself.

One thing I discovered while playing with this is that the Balance History, Date Added column is stored as a “Date time”, but formatted as Date.

Balance History, Date Added column:

So, when copy/pasting values from the template, only the Date is preserved and not the time.

The “Date time” can be preserved by formatting the Balance History, Date Added column as “Date time” in the spreadsheet where the Balances-Only Importer resides.

Just a little detail.

1 Like

Thanks for the help, Mark! I made those changes and everything tested fine. I released version 2.2 today and updated the instructions.

1 Like

I’ve shortened the template setup process so that it takes between 5-10 minutes to set up. The recurring workflow process is unchanged and takes around 1-3 minutes. I also shared links to the other more popular methods for handling investment accounts, and where to vote for Tiller to add the feature for balance-only accounts.

2 Likes