Bo, We got started in this discussion because you had trouble understanding that price isn’t necessarily concrete across splits because of forced rounding of commodities to their minimum fractions.
Clarifications first: You’re right, buying at $0.67/¥100 and spending (selling) at $0.70/¥100 is a gain, not a loss. Your books are out of balance when Assets ≠ Liabilities + Equity. The easiest way to see when that’s the case is with the Trial Balance Report. The trading accounts balances by themselves reflect an imbalance when all of the non-book-currency positions have been closed out. Otherwise gain/loss errors are reflected in differences between the trading account balances and the corresponding asset account balances. The Peter Selinger paper linked in the documentation explains it thoroughly. It might take a bit of study to fully understand it. You’re right that the amounts in the Total column on the accounts page are converted to the book currency using the most recent price from the price database. That’s a feature for people who want to see something resembling what their brokerage account tells them. It’s rather less helpful when trying to use trading accounts to assess whether you’ve booked all of your realized gains and losses. That’s also the case with the single price per day in the price database. Consider the simple case where you transfer $100 to your JPY Checking at ¥14925 (about $0.67/¥100), then use Finanace::Quote to update the exchange rate and it retrieves $0.70/¥100 and overwrites the entry for that day in the price database. The Total column on the accounts page will show that’s worth $104.48, but there’s no change in the book value of the ¥14925: That’s still $100. The $4.46 is an unrealized gain. A few days later you enter a purchase for ¥1500 and fetch the exchange rate from F::Q. It’s about $0.65/¥100 so you book it as $9.75. You neglect to record the trading loss and you again run F::Q later in the day and get some different rate, say $0.72/¥100, overwriting the earlier one from that day when you recorded the transaction. You get to the end of the month and run a trial balance, and it doesn’t because you missed the trading loss in that ¥1500 transaction. The book cost of the ¥1500 was $10.05, the booked expense was $9.75 That difference is an imbalance in your book: A $10.05 reduction in Assets but only a $9.75 reduction in Equity (income and expense are equity accounts). Using the prices in the price database would lead you do a different number and wouldn’t balance your book. In the case of normal investment transactions where you’re directly converting book currency money into shares and back—or even Forex trading where you’re doing the same with other countries' or crypto currencies in place of shares—it’s concrete as I explained it. It gets a bit more complicated in cases like yours where you’re converting USD into JPY and then converting JPY into living expenses because you have a fair amount of wiggle room on the exchange rate that you use to book the expense in USD. You could use the average cost (I don’t know of a way to get GnuCash to tell you what it is, sorry) and have no trading gains or losses. I have no idea if that would be legal, but as a private person it’s unlikely that either the Japanese or American tax authorities would put any effort into checking. Regards, John Ralls > On Feb 26, 2025, at 01:58, Bo Buckley <topherbuck...@gmail.com> wrote: > > Thank you for your response John, but I have to say I've completely lost > sight of the original question, so allow me to summarize from the beginning: > > I want to understand the meaning and underlying usage of the "Total" amount > listed next to "Trading" in the "Accounts" tab. The docs appear to explain it > as a trading loss if positive or a gain if negative. > > You further explained: >> The Accounts page Total column does use the most recent price database entry >> to value each commodity that isn’t the book currency and that will make the >> trading accounts reflect an unrealized gain or loss. >> >> The Totals column on the Accounts page presents the ending balance for each >> account (even if that’s in the future), converted to the book currency using >> the most recent exchange rate available from the account’s commodity to the >> book currency. >> >> The price database has nothing to do with this. Nothing! It’s entirely about >> differences in book-currency value between conversions to and from other >> commodities. You must use the Average Cost price source—that calculates the >> effective price of the non-book-currency commodities based on the >> transactions into and out of each of those currencies—when running the trial >> balance report > > I'm not sure how to resolve the last statement against the first two. They > appear to be conflicting information to me. What am I misunderstanding here? > Are you referring to something other than the Trading Accounts page Total > when you wrote "nothing to do with __this__"? > >> You must book trading gains to keep your book in balance. > > When you say "keep your book in balance" you mean the debit and credits match > in the Trial Balance report, yes? > >> Since both the creation of the JPY opening balance and the transfer from USD >> were on the same day, for accounting purposes the exchange rate is the same: >> 67/10000. Your purchase the following day with ¥1500 at a rate of 70/10000 >> reflects a difference of 3/10000, multiplied by ¥1500 yields a loss of >> $0.45. That third transaction will be >> Checking_jpy. ¥1500 >> Expenses:Groceries $10.50 >> Income:Trading Gains. $0.45 >> Trading:JPY. ¥1500 >> Trading:USD $10.95 > > Given the attached trading_test.gnucash book, I am unable to manually change > the values in the Trading:USD split. If I enter the 70/10000 exchange rate, > it will automatically be reentered as $10.50. In other words, with "Use > Trading Accounts" on, I cannot make the above mentioned transaction. Is the > fundamental point that I'm misunderstanding that one cannot account for > trading gains/losses if "Use Trading Accounts" is turned on?? > > Also I'm still lost as to why you refer to the $0.45 as a loss rather than a > gain. The JPY was "sold" for a higher value than when it was "bought". No? > > As always, thank you for your responses. > > On Fri, Feb 21, 2025 at 2:45 AM John Ralls <jra...@ceridwen.us > <mailto:jra...@ceridwen.us>> wrote: >> Bo, >> >> It doesn’t matter whether or not the gain is taxable. You must book trading >> gains to keep your book in balance. Please study >> https://www.gnucash.org/docs/v5/C/gnucash-guide/chapter_capgain.html for the >> different ways to accomplish that in GnuCash. >> >> One more time: The price database has nothing to do with this. Nothing! It’s >> entirely about differences in book-currency value between conversions to and >> from other commodities. You must use the Average Cost price source—that >> calculates the effective price of the non-book-currency commodities based on >> the transactions into and out of each of those currencies—when running the >> trial balance report. You can run a regular balance sheet, or any other, >> report with that price source if you find it helpful. Just be careful with >> period reports like the Income Statement because the average cost report >> calculates based on all transactions, not just those for the past year. You >> should also be aware that the average cost calculation does not work with >> three-or-more commodity transactions, see >> https://bugs.gnucash.org/show_bug.cgi?id=797796. >> >> Having an opening balance in JPY also will create trouble with calculating >> the trading gain unless the Equity account is in USD because there’s no USD >> value and no trading account splits for it. >> >> Since both the creation of the JPY opening balance and the transfer from USD >> were on the same day, for accounting purposes the exchange rate is the same: >> 67/10000. Your purchase the following day with ¥1500 at a rate of 70/10000 >> reflects a difference of 3/10000, multiplied by ¥1500 yields a loss of >> $0.45. That third transaction will be >> >> Checking_jpy. ¥1500 >> Expenses:Groceries $10.50 >> Income:Trading Gains. $0.45 >> Trading:JPY. ¥1500 >> Trading:USD $10.95 >> >> Regards. >> John Ralls >> >> >>> On Feb 20, 2025, at 00:22, Bo Buckley <topherbuck...@gmail.com >>> <mailto:topherbuck...@gmail.com>> wrote: >>> >>> Dear John, >>> >>> Thank you for the detailed example. Yes, your examples make sense to me, >>> and what I expect to happen, though I'm still not clear on how to actually >>> implement the following in GNUcash: >>> >>>> You need to book that gain as income in order for your book to stay in >>>> balance in USD. >>> >>> In your example, of a $0.45 trading gain, assuming this is a taxable gain, >>> how exactly in GNUcash would I "book that gain as income"? Assuming I >>> classify the income as Income:Other, what would I offset the transaction of >>> $0.45 against for my "book to stay in balance in USD"? I would assume such >>> a transaction would be against the unrealized loss/gain shown on the >>> Trading account, but it appears you cannot offset against this account >>> directly. >>> >>> I've attached an example book that just has only the following transactions: >>> >>> 1. 1/1/2024 10,000 JPY Opening Balance into Assets:Current >>> Assets:checking_jpy >>> 2. 1/1/2024 100 USD Opening Balance into Assets:Current Assets:Checking >>> Account >>> 3. 1/2/2024 Transfer of 100 USD to Assets:Current Assets:checking_jpy with >>> Exchange Rate of 0.0067 for a JPY value of 14,925 >>> >>> With only a single Price Database value for 1/2/2024 the Trading account >>> balance is 0.00 as expected. If I add another entry for 1/4/2024 of 0.007, >>> then the Trading account balance is -4.48 USD. (i.e. 100 - 14,925*0.007). >>> This appears to be an unrealized loss. If the value of JPY increased from >>> 0.0067 USD to 0.007 USD, then shouldn't this be an unrealized gain and not >>> a loss? >>> >>> Then if on 1/4/2024 I enter an expense of 1500 JPY against the >>> Assets:Current Assets:checking_jpy account, the Trading account balance >>> remains at -4.48. How then am I to "book the gain as income" such that it >>> would modify the unrealized loss? >>> >>> Thanks in advance. >>> >>> On Sun, Feb 16, 2025 at 3:38 AM John Ralls <jra...@ceridwen.us >>> <mailto:jra...@ceridwen.us>> wrote: >>>> Bo, >>>> >>>> For this discussion I’m using the term trading gains to refer to both >>>> gains and losses and booking both (as credits and debits respectively) >>>> into an income account. >>>> >>>> As you say, the income occurs when you go the other way. Leaving off >>>> trading accounts for a moment, if you transfer $100 to JPY @$.67/¥100 >>>> there’s no income. When you buy lunch later in the week for ¥1500 and the >>>> exchange rate that day is $.70/¥100 then you have a USD expense of $10.50 >>>> (1500 * .7) but a USD cost of only $10.05 (.67 * 1500) so you have a >>>> trading gain of $.45. You need to book that gain as income in order for >>>> your book to stay in balance in USD. Now you claim that some of those >>>> trading gains are taxable and some aren’t. If that’s true then you need >>>> two accounts to keep them separate so you know how much taxable trading >>>> income to report on your taxes. >>>> >>>> If it’s really that simple then you can use a single set of trading >>>> accounts to track your unreported realized gains. >>>> >>>> But what about JPY income? That has to be booked to a USD-denominated >>>> income account at the day’s exchange rate but when you buy lunch with that >>>> money and book the expense on a different day and exchange rate you have >>>> the appearance of a trading gain that needs to be balanced, but no trade >>>> occurred. At that point I’m in over my head and have to tell you to get >>>> professional advice on both how to do the accounting and how to use >>>> trading accounts to keep track of the trading gains. I can easily imagine >>>> the need for separate sets of trading accounts to ensure that everything >>>> stays in balance in both currencies, but remember that I have neither >>>> experience nor expertise in this. >>>> >>>> Regards, >>>> John Ralls >>>> >>>> >>>>> On Feb 14, 2025, at 19:53, Bo Buckley <topherbuck...@gmail.com >>>>> <mailto:topherbuck...@gmail.com>> wrote: >>>>> >>>>> Dear John, >>>>> >>>>> Thank you for your reply. >>>>> >>>>>> I don’t understand what you mean by “zero this out for today”. >>>>> >>>>> Maybe I misunderstood what you meant when you said, >>>>> >>>>>> If the net gains aren’t taxable then you can book them to a separate >>>>>> non-taxable income account. >>>>> >>>>> Say I'm doing my US reporting, and this transfer from USD to JPY wouldn't >>>>> be taxable (as far as I know, only the opposite would be). Then to "book >>>>> them to a separate non-taxable income account" would involve what >>>>> exactly? Say I had an Income:NonTaxable account, what would the >>>>> transaction look like? I apparently can't offset a debit to >>>>> Income:NonTaxable with either Trading account, so not sure what this >>>>> would look like. >>>>> >>>>> Thanks again. >>>>> >>>>> On Fri, Feb 14, 2025 at 10:38 AM John Ralls <jra...@ceridwen.us >>>>> <mailto:jra...@ceridwen.us>> wrote: >>>>>> Bo, >>>>>> >>>>>> I don’t understand what you mean by “zero this out for today”. >>>>>> >>>>>> The Totals column on the Accounts page presents the ending balance for >>>>>> each account (even if that’s in the future), converted to the book >>>>>> currency using the most recent exchange rate available from the >>>>>> account’s commodity to the book currency. >>>>>> >>>>>> The manually created trading splits will look just like the >>>>>> automatically created ones. The difference will be that you have to >>>>>> create them yourself, selecting the taxable or non-taxable balance. >>>>>> Remember that the trading accounts are outside of the accounting >>>>>> equation and exist to help you keep track of your conversions into and >>>>>> out of commodities so that you book the gains and losses and keep your >>>>>> overall book in balance. The goal of splitting the trading accounts >>>>>> would be to make it clearer which trading gains income account—taxable >>>>>> or not-taxable—needs to be adjusted to balance your book. Mind that I’m >>>>>> not an accountant and even if I was I wouldn’t be *your* accountant. I >>>>>> also have no experience as an ex-pat paying US taxes on income earned in >>>>>> somebody else’s currency. >>>>>> >>>>>> One other thing to be aware of: The register displays currency >>>>>> differently depending on whether trading accounts are enabled: When >>>>>> they’re enabled the debit and credit numbers represent the amount in the >>>>>> split’s account’s commodity; when trading accounts are off the credit >>>>>> and debit numbers are the values in the current register’s account’s >>>>>> currency. In other words with trading accounts off all of the amounts in >>>>>> your JapaneseChecking register will be JPY and in Banking Service Fees >>>>>> they’ll all be USD. >>>>>> >>>>>> Regards, >>>>>> John Ralls >>>>>> >>>>>> >>>>>>> On Feb 13, 2025, at 16:39, Bo Buckley <topherbuck...@gmail.com >>>>>>> <mailto:topherbuck...@gmail.com>> wrote: >>>>>>> >>>>>>> Thank you for your reply John, >>>>>>> >>>>>>> >If the net gains aren’t taxable then you can book them to a separate >>>>>>> >non-taxable income account. >>>>>>> >>>>>>> Even if I zero this out for today as an example, won't the Trading >>>>>>> account balance continue to fluctuate even after doing so as new price >>>>>>> entries come on day to day? I'm trying to understand how to finalize >>>>>>> the transaction in the same way I would wrap up a GOOG stock sale (i.e. >>>>>>> I would never expect to keep tracking unrealized gains/losses on the >>>>>>> idea that the USD received from the stock sale is waiting to be "sold" >>>>>>> to return back to GOOG. >>>>>>> >>>>>>> >GnuCash also can’t automatically handle multiple trading accounts per >>>>>>> >commodity. If you need that you’ll have to turn off trading accounts >>>>>>> >in File>Properties and manage the trading accounts and splits manually. >>>>>>> >>>>>>> Assuming I turn it off, do you have an example transaction to help me >>>>>>> understand what you're proposing here? I'm not sure I understand what >>>>>>> this would solve. >>>>>>> >>>>>>> Thanks in advance. >>>>>>> >>>>>>> >>>>>>> >>>>>>> On Fri, Feb 14, 2025, 02:37 John Ralls <jra...@ceridwen.us >>>>>>> <mailto:jra...@ceridwen.us>> wrote: >>>>>>>> >>>>>>>> >>>>>>>> > On Feb 13, 2025, at 01:19, Bo Buckley <topherbuck...@gmail.com >>>>>>>> > <mailto:topherbuck...@gmail.com>> wrote: >>>>>>>> > >>>>>>>> > In the foreign currency docs: >>>>>>>> > https://gnucash.org/docs/v5/C/gnucash-guide/currency_trading_accts.html >>>>>>>> > >>>>>>>> > The Trading and CURRENCY placeholder accounts now indicate a modest >>>>>>>> >> realized loss of 0.82 USD on the currency transactions. >>>>>>>> > >>>>>>>> > >>>>>>>> > it appears to explain that the Trading top-most account balance >>>>>>>> > represents >>>>>>>> > a loss if positive or a gain if negative. I only have a single >>>>>>>> > transaction >>>>>>>> > so far that involves converting USD to JPY for a transfer. See >>>>>>>> > attached for >>>>>>>> > the transaction. The Trading account already shows a balance of >>>>>>>> > 247.38 USD. >>>>>>>> > How does this make sense? I didn't lose money on the transfer (other >>>>>>>> > than >>>>>>>> > the fee). It appears this balance is calculated based on the most >>>>>>>> > recent >>>>>>>> > Price Database entry for the currency (i.e. JPY at 0.0066 for >>>>>>>> > 02/09/2025). : >>>>>>>> > 4,964.32 - (714,688* 0.0066) = 247.38. >>>>>>>> > >>>>>>>> > It seems to be interpreting a currency exchange from 1/9/2024 as a >>>>>>>> > loss >>>>>>>> > even though at the time of the trade it was not a loss. How am I to >>>>>>>> > interpret this and what is this Trading Balance used for elsewhere? >>>>>>>> > I don't >>>>>>>> > want it unintentionally affecting some other report calculations. >>>>>>>> > >>>>>>>> > For the sake of clarification, lets compare this behavior to stock >>>>>>>> > trading. >>>>>>>> > USD and JPY are two commodities, just like USD and GOOG. For my >>>>>>>> > example >>>>>>>> > transfer from USD to JPY, the Trading Account balance loss seems to >>>>>>>> > be >>>>>>>> > similar to an unrealized loss if I interpret the transfer >>>>>>>> > transaction as >>>>>>>> > buying JPY from USD with the intent to someday convert back to USD. >>>>>>>> > This is >>>>>>>> > similar to buying GOOG from USD and if GOOG dropped in values since >>>>>>>> > buying. >>>>>>>> > But why is it not interpreted the opposite way, i.e. I sold USD to >>>>>>>> > get back >>>>>>>> > JPY (or I sold GOOG to get back JPY for the stock analogy)? I want >>>>>>>> > to make >>>>>>>> > sure GNUcash is not unknowingly treating the correct transactions as >>>>>>>> > taxable events and not the opposite. I.e. for Japanese tax reporting >>>>>>>> > the >>>>>>>> > transfer would represent a taxable event as I "sold USD". The >>>>>>>> > opposite >>>>>>>> > would be true for US tax reporting no? I want to make sure I >>>>>>>> > understand the >>>>>>>> > implications of this balance. >>>>>>>> >>>>>>>> Bo, >>>>>>>> >>>>>>>> The Accounts page Total column does use the most recent price database >>>>>>>> entry to value each commodity that isn’t the book currency and that >>>>>>>> will make the trading accounts reflect an unrealized gain or loss. >>>>>>>> >>>>>>>> US GAAP and IAS require all foreign currency transactions to be valued >>>>>>>> at the time of the transaction in the book currency, and doing so >>>>>>>> inevitably creates trading gains an losses that must be accounted for >>>>>>>> to keep the books in balance. If the net gains aren’t taxable then you >>>>>>>> can book them to a separate non-taxable income account. Keep in mind >>>>>>>> that GnuCash has no way of helping you catch mistakes where you book a >>>>>>>> capital gain or loss to the wrong income ro expense account: It can >>>>>>>> only verify that the accounting equation balances for the whole book. >>>>>>>> GnuCash also can’t automatically handle multiple trading accounts per >>>>>>>> commodity. If you need that you’ll have to turn off trading accounts >>>>>>>> in File>Properties and manage the trading accounts and splits manually. >>>>>>>> >>>>>>>> Regards, >>>>>>>> John Ralls >>>> >>> <trading_test.gnucash> >> > <trading_test.gnucash> _______________________________________________ gnucash-user mailing list gnucash-user@gnucash.org To update your subscription preferences or to unsubscribe: https://lists.gnucash.org/mailman/listinfo/gnucash-user ----- Please remember to CC this list on all your replies. You can do this by using Reply-To-List or Reply-All.
