The price database contains only one exchange rate per day. All amounts and values are rounded to the commodity’s minimum fraction, which is 1 for JPY and .01 for USD.
Each split in a transaction has an amount in the split’s account commodity and a value in the transaction currency. The transaction currency is determined by the account of the register in which the transaction is created, even if that account isn’t used in any of the splits. Only the amount and value are stored. Price is calculated when necessary as the ratio amount/value and is rounded only as needed to be represented by a ratio of two 64-bit integers. Consequently for best results enter the correct amount in the register and the value in the exchange rate dialog and let GnuCash calculate the price/exchange rate. Regards, John Ralls > On Feb 9, 2025, at 4:36 AM, Bo Buckley <topherbuck...@gmail.com> wrote: > > Version: 5.9 > Build ID: Flathub 5.9 > Finance::Quote: 1.63 > > I recently tried to add a JPY checking account to my books, and import all > the transactions from 2024. Following along with the docs, I was able to > get most transactions to look as expected: > > https://lists.gnucash.org/docs/C/gnucash-guide/currency_trading_accts.html > > But I noticed all smaller transactions (e.g. wire fees, interest, etc.) > were all being converted incorrectly. To repeat what I am seeing, follow > these steps: > > 1. Enable Trading Accounts (File -> Properties -> "Use Trading Accounts") > 2. Create JPY account "Assets:Current Assets:JapaneseChecking" > 3. Open the Price Database in a new window for monitoring the next few steps > 4. Import Transactions from a CSV file (see attached test.csv) > 5. Assign transactions to this JapaneseChecking account > 6. In the "Match Transactions" dialog balance them against "Expenses:Bank > Service Charge" (USD account) > 7. Still in the "Match Transactions" dialog, right click with all selected > and "Assign exchange rate". This will initially be associated with the > 10,000 JPY transaction if using my test.csv. The first assigned Exchange > Rate will be blank, so "Fetch Rate" (assuming you have your Alpha Vantage > API Key set...I found that quite difficult to discover as the cause of this > silently failing btw). > 8. Assuming Fetch Rate succeeds you should now see a new entry in the > "Price Database" under Currencies:JPY for: Date:{Today's Date}, Source: > Finance::Quote, Type:last, Price:0.0066. > 9. Press OK back in the "Transfer Funds" window. You will now see a new > entry in the Price Database with: Date 1/29/2025, Source:user:price, > Type:transaction, Price:0.0066 (I would have expected "Fetch Rate" to fetch > the rate listed under "Date" in the Transfer Funds window, not the most > recent rate, but that's another issue). > 10. The next transaction (5000 JPY) will now occupy the "Transfer Funds" > window with the previously used Exchange Rate of 0.0066. Press OK > 11. The next transaction (2000 JPY) will now occupy the "Transfer Funds" > window with the previously used Exchange Rate of 0.0066. Press OK. You will > now start to see the issue. In the Price Database, the previous entry for > is replaced with Price:0.0065 instead of 0.0066. > 12. Keep going, it gets worse. The next transaction (1000 JPY) will now > occupy the "Transfer Funds" window with the previously used Exchange Rate > of 0.0065. Press OK. Now the Price Database entry is replaced with 0.007 > 13. Keep repeating and you will see the following: > > Date Description Transaction Amount ExchangeRateGivenInTransferFundsDialog > ExchangeRateRecordedInPriceDatabase > 2025/01/29 Transfer Fee 110 0.0066 0.00909090909090909 > 2025/01/29 Transfer Fee 150 0.0066 0.00666666666666667 > 2025/01/29 Transfer Fee 200 0.0066 0.005 > 2025/01/29 Transfer Fee 300 0.0066 0.00666666666666667 > 2025/01/29 Transfer Fee 500 0.0066 0.006 > 2025/01/29 Transfer Fee 1000 0.0066 0.007 > 2025/01/29 Transfer Fee 2000 0.0066 0.0065 > 2025/01/29 Transfer Fee 5000 0.0066 0.0066 > 2025/01/29 Transfer Fee 10000 0.0066 0.0066 > > My expectation is that whatever is entered into the "Exchange Rate" will be > used and saved into the Price Database. This is not happening. For the > smaller transaction amount (in this example case < 5000 JPY) the Exchange > Rate Recorded in Price Database appears to change on its own accord and not > even linearly. What really stinks is that this all happens silently, so > unless you have the Price Database open and are actively looking at each > new entry, you will be none the wiser to this process completely messing up > the whole Database for Currency:JPY for any dates that had smaller > transactions associated with them. > > My only guess is this has to be something to do with a rounding error. So > two summarizing questions: > > 1. Is this a known bug on bugzilla? I could not find it via a Google > mailinglist search or on bugzilla > 2. Is there a known workaround? > > I attached a test.gnucash file to illustrate this, though I'm not sure > these mailing lists support attachments. > > I would also like to know if there is any better way to streamline > importing a bunch of transactions like this in a foreign currency other > than importing all daily rates from an external source as "user" Price > Database entries via "Import Prices from a CSV file" and pressing OK for > every transaction's "Transfer Funds" dialog. Is there no way to fetch bulk > rates for a year or for all dates listed in the import listed transactions? > > I'm also attaching "test.csv" which is the file I use as a test in the > "Import Transactions from a CSV file". The final two columns are not used > in the import, but used to document the issue. Header names should be self > explanatory. Adding contents of csv below in case this mailing list can't > handle attachments. > > Date,Description,Transaction > Amount,ExchangeRateGivenInTransferFundsDialog,ExchangeRateRecordedInPriceDatabase > 2025/01/29,Transfer Fee,110,0.0066,0.00909090909090909 > 2025/01/29,Transfer Fee,150,0.0066,0.00666666666666667 > 2025/01/29,Transfer Fee,200,0.0066,0.005 > 2025/01/29,Transfer Fee,300,0.0066,0.00666666666666667 > 2025/01/29,Transfer Fee,500,0.0066,0.006 > 2025/01/29,Transfer Fee,1000,0.0066,0.007 > 2025/01/29,Transfer Fee,2000,0.0066,0.0065 > 2025/01/29,Transfer Fee,5000,0.0066,0.0066 > 2025/01/29,Transfer Fee,10000,0.0066,0.0066 > > Thanks in advance! > <test.csv><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. _______________________________________________ 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.
