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 5 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
121 Posted by dtd on 20 Dec, 2023 06:35 PM
Your last statement could be very problematic - as I have set up tables with multiple rows all using the same uor. So changing one row's period automatically changing the uor period could mess up all the other rows.... and I probably wouldn't want a cascade change either as I might have different periods for different reasons within the table.
Your request seems to assume that a uor will only be used by one row.
122 Posted by dtd on 20 Dec, 2023 06:44 PM
In testing, CG is often "off by a penny" in many cases. Almost certainly a rounding error of some kind versus a miscalculation.
Even my Investment accounts at the bank website get things "wrong by a penny", and I know they are "wrong" not me... it's usually actually a fraction of a cent, and they must use different rounding rules than I do with my calculator.
Wasn't the movie "Office Space" all about moving those fractions of a penny for advantage?
123 Posted by Stuart Beesley ... on 20 Dec, 2023 07:22 PM
Cannot auto update all UOR dates. Rows can be used by many others, or use many others. Each row has four dates. Some asof, some range. Would be impossible to understand which other rows could safely be updated.
124 Posted by robert.l.spauld... on 20 Dec, 2023 07:34 PM
Excellant work, and thanks for the explanations. So many use cases that have to be factored into design. I get it. I suppose something like that would only be useful for specific use cases like comparing periods (ratios). Thanks for the opportunity to participate in the discussion. Custom Balances has found a permanent spot on my Summary Page.
125 Posted by dtd on 20 Dec, 2023 08:11 PM
Although you can't autochange uor, you can add more rows/uor rows to show the different ratios, I create tables for comparitive stuff by using groupid and then I can just display the table - I have to duplicate a row and a uor, give it a different period, and then display it along with others, but that's why I have groupids, so I can just switch between tables at will.
126 Posted by he on 20 Dec, 2023 08:29 PM
Question regarding the "Include Cash" checkbox on the settings page: Cash in my investment accounts is held in a security, with its own symbol and cusip. But it appears that CB is reporting non-security cash.
How can I identify my cash security as a cash holding to CB?
I'm a novice user, still working my way around CB (great work, Stuart), so feel free to dumb this down. Thanks in advance.
127 Posted by Stuart Beesley ... on 20 Dec, 2023 08:36 PM
You don’t need to tick use cash for a security (even if to you it holds cash. It doesn’t it holds shares with a conversion to money of 1:1).
Include cash, pulls in the investment account cash balances into the cost basis calculation.
128 Posted by robert.l.spauld... on 22 Dec, 2023 03:22 PM
Average by does not seem to produce the desired results if used on a row that uses another row.
Say I want to multiply a row results by 5%. Average by should be 1/.05 and it works. If that row is not using UOR maths, but if applying the same 1/.05 to a row using UOR maths the result is not as expected.
Is there any mechanism to apply math to a rows final result. Like a list of user defined variables that could be used to manipulate the row result using math. The way Group ID's are filtered on the summary page would be a cool way to store user variables/constants that could then be applied to the row results after all other internal calculations have been completed.
result * <COLA>
result * <TaxRate>
result - <StandardDeduction>
result + (<result> * <InflationRate>)
If I'm being a pain in the butt, please let me know. Thanks again.
129 Posted by Stuart Beesley ... on 22 Dec, 2023 04:13 PM
Have you checked out the help file, for the order of calculations? My guess is that the average function is being applied to that Row’s results before the maths on another row takes place.
I’m sure at this point, Dan will chip in.
130 Posted by robert.l.spauld... on 22 Dec, 2023 04:36 PM
@Stuart I did indeed read and reread the order of calculations and think I got stuck in an infinite loop.
Would manipulating the final row result using math be less horsepower intensive than using another row?
131 Posted by dtd on 22 Dec, 2023 07:05 PM
Reread the order of calculations, and note that it basically follows the rules of math (i.e. PEMDAS)... parentheses, exponentials, multiply, divide, add, subtract. Well, at least it did until multiple uor's were allowed, now you have to make a parenthesis assumption if you do intermediary uor math with +/- since they may come before another uor multiply/divide.
1 - Derive all rows required, and/or required within other rows (irrespective of always hide / GroupID filter)
2 - (unless used within other rows) skip any 'always hide' rows
3 - (unless used within other rows) skip any rows filtered out by GroupID
4 - Calculate raw balances for derived rows/accounts, including recursive sub accounts for autosum rows
5 - Convert calculated balances to target currency
6 - Iterate over each row/calculation, apply any average/by calculations
7 - Iterate over each row/calculation, apply any Use Other Row (UOR) calculations.. Iterate the whole UOR chain
8 - Lastly, iterate over each row/calculation, apply any final calculation adjustment amounts specified
1 - For each row, calculate the value of the row, [leaving out any uor CALC, leaving out adj/by ]
2, 3 - describes in more detail 1's "all rows required'
4 - describes in more detail 1's meaning for "derive" - i.e. what to do with the picklist.
5 - convert each derived row value into the proper currency
6 - for each row, do average/by calculations (this is a multiply/divide) and comes before any uor (MDAS)
7 - Do any uor calcs (MDAS comes after MD), but with multiple uor chains, this is more complex, so by fiat, uor calcs get parentheses to allow for interim +/- order.
8. Do any adj/by (+/-) calcs for each row. [This was to follow Pemdas, as it is (+/-) but it could be discussible again, as it comes very last, and is NOT included in each uor until after the uor has been applied.]
Does that make the order of calculations not an infinite loop for you?
132 Posted by dtd on 22 Dec, 2023 07:19 PM
As for "Is there any mechanism to apply math to a rows final result." - that's what is being done, with the exception of adj/by which is deliberately set off because it is meant as an absolute +/- adjustment, which would answer a different question "Is there any mechanism to apply a +/- adjustment to the row's "final" result."
i.e. A row has a "final" result, with uor's and adj/by left out. That "final result" has one math in it - an average (MD).
That "final result" is "mathed" by its uor list (which until recently was just one row versus a possible chain) i.e. one MDAS, not many.
That new "final result" is "mathed" by its adj/by value, which is one AS calculation.
So there are currently two "Is there any mechanism to apply math to a rows final result." bits.
Hope that further explains the order of calculations.
133 Posted by dtd on 22 Dec, 2023 07:38 PM
You can do everything you ask, it's just how it is applied.
result * <COLA>
result * <TaxRate>
result - <StandardDeduction>
result + (<result> * <InflationRate>)
use the average field to put in the <COLA> value.
use the average field to put in the <TaxRate> [[ we've already discussed this one ]]
use the adj/by field to put in -<StandardDeduction. [you said result was final, so adj/by would work]
use a uor field to (generate result, with average by to put in <InflactionRate>) then apply the uor field to the other row with result)
Sounds like what you want is either an adj/by field that can do MDAS versus AS (so as to get two Avg/by results) or a table of constants such that you didn't have to put in the value you want to do an MDAS - i.e. <TaxRate> versus 1/.28 ... which could be done with a uor as long as adj/by were earlier in the calculation stream.
134 Posted by robert.l.spauld... on 22 Dec, 2023 08:07 PM
@dtd, Thanks for your detailed follow on. My problem seems to be applying a percentage to a row that was built using Inc/Exp + CapGains.
I believe this note tells me that will not work.
- NOTE: When cost basis / unrealised / capital gains is enabled, all other steps are skipped >> MUTUALLY EXCLUSIVE! (i.e. no reminders, income / expense transactions, no non-security/investment(cb & cash) accounts
The row I've built gives me the exact total number I was looking for (Taxable Income), just wanted to apply a percentage to it to calculate Taxes Due.
I believe I see a way to do it creating additional CG rows for the different rates I have and then adding them after the fact.
I was trying to do all this with the least amount of rows possible that would not have to be revisited when the rate changes, annually. Which gives me the opportunity to miss one row and have the wrong number.
Thanks again for your assistance and I hope you all have a wonderful holiday season.
135 Posted by robert.l.spauld... on 22 Dec, 2023 08:15 PM
@dtd "Sounds like what you want is either an adj/by field that can do MDAS versus AS (so as to get two Avg/by results) or a table of constants such that you didn't have to put in the value you want to do an MDAS - i.e. <TaxRate> versus 1/.28 ... which could be done with a uor as long as adj/by were earlier in the calculation stream."
Yes, a final calc to the row result. Without having to create another row. The nested dependency's become hard to keep up with.
The user created variables would allow a one stop shop to tweak a rate or deduction that could possibly be in multiple rows but would only be useful with that ability to MDAS one/or several of them to the current row value. Perhaps assigning a variable to existing rows values would also be nice. An "Expression Builder"
136 Posted by Stuart Beesley ... on 22 Dec, 2023 08:32 PM
I don’t have the patience to read all the above messages / words. Sorry. In short, I think each row is calculated individually without reference to other rows and the avg by is applied at that point, before any other rows are considered.
So the questions are:
is this what is happening?
how could this be improved (for all)?
137 Posted by dtd on 22 Dec, 2023 09:55 PM
Spaulding will need to answer your first question. In my estimation, order of operation works exactly as described, (and the long message was meant to explain it more), but maybe he has an exception.
Spaulding also made (in my mind) two suggestions for improvement.
One was to make the adj/by to allow for multiply/divide/plus/minus instead of just plus/minus - seems he feels he needs two average fields, i.e. one before uor, one after uor.
He also wants to create a set of constants with names, so he can enter the names versus the constants. I think that's a convenience request, which I think can be done manually.
138 Posted by dtd on 22 Dec, 2023 09:58 PM
@spaulding - you express this:
"Yes, a final calc to the row result. Without having to create another row. The nested dependency's become hard to keep up with."
If you don't create a uor, you already have this. The average/by is a multiply/divide as a final calc to the row result, followed in the same way by adj/by which is a plus/minus.
And you say you don't want a uor. So, this request has already been fulfilled.
139 Posted by dtd on 22 Dec, 2023 11:12 PM
Stuart and I both agreed to not discuss this issue/request further until after Christmas/New Year.
Would like to know the answer to Stuart's first question, however:
"I think each row is calculated individually without reference to other rows and the avg by is applied at that point, before any other rows are considered.
is this what is happening?"
140 Posted by robert.l.spauld... on 22 Dec, 2023 11:41 PM
@dtd @staurt apologize for my inability to clearly articulate what my struggle is. I'll think through it over the holidays and try again. Hope you all have a Very Merry Christmas and Happy New Year.
141 Posted by dtd on 22 Dec, 2023 11:59 PM
re this message (which I missed):
"@dtd, Thanks for your detailed follow on. My problem seems to be applying a percentage to a row that was built using Inc/Exp + CapGains.
I believe this note tells me that will not work."
"The row I've built gives me the exact total number I was looking for (Taxable Income), just wanted to apply a percentage to it to calculate Taxes Due." (This suggests you use the same average value for both/all)
Create a row for the Inc/Exp custom balance. Use Average by to create a percentage.
Create a uor row for the Capital Gains items (you can do all at once if the same average value). Use Average by to create a percentage.
Put the uor row in the Inc/Exp row. Use + to include it.
The average by is the "percentage applied", and the result is a $ value of taxes owed. It will show dollars, not %, since the result is a percentage of the balance itself, but is still a dollar value.
142 Posted by robert.l.spauld... on 23 Dec, 2023 12:56 AM
Thanks, I'll give that a whirl. Love to have it right there in MD instead of having to jump to a spreadsheet. Actually 2 Values State and Federal.
143 Posted by dtd on 23 Dec, 2023 01:42 AM
Actually 2 Values State and Federal - if I read you right, two percentages/averages, so you would create 2 normal rows and 2 uor rows.
144 Posted by robert.l.spauld... on 23 Dec, 2023 02:47 AM
Correct, got it done and numbers are very close to actuals from last year. Thanks for all the help. Just have to remember to update the rates for next year. Still a little manual math to figure the quarterly payment, but that will keep the calculator dust free.
I had missed the average on the first row, assuming I could add it to the second and just average the total. Rookie mistake.
145 Posted by Stuart Beesley ... on 23 Dec, 2023 11:54 AM
@robert...
A couple of questions from me:
Did you manage to achieve what you needed?
Was average/by actually working as defined?
If the final adjust by also included the ability to multiple and divide too, on the final result, would this basically give you what you need?
146 Posted by robert.l.spauld... on 23 Dec, 2023 02:23 PM
@Staurt
Did you manage to achieve what you needed? 90%
Was average/by actually working as defined? Yes
If the final adjust by also included the ability to multiple and divide too, on the final result, would this basically give you what you need? Yes
Thanks
147 Posted by Stuart Beesley ... on 28 Dec, 2023 01:46 AM
OK, latest PREVIEW on my site... You can now calculate short/long-term capital gains...
I have had to rebuild the MD cost calculation engine to do this properly. It should mirror the MD capital gains report, including the st/lt splits for LOTS.. It seems (to me) that the MD CG report for avg cost where st/lt is broken. I believe mine to be correct.
Anyway, I would appreciate testing of this and verification of my calculations.
https://yogi1967.github.io/MoneydancePythonScripts/
Let me know?
148 Posted by Stuart Beesley ... on 29 Dec, 2023 08:04 AM
Build 1044 PREVIEW is now on my site. Now the much discussed adjust/by field has gone
.. .. .. and is replaced with a new final maths calculation, which allows you to run a final adjustment / maths on the final result.
Of course, the capital gains, st/lt is there too.
Grateful for all feedback. Thanks.
149 Posted by Stuart Beesley ... on 29 Dec, 2023 10:33 AM
.. further. The only debate is whether when using final maths calculation, and divide by, and show as percent, whether to multiply the result by 100 (which is what it currently does)? An argument can be made to not do the *100 and let the user adjust the operand by 100 if that’s what they want.
150 Posted by robert.l.spauld... on 29 Dec, 2023 12:35 PM
@Staurt, Nice changes on the final maths calculation. I do like your argument for not multiplying by 100 for percents.