[GNC] Omission from help manual: formulas

Adrien Monteleone adrien.monteleone at lusfiber.net
Mon Sep 17 11:29:27 EDT 2018


David & Stan,

From my experimentation, I think your descriptions are correct.

Exponents aren’t supported. (yet)

All formulas must be simple, but nesting/precedence parenthesis are allowed.

While you can’t calculate using other splits or transactions as references/variables, you *can* use variables.

For example, while in a discussion a few months ago on envelope budgeting, I decided to set up an SX to allocate my pay to my envelopes based on the percentages I chose for each. (this was still a very simple exercise since there was no way to ‘cap’ the allocation and have the excess flow elsewhere)

I set my Cash split in the SX template to ‘received * 1’
Then I set each envelope split to ‘received *’ the percentage I wanted to allocate there. (each ‘envelope’ was a subaccount of ‘envelopes’ which was a current asset account)

I couldn’t set the SX to fire only on entering a payment transaction, but I could schedule it weekly. (a use case for ’template’ transactions that can be fired on command, *not* just a schedule)

When it fired, I was asked for the value of ‘received’ and GnuCash then created the transaction with the appropriate splits and the proper allocations. (I seem to recall if rounding created an imbalance, it created an Orphan-USD split that I could correct or leave as is)

It was an interesting exercise, though I don’t use it due to various limitations for the intended purpose. But it is an illustration of using variables in SX templates and GnuCash will prompt you for those values, then use those values to finish the calculations.

Regards,
Adrien

> On Sep 17, 2018, at 8:50 AM, David T. via gnucash-user <gnucash-user at gnucash.org> wrote:
> 
> Stan, 
> 
> I will preface this by repeating that I don’t use formulas or SX, so my advice may be corrected by others. Addressing your questions in reverse order.
> 
> With regard to exponentiation, I note that the Bank Account Interest section shows the expt (exponent) function (which comes from the underlying Scheme). When I attempted to put this into a formula (“expt (tryme 2)”), however, I got no results. Reading further down the wiki section, however, yields this: "If the formula was simple, then you could just enter it directly in the template transaction; unfortunately, the basic compounding-interest formula involves exponentiation, which can't be handled in the current expression parser, so one must resort to defining it in scheme…” Therefore, you must place any formulas with exponentiation in a defined function in fin.scm.
> 
> As for computing one split based on another, the strong implication is that this cannot be done. The section “formulas or scripts for periodic savings” says two things. First, it starts by saying “if you can express the amounts of the splits in the transaction as a simple expression,” which sets the expectation that you will be using expressions (and not references) in formulas. Second, it says there that formulas cannot “sum or obtain the value from existing accounts,” which I interpret to mean that it cannot pull information from different transactions. This, when you consider it, makes sense: how would the formula evaluation process determine which split was to be used?
> 
> I think the question of calculating one split based on another is covered in the phone bill example, in which each split is derived independently, demonstrating by example that you can’t compute one split based on another.
> 
> HTH,
> David
> 
>> On Sep 17, 2018, at 8:37 AM, David T. via gnucash-user <gnucash-user at gnucash.org> wrote:
>> 
>> Stan, 
>> 
>> I neither use formulas nor scheduled transactions, so I can’t comment on the documentation. Since you do, maybe you could add some information on the wiki to assist others after you?
>> 
>>> On Sep 17, 2018, at 8:05 AM, Stan Brown <the_stan_brown at fastmail.fm> wrote:
>>> 
>>> Thank you for the reply, Frank, but I read that before I posted. That's quite inadequate to tell people how to create formulas. 
>>> 
>>> The first three omissions that occur to me are: how do we use the value of one split in computing the value for a different split?  How do we use values from a specific other transaction to compute values for this one? How do we do exponentiation, for goodness sake?
>>> 
>>> -- 
>>> Stan Brown
>>> the_stan_brown at fastmail.fm
>>> https://BrownMath.com
>>> http://OakRoadSystems.com/
>>> 
>>> On Mon, Sep 17, 2018, at 5:06 AM, Frank H. Ellenberger wrote:
>>>> Hi Stan,
>>>> Am 15.09.18 um 15:37 schrieb Stan Brown:
>>>>> Though I have 2.6.19, I looked on line at the 3.2 manuals, and they are
>>>>> the same in this respect.
>>>> 
>>>> You missed one important place, the FAQ:
>>>> https://wiki.gnucash.org/wiki/FAQ#Q:_How_do_I_use_variables_and_formula_in_scheduled_transactions.3F
>>>> that leads to
>>>> https://wiki.gnucash.org/wiki/Scheduled_Transactions
>>>> 
>>>> 
>>>>> There doesn't seem to be any documentation of formulas for scheduled
>>>>> transactions. I'm talking about the "debit formula" and "credit formula"
>>>>> columns in the template transaction pane of the Edit Scheduled
>>>>> Transaction window.
>>>>> 
>>>>> There are specific formulas for things like mortgages and payroll, but I
>>>>> was unable to find the general rules for constructing formulas, neither
>>>>> in the Help Manual nor in the Tutorial and Concept Guide.
>>>>> 
>>>>> Can this be added, please?
>>>> 
>>>> Yes it should be added to the docs, so as David already said, raise a
>>>> bug report.
>>>> 
>>>>> -- Regards, Stan Brown
>>>> 
>>>> Regards
>>>> Frank
>>>> 
>>> _______________________________________________
>>> gnucash-user mailing list
>>> gnucash-user at gnucash.org
>>> To update your subscription preferences or to unsubscribe:
>>> https://lists.gnucash.org/mailman/listinfo/gnucash-user
>>> If you are using Nabble or Gmane, please see https://wiki.gnucash.org/wiki/Mailing_Lists for more information.
>>> -----
>>> Please remember to CC this list on all your replies.
>>> You can do this by using Reply-To-List or Reply-All.
>> 
>> _______________________________________________
>> gnucash-user mailing list
>> gnucash-user at gnucash.org
>> To update your subscription preferences or to unsubscribe:
>> https://lists.gnucash.org/mailman/listinfo/gnucash-user
>> If you are using Nabble or Gmane, please see https://wiki.gnucash.org/wiki/Mailing_Lists for more information.
>> -----
>> Please remember to CC this list on all your replies.
>> You can do this by using Reply-To-List or Reply-All.
> 
> _______________________________________________
> gnucash-user mailing list
> gnucash-user at gnucash.org
> To update your subscription preferences or to unsubscribe:
> https://lists.gnucash.org/mailman/listinfo/gnucash-user
> If you are using Nabble or Gmane, please see https://wiki.gnucash.org/wiki/Mailing_Lists for more information.
> -----
> Please remember to CC this list on all your replies.
> You can do this by using Reply-To-List or Reply-All.




More information about the gnucash-user mailing list