I just migrated from Mint and trying to figure out if I can finally deal in a better way with shared accounts. I did read the guidelines about shared expenses.
I’m using Tiller to only track my personal finances (not my spouses). We have separate finances except for a couple of credit cards and a savings and checking account. Most (but not all) purchases in the shared credit cards should only impact my spending by 50%. I could automatically tag them as “Shared” using AutoCat, but I’m not sure how to automatically reduce the amount by 50% in my Transaction sheet.
I also don’t want the balances for the shared credit cards and shared savings/checking account to impact my “Balances” sheet and net worth calculation. Only 50% of the balance should be accounted for.
The only way I’ve read about so far is to manually edit the transaction amounts in the Transactions sheet, by changing for example “-6.00” to “=-6.00/2”. But I would prefer for this to happen automatically based on a Tag. I could remove the Tag for transactions that are not shared.
But I don’t think this would work in Balances, because those numbers are overwritten.
Hi @christian.kohler - I’ve got two thoughts that you might consider.
Autocat is pretty powerful in that if you turn on the “Advanced Rule Builder” you can override pretty much any column. You can even use it to edit the Amount column. Unfortunately you can’t use it to programmatically edit the amount column, meaning you can only provide it a fixed number and not something like =Amount/2. I just thought I’d throw it out there in case any of your recurring shared expenses are something fixed like say rent or a mortgage. In that case you could potentially build your Autocat rule to straight away enter half the amount of the known fixed value. I’m guessing that this one might be a very limited use case.
Tagging the line items sounds like a good approach. Along that line, you could add a column right next to the Amount column with the following formula pasted into the cell in Row 1. Change K to the letter of your Tags column and M to the letter of your Amount column. If you want to do more than 500 rows in the range, you could omit the 500 or change it. Since I usually know that I’m working just with the most recent rows, I try to save the processing time by not running the calculation the whole way to the bottom.
This formula would label the column as “Adj Amt” and automatically calculate half of the Amount value only in rows where it finds your Tag called “Shared”. Then you could come along and do a quick copy paste where the tag matches. It would be a bit faster than manually entering formulas in the Amount column and you could run a filter on it to do a bulk edit. The downside is that as soon as you copy paste, it recalculates half of the new Amount value. So you really need a way to “call off the calculation” so to speak and tell it you’ve made your adjustment. For that I might suggest one more column that is a “Reconciled” or “Completed” or “Reviewed” column. I personally have one of these that I flag with an “x” that supports various actions that I take on my rows of data in a similar way. So putting that all together would yield a formula like this where AD column is the “Reviewed” flag column.
Lastly if you wanted to get a bit more fancy and wanted to see a count of how many you need to adjust the amount on, you could put a count along with the column name - this is a nice feature when you want to see at a quick glance how many are there without having to scroll down. It would be like the following formula and you’d make the same Column letter adjustments.
=LET(matches, IFNA(QUERY({K2:K500,AD2:AD500},"select count(Col1) where Col1 = 'Shared' and Col2 = '' label count(Col1) ''",0),0),
{"Adj Amt"&" ("&matches&")";ARRAYFORMULA(IF(AD2:AD500<>"x",IF(K2:K500="Shared",M2:M500/2,""),))})
For the Balances sheet, the data is being pulled from Balance History. Perhaps instead of modifying the Balance History, you just want to display on the Balances tab a 50% value for the selected accounts. I’m sure it’s possible but I’d have to think about it based on what you’re really after there. For instance, I think Columns E and I may be free on the Balance sheet and you could simply do a secondary totaling reflecting the “50% for certain accounts view”.