GNOME Bugzilla – Bug 732058
Multi-currency transactions with trading accounts from 2.4.13 getting messed up in latest 2.6.3
Last modified: 2018-06-29 23:31:24 UTC
My GnuCash file is sqlite format using trading accounts. Some USD/EUR multicurrency transactions entered in 2.4.13 are having the amounts of one of the two trading accounts changed to the amount of the other. Since this throws the transaction out of balance, GnuCash then adds an additional Imbalance split to rebalance the transaction. Trying to manually rebalance the transaction in the register by fixing the changed amounts of the broken trading account split line to what it was before and removing the imbalance split line is not possible: as soon as I try to submit the transaction, GnuCash changes it back to the broken version it was before. An example for a "good" multi-currency transaction in 2.4.13: Account Decrease Increase Currency ============================================================= Asset account-USD 4,522.34 USD Trading:CURRENCY:EUR 3,379.15 EUR Asset account-EUR 3,379.15 EUR Trading:CURRENCY:USD 4,522.34 USD And this is how it comes up in 2.6.3: Account Decrease Increase Currency ============================================================= Asset account-USD 4,522.34 USD Trading:CURRENCY:EUR 4,522.34 EUR Asset account-EUR 3,379.15 EUR Trading:CURRENCY:USD 4,522.34 USD Imbalance-EUR 1,143.19 EUR Note the wrong amount in the EUR-Trading account and the additional split line to compensate. In the main account overview of 2.4.13, the Imbalance-EUR account has balance 0 and no transaction. As soon as I open the file in 2.6.3, the Imbalance-EUR account changes to a balance unequal to 0, meaning there are splits in there, even before I open any account or transaction. I have two files, a good unmodified copy that has never been touched by 2.6.3 (good.gnucash) and a bad corrupted copy that I saved after opening it in 2.6.3 (bad.gnucash). I ran the following two queries in SQLite Database Browser: select * from transactions t left outer join commodities c on t.currency_guid=c.guid where t.guid='3faa907a4255bd0eee6fa1a5f260aebf'; The results of this query are identical for good.gnucash and bad.gnucash select * from splits s left outer join accounts a on s.account_guid=a.guid where s.tx_guid='3faa907a4255bd0eee6fa1a5f260aebf' order by s.guid; The only two differences between good.gnucash and bad.gnucash for this query are: 1. the additional Imbalance split line in bad.gnucash 2. the changed amounts in both value_num and quantity_num of the broken EUR-Trading account split line that the imbalance split it supposed to compensate for. Some additional information: 1. I also opened the 2.4.13 file in 2.4.15, and there is no problem there, so this was introduced at some point within the 2.6 branch. 2. Reopening bad.gnucash in 2.4.13 show only one corrupted transaction (the one I had tried to manually fix and that GnuCash 2.6.3 had then switched back to its corrupted state. The other corrupted transactions in 2.6.3 that I had not touched are OK again in 2.4.13 This suggests to me that 2.6.3 is breaking something in memory when it is loading the file, but does not write it back into the data structure unless the user touches the transaction. An indication to me that something is wrong with the 2.6.3 code, not the 2.4.13 data file. 3. More info might be found in the gnucash-user mailing list discussion with the same subject as this bug report.
Created attachment 278941 [details] Transaction query for good.gnucash
Created attachment 278942 [details] Transaction query for bad.gnucash
Created attachment 278943 [details] Splits query for good.gnucash
Created attachment 278944 [details] Splits query for bad.gnucash
Another comment: This is happening for several (but by far not all of them) of the multicurrency transactions in my file, but unfortunately I have not found a way to intentionally reproduce this situation. Usually, when I enter those transactions and the conversion dialog pops up, I do not enter an exchange rate, but rather the expected amount of the other currency. Also, I pulled up the "Prices" dialog in the main menu to check what exchange rate GnuCash has stored for USD/EUR on that particular date, and there is no entry there. Neither for USD/EUR nor EUR/USD. The closest exchange rate for those two currencies are a few months older than the transaction date of this transaction.
A hint to what might be going on: 1. There is no exchange rate in the prices for the date of the broken TX 2. Changing the amounts of the split lines and removing the Imbalance line, GnuCash undoes my changes as soon as I try to store them. 3. However, I am able to fix the transaction using "Edit exchange rate" on it and entering the desired "To Amount" in the exchange rate dialog. 4. Checking the Prices dialog again, there now is an entry for that date. So somehow, I created multi-currency transactions in 2.4.13 for which GnuCash did not store exchange rates (at least I definitely did not delete exchange rates in the Prices dialog, so I assume they never got created in the first place) GnuCash 2.4.13 was ok with that and just used the amounts of the various split lines without worrying about exchange rates in the prices repository, but 2.6.3 is not and assumes an exchange rate of 1.
Sorry, the last paragraph of Comment 6 should have been: So somehow, I created multi-currency transactions in 2.4.13 for which GnuCash did not store exchange rates (at least I definitely did not delete exchange rates in the Prices dialog, so I assume they never got created in the first place) GnuCash 2.4.13 was ok with that and just used the amounts of the various split lines without worrying about exchange rates in the prices repository, but 2.6.3 is not and assumes an exchange rate of 1 for one of the 2 Trading Account EUR split line without touching the "Asset Account EUR" split line. Thinking about this, GnuCash should definitely not mess with any of the amounts in case it can not find a price for a given date/currency/commodity combination.
Here is a second scenario that I have not tried: If there is an exchange rate in the prices repository, but a different one than was used when the transaction was created, the same thing will probably happen as well. E.g. assume a transaction was created with an exchange rate of 1.5 and later the user modifies the rate in Prices to 1.3. The existing transaction should remain at 1.5 indefinitely, until the user manually changes that. GnuCash should definitely NEVER EVER modify the amounts of existing lines without explicitly asking the user for permission. Also, exchange rates stored in Prices should be completely irrelevant for any existing transaction. The only time an exchange rate should be looked up and used for a transaction is to get a default suggestion if and when the exchange rate dialog pops up. As soon as a split line has an amount, these amounts implicitly define the exchange rate that is supposed to be used for that transaction, and it should be irrelevant if and which exchange rate exists in Prices.
This has nothing to do with the pricedb, and arises because GnuCash did exactly what you demand: It didn't modify the numbers in the existing splits, even though they were wrong. The problem you're having is the result of a bug fixed in commit 6ed3bfa6a3378dc489dce960312b825781424f9e Author: Mike Alexander <mta@umich.edu> Date: Thu Dec 13 05:13:42 2012 +0000 Better detection of unbalanced transactions with trading accounts. A transaction's value should be balanced independently in trading and non-trading splits to be considered balanced. Incorrect adjustment of exchange rates on existing splits can cause this to be an issue. The way Trading Accounts were set up in 2.4 was wrong, because they didn't encode an exchange into the transaction currency; instead the exchange rate was calculated between the source and destination trading accounts. This worked OK as long as there were only two commodities involved in a transaction (as in your example) but failed when there were more: Consider buying shares on a US or London market with money from your Euro-denominated brokerage account. You wind up with two exchange rates, from the stock into e.g. USD and then from USD to Euro. That's the root of the problem. A possible fix is for the imbalance function to notice that the price in the "foreign" (the one with a currency different from the transaction currency) trading split is 1 and in that special case to assume that the transaction was in balance in the earlier version get the amount from the other trading account split and use that as the value in the "foreign" trading split.
John, when you say the numbers in the original split were wrong, what should they have been based on my original example? Here it is again: Account Decrease Increase Currency ============================================================= Asset account-USD 4,522.34 USD Trading:CURRENCY:EUR 3,379.15 EUR Asset account-EUR 3,379.15 EUR Trading:CURRENCY:USD 4,522.34 USD I am not sure why the mechanism of a "split-line implicit" exchange rate is not working when you have more than two currencies involved. You would have one trading account line for each involved currency and commodity, and you can pair any of those two up to get the respective exchange rate / price. Lastly, should one even care about what the exchange rate was once the transaction is saved? Once you have a transaction that is saved in the DB, you have the absolute amounts of each line, and there should not be any reason for having to reconstruct the exchange rate in the first place. The exchange rate is merely a vehicle to calculate the absolute amount for one currency out of the absolute amount for the other currency, if you don't have that absolute amount yet. Once you have the absolute amounts of each split line, that's what the user sees in the register, and thus you have to assume that's what the user has signed off on. So those amounts should not be changed in the background without telling the user, no matter what...
(In reply to comment #10) > John, > > when you say the numbers in the original split were wrong, what should they > have been based on my original example? Here it is again: [Snip] Wrong part of the example. The problem is in the splits in the database (truncated for clarity) ...,"value_num","value_denom","quantity_num","quantity_denom",...,"name","account_type",... ..."-337915","100","-337915","100",...,"EUR","RECEIVABLE",... ...,"-452234","100","-452234","100",...,"USD","TRADING",..." ...,"452234","100","452234","100",...,"USD","RECEIVABLE",... ...,"337915","100","337915","100",...,"EUR","TRADING",... The USD accounts should instead have e.g., ...,"337915","100","452234","100",...,"USD","RECEIVABLE",... That ratio between the value and the quantity is the exchange rate. You noted that it was wrong in comment 6.
Oh. That's how you implicitly store the exchange rate? Then I see why that only works for two currencies. I would have thought just use the ratio between the USD trading account's value_num and the EUR trading account's value_num. That would work for any number of currencies/commodities. The example I had showed was what I do actually see in the GnuCash application's register view. For me, that's ultimately what counts, because it's the only thing the end-user sees. And that's what I think should never be changed behind the user's back. > You noted that it was wrong in comment 6. No, what I consider wrong is the absolute amount that is displayed in the cash register of version 2.6.3 as per my initial example, regardless of whatever value any of the internal fields in the database or in memory contain. I absolutely don't care about the exchange rate. All I care about are the absolute amounts, because that's what I entered in the very beginning when I created the transaction in the first place. If, when and how GnuCash internally calculates and stores an exchange rate based on those absolute amounts I entered is irrelevant to me as an end-user. I must be able to rely on GnuCash never changing those amounts after they are saved. And especially once they are stored and displayed again the way I entered them, they should not be changed half a year down the road and suddenly be displayed differently by a new version... That's where I am coming from as an end-user.
> I must be able to rely on GnuCash never changing those amounts after they are saved. It won't. The exchange rate is the ratio between the amount and value in a given split (each split has its own exchange rate). Once a split has been entered and committed, it will not be changed unless you do something to cause it to be changed. I think John explained what your problem is. I, too, had several transactions that were wrong and had to be fixed when I added the additional test for balanced transactions. In your transaction all four exchange rates were 1. You needed to fix the USD splits to have the correct exchange rate.
Sorry, I really hate to be a pest, but: > It won't. ... Once a split has been entered and committed, it will not be changed unless you do something to cause it to be changed. Yes it will, and yes, it has! I don't know how to make this any clearer: I did open the file and right away, the IMBALANCE accounts, which used to be 0, now had a non-zero balance, because THE TRADING ACCOUNT SPLIT LINES HAVE BEEN CHANGED IN THE BACKGROUND RIGHT WHEN I OPENED THE FILE, before I even made my first mouse click anywhere! My initial example that shows the line as they were shown in the register should make that very obvious. I understand what John is saying, but the fact remains: 2.6.3 changed my split lines right when it opened the file, and there was nothing I could do to prevent that. Like I said, I don't care about exchange rates being lost, but I do care about previously balanced transactions being thrown out of balance without warning. Unless there is something fundamental that I am missing?!?!
It won't add an Imbalance split unless you do something to cause it to balance the transaction. Just looking at it won't do that. You have to change something in the transaction or use something like "Check and Repair" on it. If you open a register containing unbalanced transactions, they will be marked as such, but not balanced until you touch them. This is certainly true with the XML backend, and I'm 99% sure it's true with the SQL backend too. I just tested this again by creating an unbalanced transaction of this sort in a test file. Opening and closing that file while a register showing the bad transaction is open doesn't change the transaction. If I touch this transaction, then it is balanced by adding an Imbalance split. Your basic misunderstanding is to say the the transactions were "previously balanced". They weren't, but version 2.4 wasn't smart enough to realize that. Version 2.6 is and hence shows them as unbalanced. They never were balanced, but 2.4 didn't tell you that.
Mike, have a look at attachments 3 and 4, in particular at the EUR TRADING split. In attachment 3 [details], it's 337
Darn, clicked the wrong button. Don't follow that link, either. In the third attachment, the value and quantity of that split are both 337915/100, while in the fourth split they're both changed to 455234/100, matching the USD TRADING split.
I never said that GnuCash won't balance that transaction under any circumstances. I only said that it won't balance it unless you do something to it. As you know, GnuCash will not commit an unbalanced transaction. If you begin editing a transaction and don't cancel the edit, then GnuCash will balance the transaction before it commits it. I assume this is what happened to change attachment 3 [details] to attachment 4 [details]. Simply opening a register without ever editing the transaction won't cause it to be balanced. This discussion is a little beside the point. The real issue is that the original transaction was not balanced, but version 2.4 didn't realize that. I'm going to close this bug, if you disagree with my assessment, you can open it again.
(In reply to comment #18) > I never said that GnuCash won't balance that transaction under any > circumstances. I only said that it won't balance it unless you do something to > it. As you know, GnuCash will not commit an unbalanced transaction. If you > begin editing a transaction and don't cancel the edit, then GnuCash will > balance the transaction before it commits it. I assume this is what happened > to change attachment 3 [details] to attachment 4 [details]. Simply opening a register without ever > editing the transaction won't cause it to be balanced. No one said that you had, nor even that the transaction couldn't be balanced. The issue is about whether the way GnuCash is balancing the account is correct; it could just as easily -- perhaps more easily -- change the value of the "foreign" (in this case USD) trading split to match that of the "home" split as to do the reverse. In that case the transaction would balance correctly without needing to add the Imbalance split. That might not be easy if that section of code doesn't know if the out-of-balance transaction is coming from an edit or from a saved transaction. > This discussion is a little beside the point. The real issue is that the > original transaction was not balanced, but version 2.4 didn't realize that. > I'm going to close this bug, if you disagree with my assessment, you can open > it again. I don't think it's beside the point, which is that from the user's perspective a previously-balanced transaction became unbalanced and it appears that GnuCash arbitrarily made it that way by changing the value of the "home" split. A big part of that problem is that the user can't see enough of the transaction to make sense of it; that would be easier if trading accounts showed the quantity and price the way commodity accounts do. It might be that fixing this is too hard and the resolution should be "Won't Fix", but I don't think "Not a Bug" is correct.
Perhaps it could be better, but I'm reluctant to fiddle with it too much. Balancing a transaction in the presence of trading accounts is non-trivial. You need to satisfy several criteria: 1. The overall value of the transaction must be zero. 2. The sum the value of all non-trading splits must be zero. 3. The sum of the amount of all splits in each commodity must be zero. 1 and 2 imply that the sum of the value of all trading splits is also zero. The current algorithm is 1 For each non-trading account split: 1a Add value to imbalance accumulator 1b Find the correspoinding trading split and adjust its value using the exchange rate in this split 2 If unbalanced ignoring trading splits add an imbalance split in transaction currency 3 For each unbalanced commodity in the transaction 3a Find trading account split for this commodity 3b If commodity is not transaction currency calculate value imbalance in this commodity 3c Adjust the amount in the trading split to balance the commodity 3d If commodity is transaction currency set value in trading split to amount (exchange rate 1) 3e If commodity is not transaction currency adjust value in trading split to balance this commodity's value 4 If the transaction's value is still unbalanced look for splits with non-zero value and zero amount and for each find corresponding trading account split and adjust its value by the value of this split 5 If transaction's value is still unbalanced, punt. I think this algorithm produces correct, if not optimal, results with an arbitrary number of splits in an arbitrary number of commodities. This transaction looks like this at the start: ...,"value_num","value_denom","quantity_num","quantity_denom",...,"name","account_type",... ..."-337915","100","-337915","100",...,"EUR","RECEIVABLE",... ...,"-452234","100","-452234","100",...,"USD","TRADING",..." ...,"452234","100","452234","100",...,"USD","RECEIVABLE",... ...,"337915","100","337915","100",...,"EUR","TRADING",... After step 1 the non-trading account imbalance is 1143.19 and no splits are changed. After step 2 the transaction looks like: ...,"value_num","value_denom","quantity_num","quantity_denom",...,"name","account_type",... ..."-337915","100","-337915","100",...,"EUR","RECEIVABLE",... ...,"-452234","100","-452234","100",...,"USD","TRADING",..." ...,"452234","100","452234","100",...,"USD","RECEIVABLE",... ...,"-114319","100","-114319","100",...,"Imbalance-EUR","BANK" ...,"337915","100","337915","100",...,"EUR","TRADING",... At this point the non-trading imbalance is zero, but the commodity imbalance list is EUR: -1143.19 After step 3 the EUR trading account split is adjusted and the transaction is ...,"value_num","value_denom","quantity_num","quantity_denom",...,"name","account_type",... ..."-337915","100","-337915","100",...,"EUR","RECEIVABLE",... ...,"-452234","100","-452234","100",...,"USD","TRADING",..." ...,"452234","100","452234","100",...,"USD","RECEIVABLE",... ...,"-114319","100","-114319","100",...,"Imbalance-EUR","BANK" ...,"452234","100","452234","100",...,"EUR","TRADING",... Step 4 doesn't do anything since the transaction is now balanced. Neither of the "real" splits (in the two RECEIVABLE accounts) were changed by this. This is important, I think. The transaction is balanced entirely by adjusting trading and imbalance account splits. If you want to get rid of the imbalance split you can easily do so by fixing the exchange rate in the USD RECEIVABLE split. Simply open the exchange rate dialog on that split and adjust the value to 3379.15. The imbalance split won't be created if it doesn't exist or its value will be set to zero if it exists, then it can be deleted. You said "I don't think it's beside the point, which is that from the user's perspective a previously-balanced transaction became unbalanced and it appears that GnuCash arbitrarily made it that way by changing the value of the "home" split. A big part of that problem is that the user can't see enough of the transaction to make sense of it; that would be easier if trading accounts showed the quantity and price the way commodity accounts do." I agree entirely with the second part of this. When I implemented trading accounts, I assumed that the Register2 code would be released soon and this would allow you to see both the value and amount in each split if you wanted to. This didn't happen so it's hard to see both. To help a bit, the register shows the amount, not the value, of each split if trading accounts are on. You can see the value by opening the exchange rate dialog for a split. The first port of you comment is where I slightly disagree. The transaction was not balanced previously. It's true that 2.4 didn't detect this, but it was not balanced. The bug is that 2.4 didn't detect this. Also, GnuCash didn't change the value of any non-trading account split in the transaction. Perhaps it should. It could balance the transaction in step 2 by adjusting the value in the non-trading USD split (effectively changing the exchange rate). I'm reluctant to do this since it seems to be an implicit rule that splits entered by the user should not be changed while balancing a transaction. Only imbalance account and trading account splits are changed to balance the transaction, other splits are left alone. Do you think I should change this rule? Doing it in general with an arbitrary number of splits in more than two commodities would be tricky, but we could make a special case where there are only two non-trading splits in different commodities. It would still be a significant change in behavior and might be tricky to get right in all cases. In case anyone wants to look at this code it's in xaccTransScrubImbalance around line 518 in src/engine/Scrub.c.
Hi Mike, > I'm reluctant to do this since it seems to be an implicit rule that splits entered by the user should not be changed while balancing a transaction. Only imbalance account and trading account splits are changed to balance the transaction, other splits are left alone. Do you think I should change this rule? Absolutely positively definitely noooooooooooooooooooooooooooooo :) Here is my end-user perspective: I do not want any of my split lines or account balances to change without my knowledge. Period. From what I have come to understand by now, adding the imbalance lines is basically a "safety pressure release valve". It first seemed to me like a random garbeling of my transactions, which I now understand it is not. Still: it just happened and I didn't know why, and I had a feeling of "I caught it by chance, and who knows what else got messed up that I haven't caught". Knowing what I know now, I can go into the imbalance accounts and take care of whatever transactions are in there. That way, there is one place that holds all the problems. If you want to do any kind of "automagic", you have to do it in a way that you let the user know what is happening for each individual transaction, and that there is a yes/no confirmation before each fix. That way, the user has the ultimate control of what is happening. If you want to, give the lazy ones a "fix all" button, and after that, list all the transactions with a before/after view. I know that's a hell of a lot of work for just the GUI part of it, that's why I am saying alternatively, don't change anything, throw them all into the imbalance accounts, TELL THE USER WHAT HAPPENED (which is an essential step that is missing right now), and let them fix it manually. As an end user, I know that might be a lot of work for me, but at least I know that nothing gets changed in the background without me knowing. People are using this for finances, taxes and even company financials and book-keeping, so they will get very nervous very quickly if they have a feeling something bad happened behind their backs without warning. So no matter which way you go, I would put communication with the user as one of the highest priorities. As soon as you add an imbalance account split line anywhere, tell them "Sorry, we detected one or more problems with out-of-balance transactions which were most likely caused by an earlier version - please check your imbalance accounts, all affected transactions are in there, we have things under control" - something to that effect.
GnuCash already puts up a dialog when you try to commit an unbalanced transaction asking you if it should balance it automatically or let you do it manually. I tried this again with my test file which reproduces the problem that started this discussion and it seems to work fine.
Yes, but that only happens once you have actively found, selected, and tried to modify one of those affected transactions. However, in my case, the imbalance account's balance had changed right upon opening the file. That must have happened when GnuCash was reading all the transactions from the DB into memory (if it does that, but I guess it must, otherwise it would no have noticed the problem with the transaction in the first place). So at that point, it must have figured out that there were imbalanced transactions and added an imbalance account split line to them, which then affected the imbalance account's account total. Maybe not in the DB file, but certainly in memory. So right at that point, it should have popped up the warning.
Created attachment 279868 [details] Sample gnucash sqlite file Please check out this file. Open it in 2.4.13. You will see that it looks perfectly balanced and there is not even an imbalance account in the account structure. Then close it and open it in 2.6.3. You will find that there is an Imbalance account with a non-zero total right after opening the file. So right during the process of loading the GnuCash must have done a bunch of things, among others: - add the imbalance account - add a split line to the transaction And right at that point, it should have popped up the warning "Hey, we had to add imbalance splits to your transactions, bla bla bla". Please note one thing about this file: I was not able to reproduce this by just using GnuCash 2.4.13. I think how this happened in my real-life file is by importing QFX files into a USD account and selecting the EUR account as the other side of the transaction. Somehow, by doing so, I managed to get around entering an exchange rate without even knowing, and thus messing up the transaction split lines in a way that 2.4.13 was OK with it but 2.6.3 is not... So this file is fabricated using SQliteDatabaseBrowser, but it does have exactly the same transaction split line setup as my real-life file, and it does trigger exactly the same behaviour in 2.4.13 and 2.6.3 as my real-life file.
The proximate cause of that problem is fixed in 2.6: The exchange rate is set to 1 instead of 0 if it can't find a valid one. That's better, because it doesn't create splits where you can't edit the exchange rate, but it does mean that after doing an import you need to check your forex transactions and set the correct exchange rate.
GnuCash bug tracking has moved to a new Bugzilla host. This bug has been copied to https://bugs.gnucash.org/show_bug.cgi?id=732058. Please update any external references or bookmarks.