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 Labs 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. Click Add Transaction to write the transaction to your Transactions sheet.

Notes

  • The Reconcile with Feeds dropdown 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.
  • Manually-added transactions for unlinked accounts do not update the manually tracked account balances on the Accounts/Balance History sheets.
  • 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 Labs 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.

1 Like

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