I am trying to set up this sheet and resolve all of the errors I see. I took my 401k and added balance history going back to 1/1/2018, and then manually added one data point each from 2017 and 2016.
The earliest deposit transaction I had was 3/2018, so I added a manual “Balance Adjustment” entry to try to make things work, with the same date as my initial balance. I also tried the day before the initial balance. (Note that I’ve hidden some columns before taking the screenshots to remove employer name)
There are also errors in my Connections sheet, but I am not sure if they are relevant. I took them to mean they were looking for extra metadata that I don’t use like tags.
Do your formulas rely on the ‘month’ and ‘week’ columns in the Balance History sheet? I’ve been following the Tiller instructions for manual balance history of copying an automatic entry and just changing the Date and Balance cells. Or possibly it is not liking the duplicated balance ID fields?
Edit I tried using blank Balance ID fields and manually fixed the Month fields, which did not seem to change any behavior.
The workbook only looks at the raw date, not week or month.
Do your Cash Flows look correct? If so, then it’s identifying the Accounts correctly but it looks like your balances (other than the most recent) are not being imported into the workbook. Do you have any balances in the Balance History sheet? This sheet should pull in balances for any Account class that is an “Asset”…
You you don’t have any balances in the Balance History sheet, we need to fix that. If you see the correct balances in the Balance History sheet, then let’s take a look at the Monthly Investments sheet to debug.
In the Monthly Investments sheet, columns BG through BK pull in the balances from the Balance History sheet. Then columns DD through DS arrange them into months. What do these columns say?
The Transactions and Balance History sheet both look healthy - Transactions correctly lists 322 ‘Benefit Income’ transaction to the 401k between 3/2018 and today, with one ‘Investment Deposit’ transaction 6/30/2016 to match my starting balance on 7/1/2016.
Balance History correctly shows 76 balance entries for the 401k between 1/2018 and today, plus one for 1/1/2017 and one for 7/1/2016, all classified as an Asset.
In the Monthly Investment sheet:
The BG-BK block has one apparently problem, the ‘month’ cell is blank for just this account (column BH). My other accounts have this cell filled (I haven’t really tried to get any other accounts working with this sheet).
Please filter the Balance History sheet on the 401K account and make sure all the balances are there and correct with the right Dates and Months.
On columns BG-BK in the Monthly Investments sheet , do they show the correct balances for your 401K account? The apparent problem you see doesn’t make sense as it’s just querying the Balance History data and then looking up the Account Group from the Accounts sheet. If there is a blank month in these columns, there is a blank month in the Balance History sheet.
When you said “The workbook only looks at the raw date, not week or month” I took that to mean that the “Month” column in Monthly Balance was irrelevant, and only “Date” mattered.
But I noticed that your sheet was only pulling in some of the Month values, and it turns out the reason is the date formatting was not consistent. Most of my 401k Month values were in the format of MM/DD/YYYY. I changed the formatting of the column to M/D/YY and even though I didn’t actually touch any values, that was enough to cause the Investment Returns sheet to pull in that column, and so far everything is looking fixed.
One caveat, the Tiller page about the Balance History sheet describes the Month column as “The month the balance data was pulled into the sheet.” I interpret that to mean that a balance dated 1/1/2016 could have a Month value of 11/1/23, if I just retrieved that data. I am not sure there are any solutions using it that way or if thats what the meaning is. I wonder if it’d be worthwhile to make Investment Returns less reliant on the Month value and calculate it based on the Date alone?
I’m glad it’s working, but your explanation is a bit confusing. The Month Column in Balance History should be first day of the month of the date of the Balance- e.g. Balance Date = 11/15/2023, Month = 11/1/2023; Balance Date = 12/3/2023, Month = 12/1/2023. Not sure why the formatting matters though.
FYI- when talking about the Balance History sheet, when I said "The workbook only looks at the raw date, not week or month”, I meant that I am not calculating the month in this sheet, just pulling it from the Tiller main sheet.
That’s part of my confusion, the Tiller doc defines the column as “The month the balance data was pulled into the sheet.” I pulled my old balances into the sheet this month, so it seemed to me that they should reflect 11/1/2023, for whatever reason.
If the cell reads 11/1/17 it gets pulled in, if it says 11/01/2017 it doesn’t. No idea why. But at least we’re all good, now.
Thanks for your work on this. I am new to Tiller - coming from Mint. I have added several community solutions to my Foundation, but per your recommendations, understand this is a separate file that links to the Foundation. However, I am not clear on getting this solution started.
I clicked on the link for the 1.6 version, and I get a View Only copy of the solution. Do I need access permission from you prior to being able to insert my Foundation URL into the Tiller Connection sheet? I am unable insert my URL into this because it is View Only. Did I miss something in the instructions?
I am also confused if this is the process. I do not want to be sharing this file with you after you give the me the access. What is the security set up of the file after I have editing rights?
I think this will be a nice solution once I get the access. Thanks again.
@chris.oneill619
You need to copy the template into your Google Drive; then you can edit it however you like and unless you make that public, no one else can see it.
Ok, silly me. I did the download and started working on it right away but had not saved it to my Drive yet. Sorry to bother you about that one!
Now I have it saved, and inserted the URL of my Foundation Sheet into the A2 cell, but no data was pulled. I understand that data is being pulled from the Foundation Sheets and that the cell references need to be correct, but I did not seem to get anything pulled. I checked my Foundation Transactions sheet against a new Foundation sheet and I have not changed the column headers in that sheet or the Categories, Accounts, and Balance History sheets. What could I be doing wrong? Below is a screen shot of the Tiller Connection sheet. Do I still have a linking issue?
This looks cool and I love how it tracks returns - not just investment value. But I would request to add an step by step instructions sheet similar to the original tiller foundations sheet, as the issue is I think a lot of assumptions are from the start. By step 2 I am already lost. DO i enter the initial deposits on the transaction sheet? Why does that not sheet pull in categories, even though they are available on another sheet. Playing around, I can successfully add my Cash flow categories, but nothing populates. Error cells are everywhere with no tips.
I think I will check back on this one after some time, i think it has a lot of potential for but for a more layman user it seems it would take a lot of time to get this functional. I highly respect the know how how to put something like this together though!
What questions do you have? I thought I did include instructions in my initial post, but let me know how I can help. If nothing populates, check the Transactions, Categories, etc. tabs. If there is not data in those, then your link to your Tiller workbook is incorrect or you didn’t give access.
Here are the starting instructions …
You then choose the accounts you want to see.
My first post also includes guidance for initial deposits and balances…
I missed your question on Categories. This workbook only pulls in Transactions with the Investment Cash Flow Categories you choose. It doesn’t use transactions for any of the other Categories.
As for the Tiller Workbook link, you can check if this is working very quickly by looking at the Tiller Connection Sheet. If it has populated Columns C and D, the link is working…
