Template: Balances-Only Importer

Google Sheets Show & Tell
, , ,
1

Overview

The Balances-Only Importer template is designed for those who want to include only account balances for certain accounts, not transactions, in their financial reports. This is especially useful for retirement or investment accounts that generate numerous transactions, but where only the balances matter for reports like Balances and Net Worth.

Until Tiller implements the feature request, three common recommendations are:

  • Create a separate Tiller spreadsheet for investments
  • Set up manual accounts to enter balances
  • Set Autocat rules to categorize investment accounts as “delete” and periodically delete those transaction rows

Streamlined Approach Using This Template

This template simplifies the process of the first two methods by automatically generating your latest account balances in the correct format on a separate worksheet. You can then easily copy/paste the results into the Balance History sheet of your main Tiller worksheet. The result will include a more complete financial picture without all of the transactions that come with investments.

BOAWorkflow
BOAWorkflow2536Ă—570 150 KB

Installation

  1. Create a New Tiller Spreadsheet - Start by creating a separate Tiller spreadsheet using the Tiller Console. Connect this file to the accounts for which you only want to track balances, such as retirement or investment accounts.

  2. Copy the Balances-Only Importer Template - Copy the Balances-Only Importer sheet into your new Tiller file (right-click the tab, choose Copy-To > Existing Spreadsheet). This template will generate your account balances in the correct format.

  3. Select Accounts from the Dropdown - Choose which accounts you want to track balances-only from the dropdown list. The template will pull the latest balances from the selected accounts and regularly update them from Tiller Money Feeds.

  4. Copy the Generated Rows - You now see the latest balances for the selected accounts, let’s say 5 accounts for this example. Highlight and copy the rows from columns C through Q. The “Workflow” instructions in the column H header area describe this step.

  5. Open Your Primary Tiller Spreadsheet - Switch to your primary Tiller spreadsheet. Go to the Balance History sheet and insert blank rows where you want to add the new balances (in this example, 5 blank rows).

  6. Paste as Values Only - To ensure correct formatting, go to Edit > Paste Special > Values Only to paste the copied rows into the blank rows that you inserted in the Balance History sheet.

  7. Add Accounts to the Accounts Sheet - If this is the first time these accounts are being added to your primary Tiller spreadsheet, navigate to the Accounts sheet and select the newly added accounts from the dropdown menu. Don’t forget to add a Group label.

Usage

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

  1. Open the Tiller Spreadsheet - Open the Tiller spreadsheet where you installed the Balances-Only Importer sheet.

  2. Review the Latest Balances - Ensure the balances for your selected accounts are up to date. The template will automatically pull the latest balances from the Balance History sheet fed by Tiller Money Feeds.

  3. Copy the New Balances - Select the newly updated rows with account balances and copy them.

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

  5. Check the Accounts Sheet - If this is your first time updating, or if new accounts were added, confirm that these accounts are listed correctly on the Accounts sheet.

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

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

  • I chose to keep this workflow simplistic. The Accounts sheet is its source because it provides the latest balances and account attributes.
  • This sheet has a hidden section with formulas. Do not delete or insert rows. Instead, insert or delete “cells” and shift.

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.

2

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

3

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?

4

Okay, just let the next fill sort it.

Correct to both.

5

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
6

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

2 Likes