🏆 Scheduled Transactions template: Project future account balances; calculate credit card payments; auto-reconcile transactions

[rhowell]

Overview

This sheet schedules future transactions, including calculated credit card payments, and projects the balances of one or more accounts. It’s like a check register on steroids.

Features

• Generates recurring transactions: Enter a transaction once and set it to generate future copies.
• Calculates credit card payments: Shows upcoming credit card payment amounts and current activity.
• Reconciles automatically: When Tiller downloads an actual transaction that matches a scheduled transaction, the scheduled transaction is marked as reconciled, removed from the balance calculation, and hyperlinked to the actual transaction.
• Based on a “check register” model, rather than an abstracted list of recurring transactions: Maintain a record of past transactions for as long as you want them. Change the details of one copy of a recurring transaction without changing all the other copies. Track one-time transactions, such as checks written, in the same list as recurring ones, such as bills and paychecks.
• Sortable: Scheduled transactions can be sorted, filtered, moved, copied, and pasted. Rows can be inserted and deleted. Columns can be added and moved.
• One sheet: No need to toggle between sheets to update or reconcile.
• Charts: Review balance projections at a glance. (Similar to Quicken’s Projected Balances graph.)
• No scripts.

I built the sheet because I needed it. My wife and I undertook a large home project, which we paid for from our checking account. The funds came from other accounts so we needed a way to know when to transfer money into the checking account to cover upcoming expenses. I started with a simple table that pulled our checking account balance and added it to income and expense transactions that we entered below. This worked, but many of our expenses recur periodically and it became tedious to re-enter them each month. I considered using a few Tiller solutions, e.g., the Projected Balances and Bill Payment Tracker sheets, but they didn’t let me account for one-off transactions such as the checks we wrote. I wanted to maintain the simplicity of our table, with its free-entry transactions, while generating copies of recurring transactions for the next month.

After releasing the initial version, I incorporated a community request to automatically calculate credit card payments. I also wanted to reduce the time I spent manually reconciling transactions, so I built in automatic reconciliation. I also added the ability to track multiple asset accounts.

Transaction entry table

Projected balance charts

Combined list of entered and generated transactions

Installation

Requirements

Your Tiller spreadsheet must contain these core sheets: Balance History (hidden by default, no need to unhide), Accounts, and Transactions. They supply
– the current balances of the accounts you’re tracking,
– the hidden status of accounts, and
– the transaction data for automatic reconciliation.

Copy to your Tiller spreadsheet

• Open my shared spreadsheet: SHARED Tiller Foundation Template - Google Sheets
• At the bottom of the window, right-click on the Scheduled Transactions tab and select Copy to, then Existing spreadsheet.
• Select your Tiller spreadsheet in the window that appears and click Insert.
• The sheet will appear in your spreadsheet as Copy of Scheduled Transactions. You can rename it.

To uninstall

• Right-click on the sheet’s tab at the bottom of the window and select Delete.

Setup

1. Delete sample data

The sheet you copied contains sample transactions in the light green cells below columns A through J. You can delete them. (You can insert, delete, move, sort, and filter rows without breaking the sheet. Do not insert rows above row 1. When copying and pasting data – on this or other sheets – it’s best to use Paste special - Values only to avoid formatting problems.)

2. Enter the URL (web address) of your Transactions sheet

This step is required to create hyperlinks to reconciled transactions.

• View your Transactions tab.
• Select all the text in the address bar and copy it. It will look similar to this:

https://docs.google.com/spreadsheets/d/1dHwvO0RibgFwhZ-7Xn64yzgfpj0RGHSyQeXBHYVGiBo/edit#gid=1256593101

• Go back to the Scheduled Transactions tab and right-click on cell BC1. Select Paste special, then Values only.
• Hide the setup area by clicking the minus (-) box above column BF:
  

Usage

Enter and change data in light green cells only.

Enter scheduled transactions

Columns A through J are where you enter transactions. To create a simple, one-off transaction:

• Use the drop-down in the Asset Account column to select the account that the transaction will be debited from or credited to. (See the Options section, below, if the account you want doesn’t appear in the drop-down.)
• Enter the date the transaction is scheduled to occur in the Transaction Date column.
• Enter a description of the transaction in the Description column. This can be any text you like. (But see below under Calculate credit card payments if you’re entering a credit card payment and want the sheet to calculate the payment amount.)
• Optionally enter notes in the Notes column.
• Enter the transaction amount in the Actual Amount column. Credits should be positive, debits negative.

Look at column T, the Asset Account Projected Balance column, in the same row: It will show the current balance of the account you selected plus the amount of the transaction you just entered.

Set transactions to recur

To generate future copies of a transaction:

• Enter a number value in the Recurs Every … column and select a period from the … Day(s) or Month(s) column. This sets how often the transaction will recur. (See below, under Limitations and
  known issues, for transactions that occur twice a period.)
• Enter a date in the Starts Recurring column. This is the date that will be used to calculate the dates of future copies of the transaction. It doesn’t need to be the same as the Transaction Date, so, if a transaction doesn’t occur on the date it’s supposed to, you can use the Transaction Date to reflect the actual date it occurs, without affecting future copies.
• Enter a value in the Recurring Amount column. This is the value that will be used for future copies of the transaction. It doesn’t need to be the same value you entered in the Actual Amount column. As an example, consider a monthly electric bill: you might set the Recurring Amount to the average amount you spend per month, and, when you receive the bill, set the Actual Amount to the amount due.
• The transaction’s description and recurring values will appear in bold if it’s correctly set to generate future copies.

Chart and list future transactions

Schedule a few more transactions, then look at the charts to the right, under the sheet title. The left chart shows the minimum daily balance for every account you entered in the Asset Account column. The right chart shows the balance of the account selected in cell BL1 after each scheduled transaction. Enter the number of months you’d like to chart in cell BJ1.

The table to the right of the charts shows a list of all scheduled transactions for the number of months entered in BJ1. The checkboxes in cells BN1 and BO1 filter the transactions. Checking the box in BN1 will show only transactions from the account selected in BL1. Checking the box in BO1 will show only generated transactions, hiding transactions you’ve manually entered. This filter comes in handy when setting up the next month as explained in the Workflow section, below.

Calculate credit card payments

The sheet can automatically calculate credit card payment amounts for any credit card accounts you’ve linked to Tiller. To calculate a credit card payment:

• Enter the credit card account name in the Description column exactly as it appears in the dropdowns in column A (the first Account column) on the Accounts sheet. It usually looks like the account name followed by the partially-redacted account number and an identifier in parentheses, e.g., Inspirus Savings - xxxx0001 (F538).
• The transaction’s description will appear in italics if it matches a liability account.
• In the Starts Recurring column, enter the date the credit card payment is due as shown on the statement.
• In the Credit Card Closing Date column, enter the closing date as shown on the same statement. Note that the closing date may vary from month to month; update it when you copy generated transactions to the entry area as detailed in the Workflow section, below.
• The amount due will appear in the Credit Card Pmt. Amount column and will reduce the projected account balance. Note that if credits, such as returns or redeemed points, are applied after the closing date the calculated payment amount will be higher than the eventual amount you owe.
• The sum of transactions made after the current statement closing date but before the next closing date will appear in the Credit Card Current Activity column. This column is shown for information; the amount doesn’t affect any other calculations. Note that this amount may be lower than the amount due on the next statement, once it closes, if credits were applied to the account.

Reconcile transactions

Transactions are automatically marked reconciled with a checkmark in column Q and removed from the balance calculation when Tiller downloads a matching transaction to the Transactions sheet.

The Link to Reconciled Transaction column shows the matching transaction. Clicking on it will take you to that transaction on the Transactions sheet.

The matching algorithm isn’t perfect. If the wrong match is shown, use the Override column to select the correct match or to indicate that the transaction isn’t reconciled. If the correct match still doesn’t appear, you can enter one or more Transaction IDs from the Transactions sheet, separated by commas, in the Override column. Ignore the red triangle error; the transaction matching the entered ID will still appear in the Link to Reconciled Transaction column and the hyperlink will take you to the transaction.

You can quickly reconcile a transaction and remove it from the balance calculation by checking the box in column P instead of using the override column. Doing so will remove the hyperlinked transaction in the Link to Reconciled Transaction column.

Any transaction whose Actual Amount is equal to $0.00 is marked reconciled.

Workflow

Daily – Weekly

Double-check that transactions have been reconciled correctly. (Note that the balance available to Tiller usually lags the actual account balance; reconcile accordingly. A transaction that has hit your bank hasn’t necessarily hit Tiller.)

Refine the list of transactions and update Transaction Dates and Actual Amounts.

Enter new rows to capture future changes to recurring transactions, such as a pay raise or final car payment:

• Set the Recurring Date to the effective date of the change, or, for transactions that are ending, delete the Recurring Date and recurring period data.
• Update the Recurring Amount.

Monthly

Move generated transactions for the upcoming month to the data entry table:

• Check the box in BO1 so that the table to the right of the charts shows generated transactions only.
• Select and copy the generated data from the white columns only for the next month’s transactions.
• Paste the data at the bottom of the data entry table. Use Paste special - Values only to preserve formatting.
• Go through the copied transactions, checking that they’re still needed, updating Transaction Dates, and entering Actual Amounts.

Prune the rows of reconciled transactions, if desired. Use caution – rows that appear in bold are still being used to generate future transactions.

Permissions

Free for personal use. If you share this sheet, here or elsewhere, modified or not, please link back to this post.

Options

Use the plus and minus boxes above the column letters to show and hide column groups.

The checkbox in cell BE1 controls the filtering of data queried from the Accounts sheet. If an account you want to track doesn’t appear in the drop-downs in the Asset Account column, uncheck the box. The drop-downs will now list all accounts classed as Asset rather than listing only checking and savings accounts.

Additional advanced options are discussed in the Notes section, below.

Troubleshooting

• If you’ve made changes to a transaction but they’re not reflected as expected in the generated copies of it, you may not have edited the most recent (by Recurring Date) transaction with the same Description. If there are multiple copies of a transaction with the same Description and the same recurring settings, only the copy that appears lowest in the main table will be used to generate future copies.

• If multiple copies of the same transaction are being generated, check that all copies of it in the main table have the same Description and recurring frequency.

• See above, under Options, if the account you want to use doesn’t appear in the drop-down on the balance line.

• To end a recurring transaction, you’ll need to maintain a copy of it with either a $0.00 Recurring Amount or the recurring data (in the Recurs Every …, … Day(s) or Month(s), and Recurring Date columns) deleted until you delete all copies of it from the data entry (left) table.

Notes

Limitations and known issues

• Transactions can only be set to recur by multiples of days or months. This means that bimonthly transactions must be entered as two separate transactions with different Description values, such as John paycheck 1 and John paycheck 2, set to recur on different days of the month. The lack of additional options is intentional. It reduces the complexity of parsing additional recurring periods, increases the readability of the formulas, and makes the expected results very clear at the small cost of requiring a few more transaction rows.

• There’s no option to set transactions to recur on the last day of the month, so Starts Recurring dates entered as the 29th, 30th, or 31st will generate copies with the date changed to the 28th. You’ll need to edit the Actual Date after copying the generated transaction to the main table.

• Erroneous $0 points may appear at the right end of the chart:
  If data doesn’t exist in one or more series in a chart’s range, Sheets won’t preserve the settings applied to each series, but rather will apply them only to the series that do have data, in the order they appear. In order to keep the colors and shapes in the chart assigned to the same series (“Projected Balance (+)”, “Projected Balance (-)”, etc.) as the data changes, I’ve had to create a $0.0001 balance at the end of any series that would otherwise have no data.

• There’s no way that I can figure out to definitively link data from the Balance History sheet to data from the Transactions sheet. Often, the downloaded transaction data lags the downloaded balance data by a day or three. This means that you’ll need to double-check (and possibly override) the reconciled status of recent (within the last few days) scheduled transactions to ensure that they’re applied correctly. If you find that the lag creates a consistent problem for reconciling your account(s), the sheet can be set to use the account balance from the date of the most recent transaction in the account rather than using the most recent balance, but it’s not foolproof. See below, under Account balances, for instructions.

• If you accidentally edit formulas or otherwise manage to break the sheet, you can reinstall it and copy over your data from cells A – J. I’m happy to help troubleshoot and welcome bug reports: just reply to this post.

Recurring transactions

The sheet will generate future copies of any transaction row in the data entry table table where
– the three recurring columns (Recurs Every …, … Day(s) or Month(s), and Starts Recurring) aren’t blank,
– the sum of Recurs Every … and Starts Recurring is within the period set in cell BJ1, and
– the sum of Recurs Every … and Starts Recurring isn’t on or after the Starts Recurring date of another transaction in the main table with the same Description.

Future copies will be generated until
– the sum of Recurs Every … and Starts Recurring exceeds the period set in cell BJ1, or
– the sum of Recurs Every … and Starts Recurring is on or after the Starts Recurring date of another transaction in the data entry table with the same Description.

Reconciling transactions

Scheduled transactions are automatically reconciled with downloaded transactions from the Transactions sheet by matching
– the Transaction Date of the scheduled transaction with the Date of the downloaded transaction and
– the Actual Amount of the scheduled transaction with the Amount of the downloaded transaction.

To make reconciliation more robust, the matching isn’t exact. The values in hidden cells AE1 and AF1 set the parameters for filtering downloaded transactions. From the filtered list, the sheet selects the transaction whose Date is closest to the scheduled transaction’s Transaction Date. If you’re finding a lot reconciliation errors, you can try adjusting the values in the hidden cells. (The default values are 4 days and 1.5%.)

The drop-down lists of downloaded transactions in the Override column are created similarly. The parameters for filtering are set in hidden cells AL1 and AM1. (The default values are 31 days and 15%.)

Account balances

Columns R & S show the date and amount of the downloaded balance used to calculate projected balances. By default, the sheet uses the latest available account balance (from the Balance History sheet). As noted above, there’s no way to definitively link downloaded transactions with downloaded balances. Downloaded balances are often several days more current than downloaded transactions. If you find that the lag from balances to transactions creates a consistent problem for reconciling your account, the sheet can be set to use the account balance from the date of the most recent transaction in the account rather than using the most recent balance. It’s not foolproof, but it may be worth a try: set hidden cell EA1 to a value greater than 0. (The default, 0, sets the sheet to use the latest account balances available.)

(Lack of) brittleness

This sheet is designed to be as robust as possible because I find that the freedom to insert, delete, move, and sort rows makes it easier to capture transactions as I think of them and keep the sheet up to date. You should be able to

• delete any row except the header row,
• move any row except the header row,
• insert row(s) anywhere except above the header row,
• copy and paste (values only)* in the editable (green) areas,
• sort rows, and
• move and insert columns
  without breaking the sheet. Please let me know if you do break it so I can improve it.

*Note that cut, copy, and paste operations can quickly mess up formatting (especially conditional formatting) and data validation, which won’t break the sheet but will make it less useable. You’ll want to avoid cut operations – use copy instead and then delete the orginal data – and use Paste special - Values only to avoid formatting problems. This caveat applies to any spreadsheet, not just this one, because of the way formatting is implemented.

Most of the sheet’s flexibility is achieved by putting every formula in the header row. As long as the first row isn’t changed, the formulas are preserved.

There’s an annoying Sheets behavior that, if a row is moved from a position covered by a result array to a position below (or, presumably, above, but I haven’t tested) the result array, it brings with it the result row from its old position. To ensure that rows can be moved freely, we need to prevent the accumulation of these orphaned results from blocking the automatic expansion of arrays. My solution is to stack empty arrays below all result arrays that don’t fill to the bottom of the sheet (e.g., below query results):

=let(this_result_,
query(...),
{this_result_; makearray(rows(A:A)-rows(this_result_), columns(this_result_), lambda(r_, c_, iferror(1/0)) )})

Array formulas with input that includes one or more entire columns (e.g., A:A) should already fill to the bottom of the sheet and don’t need the padding.

Dynamic references

I wish the QUERY() function lived up to the promise of its documentation and accepted references to header names. As of April, 2024, it doesn’t. There’s a clever workaround that wraps QUERY() in a LAMBDA() function to replace header name inputs with Col1-type references while remaining reasonably readable: google sheets - Can I use column headers in a =QUERY? - Web Applications Stack Exchange
This eliminates the helper table and INDIRECT()s otherwise needed to create dynamic references to the Tiller Core sheets and, I think, speeds things up a bit.

Changelog

1.01 (2024-06-14)

• Removed $0.00 values from column M in otherwise empty rows.

1.0 (2024-05-17)

• Initial release.
• Column locations and names adjusted.
• Updated documentation.

0.9 - 0.96

• Beta releases.

This sheet was previously named Short Term Cash Flow. It served the same purpose, but didn’t automate transaction reconciliation, calculate credit card payments, or project balances for multiple accounts.

[peter]

Planning for future cash flows! This addresses a really important riddle, especially for an expensive endeavor like home projects (my wife and I went through this ourselves a couple years ago). Thanks for building, sharing, and documenting this @rhowell !

[randy]

Hey @rhowell!

Thanks for documenting and sharing your short-term cashflow template. Forecasting balances based on recurring transactions is quite a riddle. While many people throw up their hands, it does seem that some brave builders have optimized workflows that work well for them particularly once the groundwork is laid and the forecast transactions can be refined with time to improve accuracy.

Your sheet allows a user to forecast cashflow and works particularly well for those in the midst of a home project and using one account for the transactions (like a checking account). It uses Tiller Money feeds to maintain the latest balance from the funding account and then projects from there as forecast transactions are reconciled manually. When the forecast and reconciled transactions are maintained, the chart is a really cool tool to see where your balance is heading. The tooling to build the recurring transaction rows is well implemented— much like what we’ve seen in some other solution shares.

It would be pretty cool if the tool integrated more with the Tiller Feeds transactions data— both to pull in recurring transactions as the forecast is constructed and also to help reconcile transactions as they land in the Transactions sheet.

We are grateful for your share as it inspires and provides a launching pad for others facing similar riddles. Through our Builder Rewards Program, we offer cash awards for template submissions. Your template is a great starting point for someone who wants to project their cash flow into the future during a project and who is willing to do the manual work to keep up with their project. Tiller is excited to award you $75.

[rhowell]

Thanks @randy ! I’ve been thinking about ways to incorporate the Transactions sheet, but haven’t decided how or if to proceed.

I’ve uploaded version 1.1 to my shared spreadsheet, which

• makes the transaction generation more robust (will fill in missing transactions),
• allows the user to generate and chart transactions more than one month at a time, and
• removes the option to calculate balance by position.

For now, both versions are available from my shared sheet. Once I update the original post, I’ll remove the older version.

[randy]

I love that you’re already thinking about enhancements, @rhowell. No pressure at all to implement. Just an idea if you wanted to go further with what you’ve built.

Thanks again for sharing your workflow.

[rhowell]

VERSION 1.1

I’ve updated the sheet to generate and chart transactions for a user-entered number of months into the future. It also will generate transactions to fill in between two entered transactions. This allows the user to capture a future change to a recurring transaction and have the sheet generate the recurring transactions both from now until that change and from that change on. For example, to capture a raise, you enter a new transaction with the same name as your other paycheck transactions, set the recurring date to the first pay date that will reflect the raise, and set the amount to your new pay.

The chart reflects balances obtained from both entered and generated transactions, and differentiates between them. (I think these changes make the chart similar to Quicken’s Projected Balances graph, though I haven’t used it.)

I’ve made a few more minor changes as well; they’re reflected in the changelog.

[sskennel]

I’ve been experimenting with this template today and I really like it. It does almost everything I want. It will be replacing a more labor-intensive solution that I created for my own use a few years ago.

Bug report

I think I’ve found a bug. Here are the steps I used to reproduce it.

1. Copied your shared sheet to my Tiller spreadsheet.
2. On the BALANCE line, changed the dropdown value to my checking account.
3. Selected and copied all of the generated transactions (cells AA2 to AH58).
4. Pasted (values only) at cell A57.

This removes all of the original generated data rows, but leaves two “Sequence Error” entries in cells AA2:AA3 and AF2:AF3 and some random-looking data in other cells.

I originally triggered this bug while experimenting with my own transaction data, but was able to boil it down to this sequence of steps.

Enhancement request

I typically pay off the total balance due on each of my credit card statements each month. I’d like the Short Term Cash Flow (“STCF”) sheet to more accurately track the effect of my credit card balances
on the projected balance of my checking account. I think this can be done without having to grovel through data in the Transactions sheet.

Proposal: add a Closing Date column to the STCF sheet. When a cell in this column contains a date, the Notes column in that row gains a dropdown containing the credit card accounts in the Tiller Accounts
sheet. Selecting a credit card account in the Notes column causes the most recent Balance column value for this account in the Balance History sheet that chronologically precedes or equals the closing date to be copied to the Actual Amount column in the STCF sheet.

As Tiller adds Balance History entries, the most recent actual amount for the credit card account is automatically updated in the STCF sheet. Once the closing date passes, the Actual Amount stops updating and should now equal the credit card statement amount due. The Recurring Date for the row indicates the date this amount is deducted from the checking account.

When a new row is generated and pasted in for this credit card account, its Closing Date column is incremented by a month and its Actual Amount column is once again filled with the most recent Balance column value for this account in the Balance History sheet that chronologically precedes or equals the new Closing Date, minus the Amount in the preceding row for this credit card account.

Once the Recurring Date of the preceding row passes, we can assume that more recent Balance History entries for this credit card account are net of the payment on the last statement, so the Actual Amount for the latest row no longer needs to subtract the Actual Amount in the preceding row.

A user who doesn’t wish to track credit card balances in this way can delete the Closing Date column, hide it, or ignore it.

I hope this is both understandable and feasible to implement.

[rhowell]

Hey @sskennel ,

Thank you for the feedback! I’ll look into the bug and post a fix asap.

As for the enhancement request, that’s an interesting idea. Let me play around with it to gauge what would be required and it’s effect on the legibility of the sheet.

[rhowell]

I think I understand what’s happening. When you copy and paste all the generated transactions, there’s nothing left for the sheet to generate, so you see the sequence error. I’ll update with a more descriptive error message.

If you have a chance, would you confirm that if you either
(1) copy and paste fewer than all the generated transactions, or
(2) increase the number of months in cell Z1,
there are no errors?

[sskennel]

Thanks for looking into the error. I did two experiments:

1. I copied all but the last row of the generated data (2/28/2024 cable bill) and pasted the copied rows (values only) at A57. The 2/28/2024 cable bill remained in the generated data area, followed by the two sequence error rows.

2. I increased the months value in cell Z1 to 4, then copied all of the generated data and pasted it to cell A57 (values only). All of the generated rows were removed, and a row was added containing only 03/29/2024 in the two Generated Date columns, a Generated Estimated Amount of $2,000, and a Gen. Reconciled cell set to FALSE. There is no Generated Description.

I don’t think your hypothesis that emptying the generated data area always leads to the error is correct. In a STCF sheet I’ve populated with my personal data, I copied all of the generated rows to columns A:I and was left with “No transactions have a Recurring Date plus Recurs Every value that falls between 11/1/2023 and 2/29/2024.” in cell AB2, which seems exactly correct. There’s something more complicated going on.

[rhowell]

Thanks for looking further into this. I’ll let you know what I discover.

[rhowell]

@sskennel You actually found two bugs and I caught a third while investigating the two you found. Thanks very much for the testing you did! I’ve uploaded an updated copy of the sheet to my shared drive – it’s called Short Term Cash Flow 1.11. I’ll leave it there for a day or two to ensure I’ve solved the problem and haven’t introduced any other bugs, and then I’ll make it the only available version.

• One bug was a result of careless use of the edate() function when generating dates for recurs-by-month(s) transactions. A comparison rejected transactions set to recur on days 29, 30, or 31 when the number of days in the month decreased (such as in February). I’ve updated the sheet to force all recurs-by-month transactions with recurring days > 28 to recur on day 28, which I erroneously assumed would eventually happen and is what the documentation says should happen.
• The second had to do with the case where there is only one generated transaction. The Description and Day(s) or Month(s) values weren’t getting copied over. When a transaction was generated for only one period type, a result was still generated for the other period type , but it was filtered out by a query. (E.g., if one recurs-by-month transaction was generated, a recurs-by-day transaction was also generated, but hidden.) The hidden result was mangling the query result because the result returned values (custom errors from previous debugging) of different types than the columns in which they occurred.
• The third was that the sheet wasn’t gracefully handling cases where there are several copies of a recurring transaction in the leftmost table and the user deletes the recurring information (columns D, E, and F) in one of those copies. I think most users would expect that this would stop the transaction from recurring from that point on (unless another copy restarted it), and I updated the logic to reflect this expectation.

[dstruve]

Thanks for this, Im trying to convert my wife from her budget to mine. Its quite painful . its compounded because im such a noob at this. Im hoping this will help us see how much we have to pay after each paycheck(every two weeks) instead of monthly. A month is a long time to wait to see how much you have.

[TBS]

Excellent! Thanks so much.

[aaronfox79]

Just wanted to say this is really awesome and exactly what I was looking for. I’m nearing the end of my Tiller trial and before I found this, I wasn’t sure if I’d continue because I hadn’t found a template that does what I need. Now I’ve found it!

[rhowell]

Glad you’ve found it helpful!

I’m close to releasing a new solution that builds on this template but with the major additions of automatic transaction reconciliation and automatic credit card payment amounts.

[sskennel]

Sorry I missed your reply to my bug report back in November. I really appreciate the bug fixes. I’ve updated my copy of the sheet to 1.12 now and everything seems to be working well. I’m looking forward to the enhancements you mentioned.

— Roger

[rhowell]

@sskennel and others who may be interested:

I’ve re-worked the Short Term Cash Flow template to add

• ability to track multiple accounts
• automatic reconciliation of transactions (complete with links to the Transactions sheet) and
• automated credit card balance calculation.

The changes are substantial enough that I’m renaming the template (to Scheduled Transactions) and re-starting the versioning. Please check it out:

I’ll formally release it when I have time to create documentation. However, the in-cell notes should be good enough to get you up and running. I’d very much appreciate feedback and bug reports.

Some additional info:

• Your Tiller workbook must contain the core Accounts, Balance History, and Transactions sheets.
• Install is the same: open my shared spreadsheet and copy the Scheduled Transactions tab into your Tiller spreadsheet.
• Setup requires you to copy your Transactions sheet’s URL (“https://docs.google.com/spreadsheets...”) and paste it into cell BC1. After you’ve done that, you can click the plus sign above cell BD1 to hide the setup area.
• There’s no longer a balance row. Instead, each scheduled transaction is assigned to an account.
• To clean up the sheet, I’ve collapsed various columns into groups. Click the plus signs above columns M, Q, and T to see the collapsed columns. (You can reorder columns and change the grouping to fit your workflow: Right click on the column header(s), scroll down and select View more column actions, and find the group/ungroup actions.)
• You should be able to copy and paste transaction data from your Short Term Cash Flow sheet and have it work. Just make sure to paste it in the correct columns. (There’s now an Account column to the left and the reconciled column is separated from the rest of the entry data by additional columns.)
• Note that there’s still no way (that I can figure out) to definitively link data from the Balance History sheet to data from the Transactions sheet. This means that you’ll need to double-check (and possibly override) the reconciled status of recent (within the last few days) transactions to ensure that they’re applied correctly. If you find this to be a problem, the sheet can be set to use the account balances as of the date of the most recent transactions rather than the latest account balances available, but it’s not foolproof. (Columns R & S show the date and amount of the balance that the sheet is using.) If you want to use the balances as of the most recent transaction dates, set hidden cell DZ1 to a value greater than 0. (The default, 0, sets the sheet to use the latest account balances available.)
• Check the in-cell notes in cells C1, J1, K1 and L1 to calculate credit card balances.

[sskennel]

Fabulous. I can’t wait to try this!

[sskennel]

Here’s a problem: Asset Account Start Balance in column R1 did not update when Tiller pulled in a fresh balance for my checking account. (The balance did update in my Short Term Cash Flow sheet.)