Re: [GNC] Exchange Rate to Price Database issue with low transactions amounts
Thank you for your input David, but based on what I'm reading from John it appears the "inaccurate exchange rate" is much more inaccurate on my system than expected by John, so thats what I'm trying to target right now with this issue. I could indeed make a separate JPY account for bank fees, but this would be a domino effect as I would essentially have to duplicate ALL my accounts to have a JPY and USD version, which I'm trying to avoid for a fairly long list of reasons, but yes, it is one workaround. Cheers! On Mon, Feb 10, 2025 at 3:19 PM sunfis...@yahoo.com wrote: > I hesitate to jump in here, since you've already got the best advisor in > John, but... > > You keep entering the exchange rate, and it's been stated clearly before > that the exchange rate is not stored and is not reliable. > > You mentioned in passing previously that you don't know the destination > amount, and also that the JPY fees are entered into a JPY account, which > explains your not knowing how much that turns into in USD. I think the > proper solution here would be to create a separate transaction fees expense > account denominated in JPY, thus obviating the need to rely on an > inaccurate exchange rate altogether. > > I believe that GnuCash can provide USD amounts for these JPY expenses in > the reporting system. > > David T. > > On Feb 10, 2025, 8:18 AM, at 8:18 AM, Bo Buckley > wrote: > >Thank you for another reply John. > > > >What you explain makes sense to me, but it is not the observed behavior > >from my version of GNUCash. If it were the same, I would not have an > >issue. > > > >Let me use your same example values. > > > >You postulated an exchange rate of $0.66/100¥. Suppose you have two > >splits, > >> one for 175¥ and one for 270¥. The first one is $1.155, but there’s > >no > >> such thing as half-pennies in US currency so it gets rounded to > >$1.16. The > >> second is 1.782 and is rounded to $1.78. The *stored numbers* for the > >first > >> split are 175¥ (amount) and $1.16 (value), *implying* a price of > >116/17500 > >> or ~.006685714285714… $/¥, The second split stores an amount of 270 > >and a > >> value of 1.78, *implying* a price of 178/27000 or ~.006592595 > > > > > >Take the following values (Note I leave in two extra examples of 110 > >JPY > >and 20 JPY as the exhibit extreme examples: > >Date Description Transaction Amount > >2025/01/29 Transfer Fee 20 > >2025/01/29 Transfer Fee 110 > >2025/01/29 Transfer Fee 175 > >2025/01/29 Transfer Fee 270 > > > >Within the Transfer Funds Dialog for each, I manually set the Exchange > >Rate > >to 0.0066. As you explained, I would expect that I get > > > >The second split stores an amount of 270 and a value of 1.78, > >*implying* a > >> price of 178/27000 > > > > > >But what I actually get is a price of 1/135, which if brought to a > >common > >denominator would be 200/27000, not 178/27000. Thats over by 200/178 or > >~12% > > > >Moving forward to the 175 JPY case, I again enter 0.0066 as the > >exchange > >rate in the Transfer Funds dialog expecting whatever I specify to be > >used > >instead of any existing entry in the Price Database. Now I would expect > >based on your explanation: > > > >The *stored numbers* for the first split are 175¥ (amount) and $1.16 > >> (value), *implying* a price of 116/17500 > > > > > >But instead, I see the Price Database entry is now updated to have a > >value > >of 1/175, which bringing to the common denominator would be 100/17500 > >not > >116/17500. Thats under by 110/116 or ~ 5%. > > > >This alone should be alerting, but moving forward to the more extreme > >examples brings further alarm. > > > >For the 110 case, I again enter 0.0066 manually for the Exchange Rate, > >and > >would expect based on your explanation that 110 JPY * 0.0066 would give > >0.726 USD then rounded off to 0.73 USD. Or a price of 73/11000, but in > >face > >the Price Database records 1/110 as the Price. Matching denominators > >that > >would be an expected price of 73/11000 vs an actual price of 100/11000. > >Thats over by 100/73 or ~ 37%. > > > >Even more extreme, for the 20 JPY case, entering in 0.0066 for the > >Exchange > >Rate, I would expect from your explanation to see 20*0.0066 = 0.132 USD > >rounded to 0.13 USD, with a price of 13/2000, but instead the Price > >Database records 0. Not an ratio of 64 bit ints. Not a decimal > >representation, but flat out 0. The Match Transaction Dialog, even > >after > >pressing OK in the Transfer Funds Dialog, will still give an error > >"UNBALANCE (need price to transfer JPY-20 to acct Expenses:Bank Service > >Charge)!" ... i.e. the same error as prior to setting the exchange > >rate. > > > >If I then press apply and close, and view the JapaneseChecking account > >(see > >attached), You will see all the results I described above. I'm also > >attaching the test.gnucash file that was used to generate these > >numbers. > > > >Looking forward to your reply and thanks again. > > > >On Mon, Feb 10, 2025 at 1:25 PM
Re: [GNC] Exchange Rate to Price Database issue with low transactions amounts
Dear John, Thanks for your time again. I uploaded the video, test.gnucash and the test.csv file used to import the example transactions to my Network Accesible Storage. You should have public access here: https://gofile.me/7Ad2L/z8EsrYEzD Let me know if that also fails for some reason and I'll upload to Youtube or something :D. Are you using the Transfer Dialog rather than the register to create > transactions? Yes and no. I'm importing the test.csv and when right-clicking to "Add Exchange Rate" the Transfer Dialog comes up (hopefully this will be clearer from the video). The transactions only actually generate after finishing the import. To your question about which box is on the left, it depends on the > setting/Preference Accounts>Use formal accounting labels. If that’s turned > on, debit is on the left as it should be. If it’s turned off Transfer from > is on the left and for debit-balance accounts that means credits is on the > left. Ah, no I meant assuming it is on (i.e. I see Debit and Credit) do you see USD as the Debit account or JPY? Thanks again in advance. On Tue, Feb 11, 2025 at 1:48 PM John Ralls wrote: > Bo, > > The video didn’t make it. I just get a black rectangle shown in the screen > shot at the bottom. It’s not active and I don’t see any representation of > it in the source. It came directly to me from Google so it wasn’t the list > that stripped in out, but I don’t see any clues in the headers suggesting > which agent did. > > Sounds like you made a test account. How about attaching that? Or you > might prefer to open a bug report. Attachments in Bugzilla are a bit more > reliable than those on emails. > > Are you using the Transfer Dialog rather than the register to create > transactions? Not that it matters, I get the same results either way. To > your question about which box is on the left, it depends on the > setting/Preference Accounts>Use formal accounting labels. If that’s turned > on, debit is on the left as it should be. If it’s turned off Transfer from > is on the left and for debit-balance accounts that means credits is on the > left. But getting that wrong will change the currency that you enter in the > Amount field at the top. Instead of entering 20¥ you’d enter $0.13. > > The default currency account is only a default for generating reports and > creating accounts. You can override it. You are correct that you can’t > change the minimum fraction on currencies. Those are hard-coded into the > program. > > Regards, > John Ralls > > ___ 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.
Re: [GNC] Batch transfer from one expense category to another.
I'll second what David T. mentioned below. Cut-&-Paste using CTRL-X combo seems to go much faster than exporting-modifying-importing cycles. Just note that you will have to highlight the transfer account name before pressing CTRL-C to capture it to the clipboard in order to paste it. If you are on Windoze, you may want to look at nifty little open-source utility called Hotkeys from https://www.autohotkey.com/ ... and specifically the "How to Send Keystrokes" with a single hotkey ... -Original Message- From: sunfis...@yahoo.com Sent: Tuesday, February 11, 2025 4:21 AM To: Geoff Cc: Adrian Holbrook ; gnucash-user@gnucash.org Subject: Re: [GNC] Batch transfer from one expense category to another. I'd go a different route. In your register window sorted by description so the following: 1) Edit one transaction with the new account, and before leaving, CTRL-C the complete transfer account name. Save the transaction. It will disappear from the register. 2) I'm the next transaction, highlight the transfer field, press CTRL-V to paste the new account name, and press enter several times to commit the transaction. Repeat step 2 until all phone transactions are gone from the register. Although it sounds odious, this process can go pretty quickly. David T. On Feb 11, 2025, 11:18 AM, at 11:18 AM, Geoff wrote: >Hi Adrian > >Unless you are comfortable saving your data into an SQL database (eg >SQLite) and doing manipulation using SQL queries, your suggestion of >exporting, manipulating, and reimporting is the best solution. > >I suggest you: >(a) Take a backup >(b) Export all transactions in your Utilities Account >(c) Rename the Account to Utilities-OLD >(d) Import your modified transactions >(e) Check that Utilities + Phone = Utilities-OLD >(f) Delete the Utilities-OLD Account. > >Regards > >Geoff >= > >On 11/02/2025 6:47 pm, Adrian Holbrook wrote: >> I have a number of entries that I have classified under the expense >category "Utilities". I now wish to remove all my phone payments from >that category and have a new category for "Phone". The description for >all these entries is the same so I have sorted by description and am >laboriously going through 20 years of records to transfer each one >individually. Is there a way of doing this as a batch process? I have >thought of exporting the transactions as a CSV file, importing into >Excel, filtering them and editing the transactions and importing back >into 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. ___ 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.
Re: [GNC] Batch transfer from one expense category to another.
On Tue, 11 Feb 2025 12:21:20 +0300 sunfish62--- via gnucash-user wrote: > I'd go a different route. In your register window sorted by > description so the following: > > 1) Edit one transaction with the new account, and before leaving, > CTRL-C the complete transfer account name. Save the transaction. It > will disappear from the register. > > 2) I'm the next transaction, highlight the transfer field, press > CTRL-V to paste the new account name, and press enter several times > to commit the transaction. > > Repeat step 2 until all phone transactions are gone from the > register. > > Although it sounds odious, this process can go pretty quickly. > > David T. I agree. Watch a video or listen to some agreeable music while you punch the keys. Liz ___ 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.
Re: [GNC] Batch transfer from one expense category to another.
That said I was able to modify the Import matcher window to allow multiple selections using the CTRL and Shift keys and then assign a selected transfer account to the selection of multiple transactions. I remember looking at the registers at the time to see if it could be easily implemented there, but if I remember correctly at the time the code for the registers (single line ledger, autosplit,transaction journal modes in particular) somehow made doing that difficult. It was distinctly easier in the import window because the account being imported into was fixed. It is less clear in the case of the register which split, particularly where there are three or more splits to a transaction. The selection to which the change of transfer account would have to be of specific splits in each transaction to which another account is to be assigned to which makes the code a lot more complex than in the case of the import matcher window. Also the autosplit mode only displays the split entries for the currently selected transaction and the split lines are added dynamically if my memory still serves me, which would make checking that the selection does not include splits you don't want to change very difficult. It may be worth taking a second look however as this does come up every now and then On Wed, 2025-02-12 at 07:40 +1100, Liz wrote: > On Tue, 11 Feb 2025 12:21:20 +0300 > sunfish62--- via gnucash-user wrote: > > > I'd go a different route. In your register window sorted by > > description so the following: > > > > 1) Edit one transaction with the new account, and before leaving, > > CTRL-C the complete transfer account name. Save the transaction. It > > will disappear from the register. > > > > 2) I'm the next transaction, highlight the transfer field, press > > CTRL-V to paste the new account name, and press enter several times > > to commit the transaction. > > > > Repeat step 2 until all phone transactions are gone from the > > register. > > > > Although it sounds odious, this process can go pretty quickly. > > > > David T. > > I agree. Watch a video or listen to some agreeable music while you > punch the keys. > > Liz > ___ > 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.
Re: [GNC] Exchange Rate to Price Database issue with low transactions amounts
Bo: On 2025-02-11 04:30, Bo Buckley wrote: Dear John, Thanks for your time again. I uploaded the video, test.gnucash and the test.csv file used to import the example transactions to my Network Accesible Storage. You should have public access here: https://gofile.me/7Ad2L/z8EsrYEzD I was able to watch your "output.mp4" video. Good idea to post it! It was more informative than I expected. I have three observations from watching the video: 1. Your interaction appears to be: a) Transfer Funds dialogue appears, b) you type in an exchange rate of 0.0066, c) you press the OK button on the Transfer Funds dialogue. 2. At the moment between b) and c), there is a difference between the value in the Exchange Rate field, and the equation just to its right. e.g. at 0'49" in the video, the Exchange Rate field reads "0.0066", and the equation reads "1 JPY = 0.009091 USD". This is an interesting contradiction. I would expect the value in the Exchange Rate field to replace the previous value in the equation. 3. The result of importing the transactions e.g. at 0'56" in the video is that the charges to the Expenses:Bank Service Charge account are whole numbers of USD. It as if the arithmetic did not realise that the minimum increment for USD is 0.01, and treated it as 1. This might well be evidence of a bug in this part of GnuCash, I'm not sure. It is possible, for example, that when the CSV import kicks off a Transfer Funds dialogue for one transaction, it mistakenly labels the minimum increment of USD with the minimum increment for JPY. For a lot of currencies, the minimum increment in both currencies will be the same, 0.01, so I can imagine this getting overlooked. But one workaround which might be interesting for you to try is to spend a little more time interacting with the Transfer Funds dialogue. After b), you enter an exchange rate in the Exchange Rate field, press tab to move focus out of that field. See if the exchange rate in the equation to the right updates with the Exchange Rate you supplied. Another thing to try is to click the radio button by Debit Amount in the Transfer Funds dialogue, then in that field, enter a formula of charge as a positive number> * 0.0066 . I expect Gnucash can calculate a USD debit amount and put it in that field. If the amount is a whole number of dollars, that is further evidence of a bug. A workaround is not as good as a bug fix, but you can take advantage of it faster, while you wait for a bug fix. Thank you for diligently collecting evidence and for your clear explanations. I hope this helps, —Jim DeLaHunt ___ 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.
Re: [GNC] Batch transfer from one expense category to another.
Hi Adrian Unless you are comfortable saving your data into an SQL database (eg SQLite) and doing manipulation using SQL queries, your suggestion of exporting, manipulating, and reimporting is the best solution. I suggest you: (a) Take a backup (b) Export all transactions in your Utilities Account (c) Rename the Account to Utilities-OLD (d) Import your modified transactions (e) Check that Utilities + Phone = Utilities-OLD (f) Delete the Utilities-OLD Account. Regards Geoff = On 11/02/2025 6:47 pm, Adrian Holbrook wrote: I have a number of entries that I have classified under the expense category "Utilities". I now wish to remove all my phone payments from that category and have a new category for "Phone". The description for all these entries is the same so I have sorted by description and am laboriously going through 20 years of records to transfer each one individually. Is there a way of doing this as a batch process? I have thought of exporting the transactions as a CSV file, importing into Excel, filtering them and editing the transactions and importing back into 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.
Re: [GNC] Batch transfer from one expense category to another.
I'd go a different route. In your register window sorted by description so the following: 1) Edit one transaction with the new account, and before leaving, CTRL-C the complete transfer account name. Save the transaction. It will disappear from the register. 2) I'm the next transaction, highlight the transfer field, press CTRL-V to paste the new account name, and press enter several times to commit the transaction. Repeat step 2 until all phone transactions are gone from the register. Although it sounds odious, this process can go pretty quickly. David T. On Feb 11, 2025, 11:18 AM, at 11:18 AM, Geoff wrote: >Hi Adrian > >Unless you are comfortable saving your data into an SQL database (eg >SQLite) and doing manipulation using SQL queries, your suggestion of >exporting, manipulating, and reimporting is the best solution. > >I suggest you: >(a) Take a backup >(b) Export all transactions in your Utilities Account >(c) Rename the Account to Utilities-OLD >(d) Import your modified transactions >(e) Check that Utilities + Phone = Utilities-OLD >(f) Delete the Utilities-OLD Account. > >Regards > >Geoff >= > >On 11/02/2025 6:47 pm, Adrian Holbrook wrote: >> I have a number of entries that I have classified under the expense >category "Utilities". I now wish to remove all my phone payments from >that category and have a new category for "Phone". The description for >all these entries is the same so I have sorted by description and am >laboriously going through 20 years of records to transfer each one >individually. Is there a way of doing this as a batch process? I have >thought of exporting the transactions as a CSV file, importing into >Excel, filtering them and editing the transactions and importing back >into 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. ___ 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.
Re: [GNC] confusion on new fields in csv import
Op zondag 9 februari 2025 19:18:38 Midden-Europese standaardtijd schreef Steve Butler: > I had to experiment with the fields to find that amount negated worked the > best for me. > > Make a copy of your file and run experiments using the different amount > fields to see what happens. Once you find a setup that works for you, > switch back to your production file and move forward. > ...And save the combination that works for you as a preset, so you only have to do the trial and error step once. Regards, Geert ___ 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.
