Custom Balances Extension: Questions and Answers
Stuart Beesley has developed some amazing extensions (Toolbox!) for Moneydance, but I feel one needs a bit more attention and usage - Custom Balances.
It's on my top right on my summary page. To put it simply, it allows you to create balances to view, on your summary page.
But it's much more powerful than that.
It's basically a custom report generator. You can create your own reports to view, and flip through them with the group id filter (i.e. a group id identifies a specific report you made)
It's basically a custom view into multiple reports. You just add another group id (i.e. "CustomView") and you can see important lines from multiple reports you created.
It's basically a warning window - you can place in hidden rows that trigger if a problem value is found (i.e. too low, too high, etc) and it will suddenly show up, and even blink if necessary.
You can change colors, show title bars, separation lines, etc.
You can do ratios, percentages, and so on. You can show categories by timeline - i.e. how much did I spend on gas THIS MONTH, or THIS YEAR?
It's uber powerful.
That said, anything uber powerful might elicit "how do I do this or that" questions. So far, these questions have shown up in "strange places" or in threads with a totally different title. As Stuart's main tester for Custom Balances, I can certainly answer such questions, and Stuart will probably check in as well. We would also entertain suggestions for improvement, but we both think CB is fairly mature at this point.
So, feel free to ask about Custom Balances here. It's a FANTASTIC extension (IMNSHO - in my not so humble opinion)
Showing page 10 out of 11. View the first page
Keyboard shortcuts
Generic
| ? | Show this help |
|---|---|
| ESC | Blurs the current field |
Comment Form
| r | Focus the comment reply box |
|---|---|
| ^ + ↩ | Submit the comment |
You can use Command ⌘ instead of Control ^ on Mac
271 Posted by Stuart Beesley ... on 16 Sep, 2024 03:04 PM
Would your row1 (that could be +value or zero) be added into more than just row2 - or would this roll up into row2 and then row 2 rolls up into row 3?
For example, we could have a formula that says takeIfPos(row) and then on the end row.. a formula that says
=sum(takeIfPos(row1), takeIfPos(row2), @networth) etc
272 Posted by avp2 on 16 Sep, 2024 08:48 PM
Each of the scripts I am planning does have more than one row, each rolling its math calculations to another row in sequence.
And, though I do not fully understand the formula line yet, your proposed line formula function looks to me like it would work. I assume, by the way, a zero row balance would not blow up a line formula evaluation. I assume also that a line formula is can be applied with, and after, any normal math calculations are completed. If a row can not have both, however, I think it would just mean more rows might be needed in a script.
273 Posted by Stuart Beesley ... on 19 Sep, 2024 06:20 AM
UPDATE:
I have just added: useifeq() useifneq() useifgt() useifgte() useiflt() useiflte()
as forumlas that can be used. The syntax is:
useifgt(value, compare)
Example:
useifgt(100, 1) = 100
useifgt(100, 999) = 0
1st parameter is your original value
2nd parameter is the comparator
will return zero if the test fails
eq = equal to
neq = not equal to
lt = less than
lt = less than, or equal to
gr = greater than
gr = greater than, or equal to
Probably the best usage is these in combination with sum() and row tags..
Let me know how you get on?
274 Posted by avp2 on 19 Sep, 2024 02:39 PM
Thanks Stuart. Looks like that will do the job. Might take a couple days to get to it, but will post how it works.
275 Posted by Stuart Beesley ... on 19 Sep, 2024 05:13 PM
If anyone can dream up any other interesting / useful formulas, shout?!
276 Posted by avp2 on 20 Sep, 2024 07:36 PM
Before reading the help file, I assumed the formula balance of a row was the same as a math’s balance. I guess there is a good reason the formula result is a display only value, not useable in other rows, just did not occur to me that was how it works - seems like it could be more useful and/or intuitive as a math function that sit just ahead of the PUM operation line precedence wise.
In any case, after I got how the formulas work and got used to the calculation implications, I was able to do what I wanted and the new formula comparison functions seem to work fine. I am a happy camper again. Thanks for the quick response to my need.
277 Posted by dtd on 20 Sep, 2024 08:23 PM
The circular implications of a formula impacting another formula(s) are very tough to handle - even excel has spent an inordinate amount of time handling infinite loops, indirect circular references and so on.
Even UOR math (multi-level), which Stuart finally allowed to occur, has a staging approach - one row can affect another row, and so on, and even there circular references can occur.
Are there ways around this? Sure, Excel has exception checking and so on, but this is intended to be "custom balances on your summary page for financial calcs", not something like a full fledged spreadsheet.
I'd like to be able to use tables (like tax payments based on salary), but again, that's what spreadsheets are for.
278 Posted by Stuart Beesley ... on 20 Sep, 2024 09:07 PM
It’s funny. As I added the new functions, I thought to myself… He’s going to want to reference formulas from other formulas, and why don’t we allow it?! 😝
Everything Dan says is correct.
I will ponder this issue though and consider if it’s ‘easyily’ solvable (like UOR).
For now, glad it’s working.
CB+1 😃
279 Posted by Stuart Beesley ... on 08 Oct, 2024 08:34 AM
I’ve pondered. It’s not easily doable. The method to resolve formulas cheats and we just let Jython work it out. Hence there is no way to (currently) detect what formulas reference other formulas in advance. Hence no plans to change this.
280 Posted by avp2 on 08 Oct, 2024 02:18 PM
The current functions are doing what I want/need for now, so no problem.
281 Posted by dtd on 09 Oct, 2024 01:02 AM
I expected such to be true. Custom Balances is uber-powerful, but again, things like formulas impacting formulas and table extractions (what I wanted) are what spreadsheets are for.
As it is, it is still a multi-report customizable extension that can have hundreds of rows/tables (I'm probably still the record holder on number of rows and tables.)
282 Posted by pctomac on 15 Oct, 2024 12:39 PM
Hi, I am trying to create a report that would show me just the accumulated interest I have received in a given account. I am able to get the total balance for the account, but not just the interest. Any suggestions?
283 Posted by pctomac on 15 Oct, 2024 12:41 PM
Oops, sorry, just realized this is the discussion for development. Will move my question to general questions area.
284 Posted by Stuart Beesley ... on 15 Oct, 2024 01:37 PM
It's not development...
Do you want to use the Custom Balances extension - or a report?
If CB, you will need to create a row, select the interest category then select inc/exp date range..
As I write this, I realise that you cannot select by category and then filter by account too in CB.... Hmm......
285 Posted by pctomac on 15 Oct, 2024 09:10 PM
Hi Stuart, yes I would like to use the Custom Balances extension. And you have hit the problem I came across as well: unable to select by category and filter by acct. (or in my case, I was trying to filter by category within the account). Don't know if this is something that can be requested as an enhancement for any further development? Thanks.
286 Posted by dwg on 15 Oct, 2024 09:49 PM
If what you want to see is a report of interest received in an account what about using an Income & Expense detailed report, set to use only that account and with just the relevant Interest category selected. That should should show each interest transaction and the total.
287 Posted by Stuart Beesley ... on 15 Oct, 2024 09:50 PM
For now, not possible. The workaround is to create various interest sub categories and then you can auto balance those. I do this for example with tax free vs tax paid interest
288 Posted by pctomac on 16 Oct, 2024 01:36 AM
Ok, thanks Stuart.
289 Posted by Moray McConnac... on 18 Oct, 2024 06:36 AM
Trying to use the formula function for calculating.
I have 2 subtotal rows, tagged rSubTotalOne and rSubTotalTwo, which add up the contents of other rows, let's say rSubTotalOne is rGroupOneA + rGroupOneB, and rSubTotalTwo is rGroupTwoA + rGroupTwoB. These work as expected.
Later I want a grand total row. I have a formula of rSubTotalOne + rSubTotalTwo
This initially produced [ Formula error: TypeError 'unsupported operand type(s) for +: 'NoneType' and 'NoneType'' ] - it looks as though referenced values don't have an explicit numeric type. I tried using float function to explicitly cast them, but it can't convert fro NoneType to float.
I solved this by adding a dummy zero-valued account to the account list for rSubTotalOne, and changing the formula to @this + rGroupOneA + rGroupOneB, and repeating likewise for rSubTotalTwo. They show the correct values
However my grand total now reads zero - it isn't actually using the formula value for rSubTotalOne and rSubTotalTwo, it seems only to be using the account list value? Have I done something wrong or is this a bug or other known issue?
290 Posted by Stuart Beesley ... on 18 Oct, 2024 07:25 AM
Hi... There are no known issues!! :wink:
Are you able to setup a small test/dummy dataset that demonstrates this issue.. Use File/New to create.. Then File/Export backup to back it up, then File/Open to reopen your live dataset...
Upload the test dataset here
Poss?
291 Posted by Moray McConnac... on 18 Oct, 2024 08:14 AM
Thanks Stuart. I get a security issue trying to upload the file here, so here's a link to the .moneydancearchive file
https://1drv.ms/u/s!AjMdxzipZ9KBhvt0TF-2shORXWcrSQ?e=doS4UC
The final row in custom balances is zero but shouldn't be as it should be adding up other subtotal rows. Note the use of the dummy zero account which gets round the first issue I mention above.
A work around is for the grand total row to use for calculations not the subtotal rows but the rows the subtotal rows are using, but in my real world example this is quite a lot of extra rows and would mean editing the total row any time any of the subtotal rows are used.
292 Posted by Stuart Beesley ... on 18 Oct, 2024 08:41 AM
(PS - you can just zip the file to bypass this site's upload security blocker)
293 Posted by Stuart Beesley ... on 18 Oct, 2024 11:35 AM
I’m haven’t looked yet, but can you confirm that you are NOT trying to reference formulas from other rows? (Formulas cannot access other row’s formula results).
Let me know?
294 Posted by dtd on 18 Oct, 2024 08:40 PM
TL/DR - You cannot have formula rows using other formula rows. But there are workarounds.
-----------------------------------------
@Moray - The below is not based on looking at the file you attached, but instead based on your original description (which was well stated).
Based on the description of what's going on, it seems like you are trying to do a formula row that is dependent on other rows with formulas. That doesn't work.
Sounds like Subtotal rows are subtotals of other group rows which are the sum of individual items. These subtotal rows are calculated via a formula, versus the group rows being calculated by picking individual items. This will work, as you have formula rows calculating from non-formula rows.
When you try to do a grand total, you create another formula trying to sum the subtotals (which also use a formula). As said, this doesn't work. What IS happening is that the values passed from row to row are based (if I remember correctly, I haven't worked with Stuart on the inner logic since January) - on calculations up through PUM, but not including Formula or FDA. So what you pass "does work", but the formula value passed for Subtotals is the calculated total up to and through PUM, but not including the formula. So, the values "passed" are probably zero.
This is one reason I suggested to Stuart to maintain the "old way" versus formulas only as that older way does have cascading potential. --- Stuart likes formulas (and I understand why, I was an advocate for formulas as well. and they provide a LOT of functionality the old way did not.) Still, the old way allowed cascading through UORs, so if you just pretend Formulas and FDA weren't there, here is an alternative. Note the line between Formulas and FDA? Pretend the line separator is ABOVE Formulas, and that might make clear what I say next. [This IS all in the documentation, but...]
So instead of using formulas for BOTH Subtotal and Total, use the RMC/UOR/PUM method for subtotals, and that will pass the values to Total when you use a formula there. This would also work if you have 2-3 levels of subtotals. Just note that once you use a formula (again, very powerful) don't try to use THAT result in another formula row.
Given UORs and Formulas/FDA - note that you can simulate the result of a row, which includes all items (Older calcs, Fomulas/FDA) - Writing this (long) bit makes me think that simulate row should also show the "passed row value" which would show the result before formulas or FDA is calculated (versus what would be the displayed result for the row.)
If I totally missed what you actually did, just consider these "notes to myself" on reminding me where Stuart (programmer) and I (documentation and discussion) ended up in January.
I'll discuss this with Stuart when he is available... and I'll need to be short and sweet, as he kindly tells me that I need to summarize versus wax poetic (and he's right). So, since I just wrote this on the fly, you get the detailed version...
295 Posted by dtd on 18 Oct, 2024 08:43 PM
For example (on my wordiness) note his post 293 describes everything I said in detail in a couple of sentences, and I think your answer would be "No, I AM trying to create a formula row using another formula row(s).
296 Posted by Moray McConnach... on 18 Oct, 2024 08:48 PM
Thanks both Stuart and DTD "No, I AM trying to create a formula row using
another formula row(s)." :->
I appreciate both of your time. Good to understand that formulas cannot
cascade in this fashion.
I will investigate whether it is better for me to "use the old way" or just
repeat the subtotals' formulas in the grand total row(s).
Many thanks
Moray
297 Posted by dtd on 18 Oct, 2024 09:03 PM
Another way to do it is to just create the subtotal from the individual items (i.e. subtotal contains the two items in each of rcurrent and rsavings versus adding those two up in a formula [i.e. subtotal contains currentone/currenttwo/savingsone/savingstwo) - yes that means you'd have to change more than one row if you added more items, but it would be a workaround.
Yes, I've now looked at your sample file - and just saw your previous message which is similar to this one.
298 Posted by dtd on 18 Oct, 2024 09:20 PM
@this + rCurrent + rSavings + rCreditCards + rLoans
in this situation is probably the simplest solution.
299 Posted by he on 12 Nov, 2024 02:56 PM
Is it allowed to post suggestions in this thread? If so, may I suggest that the Row Name Formatting include the option to change font size in addition to bold, underline and italics. It would be very helpful when quickly scanning certain rows.
300 Posted by Stuart Beesley ... on 12 Nov, 2024 03:07 PM
All suggestions welcome. I’ll certainly consider that