Adding & Reconciling Manual Transactions

This is great work. Can I create a google form to input the manual transaction?

I think you could, @JWienecke. The trick would be to get the support scripts to add it to the Transactions sheet with all of the fields properly populated (e.g. Account, Account #, Institution, etc), to generate a random Transaction ID (UUID) in the proper format, and then to create a viable Metadata column entry that the Reconcile Wizard could parse.

I think all this is doable if you’re up for a little coding. Let me know if you have questions about any of the fields.

Randy

Ok, is there a requirement of the transition ID format and Metadata data format and requirement?

A few pointers, @JWienecke

First, I generate the Transaction ID column content with the following call:
transactionId = 'manual:'+Utilities.getUuid();
(The important part is the manual: prefix. After that, any random number or string is sufficient.)

Second, you’ll want to put this in the Metadata column:
{"manualTransaction":{"reconcile":true}}
This JSON object will flag it for later reconciliation. Use false if you don’t want to reconcile the manual transaction.

Finally, the account fields (e.g. Account, Account #, Institution and Account ID) need to match between the manual transaction and the subsequent Tiller Money Feeds entry for the reconciliation to be recognized.

Please share your solution with the community if you get it up and running. I’m certain others will be interested— you’ve got a great concept!

Good luck,
Randy

Hi Randy,

I am working on this, you reference the Account ID (Finally, the account fields (e.g. Account, Account #, Institution and Account ID) need to match between the manual transaction and the subsequent Tiller Money Feeds entry for the reconciliation to be recognized.). I can not find Account ID in the transaction sheet. Am I missing something?

Good question, @JWienecke
The Account ID field is optional in the Transactions sheet. If it is present, it must match for reconciliation. If it is absent, it is ignored.

If you’d like to start using Account ID in your Transactions sheet, just insert a new column with this header and Tiller’s feed service will populate in for all future rows.

Randy

I cannot get this to work by adding Account ID field in transactions sheet. I am trying to add manual transactions but cannot figure out Tiller !

As I noted just above in the thread, @waterworkscarwash, the reconcile function should work without the Account ID column in the transaction sheet.

  • Are you having trouble adding or reconciling a manually-added transaction?
  • Is there another transaction in your sheet within the sidebar date range that has an exact amount match and also matches on all available account fields (e.g. Account , Account # , Institution and Account ID ) in the Transactions sheet?
  • Did you set the reconcile flag when you created the manual transaction? (If you did, the Metadata column should say: {"manualTransaction":{"reconcile":true}}).

Hope this helps. Let me know if you’re still stuck.
Randy

Thanks. I just sent up a new sheet and everything seems to be working now. This is the third time I have had to start with fresh sheets in Tiller. I guess I am learning !

Glad to hear it is working, @waterworkscarwash. Sorry you’ve had to do start over so many times.

I love the reconcile feature, and use it regularly - thanks! Unfortunately there is one change from the old version that is problematic for me. Though manual transaction creation allows specification of a check number, and I see it is still recorded in the Transactions sheet, the check number is not shown in the reconcile dialogue, e.g.:
image

My banks use the check number in their transaction descriptions, so it shows in the Possible Matches, e.g.:
image

I use the check number as the quickest and easiest means of matching, so I’d be very grateful if you would add it to the Unreconciled Transaction view, ideally in the top section that is always visible so it won’t be necessary to open the Details section each time. Thanks!

1 Like

I cannot get the reconcile wizard to work. I followed the directions to setup my Transaction ID with the correct entry as well as the Metadata column with the correct entry. When I try to reconcile, I get the message: “Searching for unreconciled transactions in your Transactions sheet…” I left it searching for two hours and it never did anything else. Could you help, please?

Sometime around a month ago the wizard stopped finding matched transactions. I’m not sure what I changed, but no matter what I do, no matches are ever found (even though the account, amount, etc match exactly on my manual transactions with what is downloaded). Yes, I have marked them for reconcile. I’ve also tried uninstalling and reinstalling the Labs Add-on. Any suggestions?

I’m sorry to hear that, @jbalian. Would you be willing to share a sheet with one or two transactions you think should register as matches? To share:

  1. Create a new spreadsheet
  2. Copy the header (row 1) of your Transactions sheet
  3. Copy a few individual rows that should register as matches below
  4. Modify any data that is personal— e.g. change account numbers— (but be careful not to delete or modify data so that (fake) account names don’t match)
  5. Share this sample sheet publicly by clicking the share button in the upper right

My guess is that an account name changed… though it is possible there is an issue with the add-on.

Randy

Hi Randy,

I had deleted all of the unmatched manual transactions (and just kept the downloaded copy in my Tiller sheet), so I don’t have any examples right now. I have a few transactions that SHOULD match within the next few days, so once those download I’ll share a new sheet with the downloaded and unmatched transactions if it still doesn’t work.

Interesting that I did go through an account renaming stage a while back, but I THINK the reconcile wizard was working for a while after that, I honestly don’t remember. And I have made sure that the acct names did match (downloaded vs. manual). If it does have something to do with account renaming, what do you suggest?

Thanks for the help.

Reviewing the code of the matching criteria:

  • Amount must match exactly
  • Date must be within your specified range (set in the sidebar)
  • An exact match on one of these fields: Account ID, Account # or Account

If you changed an account midstream, it’s possible that it would break the match criteria.

Please let me know if it is still non functional on your next attempt.

Randy

Hi again Randy,
Here is a shared sheet with 10 transactions, 5 were manual that I had added, 5 were downloaded this morning. The reconcile wizard was unable to match any of them.
Thanks for the help!
John

Sorry for the slow response, @jscase.
(I’ve been updating some other features in the Tiller Labs add-on and finally had a chance to peek at this issue.)

First, it’s not intuitive but when you see a progress bar for more than 5 minutes it is likely the add-on has crashed. And when the add-on crashes, it cannot clear the progress bar. So, the add-on had probably long-since crashed when you closed it after two hours.

I saw a crash in the logs that may have been responsible for the issue you reported. I just published fix for that issue. Could you try the reconcile workflow again and let me know if it is working now? (It is possible it is yet another issue, but this is worth trying. We can dig deeper if the problem doesn’t resolve with the new build.)

Thanks for your patience,
Randy

Thanks for the recommendation, @ossher.

I just published a new build of the Tiller Labs add-on and added Check Number to the reconcile-wizard fields. Have a look and let me know what you think.

Randy

1 Like

Thanks for sharing these transactions, @jbalian. I can reproduce the problem and I agree these transactions should reconcile. Let me dig into how the code is parsing these transactions and I’ll get back to you with a fix or a suggestion.

Randy