How do I set a specific end date to review a yearly budget?

pablocruz23

Our transactions in Quicken are never up to date as of today. They're always 1-3 weeks behind.

This is a problem when I'm looking at our annual budget. The budget comparisons are always through today. That means my budgets will always be off by the amount of the transactions since the last statement.

Is there a way around this? In Quickbooks, you can set a specific end-date for your budget. Is there anything like that in Quicken?

jacobs

Sorry, I only use Quicken for my finances, so I'm only lightly aware of other products. And the Quicken moderators won't allow discussion of competing products on this forum anyway. Since "you need a budget", Google that and find one such app. It's web-based, and not everyone wants their data in the cloud; it's also more expensive than Quicken. I know the herb-named product you referenced has some budget features, but I haven't used it and don't know how robust the budgeting is.

Just to clarify one thing in my post above: someone with basic spreadsheet skills can create a budget report in Excel or Numbers or Sheets; it just takes a little time to set up each time you want to do it. Building a multi-sheet workbook like I described to automate it each month requires some advanced spreadsheet skills, but manually building totals for an exported spreadsheet does not. Here's how:

• Export the Full Budget from Quicken.
• Open the CSV file in the spreadsheet program of your choice. You'll see there are three columns for each of the 12 months in the year: Actual, Budget and Difference.
• Select and delete the columns past the month you want. For instance, for an October YTD actual versus budget report, you'd select and delete the 6 columns for November and December.
• On the first category row, to the right of the existing columns, create a formula to add January Actual + February Actual + March Actual and so on up to October Actual. Press Enter to get the total for actual year-to-date.
• Click on the cell you just created and copy it one cell to the right. This will copy your formula for adding up January through October columns, but one to the right, so it will be the Budget values.
• Optionally, you can repeat the step above if you want a total of the Difference amounts in a third column.
• So now you have Actual YTD and Budget YTD (and optionally Difference YTD) for the first category row. Select those cells and copy them down through all the rows in your spreadsheet. (This is done slightly differently in each spreadsheet program, but they all have a "copy down" feature.)

The steps above are pretty straightforward on will only take a minute or two. Now comes the only tricky part: because there are categories and subcategories, you can just sum each column to get your totals; if you did, any categories with sub-categories would be double-counted. So you need to total the Actual YTD column without double-counting the categories which have sub-categories. There are a few ways you can approach this; I go for carefully building a Sum cell at the bottom of the column, and selecting only the rows which are stand-alone categories or category totals for those which have sub categories. Let me give an example; let's say these are your budget rows:

You want to create a formula at the bottom of the Actual column which sums the lines I marked bold and skips the sub-categories which I marked in italics. (E.g. 800 + 500 + 250 + 400 + 25 + 1500 is the total of your expenses in this example.)

Once you have a sum of the Actual column, copy that cell to the right to replicate the formula for the Budget and Difference columns.

It might sound like a lot, but it's not too complicated, and once you do it once or twice, you'll probably find it goes pretty quickly. It just takes a little time to set up the formulas to do the totals because of the sub-categories.

jacobs

You have unfortunately discovered the very major gap in budget functionality in Quicken Mac. There is no way to generate an actual-versus-budget report as of a past date, such as the end of the prior month. So the actual-versus-budget is only correct 12 days a year, and only then if you catch up entering all your transactions on the last day of the month!

The only way I've found to make the budget is useful is if I export the budget data to a spreadsheet (Excel, Numbers, Google Sheets), and build an actual-versus-budget report through the end of the prior month. (If you're a spreadsheet power user, you can build a workbook so that you copy-and-paste the Quicken budget export into one worksheet while another worksheet has lookup formulas to pull the data from any month range you want; it takes quite awhile to build, but then it takes only a few seconds a month to get a budget report. But building such a spreadsheet goes beyond most people's spreadsheet knowledge.)

To me, this is probably the single largest failing of Quicken Mac, and I don't really understand why the developers have allowed it to linger for so long. (I'm guessing it's because of the complexity of the budget code and their need to re-write it to add this and a number of other budget features that users want.)

There's a long-running Idea thread asking for this feature, which you can see here. The good news is that a few months ago, the developers finally marked this thread as "Planned", meaning they not only agree it needs to be implemented, but have time slotted for it on their development roadmap. Yay! The bad news: we're not privy to the roadmap, and have no idea when this functionality is coming.

paulpsd7

Thanks for that.

That's actually a deal-killer since the main reason we use Quicken is to track budgets. And I don't have the Excel skills to set up that spreadsheet you describe.

Do you know if any competing products like Mint have workable budget features?

jacobs

Sorry, I only use Quicken for my finances, so I'm only lightly aware of other products. And the Quicken moderators won't allow discussion of competing products on this forum anyway. Since "you need a budget", Google that and find one such app. It's web-based, and not everyone wants their data in the cloud; it's also more expensive than Quicken. I know the herb-named product you referenced has some budget features, but I haven't used it and don't know how robust the budgeting is.

Just to clarify one thing in my post above: someone with basic spreadsheet skills can create a budget report in Excel or Numbers or Sheets; it just takes a little time to set up each time you want to do it. Building a multi-sheet workbook like I described to automate it each month requires some advanced spreadsheet skills, but manually building totals for an exported spreadsheet does not. Here's how:

• Export the Full Budget from Quicken.
• Open the CSV file in the spreadsheet program of your choice. You'll see there are three columns for each of the 12 months in the year: Actual, Budget and Difference.
• Select and delete the columns past the month you want. For instance, for an October YTD actual versus budget report, you'd select and delete the 6 columns for November and December.
• On the first category row, to the right of the existing columns, create a formula to add January Actual + February Actual + March Actual and so on up to October Actual. Press Enter to get the total for actual year-to-date.
• Click on the cell you just created and copy it one cell to the right. This will copy your formula for adding up January through October columns, but one to the right, so it will be the Budget values.
• Optionally, you can repeat the step above if you want a total of the Difference amounts in a third column.
• So now you have Actual YTD and Budget YTD (and optionally Difference YTD) for the first category row. Select those cells and copy them down through all the rows in your spreadsheet. (This is done slightly differently in each spreadsheet program, but they all have a "copy down" feature.)

The steps above are pretty straightforward on will only take a minute or two. Now comes the only tricky part: because there are categories and subcategories, you can just sum each column to get your totals; if you did, any categories with sub-categories would be double-counted. So you need to total the Actual YTD column without double-counting the categories which have sub-categories. There are a few ways you can approach this; I go for carefully building a Sum cell at the bottom of the column, and selecting only the rows which are stand-alone categories or category totals for those which have sub categories. Let me give an example; let's say these are your budget rows:

You want to create a formula at the bottom of the Actual column which sums the lines I marked bold and skips the sub-categories which I marked in italics. (E.g. 800 + 500 + 250 + 400 + 25 + 1500 is the total of your expenses in this example.)

Once you have a sum of the Actual column, copy that cell to the right to replicate the formula for the Budget and Difference columns.

It might sound like a lot, but it's not too complicated, and once you do it once or twice, you'll probably find it goes pretty quickly. It just takes a little time to set up the formulas to do the totals because of the sub-categories.

pablocruz23

Thank you so much!! I'll go through that later today. I really appreciate that.

pablocruz23

I followed your instructions, which were very helpful. Thank you so much!

Regarding excluding the sub-categories from the totals, I found I didn't need to bother with that. The export from Quicken already includes the totals which were calculated without the sub-categories.

However, it would be helpful to format the subcategory numbers differently so I can tell them apart as I skim down the list. For that, I used conditional formatting based on the fact that the subcategories all start with "- ". So if the B column (where the category names appear) starts with "- ", then I make the text in the totals gray. (Does that make sense?)

jacobs

Nice work. And you said you weren't a power spreadsheet user!

So as you see, you can get to a workable actual versus budget YTD document to review how you're faring versus your budget. The main downside of doing this in a spreadsheet is that the next month you want to do it, you have to recreate your formulas and formatting. (You might be able to open the new CSV export from Quicken, Copy, then open your prior month spreadsheet file, and Paste Values; this will retain your formatting, but since you'll have a new month's columns, you'll still have to create your year-to-date values again.) Play around with it and find what works best/fastest for you. And hope that the Quicken developers will finally grant us a YTD spreadsheet report in the not-too-distant future!

pablocruz23

Actually, I think I can avoid having to recreate the formatting and the formulas.

In Google Sheets, there's a function to paste values only. So my thought is I can paste next month's report right on top of this month's report and all the formatting should remain.

Regarding the formulas, I created the formulas to include Oct, Nov, and Dec. Then I deleted the values in those columns but left the columns there. So my totals column calculates all 12 months, and then I delete the values for months where the values are not correct.

And yeah, I think I sold my spreadsheet skills short!