Payee support

[stevenwenxu]

Hi there,

I really love this spreadsheet! It's so advanced and fully featured, I've switched from YNAB to Aspire for about a month now. The only thing I miss from YNAB is payee support. Properly tracking the payee is more than adding a note in the memo field, since you can build a bunch of cool reports with it. I've spent a few hours at it and here's my modification.

Feel free to make a copy of this and inspect.
https://docs.google.com/spreadsheets/d/1cfFQzQf6NYSLnxBPZJARIRsfEgao1GV-qp3DaMk-bYQ/edit?usp=sharing

Here's a quick walkthrough of the changes:

Transactions and 'My Transactions' sheet

In the official version, users are supposed to manually enter transactions in the Transactions sheet. Since there are too many formulas and named ranges that depend on this sheet and its column structure, I created a My Transactions sheet.

The My Transactions sheet follows the column order of YNAB, which is payee, category, memo, outflow, inflow, and status. This is my preference as I have 5+ year muscle memory of YNAB. The old Transactions sheet is automatically populated from My Transactions, it is therefore hidden and not supposed to be touched at all. The purpose of keeping Transactions is to maintain all calculations and named ranges that are defined with it. I've removed all the conditional formatting on the Transactions sheet since it's hidden, this improves the performance a bit.

The PAYEE column on My Transactions is free to edit but also has data validations. I've defined a Payees named range which is the new column AQ on the hidden BackendData sheet. This is defined as a combination of:

1. unique list of all user-entered payees, such as Starbucks, Costco, etc
2. Payment: <credit card account> for each credit card account
3. Transfer: <bank account> for each bank account

Now the memo column can be used for actual memo!

Balances

The Balances sheet has been updated to include payees. This is better because now you can see where your "Account Transfer" transactions are going. Are they credit card payments? Or just to another bank account? Without payees, I'm clueless about these transfers.

Income vs Expense report

I've spent a lot of time on this, since this is the biggest thing I'm missing from YNAB and I use it every month. I just can't live without this!

This is an extension to your Category Reports sheet. With payee support, we can list out all the income sources. And we can calculate total income, total expenses, and net income which is "did I actually save money this month".

New spending report

You know what else you can do with payees? This new report lets you pick a date range and a category, and it will display 4 charts.

1. What credit card / bank account do I typically use for this category? (useful for people with multiple cards with different rewards structure)
2. Where do I actually spend money for this category? (pie chart sorted by $$ spending)
3. Where do I most frequently go to? (chart sorted by number of visits in selected date range)
4. How much on average do I spend at these places each time? (chart sorted by $$ spent per visit)

Net Worth Report and Net Worth Helper

This isn't payee related, but I thought I'd share as well. It doesn't make sense to me to manually enter my liquid assets (bank accounts) every month when the sheet already has the data, so I created a helper sheet to calculate it for me. Personally, in the helper sheet I also use IMPORTRANGE to pull in stuff from my investment tracking sheets, but I have not included those in this example.

A word on migrating from the official sheet

If you're already a long time user of the public version, you might find it very difficult to migrate because you'd have to manually add in payees for all your transactions. With that said, payees are optional, so you can leave those out for old transactions, and only start adding payees for new transactions.

As explained in the "Transactions and 'My Transactions' sheet" section, you should not touch the Transactions sheet at all. So the migration step is:

1. Make a copy of my sample sheet. Set up all the configurations like normal.
2. Copy all the data into Category Transfers.
3. Copy data from your old Transactions sheet column by column into the My Transactions tab. Note that "column by column" is important since the columns are changed. Practically this means pasting all the dates, then account, then category, memo, etc with the "Paste value only" option.
4. Use this new sheet going forward.

[stevenwenxu]

@mattalco it looks like you aren't a contributor or watcher for this repo, tagging you in case you didn't see this.

[MagisterEx]

Hello,
It looks like the Aspire project is a bit dead (I hope I am mistaken)....
I recently discovered it and love it !
I would love to see this grow and with a little luck eventually seeing it able to connect through a service like plaid to grab transactions automatically.

I looked at your work and think it adds some good functionalities... I love the Payee column (I was missing this too, coming from YNAB). The Balance sheet and the reports are great too.
I will be using your version going forward (the switch wasn't too painful as my data is recent)
I would love to see these implemented officially in the next version.

Just one comment:
on D5 in configuration, extend the range to D108
=ROUND(B5-SUM(D9:D108), 3)
This was something that seem to have been forgotten when Matt added the extra lines in the latest version.

Something to consider maybe in the future (I don't know much programming so it is just wishful thinking at this point), would be to add a budget template sheet that would fill up category transfer at the click of a button.
So let say people would setup in one sheet

Paycheck 1 - (amount expected for reference)
Total amount paycheck 1 - allocated amounts
Transfer from Available to Groceries, $200
Transfer from Available to Gym, $50
Transfer from Available to Car Insurance, $30
Date
Send to Category transfer button

Paycheck 2 - (amount expected for reference)
Total amount paycheck 2 - allocated amounts
Transfer from Available to Groceries, $150
Transfer from Available to Electricity $100
Transfer from Available to Gas Car $150
Date
Send to Category transfer button

Paycheck 3 - (amount expected for reference)
Total amount paycheck 2 - allocated amounts
Transfer from Available to Groceries, $10
Transfer from Available to Misc. Needs $150
Transfer from Available to HOA $60
Date
Send to Category transfer button

Wouldn't that be neat ?!

[stevenwenxu]

Thanks for your comment, great catch on the configuration issue!

In terms of the project being dead, the author said he's on sabbatical and feels good with its current state. IMO I like it this way - it's the reason I switched away from the YNAB subscription which keeps funding new development. My workflow doesn't need new development any more, I just need it to stay the way it already is.

There are a couple of reddit posts on automated category transfers, here's an example. I might share my simpler version later.