GNOME Bugzilla – Bug 742795
does not handle security purchase correctly with trading account
Last modified: 2018-06-29 23:37:44 UTC
My books are HKD-dominated, but I also use CNY and buy stocks using CNY, and bugs appear when I enter those transactions with gnucash: demo1.gnucash: I attempted to buy 50 TEST with 50 CNY, so I opened the Stock account, selected the Cash (CNY) account as the counterpart, entered 1 as the price and 50 as the amount. However, my Cash (CNY) balance is not correctly decreased. demo2.gnucash: My action was the same as the above, but I attempted to enter the transaction in a different way. I opened the Stock account, entered split mode and manually selected the splits. The transaction was "Stock Dr 50, Cash (CNY) Cr 50", and the trading splits seemed to generate correctly. However, when I attempted to sell those 50 TEST at 2 CNY / TEST, such that 100 CNY would be received, and entered the transaction the same way, "Cash (CNY) Dr 100, Stock Cr 50", the books went out of balance, and a Trading:CURRENCY:HKD split was mysteriously created, and also an Imbalance-HKD account. However, the transaction did not involve any HKD. demo3.gnucash: My action was the same as demo2.gnucash, however, I entered the transactions in the Cash (CNY) account. An exchange dialogue appeared, and I entered the correct stock price into the box. The transactions handled correctly. Then I converted the CNY back to HKD, and attempted to "realise the gains" by "Trading:CURRENCY:HKD Dr 65.5, Income Cr 65.5". However, the trading split was automatically zeroed out and an imbalance-HKD account was created.
Created attachment 294322 [details] demo1
Created attachment 294323 [details] demo2
Created attachment 294324 [details] demo3
Created attachment 294371 [details] Demo 3 with the gains correctly accounted for. Demo 3 is the correct way to handle the stock transaction because it's the only way to over-ride the default currency. Your attempt to realize the gains went off the rails because you tried to do it from the HKD trading account, which is the wrong place. I got the realized gain to work by using the Lots feature: Select Actions>View Lots... from the stock account and click the "scrub lots" button. This created an almost-correct transaction with CNY50 in Orphaned Gains-CNY. Obviously we want the value in Income and denominated in HKD, so I changed the account to Income, changed the amount to 40, and set the price at 1.25. The result is attached as demo4.gnucash. The all-in-one-transaction approach for realized gains doesn't work with trading accounts because GnuCash isn't smart enough to handle more that two trading accounts in a single transaction.
Created attachment 294373 [details] Hand-created G/L transaction For this one I went to the stock account and created the G/L transaction by hand. I did it directly as HK$40 directly, that being the default currency. The disadvantage to this method is that the two transactions aren't tied.
I forgot to mentioned that, when I converted the CNY back to HKD in demo3, the exchange rate was changed to 1.28, but I couldn't "realise the gains" to the Income account from Trading account, as shown in demo4. I could change the Trading account type to income, transfer those money to Income account to clear out the Trading account. However, if the account type is converted back to trading and do a "check and repair all", the system will override my manual entry and create an imbalance account. Moreover, if I did not change back the account type and set the trading account type to income, doing a "check and repair all" did not affect the gain-realising transaction. Attached is demo6. You can first check and repair all, and change the trading account type and do another check and repair all to see the difference
Created attachment 294385 [details] demo6 changed trading account type to income and realised the gain
Again, don't mess with the trading accounts. They're there to separate the exchange part of the transaction from the rest and they're under GnuCash's control. If you operate on them manually you'll just mess things up. Note that they're also optional and that GnuCash handles most multi-commodity transactions correctly without them. The realized gain transaction (or split pair in the sell transaction if the stock sale and income account are in the same currency, see below) should be between the stock account and the income account. Here's another way to work around the two-commodity rule: Create a CNY-denominated income subaccount and send the realized gain there, then transfer to a HKD-denominated income subaccount to fix the exchange rate to the transaction date.
Gnucash does not handle multi-commodity transaction correctly because it allows the books to go out of balance, unless the trading account feature is switched on. However, If I buy some currency and sell them at a different exchange rate, I need a way to realise the gain.
(In reply to comment #4) I haven't looked at the problems discussed here in detail, but GnuCash does handle more than two trading accounts in one transaction. I happen to have entered several such transactions last week. For example I paid AUD to get some SBD currency and was charged a fee which I recorded in my base currency which is USD. This produced a transaction with 6 splits, three of the trading account splits.
(In reply to comment #10) > (In reply to comment #4) > > I haven't looked at the problems discussed here in detail, but GnuCash does > handle more than two trading accounts in one transaction. I happen to have > entered several such transactions last week. For example I paid AUD to get > some SBD currency and was charged a fee which I recorded in my base currency > which is USD. This produced a transaction with 6 splits, three of the trading > account splits. The problem here is booking the gain or loss, and if it's supposed to work then it's the bug that needs to be fixed. Introducing a third currency/commodity breaks the empty-amount gain/loss split in the stock account. Perhaps the difference lies in the operation of a commodity register where the amount and price are displayed in the register and a currency register where the only access to price or amount is via the transfer dialog?
Created attachment 294622 [details] Sample with consistent currencies I think I've figured out what's going on here and how to make it do what I think you want without too much trouble. First a bit of background just to make sure we all understand how things work. Most of the people reading this will already know all of this, but jut to be explicit I'll repeat it. Every account has an associated commodity (which may be a currency, currencies are a special case of commodities). Most registers are associated with an account. Every transaction has an associated currency which is set to the currency of the account associated with the register in which it is entered. If that account has a non-currency commodity, the currency of the nearest ancestor account which has a commodity that is a currency is used (or the default currency if no ancestor account has a commodity which is a currency). Each split in a transaction is associated with an account and has an amount in that account's commodity (which needn't be a currency) and a value in the transaction's currency. Amounts are in the split's commodity and values are in the transaction's currency. The price (or exchange rate) is the ratio of these two. If using trading accounts, a transaction is balanced if three things are true: 1. The sum of the value of all non-trading account splits is zero 2. The sum of the value of all trading account splits is zero 3. For each commodity used in a split, the sum the amount for the splits in that commodity is zero In the simple case of all splits having an amount in the transaction's currency, this degenerates to making sure that the sum of the values of the splits is zero. If you enter an unbalanced transaction GnuCash will add or change imbalance or trading account splits to make it balanced. It won't change any other splits. If you are trading a stock in CNY then the stock account should be a child of an asset account denominated in CNY. That way when you open a register for the stock account transactions will have a currency of CNY by default. In demo5 this isn't true (the parent account is in HKD) and as a result some transactions with splits in the account are in CNY and some are in HKD. This confuses things, especially when creating the gains split. In the attached demo6 I created a new Stock2 account that is a child of a CNY account so that transactions entered in a register for that account will have a currency of CNY. Then I entered a buy and a sell transaction in that register. These are very similar to the buy and sell transactions in demo5. For some reason those transactions in demo5 use CNY for a currency (they must have been initially entered in a register for a CNY account). In demo6 they are CNY transactions because that's the default currency for transactions entered in this register. I then scrubbed the lots in that account and this is where things were different from demo5. Since the Stock2 account is a child of a CNY account, the realized gain transaction is a CNY transaction. Since you wanted the gain in HKD I manually added two more splits to this transaction to debit Gains-CNY and credit Gains-HKD. After it was balanced this transaction has 7 splits including three trading splits (one of which has value and amount both zero and could be deleted). The Gains-CNY account probably shouldn't exist, but if you do things this way it will always have a balance of zero so it may not matter too much. You could probably delete the two splits in this account and then the account itself, but it isn't necessary and might cause trouble later. GnuCash could have better support for this use case (trading commodities in a currency other than the base currency), but it isn't too hard to make it work.
I believe that, when trading account feature is on, - The currency register window should be used for all commodities, i.e. for all currencies and non-currencies. The price and quantity columns should not appear. (Imagine the scenario of buying some gold with a mixture of HKD and silver, in this case it is cumbersome and unnecessary to calculate the money amount of everything, unless you want to calculate a gain at that time). - There should not be an "associated currency" for a transaction, because there is no way to accurately determine one. You can trade a currency with another, or even trade a security with another without involving any money. - The CURRENCY namespace should not have any special treatment. A currency or a security should be treated the same for any purposes, i.e. you should even be able to generate a balance sheet using "barrels of oil" (or BTC or whatever) as the denomination, given the oil price at the end of the accounting period. To a further extent, there should be a "strict multi-currency double entry accounting" option, for which "use trading account" is a sub-feature of that. The former forces the engine to do multi-currency accounting correctly, i.e. ensure that the transactions are balanced in every currency. To explain, moving 100 USD from Account Bank-USD to 100 CAD to account Bank-CAD, and move those 100 CAD from Bank-CAD back to 120 USD to Bank-USD Current behaviour without trading account (problematic): Bank-CAD 100 CAD Dr Bank-USD 100 USD Cr Bank-USD 120 USD Dr Bank-CAD 100 CAD Cr Now, the 20 USD comes from nowhere, and it is a bug which must be fixed Current behaviour with trading account: Bank-CAD 100 CAD Dr Trading:CURRENCY:CAD 100 CAD Cr Trading:CURRENCY:USD 100 USD Dr Bank-USD 100 USD Cr Bank-USD 120 USD Dr Trading:CURRENCY:USD 120 USD Cr Trading:CURRENCY:CAD 100 CAD Dr Bank-CAD 100 CAD Cr Now, the 20 USD is earned from Trading:CURRENCY:USD, but GnuCash prevent me from moving the gain to a real income account Behaviour with my proposed "strict multi-currency double entry accounting" without trading accounts, which enforces balance in every currency but does not automatically create trading accounts: Bank-CAD 100 CAD Dr Bank-USD 100 USD Cr Imbalance-USD 100 USD Dr Imbalance-CAD 100 CAD Cr Bank-USD 120 USD Dr Bank-CAD 100 CAD Cr Imbalance-CAD 100 CAD Dr Imbalance-USD 120 USD Cr Now, the user can move those 20 USD Cr from the Imbalance-USD account to a real income account, of course, without the automatic help from the software, the user should create an income account specifically for this purpose to calculate the gain/loss of commodity transaction. The "use trading account" feature is only to help the user to automatically create those transactions, but the user should be able to create whatever account hierarchy he like to record the gain/loss.
OK, you're welcome to your opinion, but that's not the way that GnuCash is currently designed. I've changed the bug to an enhancement request to reflect that, as it's demonstrated that while GnuCash doesn't work the way you want it to, it is capable of recording the gain/loss when used as designed. I'm not closing it as won't fix yet because we're doing a rewrite and it might be worthwhile to incorporate your ideas when we get to that stage.
GnuCash bug tracking has moved to a new Bugzilla host. The new URL for this bug is https://bugs.gnucash.org/show_bug.cgi?id=742795. Please continue processing the bug there and please update any external references or bookmarks.