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.
However, as long as itās working, youāre good!
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?
You need to make sure the URL is correct and you have to allow access to it. The āspreadsheet cannot be foundā error indicates the URL is incorrect.
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ā¦
Iām having a similar issue to jmilner, except that Iām getting the āResults too largeā error on the āTransactionsā sheet. I receive the same error regardless of which category I select in the āSingle Investmentā sheet. That said, everything seems to be loading correctly on the āTiller Connectionā sheet.
Any suggestions why the error is preventing Transactions from loading?
That should only happen if you try to import >240,000 rows since each column is a separate Import (it used to be 175,000 cells but when I Google it now, it says 240,000). Itās hard to believe you are importing that many.
Maybe pick a category that you know doesnāt have many entries in your Main Tiller workbook and see what happens.
Also, make sure you check the version you are using. Should be 1.6.
Thanks for the response - Iām definitely not trying to import that many cells. Iāve tried several tweaks but cannot locate the error. The funny part is that Categories, Accounts, and Balance History all loaded exactly as I expected, and yet the Transactions loading error persists.
@aaron18
Iām not sure whatās going on. Have you changed the āInvestment Categoryā to a category that you know has very few transactions- maybe an annual tax bill, to see if that populates correctly?
