#13 accepted
Virgil Dupras

Calculated transactions

Reported by Virgil Dupras | June 4th, 2009 @ 04:28 PM

It would be possible to enter expressions (like "42 - balance") in the amount field to create calculated entries. When such expressions are detected, the transaction goes in "calculated mode" and behaves much like the current balance transaction, except for that "always last in sort order" requirement (as well as "only one per day max"). For now, there would only be one possible variable, "balance", but with recurrent transaction, the power of the calculated transaction will be expanded.

Comments and changes to this ticket

  • Virgil Dupras

    Virgil Dupras June 4th, 2009 @ 04:29 PM

    from Eric

    I don't think we need to wait for recurrent transactions to have full calculated transactions. The number and variety of variables does not change if the transaction is recurrent or not.

    There are a lot of calculated values the user could want to use. Let me give my two favorite examples.

    • I want to estimate how much interest my account will earn at the end of the month. I create a transaction on the 31st that calculates the "average balance this month" * 5%.
    • I want to estimate how much income tax I'll have to pay for 2007. I create a transaction in April 2008 that calculates the ("total income in all accounts in 2007" - 10000) * 30%.

    Here is my list of interesting values:

    • The balance on a given day in a given account
    • The average/min/max balance in a given period in a given account
    • The cash flow in a given period in a given set of accounts
    • The sum of debits in a given period in a given set of accounts
    • The sum of credits in a given period in a given set of accounts

    The "given period" parts are the one that can give us the most trouble. Here are some examples:

    • March 18th 2008, March 2008, 2008
    • From Sept 3rd 2007 to March 18th 2008
    • Last day/week/month/year
    • Last n days/weeks/months/years
    • From 5 months ago to 2 months ago...

    Of course, we can decide on a subset of these possibilities.

    The other thing that we'll need is some ability to constrain the values. In my income tax example, I don't want the calculated value to be negative if my income is not yet over 10000$. I want it to be zero in that case.

    Now, the problem is that these values have to be part of expressions. We can do 3 things:

    1. Have a mini-language that can express all these values inside expressions.
    2. Have a GUI for creating calculated values that can be inserted as objects in a bigger expression (just like when you create a personalized system date format on the mac).
    3. Have a complete GUI for calculated values that allows the user to choose one calculated values and to apply linear transformations and constraints to it.

    The problem with 1. is that the language will probably become very complex. Too much to be useful in general.

    The problem with 1. and 2. is that we need to do error checking and reporting. For example, it makes no sense to multiply the sum of credits by the sum of debits, but if the user does that, moneyguru will have to give some kind of error that will very much look like a compiler error.

    The problem with 3. is that you need to express your calculation in a way that is possibly not natural. In my income tax example, if the expression model is (value * factor) + offset, I have to select "total income in all accounts in 2007" as the value, 30% as the factor and I have to figure out that the offset will be -10000 * 30% = -3000. In the "balance entry" scenario, I have to select "current balance" as the value, -100% as the factor and 42 as the offset...

    I still think that 3. is the easiest solution for both the user and us. We could also do a mix between 1, 2 and 3: let the user select only one calculated value, bind it to a variable (say "value") and let the user build an expression with that variable and numbers (of course, we still have to check that the variable occurs only once). We can also have predefined variables for oft-used things like "current balance". Again we'd have to make sure that only one calculated value appears in the expression.

    I'm not sure what's best, but that's the state of my thoughts for now. What do you think?

  • Virgil Dupras

    Virgil Dupras June 4th, 2009 @ 04:30 PM

    First and foremost, I think your whole argument is based on a flawed assumption: That we need a complex way to express date ranges in the expressions. I think that date range selection (for avg, max, min) should be exclusive to recurrent transactions. Both your examples should be recurrent transactions, and I can't think of a case where someone would want to create an expression for a transaction that is not exactly the date range between this transaction and the previous recurrence (or an amount of time equal to when the recurrence would be if there were any, for the first occurrence of a recurrent transaction).

    What I see for calculations (and conditions) is a little section in the transaction inspector, hidden by default. There is two fields: "expression" and "condition". "condition" is an expression making the amount null if the condition is not met. Next to those fields is a list of blue tags (like in Mail in the sender field) with possible variables (balance, avgbalance, etc..) that the user can drop in any of those 2 fields. Next to each of those draggable field would be displayed their current value.

    For the account selection, that's something else (Can you think of any other case for this one that income taxes?). Maybe that in the tag list, we could have a "Special" tag that the use can edit. In that edition window, the user selects the basic aggregator and chooses his account a a name for the special tag. Then, he can drag it in the expression/condition fields, and create another special field if he wants.

  • Virgil Dupras

    Virgil Dupras June 4th, 2009 @ 04:31 PM

    from Eric

    I have shown my assumption with my income tax use case. The tax is not paid on Jan. 1st, but in April. Now, before you say that this is just one use case and that I can't come up with others, let's make a list of all use cases we have for calculated transactions. Mine is:

    • Income tax
    • RRSP contribution
    • Interest

    The pseudo balance entry is not in my requirements anymore because we deal with that in the reconciliation.

    Let's see what your list is, but if the power of calculated transactions only allows us to calculate interest, then I'd rather have it as an account parameter instead.

    The other part of what I was saying is that we don't want to be too permissive in the expression editor. If it's too easy to create invalid expressions, then we'll have to display an error message and that error message won't be easy to figure out. That's why I was referring to compiler errors.

    But first, I'd like to see your list of use cases...

  • Virgil Dupras

    Virgil Dupras June 4th, 2009 @ 04:32 PM

    Ok, it escaped my mind that a "taxes" transaction is not necessarily based (in fact, pretty much never) on transaction that occurred just before it. So I agree with you, and I would also go with your 3. proposal (completely GUI based). Here's a GUI proposal:

    First of all, it has to be a separate form. So in the transaction inspector, the user clicks on a button to summon that calculation form. This form would have 4 basic sections: Entry selection, aggregation, adjustments, condition.

    In the entry selection part, the user selects that accounts to select entries as well as the date range. I'm not sure yet what type of controls would be suited for these 2 values, but the default should be "current account" and "past recurrence period" (if this is not a recurrent transaction, "all past entries")

    In the aggregation section, there would be 2 combo box. One with the choice between debit, credit and balance. The second with the choice between average, sum, max, min. If balance is selected, the "sum" choice is disabled (doesn't make any sense)

    In the adjustment section, there's a text field where it's possible to write an expression. Only the variable "amount" is allowed. The rest are constants.

    In the condition section, there's also a text field editor where it's possible to write an expression.

    BTW, I'm removing that note about replacing balance entry in the description, balance entry has already been replaced.

  • Virgil Dupras

    Virgil Dupras June 4th, 2009 @ 04:32 PM

    Oh, I forgot. I wanted to insist on the term "entry" in entry selection. I don't think it makes any sense to select transactions.

  • Virgil Dupras

    Virgil Dupras June 4th, 2009 @ 04:33 PM

    You know what? I think we should revert to what I was proposing with the 2 text fields and draggable tags. I was thinking about one use case that couldn't be done with the other proposal:

    I have a liability called "Royalties owed" and add a monthly recurrent transaction in there. It takes the sum of the amounts of the "moneyGuru income" account for the past month and subtract the sum of HS Expense divided by 2.

    So I think we should adopt the tag model, and it would be possible to design custom tags. The design window for custom tags would contain the "entry selection" and "aggregation" sections.

  • Virgil Dupras

    Virgil Dupras June 4th, 2009 @ 04:36 PM

    from Eric

    That certainly is an example of a complex recurrent transaction. You'll have to tell me why you need such a weird calculation, though.

    Now, let me use this example to show you why a free expression is a dangerous thing to allow. Now, let's say that instead of "[Moneyguru income] - [HS Expense] / 2", you write "[Moneyguru income] / [HS Expense]". Now, this should be illegal because you dividing two amounts together doesn't yield an amount. If anything, it yields a factor. Now how are you going to say this to the user? Maybe something like "Sorry, you can't divide two amounts together". But now, we'll have to check for all such possible mistakes and compose meaningful, easy to understand error messages for all of them. That's why I'm saying that these will soon look like compiler errors.

    Here's another full GUI proposition that feels easy to me and that can do your example too.

    We could say that the final amount is a sum of values. The values are either constants or calculated values multiplied by a factor. Instead of a condition, you'd be able to specify a minimum amount and a maximum amount.

    So, in your example, you'd have two values: 100% of "moneyguru income" and 50% of "HS expenses". For a "balance transaction", you'd have -100% of "balance" and, say, 42.

    The values would be presented in a list. Pressing the "+" button would bring up a dialog asking for either a constant or the "aggregation" and "entry selection" parameters and a factor.

    It would be nice, of course, to present the "aggregation" and "entry selection" stuff in a friendlier way. For example, since we can add as many values as we want, the account selection can be super simple and allow the selection of only one account or one group. So, you'd have two pulldowns: one for the aggregation (sum of debits, max balance, etc.), one for the account or group.

    Here's an approximate fictitious example of what the amount part of the transaction inspector could look like:

    Amount:   200 CAD (delete)
            + 100% of all expenses last month (delete)
            - 50% of computer expenses last month (delete)
            + 2% of the average balance of my operations account last year (delete)
    Min:    0 CAD
    Max:    1000 CAD
  • Virgil Dupras

    Virgil Dupras June 4th, 2009 @ 04:38 PM

    I like that very much. One things that bothers me a little bit is the constant amount being among the rest of the lines. I can't see any way that there could be any more than one of these lines. Maybe that we could have a separate "Adjustment" line below, with Min and Max? That would allow us to simplify further line definition.

    Let's use your proposition as a base for defining the popups. So, if I understand well, each line would have 5 fields (and one delete button):

    • +/-
    • Percentage text field
    • An account selection popup. The values of this popup would be:
      • [selected account]
      • All assets/liabilities
      • All income
      • All expenses
      • [a list of all accounts, including groups]
    • An aggregation popup: (only visible for asset/liability because for income/expense, the only thing that makes sense IMO is the sum of amounts)
      • Average balance
      • Min balance
      • Max balance
      • Cash Flow (debits - credits)
    • A date range selection popup. The values of this popup would be:
      • [recurrence date range] (present only if the transaction is recurrent)
      • last week
      • last month
      • last year
      • since...

    The "since" item of the date range would make a [datefield] until [datefield] section appear.

  • Virgil Dupras

    Virgil Dupras June 4th, 2009 @ 04:39 PM

    from Eric

    Ok, let's keep the constant separated. But I'd present it the other way around. The main amount is the constant amount and the calculated values are the "adjustments" (that should be another name). This way, calculated values strictly add something to a normal transaction.

    The other thing is that for legibility, the aggregation popup should come before account selection, I think. And for income/expense, you could also want only the debits or only the credits.

    Therefore an example of a calculated values line would be (brackets surround fields):

    [+] [ 50 ] % of [the sum of amounts in] the [Computer] account [last week]

    In the date range selector, the "since..." item is not well defined in the context of recurrent transactions. If I have a monthly recurrent transaction on Feb. 4th and I say "from Jan. 4th to Jan. 18th". What's the date range for the occurence on Mar. 4th? I see three possibilities:

    1. Jan 4th - Jan 18th (does not move)
    2. Feb 4th - Feb 18th (add one month)
    3. Feb 1st - Feb 15th (add 28 days)

    The number 3. probably doesn't make sense in the context of a monthly transaction, but it does in the context of a daily transaction.

    Anyway, I don't have a use case for "since..." so, until we have one, we might as well drop it.

  • Virgil Dupras

    Virgil Dupras June 4th, 2009 @ 04:41 PM

    Drop "since..." Doesn't that break your "taxes" use case? That's the reason I put the "since" in. In fact the only use case we have for having any date range other than "since last occurrence" is your taxes example. Another thing: should "last year" mean "Jan 1st - Dec 31st" or "transaction date - 1 year"? Besides, not everyone's taxes are counted based on the civil year...

  • Virgil Dupras

    Virgil Dupras June 4th, 2009 @ 04:41 PM

    I thought about a behavior we have yet to define: Calculated transactions & reconciliation. Also, what if the user writes an arbitrary amount in the transaction? For these reasons, I think there should be a enable/disable button for calculation (we can't simply clear the calculations. the user might have put a lot of time in it). If you write down an amount, the calculation are disabled. If you reconcile, same thing, but it's not possible to re-enable until you de-reconcile.

  • Virgil Dupras

    Virgil Dupras June 4th, 2009 @ 04:42 PM

    It occurred to me recently that we need conditions that take something else than the calculated value (in other words, it can't be just "minimum" and "maximum") if we want to support things like credit card interests rules, bank fees rules, and arg... I had thought about one that was much more important, but I can't remember it...

  • Virgil Dupras

    Virgil Dupras June 4th, 2009 @ 04:42 PM

    I remember now: Taxes! We can't ask the user to add calculated transactions for taxes in the form of "Add a X$ transaction if that X is bigger than Y". What the user wants to be able to say is "Add a X$ transaction if Z is bigger than Y", Z being another calculated value.

  • Virgil Dupras

    Virgil Dupras June 4th, 2009 @ 04:42 PM

    from Eric

    About taxes: that Z you're talking about is probably tied to X, and your condition can probably be translated in a min/max condition on X. Can you give me a more precise example. What is Z?

  • Virgil Dupras

    Virgil Dupras June 4th, 2009 @ 04:42 PM

    from Eric

    About the enable/disable button: you make a good point when you say that the user might have put a lot of time in his calculation. On the other hand, we have a undo/redo system. Isn't this enough if the user makes a mistake? In any case, reconciled entries cannot be calculated, that's true.

  • Virgil Dupras

    Virgil Dupras June 4th, 2009 @ 04:43 PM

    In the taxes example, Z is different from X. So what we want to be able to say is add a "(sum of income - 9000) * 24%" (X) if "sum of income" (Z) > "9000" (Y).

    Yes, Z is related to Y, and we still can make a calculated transaction work through twisting parameters of the transactions, but it couldn't look right. So I have a transactions for my second "tax stage" that says "(sum of income - 9000) * 24%" min: 2160.

    It just doesn't seem right to me.

    Besides, the other things I wrote about still apply, and they just won't work with just a min/max. You can't say "add a 7.95 service charge if my account ever goes under 1000 in the month" with just min/max.

    About enable/disable/undo. Well, so when a split is added (because normally, the split table should be empty when the transaction is calculated, or should the calculation be split based? hum, yeah, how do we make this fit with the splits???) Anyway, the undo will only be recorder when the user saves the transaction panel. if he does the mistake of adding a split, we wouldn't want to clear the calculations he just designed. But anyway, that's another thing to define: Where does the calculation interface fit, split-wise? How does the user define how the calculated amount will be transfered to other account(s)?

  • Virgil Dupras

    Virgil Dupras June 4th, 2009 @ 04:43 PM

    from Eric

    Oh well... service charges. We need to stop somewhere. I don't feel it's that useful to calculate service charges. Moreover, service charges on a bank account are sometimes based on the number of transactions of a certain type... I don't think we want to go there.

    Credit card interest could be interesting, but it just seems so complicated. As we discussed before, you can either try to pay your credit card entirely every month, in which case you don't need a calculated transaction, or decide that you keep some amount on your credit card, in which case, you can get a pretty good estimate of the interest you'll incur with the calculated transactions we described up to now.

    Now, splits make the matter worse, you're absolutely right. We have to do more thinking on this.

  • Virgil Dupras

    Virgil Dupras June 4th, 2009 @ 04:44 PM

    Well.... I like the idea of being able to compute the service charges. My US bank charges 7.95 (that's big) if I don't make at least one direct deposit per month in it (whatever the amount). I'd like to be able to say "oh, that recurrent calculated entry is still upcoming? ah, gotta make a deposit". But then, it's just a tiny use case.

    Ok, so we gotta stop somewhere, but we also gotta start somewhere then! So far, what are our use cases:

    • Accounts with interests
    • Taxes

    The accounts with interests could be covered by just a "Interest" property in the account panel. So, are the account taxes worth this whole complicated thing? Yeah, I know, "letting the user do whatever calculations he wants" would be cool, but it's not compatible with "We need to stop somewhere". I think that a simple min/max scheme is too limiting for us to be able to say "You can do whatever calculations you want". Is there anything else than interest and taxes (taxes by using a twisted scheme using mix/max) that can be achieved by using the latest proposal?

    Anyway, we still have to rethink the whole thing by including splits in the picture, but I think we should also include a better conditional aspect to the feature.

  • Virgil Dupras

    Virgil Dupras June 4th, 2009 @ 04:45 PM

    Here is something:

    What about having a separate "variable definition" window, which wouldn't be tied to a transaction or anything. Then, those variables could be used in amount expressions. Of course, one problem that arise is that amount fields are usually not large enough to put big expressions in it, but when editing such field, we could add a "..." button that would summon an "expression editor" where the field is larger and the defined variables are listed.

    The variable would still be defined using the GUI editor we talked about, but the condition min/max would be changed to an expression, which could use other defined variables.

    This way, someone who wants to compute taxes would define "taxable_income". Then, to calculate his taxes for the

    This way, he could build upon the complexity of his other definition to build a truly complex system without being lost in big GUI mess and he would be able to do whichever calculations he needs.

  • Virgil Dupras

    Virgil Dupras June 4th, 2009 @ 04:46 PM

    Ah, the splits, I was forgetting about it. Well, I think that we should have a special variable that means "balance the rest in here". This way, it is possible to add a few other splits with constant values here and there, and still have a transaction that balance all the time without automatically creating a new "Unassigned" split.

    Maybe we could add a way to do something like "put 50% of the calculated value here" instead...

  • Virgil Dupras

    Virgil Dupras June 4th, 2009 @ 04:47 PM

    Ok, so after a little iChat discussion, we agreed that we would stay with the "GUI-all-the-way" way, but in a "variable" fashion. Here's a new proposition:

    We would have a new view, the "Variables" view where it's possible define variable. The variable definition window would look like what was proposed earlier, but it would be possible to use previously defined variables as well. To use my previous tax example, my "income taxes" variable could look like this (I put [] to show the presence of a drop-down):

    [+] [100%] of [first_level_taxes] [this year] (delete)
    [+] [100%] of [second_level_taxes] [this year] (delete)
    [+] [100%] of [third_level_taxes] [this year] (delete)
    [+] [100%] of [fourth_level_taxes] [this year] (delete)

    And my second_level_taxes would look like:

    [+] [24%] of [taxable_income] [last year] (delete)
    [taxable_income] [is between] |9000| |24000|

    Something like a service charge would be:

    7.95 (delete)
    [sum_of_deposits] [is higher than] |0|

    Amount could not contain expressions themselves, they could just contain a variable. I think that instead of a "..." button, it should be an arrow or something, and when you click on it, you have a popup with all defined variables, and a "new variable" item. that allows you to define one on-the-fly.

    There's a little problem though regarding the display of the current value in date range that you talked about. I think it should be possible to define variable that are account dependent, like "sum_of_deposits". It would then be impossible to display their values in the Variable Definition window.

  • Virgil Dupras

    Virgil Dupras June 4th, 2009 @ 04:47 PM

    from Eric

    Looks good, except I would make variables always date range dependent and account independent. So all our examples stay as they are, except we ditch "last year", "last month", etc. and statistics are always relative to an account. For example, for your service charges, you would create a variable "Sum of deposits at My Bank" and this variable would be defined as the "sum of debits" in the "My bank" account.

    In the variables display, the value would always be relative to the current date range. So, if I want to see my taxable income last year, I look at my "taxable income" variable last year. If the "last year" date range was included in its definition, I would have to look at it in 2008 to have the value in 2007...

    When using the variable in a transaction amount, you would have to give a date range too. The date range would default to the last recurrence period for recurrent transactions and to... I don't know... last month? for non-recurrent transactions.

  • Virgil Dupras

    Virgil Dupras June 4th, 2009 @ 04:48 PM

    • Tag changed from big, design to big, design, from-trac
  • Virgil Dupras

    Virgil Dupras June 8th, 2009 @ 05:46 PM

    (from [17]) branch v1.5: Backported [9] and [13] (test unit re-organization).

  • Virgil Dupras

    Virgil Dupras September 5th, 2009 @ 07:10 PM

    • State changed from “new” to “accepted”
    • Tag changed from big, design, from-trac to big, feature, from-trac

    [not-tagged:"design" state:"accepted" tagged:"feature" bulk edit command]

  • Randy Becker

    Randy Becker May 28th, 2010 @ 02:23 PM

    I used to think symbolic calculated transactions would be awesome, but now I'm not so sure. As was mentioned, this can get really complex, really quickly, and you have to stop somewhere, especially if you're building a GUI to handle it. Even without a GUI, this starts to look like implementing a generalized spreadsheet. At that point, you might as well create an API.

    Instead, I think it would be better to just solve specific use cases. Implementing interest calculations, for example, would solve a substantial subset of the use cases discussed. Indeed, balance transfers are a subset of interest calculations: the first date in the date range is always the date of the oldest transaction, and the interest rate is always 100%. Offsets can already be implemented by a separate line item in a split transaction.

  • Virgil Dupras

    Virgil Dupras June 1st, 2010 @ 01:51 PM

    I had came to pretty much the same conclusions. I'll start with interests, and then, if I come to ever implementing calculated transactions, the interest implementation can be integrated into the new system.

  • Virgil Dupras

    Virgil Dupras June 7th, 2011 @ 02:17 PM

    • Tag changed from big, feature, from-trac to big, feature
    • Milestone order changed from “0” to “0”

Please Sign in or create a free account to add a new ticket.

With your very own profile, you can contribute to projects, track your activity, watch tickets, receive and update tickets through your email and much more.

New-ticket Create new ticket

Create your profile

Help contribute to this project by taking a few moments to create your personal profile. Create your profile ยป

Shared Ticket Bins