Rollover Adjustment Warning Wrongly Displayed on Envelope Budget Dashboard

@randy

I finally got a chance to try doing this today. I went through the migration using the “Tiller Envelope Budget Migrator” and the “Migration Helper.”

In the end, the Savings Budget was only showing one Display Period: Nov 2019. I realized that this was my first actual budget period in my old sheet which led me to the solution. The Migrator sheet puts the columns in the Categories sheet with the newest period to the left, but the Savings Budget seems to depend on the newest period being to the right. Once I reordered all of my columns I was able to see all of my display periods.

In my Categories sheet, none of my “Transfer” types were migrated. Is this expected, normal?

(Tangent warning) I’m not yet sure how this affects the Savings Budget. The Categories sheet migration also highlighted a problem I’ve been kicking down the road for a while: In my Envelope Budget, there are a few categories that I’ve deleted since they were temporary and no longer needed. I want to get rid of them, but also I want them reflected in past budgets so they continue to balance. In the past, I’ve handled this for one or two categories by manually moving those amounts from the column I wanted to delete to a related one, but this is time-consuming and error-prone so there are a few that have been happily sitting in the “Obsoleted” system category for a while. I’ve thought about writing something that would automate this because there are actually quite a few other categories that I’d like to combine just to simplify my budget, but I don’t really have the time, especially since my knowledge of Google Sheets is limited to the basic formulas.

I don’t really understand the purpose of the “Track” column in Categories. Or maybe more specifically I don’t understand what the “Debt” is supposed to be used for. I read the version history note but am not getting how it fits in or should be used. Right now I have everything set to “Savings” but I feel like that may be contributing to the next item…

In the Savings Budget, my “Savings” and “Available” are wildly off for both Income and Expense. I feel like that’s related to either or both my Transfer types not appearing and the “Track” category settings not being correct. Unfortunately, I’m out of time today to spend on it.

Thank you for your work on this. I think it’s going to be really great once I get it up and running!

I realized this was because “Rollover Tos” have to be manually entered #rtfm

I also manually added my Transfer types and things seem to be working fine there. I’ll have a better idea after I get all those Rollover Tos migrated.

Well, now I’ve realized this realization was wrong. The Rollovers were actually there but not recognized. When I manually added my first month’s, they were duplicates but still not recognized. Example:

Period	    Category	        Change	Prior Amount	New Amount	Type	Journal	Timestamp	Note
11/1/2019	Interest Received	$55.66	$0.00	        $55.66	    Savings	S+55.66	9/8/20 10:27 AM	
11/1/2019	Interest Received	$55.66	$0.00	        $55.66	    Savings	S+55.66	9/6/20 8:57 PM	
11/1/2019	Interest Received	$55.66			                    Savings	S+55.66	9/6/20 3:59 PM	

The 3:59 entry is the migration, the 8:57 is when I did my manual entries. I just now did the top entry and again it shows Prior being $0 and doesn’t reflect the amount in my budget.

Edit for clarity: When I say I manually added rollovers, I mean I entered them on the Savings Budget sheet’s “Adjust” column and then ran the “Update Budget” function.

Hey @aronos!

Let me try to address the points you raise:

  • Great catch on the budget period order as generated by the Envelope Migrator (Prototype). I’m embarrassed I missed that it is backward as you observed. I just updated the Envelope Migrator (Prototype). I believe the issue is resolved.
  • Regarding your transfers… the Envelope Migrator (Prototype) is only built currently to move over categories in the Budgets History sheet (of your old envelope budget). I don’t believe transfer-types are in that sheet (right?) so those would need to be reconciled from the migration tool into your final Categories sheet manually. Do I need to add a note about this in the instructions? Or make copying Categories into the migration tool part of the process so that it can be reconciled via formula?
  • Regarding categories you wish to deprecate… this is a riddle for sure. I think you are correct that the solution cannot be deleting them since that will throw your present and future budgets out of whack. What if we make it so that categories without budgets in the active period (in the Categories sheet) don’t show on the dashboard (unless they have actuals). This would leave some clutter in the Categories sheet but would allow you to hide deprecated categories by just blanking out their budgets in months going forward. Would this address the problem satisfactorily?
  • The “Track Debt” option… The Debt option is something we are seeding for a Savings & Debt companion dashboard we are toying with. We haven’t shared that yet and the Debt workflows are not documented yet, so just ignore the option (and select Savings) for the time being. Apologies for the confusion…

I’ll address the income/expense issue in a separate response.
Thanks so much for digging into this and all your detailed feedback— it is already making the solution better.

Randy

I have not yet added the tooling to migrate Rollover-Tos via script. I don’t think it will be too hard and I can take a stab at it this afternoon.

(One reason I have been putting this off is that I don’t have very sophisticated sample budgets to test and compare with.)

In the example you shared, are you saying that the Budget Journal entries are not making it onto the dashboard? Most likely causes are that a) you’re looking at the wrong period in the dashboard or b) you don’t have the Interest Received category set to Savings in the Categories/Track column. Are you good on both counts?

I just added code to migrate Rollover-To settings, @aronos. It’s is challenging for me to test beyond quite simple test cases without a larger real-world envelope budget. The new code performed well in my somewhat-limited testing.

Any chance you can load the the updated migration spreadsheet and let me know if new reverse budget column order and rollover-to-migration functionality work for you?

Hoping we have a few fixes in place based on this thread.
Thanks again for your time and dilligence.
Randy

I think a note in the instructions is probably fine! Once I realized how you were building that it made perfect sense. I only have 4 transfer types and I have a feeling that may be more than most. Writing a few in no big deal, especially since it’s a one-time thing.

I’ve thought about this before, where if a category is “inactive” (in the way you described) for a particular month it doesn’t get built into the dashboard list. I think it’s a good solution, my only concern would be the category limit. I know I’m probably on the extreme end, but I have about 100 now including my obsolete ones (and some I’d like to obsolete). If it’s doable in the new sheet I’d love to see it.

As a separate thing, if someone over there had a little spare time (haha), a tool to easily combine (including all of the past values) categories would be awesome too. I’d be surprised if I am the only one who has a budget that has become more complicated than they want it to be, but simplifying it after the fact without starting over is daunting.

This is very confusing to me because all of my Rollovers are in the Budget Journal. I must have done it and forgot? Eeek. :confused:

Right, I’m on the Nov 2019 budget in the dashboard and I have every category set to Savings. Looking closer, nothing shows up in the Savings column until my Feb 2020 budget, so 4 in. So even what’s left from the “Available” the previous month isn’t showing until then. I haven’t looked in detail to see if the Jan to Feb savings are accurate but a quick glance looks like they are, though without anything from the previous months included.

So after I wrote all of the above, I did this using a fresh Foundations sheet. The behavior I’m seeing is pretty much the same as I described above, no Savings until looking at Feb 2020. The Budget Journal now has 2 distinct types of lines. The top rows look just like the ones I pasted in my example earlier, including the same “Interest Received” entry that I don’t remember moving over.

Period Category Change Prior Amount New Amount Type Journal Timestamp Note
11/1/2019 Interest Received $55.66 Savings S+55.66 9/8/20 4:41 PM

Below those, there are a bunch of new entries that look like this:

8/1/2020 Capital Gain $0.00 Savings S0.00 9/8/20 4:41 PM Rollover-to to
8/1/2020 $0.00 Savings S0.00 9/8/20 4:41 PM Rollover-to from Capital Gain
7/1/2020 Capital Gain $0.00 Savings S0.00 9/8/20 4:41 PM Rollover-to to
7/1/2020 $0.00 Savings S0.00 9/8/20 4:41 PM Rollover-to from Capital Gain

And just now, I’ve realized that I’ve brought a lot of confusion to this (and wasted your time) by misusing “Rollover To” earlier in the thread when I meant “Rollover Mod.” I’m sorry. :frowning:

So what were my ROLLOVER MODS which were in fact migrated, should be showing up in the Savings column, right? Since Nov 2019 was my first budget in Tiller I had quite a few but as I mentioned, nothing shows up there until Feb 2020 and even then it looks like it’s only including rollovers from Jan. I don’t know if there’s any significance to that being the first month of the year or not, but it does kind of stick out to me.

This was keeping me from focusing on my homework so I had to try something… I went through my Categories sheet and Budget Journal, shifting everything 2 months in the future, so the budget and journal entries for Nov became for Jan, Dec became Feb, etc… Then I looked at my “new” Jan (which was actually my Nov data) and the Savings column was populated properly, including my soon to be famous $55.66 of interest I received in Oct. :thinking:

After I changed everything back, I took a peek under the hood of the Savings Budget sheet. I noticed “Selected Start Period” showed “1/1/2020” in AX3. I changed the formula to “=AX4” (which said 11/1/2019) and the Savings started showing values again. Looking closer at the vlookup, I noticed the “Starting Period” under “Dashboard Settings” and set that to Nov 19, and now all is fine, just a simple settings issue. Though I’m curious why that is a setting since I can’t imagine setting it to anything other than my first budget period since doing so would throw off all of my savings amounts.

It looks like this is already implemented? I have noticed that some go away which is fantastic, but I’m having some more weird floating-point issues on some others. Scanning down the AJ and AK columns in the Savings Budget sheet I see multiple entries that have fractions of a cent. Some are crazy small like “-2.7285118608944E-13.” These are keeping the sheet from treating them like zero values for this purpose.

I figured I’d include a few real-world examples of how I end up with these categories that I eventually don’t want anymore. In some cases, it’s simply an extension of how I set up my first budgets which mirror my bank’s own built-in budgeting features. They allow me to create as many little savings envelopes as I want. In a misguided attempt to keep it simple, I mirrored these in Tiller. For example, in my bank’s setup, I have separate envelopes for “Netflix,” “Amazon Prime,” “Disney+,” and “Spotify” and conversely a line in Tiller for each of them as well. But what I really want is to create a new Category called “Subscriptions” or “Streaming,” or maybe even use my existing “Entertainment” and “collapse” all of those onto one. The kicker is that I don’t even subscribe to most of them anymore and never did all at the same time.

In another case, I save every month into a “Travel” budget. But for about a year I was planning on going to Hong Kong last spring (haha) so set up a separate, additional budget specifically for that. Well, I completed my savings goal for that trip but it obviously didn’t happen and probably won’t any time soon. Now I want to “collapse” that Hong Kong savings into my regular Travel savings. I can go back and manually -/+ for every month, but that’s a pain and error-prone… and that’s a fairly simple case.

In another case, when Google announced the new Pixel Buds I knew I wanted them. I normally have a “Blow Fund” budget to spend on things like that, but I wanted to save specifically for them so I did what I do: Set up a budget line and stashed a little away each month so that when they went on sale, I’d have the money already saved and ready to spend. Now that that has happened, I have this “Pixel Buds” category that I’ll never use again. I’d be perfectly happy if I could “collapse” it onto my “Blow Fund.”

I’m contemplating starting with a fresh sheet in 2021, but… ugh. I don’t want to. haha

Regarding unbudgeted categories…I added this note to the migration instructions:

Only budgeted categories are shown. Reconcile and merge any missing categories (e.g. Transfer) your original Categories sheet.

Regarding deprecating categories…

  • I went to build functionality that hid categories that were unbudgeted within a period and found that the feature is already implemented. Give it a try.
  • There is no real “category limit” within the Tiller Money ecosystem. It is not our intent to restrict users. That said, we do build most templates to hold about 200 categories to maintain calculation performance and also to keep sheet UX accessible and contained. There are some tricks to adding capacity in a compatible way but it is absolutely possible if you are willing to modify your templates.
  • You make a good point about sunsetting categories. I can’t promise a timeline, but I’ve added it to my development backlog to build a feature that helps with that. I think the steps would be a) merge budget into destination category in the Categories sheet, b) overwrite source with destination category in the Transactions sheet, and c) overwrite source with destination category in the Budget Journal sheet. Am I missing anything?

Regarding your savings not accruing until Feb 2020, can you check hidden cell J2? This cell defines your budget start date. It is meant to be formula driven and pickup your first budgeted month from the Categories sheet but perhaps it is not working correctly. If it is not pulling in your first month from your budget, you could try manually overwriting in J2 with your budget start month (e.g. “11/1/2019”). I’m pretty sure this is the issue. In my sheet it is set to Jan 2020 which mean Jan 2020 will show zero savings and the first savings accrue and show in Feb 2020.

One last thing… I noticed and fixed a few issues with the Budget Journal script:

  • The script should NOT migrate rollover-to values where the target category is set to blank. Setting the rollover-to to blank should clear the rollover in the subsequent period.
  • The script was reading and moving the subsequent period’s rollover when the rollover-to was a different category. I realized this approach isn’t reliable and have changed the code to calculate the rollover at the end of the period using budget, actuals and rollover mods.

These changes are published if you’re up for trying them again. The script continues to become more accurate with each round of feedback and development. (Sorry it is so iterative.)

Thanks for the additional feedback, @aronos.

Budget Starting Period Issue

You did some real digging and found the root of the problem, @aronos. :hole:
I’m impressed.

The way those formulas are supposed to work is:

  • AX4 calculates the first/min budget period
  • A formula in J2 uses that data and presets the users budget to the first period

For most users, I think this will work well— as you say “I can’t imagine setting it to anything other than my first budget period”. For some users, I think they may want to start anew after a false start with their budget. Rather than deleting earlier budget periods, the J2 setting would allow them to start accruing savings at a later period by overriding the formula-driven start date in J2.

The issue you were running into is that I deleted the formula in J2 during testing and forgot to put it back (so your budget was hardcoded to not your start date). I readded the formula in the master template. Really sorry for the hassle.

Microvalue Issue

You mentioned the super small values that the formulas from the old budget created. You mentioned these causing issues in your migrated budget. Can you clarify if the microvalues are in the budgets (i.e. Categories sheet) or in the rollovers (i.e. Budget Journal sheet)? I can probably fix them either way once I know. You will need to remigrate your budget though once I clean up the formulas and scripts.

Do you think categories should hide if they are zero? Or if they are null/blank? To me, it feels like they should only be hidden when blank. (If we show zero budgets, then I’m not sure rounding the microvalues to the nearest cent will have the hiding effect you are hoping for.)

Merge Categories Workflow

I understand what you’re looking for and I believe it will have value to other users. It’s a couple of hours of coding and I will try to knock it out. (Don’t start a fresh sheet! :stop_sign: We can solve this problem.) More soon…

It took longer than expected, @aronos
I built out a category-merge workflow for the Savings Budget today.

It is built into the Tiller Money Labs add-on’s Tools / Savings Budget menu.

The Merge Categories workflow performs the following steps:

  • Recategorizes all transactions from source to destination category in the Transactions sheet
  • Reclassifies all savings/budget modifications in the Budget Journal sheet
  • Moves all source budgets into the destination budgets in the Categories sheet
  • Deletes the source category row from in the Categories sheet

Take it for a spin and let me know what you think.
(I wouldn’t test on a production budget yet… :wink:)

P.S. You will need to reload your browser tab to get the latest add-on build.

Fair enough. I suppose one could always enter new Savings values when doing something like this. :slight_smile:

I’m not sure they’re from the old budget, I just know this issue seems to pop up from time to time with the Envelope Budget. Heck, this thread was originally about this very thing. I do know that my first migration had these in the Budget Journal and I went through and removed them all. I feel like the second one did not. At some point, I was looking through the hidden data on the Savings Budget sheet, trying to see which columns contained them and which didn’t but make note of it at the time. I’ll do that again tomorrow or this weekend.

I think showing categories when zero and hiding when blank is a good solution. It gives the user some control over the behavior and just sorta makes sense. I’d rather figure out why the microvalues keep happening in the first place and getting rid of them.

This is so cool! I’ll give it a test spin soon. Thank you!

1 Like

About Microvalues: In the Savings Budget I scanned through columns M, P, T, X, AE, AF, AG, AH, AJ, and AK for my Nov 2019 an Sep 2020 budgets. The only place these microvalues appear is in AJ and AK. In Nov 2019 I have 2 affected categories, and in Sep 2020 I have 13.

I originally thought this had something to do with adjustments but in my 13 there’s at least 1 that has never had an adjustment. There are multiple that have only an adjustment from my initial Nov 2019 budget but aren’t affected in that period. :man_shrugging:t2: It’s weird.

I made a conditional format for all of the Rollover Mods rows in my Envelope sheet that highlighted non-zero values. I checked everything that highlighted and there was only one microvalue here. I did a quick scan of some Rollover and Actual lines and found quite a few. I chose 3 categories and went through manually correcting them. After I ran Analyze Budgets History they came right back so I think the source of the problem is likely in that function. At some point, I may correct the entire sheet, then run the migration to the Savings Budget without running Analyze Budgets History and see if that keeps them from carrying over to the Savings Budget but that sounds kinda terrible. :joy:


I was hoping to use the migration tool to keep my Transactions synced between my regular Envelope Budget and my test Savings Budget but it doesn’t seem to be working out. I know this is an “off label” use but I’m hoping I can figure out what’s going wrong. When I run the migration, it seems like it’s working and at the end it’ll say XX transactions migrated, but what I find is that new transactions are excluded and then transactions from an account that I have to enter manually are duplicated. I can run it twice in a row and that XX number will be different.


Today I went through the migration process again with a clean sheet.

The “Hide From Reports” flag is not transferring to the “Categories Migrator” sheet.

At first glance most of the “Available” values in my most recent month match between the two budgets. I figure this is a pretty good way to check for accuracy. I did notice a few that were wrong. I’m pretty sure the source of the discrepancy is that some of my manual transactions didn’t transfer over. I’m also pretty sure that this is because of errors I made early on by not using the Manual Transaction tool, some combination of initially not knowing it existed and later sometimes thinking it was easier to just copy an existing similar transaction and making the necessary changes. This was before I know about the hidden transaction id column. :man_facepalming:t2:

Overall I think this is working really well!


Will we be seeing the “categorize transactions,” “expense budget doesn’t match income budget,” and “net transfers” warnings implemented? Those are all very useful.


Update: The budget for the last category in the Categories list isn’t being recognized, so whatever that category happens to be goes negative by the Actual every month.

Related issue: The category that was originally my last never shows the Actual, even though it’s clearly deducting it because the Available continues to decrease. When I moved a different category to the last, the original continued to behave this way while the “new last” was affected by the issue, it had all of its Actuals properly displayed. Also when that original category was no longer last, and continued to not show it’s Actual, the “Available” always remained the budgeted amount, but didn’t carry over to the next month, so for example each month it would say: Savings: $0, Budget: $9, Actual: (blank), Available: $9. It’s not carrying over the Available to the next period so it’s seeing that it was spent, but the display is not reflecting it. It does show in column P.

Update to the update: I feel like there are gremlins in the machine. I’m positive the above behavior was happening, I saw it, changed the categories, reproduced it… this morning when I reloaded my sheet it wasn’t happening anymore.

Not-really-related issue: I feel like E6 should be “Actual” and not “Actuals” to match style with the other headings.


I tried the Merge Categories on a slightly complex case and it had a minor failure:

When it wrote the budget values in the Categories sheet, it put an apostrophe in front of it. This caused the value to show up in the Budget column on the Savings Budget sheet, but it could not accumulate Savings.

Update: I went a little merge crazy in my sheet. In some cases these merges were simply category renames, another fun benefit of this new functionality. I didn’t see the “apostrophe” issue again (gremlins) but I did repeatedly see another issue. When it was writing out the new budget values in the Categories sheet, if the cell had a number it worked fine, but if the cell was blank it would write “n/a”.

Hey @randy, wanted to draw your attention back to this thread which I think got lost in the mix… I did run into this issue with the apostrophes issue again this weekend. It was again when merging a budget onto another one that has “blank” budget periods. I don’t see a pattern as to why sometimes it does the apostrophe and sometimes “n/a”.

Great catches on the Merge Category workflow. I published a fix this morning that better handles apostrophes & special characters in category names and also addresses the n/a bug when merging empty budgets in the Categories sheet. Thanks, @aronos!

(Belatedly) reviewing your Savings Budget feedback from Sept 13, @aronos

  • Is the “microvalues” issue resolved once you are in the Savings Budget template? We made some changes to address that about a month ago. (I’m not as worried about the old Envelope Budget anymore.)
  • The Analyze Budgets History workflow regenerates and overwrites much of Budgets History every time it is executed, so I’m not surprised that the micro values came back after your ran it.
  • “At some point, I may correct the entire sheet, then run the migration to the Savings Budget without running Analyze Budgets History…” I think this would work… but you could also just fix the values post-migration once the data is in the Savings Budget.

There are many issues with trying to do this :point_up: but you might try using the IMPORTRANGE() function if you want to sync data between two sheets. @heather wrote up some instructions a while back…

Is this still an issue? I thought some new formulas from @cculber2 resolved this about a month ago.

Yes… if the Available values are close, that is a very good sign. The calculations are conceptually the same but implemented completely differently, so some discrepancies— especially with complex budgets with extensive history— would not surprise me.

I think you’ve seen that these measurements are prototyped in the hidden area. We will be moving them to the main area in the coming months. For now, you can cut and past them wherever you want them in your sheet.

Can you clarify… is this a migrator issue? or a Savings Budget issue? Is it possible we resolved this since your post?

I changed this a few weeks ago. Thanks for the suggestion.

Thanks for all the detailed feedback!