I haven’t found the need for the full check register functions you have here, but I am constantly challenged with remembering online payments which I have scheduled but which haven’t hit my bank yet. If I’ve forgotten a big one, I might have cash flow issues, so as a reminder I have created a “Register scratch” manual account. When I schedule a bill for future payment, I enter it in Transactions with the expected date. Since the expected dates are future, they are always sorted to the top. The rows are conditionally formatted for high visibility. In the example below, I have 3 payments to the same vendor, but with with different categories. When the actual payment hits the bank, I can easily copy the proper category and note to the bank transaction with the matching amount and delete the scratch row.
Clever, thanks for sharing your solution!
I just updated the template to include the ability to sort Ascending or Descending. Once @randy flips the switch, it should show a new version (1.40) available in the Community Solutions template manager.
Change: Added ability to sort transactions ascending or descending.
Was the Excel version updated?
I was traveling. This is pushed and should be live at version 1.40 shortly.
I removed an unneeded helper column, and updated how sorting is done so it should work better when sorted Descending. The version will be 1.50 when @randy pushes it.
Version 1.50 is live now in the Tiller Community Solutions Add-on. Thanks for the incredible commitment to maintenance and enhancements, @jpfieber.
I have an account that has an apostrophe in its name (e.g. “Someone’s Checking”). This account is reconciled. However, in the “Account Register” (Google Sheets) only one “Opening Balance” transaction shows either in columns B-E or R-V.
it might not be helpful/necessary for others and too much work to explain to me, but I have a question. How would I add another column for ‘Category’ with the ability to choose a category from a dropdown as well? Thanks so much for all your work.
The issue is indeed with building queries when cell content has an apostrophe. To avoid this problem, all single quotes (apostrophes) have to be replaced with two double quotes. In particular, cell R5 must become
=IFERROR(IF(
VLOOKUP(G5,I16:J,2,FALSE)=“Asset”,
QUERY({INDIRECT(L4),INDIRECT(L5),INDIRECT(L6),INDIRECT(L7)}, “select Col1,Col2,Col3 where Col1 >= date “””&TEXT(N11,“yyyy-mm-dd”)&“”" and Col4 MATCHES “”" & G5 & “”" Order by Col1, Col3 DESC Label Col1 “”“”, Col2 “”“”, Col3 “”“”“),
QUERY({INDIRECT(L4),INDIRECT(L5),INDIRECT(L6),INDIRECT(L7)}, “select Col1,Col2,Col3*-1 where Col1 >= date “””&TEXT(N11,“yyyy-mm-dd”)&”“” and Col4 MATCHES “”" & G5 & “”" Order by Col1, Col3 DESC Label Col1 “”“”, Col2 “”“”, Col3*-1 “”“”“)
),”")
Please consider updating the master template.
Thanks for pointing out this issue. I updated the template as you suggested, the version is now 1.60 and should be available as soon as @randy flips the switch.
Change: Updated to handle apostrophes in account names
I don’t think filtering by category would be a good fit for this tool since it’s goal is to display a running balance for an account. It’s certainly possible to alter the query to filter by a category drop down menu, but I think making the Balance column work and make sense would be difficult.
The “switch” has been flipped. Cheers!
I just had this thought myself to view the category and was able to successfully add in the category column but just for viewing purposes. As @jpfieber mentioned,
I did however see it useful to see how it was categorized on it though. There are a couple of steps involved in adding this, but the template with changes is able to automatically show the category assigned for these transactions now. This has helped me catch mis-categorized transactions reviewing them in this format on this template.
Downside is I haven’t updated the sheet and am on version 1.30 and I’m not very good with the queries, it took me about an hour to read the formula and make the changes to make it work. I wish now I had realized I had not already completed this step as I will need to make the changes once more, but don’t feel up to it tonight.
If you’re interested in the steps it takes let me know and I’ll update my template to the most recent version, make the changes once again, and document for you.
Also, Sorry @jpfieber I never got back with you but the sort by worked perfectly. Thank you for the template and all of your help.
Thanks for the template!
Similar to the issue with apostrophes, I have account names with brackets in them, which need to be escaped for the QUERY to work correctly. I adjusted the formula to the following:
=IFERROR(IF(
VLOOKUP(G5,I16:J,2,FALSE)="Asset",
QUERY({INDIRECT(L4),INDIRECT(L5),INDIRECT(L6),INDIRECT(L7)}, "select Col1,Col2,Col3 where Col1 >= date """&TEXT(N11,"yyyy-mm-dd")&""" and Col4 MATCHES """ & REGEXREPLACE(G5, "(\(|\))", "\\$1") & """ Order by Col1, Col3 DESC Label Col1 """", Col2 """", Col3 """""),
QUERY({INDIRECT(L4),INDIRECT(L5),INDIRECT(L6),INDIRECT(L7)}, "select Col1,Col2,Col3*-1 where Col1 >= date """&TEXT(N11,"yyyy-mm-dd")&""" and Col4 MATCHES """ & REGEXREPLACE(G5, "(\(|\))", "\\$1") & """ Order by Col1, Col3 DESC Label Col1 """", Col2 """", Col3*-1 """"")
),"")
The only change is changing both occurrences of G5 to REGEXREPLACE(G5, "(\(|\))", "\\$1").
As I’m just working through porting our finances to Tiller (and using this template to help make sure that our historical data is healthy) I’ve encountered some instances of old transactions that have an empty amount. This causes the Account Register to produce odd results, basically starting the balance back at 0 every time a blank is encountered. I probably want to clean up those old lines, but also felt like the register should handle them. Over in U3 I tweaked the formula thusly:
={"Balance";
SCAN(
0,T4:T,
LAMBDA(
runningTotal,currentValue,
runningTotal + if(currentValue ="",0,currentValue)
)
)
}
The result is that it treats empty amounts as 0 and continues on with the running total. It seems the intent of the if statement in there right now is to prevent the sum from continuing on to the bottom of the sheet, but that seems to only have a visual impact, and only in this hidden section. Conditional formatting can be used to hide those, if desired.
2023-08-25
I am getting an error message when I try to add the ACCOUNT REGISTER or ACCOUNT RECONCILIATION templates. REGISTER is dependent on RECONCILIATION but I can’t add either one in any order without getting the error message.
Tiller on Google Sheets using Safari v 17.0
Unable to Add Sheet
The add-on could add the requested sheet
or its dependencies.
The process failed with message: "Cannot
call method “copyTo” of undefined."
Resolve the issue and then try again.
Any suggestions?
Thanks in advance.
Try again. I think the master template that holds this template was having an issue that has since been fixed.
Any suggestions as to why the “account register” sheet is not populating?
Expand the helper data by clicking the ‘+’ above column H. Do you see a list of accounts starting in I16? If not, check if K11-K13 have column letters in them, this is where the template finds the appropriate columns on your “Accounts” sheet.