Docs: Adding & Reconciling Manual Transactions

Why Use Manual Transactions?

Tiller feeds will keep your financial data current for linked accounts by pulling in transactions and balances into your Google Sheet. Sometimes, however, you may need to manually add a transaction before it has posted at your bank.

For example, you might want to capture a check paid to a vendor, an invoice submitted to a client, or a bill that you paid electronically. Logging a transaction when you make it (rather than waiting for it to post at the bank), records a pending change in asset value to better manage cash flow and avoid overdrafts.

Manual transactions are helpful when:

  • Writing checks that may not post for days or weeks
  • Tracking invoices that may not be paid for days or weeks
  • Working with unlinked accounts

What Is Manual Transaction Reconciliation?

When, for example, an outstanding check is manually written into your Transactions sheet, the expense will be duplicated when the check posts and Tiller Money Feeds adds a new transaction row for the linked account. (At this point, the expense is double counted in the Transactions sheet.)

Reconciliation is the process of recognizing matched transactions and consolidating the two transactions into a single income or expense.

(Note that reconciliation is not required for manual transactions in unlinked accounts.)

Adding Manual Transactions

When creating manual transactions, the add-transaction sidebar workflow is designed to simplify populating all of the Transactions-sheet fields. It also allows you to flag manual transactions for reconciliation (when a Tiller Money Feeds transaction arrives later).

How to Use It

  1. Open the Tiller Community Solutions add-on.
  2. Click on the Tools menu item in the main sidebar.
  3. Click on the Manual Transactions menu item in the Tools sidebar.
  4. Click on Add Manual Transaction in the Manual Transactions sidebar, which will populate with account and category information from your spreadsheet.
  5. Fill in the sidebar fields for your new transaction.
  6. When updating non-feeds/manual accounts with new transactions, an Update balance in Balance History checkbox will display. When checked, this option will add not only the new manual transaction, but also a balance entry to Balance History that updates the last balance by the incoming manual transaction’s amount.
  7. Click Add Transaction to write the transaction to your Transactions sheet.

Notes

  • The Reconcile with Feeds dropdown (visible for Tiller Money Feeds accounts only) allows you to choose to flag a manual transaction for later reconciliation. Set the dropdown to “Flag for Reconciliation” if you want to later reconcile the match when Tiller brings it in automatically. Alternatively, set the dropdown to “Don’t Flag for Reconciliation” if you’re adding a transaction manually for an unlinked account.
  • If you would like to add more than one transaction, click the Add Transaction + 1 button, which will add the transaction then refresh the sidebar for subsequent manual transactions to be added.
  • Tiller’s architecture recognizes manual transactions two ways… First, a unique id is created for manual transactions in the Transactions sheet’s Transaction ID column with the prefix manual:. Second, information about the manual transaction is stored in the Transactions sheet’s Metadata column (in JSON format) including whether the transaction is flagged for reconciliation and if any possible reconciliation matches have been identified as non-matches.

Reconciling Manual Transactions

If you’ve manually added a transaction flagged for reconciliation (e.g. a check you wrote to a vendor), you’ll want to “reconcile” the transaction with the match when the transaction posts and Tiller Money Feeds brings it in automatically.

Run the workflow below to check for and review possible transaction matches and to reconcile them when matches are manually verified.

How to Search for Possible Matches

  1. Open the Tiller Community Solutions add-on.
  2. Click on the Tools menu item in the main sidebar.
  3. Click on the Manual Transactions menu item in the Tools sidebar.
  4. Click on Reconcile Wizard in the Manual Transactions sidebar. The Reconcile Wizard will review your Transactions sheet looking for outstanding manual transactions flagged for reconciliation.
  5. The Reconcile Wizard will show a count of pending unreconciled transactions and a “Date Bracketing” slider. The Reconcile Wizard will look for exact dollar matches from Tiller Money Feeds within the bracketed date range of the unreconciled manual transaction. Set the date bracketing slider and click Reconcile Transactions to scan the entire Transactions sheet for matches. (A narrower date bracket range is more likely to miss transactions. A broader date bracket range is more likely to identify false positives.)
  6. The Reconcile Wizard match review pane will appear in the sidebar. See next section.

Understanding the Match Review Pane

The Reconcile Wizard match review pane is comprised of several elements:

  • Group selection dropdown
  • Group navigation arrows
  • Manual transaction primary fields
  • Manual transaction details fields
  • Possible match(es) list
  • Ignored transaction(s) list

Group Selection Dropdown

The group selection dropdown filters the outstanding manual transactions by their status:

  • All Unreconciled
  • Unreconciled With Possible Matches
  • Unreconciled Without Matches

Each option will show the count of manual transactions in that group.

image

Group Navigation Arrows

The group navigation arrows traverse the transactions (forward & backward) within the selected group.

Manual Transaction Primary Fields

The primary fields of the active manual transaction (within the selected group) are shown below the group selection dropdown and the navigation arrows. The fields Date, Amount, Description and Note (if populated) are shown.

Manual Transaction Details Fields

Additional fields such as Account and Institution are shown when the Details container is opened.

image

Possible Match(es) List

Transaction details for possible matches to the active manual transaction are shown in this section in green containers. Each possible match can be verified with the blue Match button or ignored with the gray Ignore button.

image
Ignoring a transaction a non-matching transaction not required, but the action will prevent it from being displayed in the future as a possible match.

Ignored Transaction(s) List

When possible matches are ignored, they will drop into the ignored transaction list. If a transaction is mistakenly ignored, you can click Unignore in this section to return it to the possible matches section.

image

How to Reconcile Matches in the Match Review Pane

The process of reconciling matches is straightforward:

  1. Ensure “Unreconciled With Possible Matches” is selected in the group selection dropdown in the match review pane.
  2. Step through each manual transaction in the possible matches list, reviewing the possible matches. For each possible match, click either the Match button to verify a match or the Ignore button to mark the possible match as a non-match.
  3. When you have completed your review of all manual transactions (with possible matches), click the Reconcile Transaction(s) button to complete the process.

Once the Reconcile Transaction(s) button is clicked, the workflow scripts will merge reconciled/matched transactions and will mark any ignored transactions as non matches.

Reconciled transactions will not be updated in your Transactions sheet until you click the blue Reconcile Transactions button at the bottom of the sidebar. (In other words, they are not reconciled when you click the Match button.) If you cancel the reconciliation process before clicking the Reconcile Transactions button, your changes will be lost.

Webinar replay

Troubleshooting

If you have a question or need help first search the community to see if someone has already asked and if not click here to quickly post a question about this tool in the Google Sheets category.

Be sure to customize the title of your post with keywords about the issue or question so others can easily find the Q&A in search.

3 Likes

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
  • The Transaction ID must be non-blank

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