[GNC] Account Currency Screwed Up Again!

rsbrux rsbrux at yahoo.com
Tue Feb 11 04:52:39 EST 2020 

It was probabably due to my placing the securities accounts under 
accounts in CHF and entering the first transactions in the securities 
accounts instead of in the associated accounts for purchase & sale (see 
my reply to John Rails).  Stock quotes retrieved from Yahoo as json do 
show the correct currency, but my AUD investments have been in bonds and 
ETFs, for which I haven't found any online quote sources,  The price 
editor does in fact have a currency settting, however, it doesn't have 
any effect on prices which were taken from transactions.  I can edit 
such a price and change the currency in the dialog, but when I click 
"Apply" and "Close", nothing is changed.  Going through the price 
database has uncovered several other cases where securities transactions 
were entered with incorrect currencies.  To prevent future occurences I 
have now inserted additonal account levels for currencies where they 
were previously missing, even though the resulting account structure is 
even unwieldier than before:

Assets - Investments - <bank> - <investment type> - <currency> - <security>

where <investment type is one of bond, fund or stock.  Perhaps I should 
dispense with the <investment type> level.

I did find one price in CHF for a security under a GBP account, but it 
is possible that I had entered the purchase transaction before 
introducing the currency-specific subaccount structure.

Our tax authorities insist on reporting produced by the banks (which 
costs us extra), so the mistakes in my accounting are irrelevant for tax 
purposes.  We do have a well-versed Swiss tax accountant and don't have 
to worry about foreign taxes because they are reported in the bank data 
and generally deducted at the origin.

Unfortunately for us, dividends are taxed as income in Switzerland, 
unless they are paid out of corporate reserves as "return of capital".

On 10.02.20 22:09, David Cousens wrote:
> Not sure, but I don't understand why the prices of the securites were in CHF. Since you have AUD income and expense
> accounts associated with the securities and you record their purchase/sale against an AUD foreign currency account I
> would have thought the securities would have been in AUD if traded on the ASX.
>
> A lot will depend upon what you are required to supply to your tax authorities, where tax is paid, and what rules apply
> on timing of conversion from AUD to CHF for tax recording purposes. For that sort of info you will need a Swiss tax
> accountant with foreign tax experience.
>
> In Australia companies operate under a company tax regime where tax paid on company profits is not normally taxed again
> as a dividend in the hands of a shareholder. The shareholder receives a franking credit for the tax paid in the hands of
> the company to offset against their income tax. Not all share dividends are franked however. This of course only applies
> to Australian shareholders who pay income tax in Australia unless  some sort of reciprocal tax agreement between
> countries exists covering these taxes.
>
>
> On Mon, 2020-02-10 at 14:21 +0100, rsbrux via gnucash-user wrote:
>> Many thanks to both of you for your suggestions!
>>
>> @Adrien,
>>
>> The accounts in question already had the correct currency (AUD)
>> assigned.  The accounts in question are a foreign currency account in
>> AUD, three accounts for AUD-denominated securities (which I bought/sold
>> in the above-mentioned foreign currency account), various expense
>> accounts in AUD and an income account in AUD for interest.  The
>> transactions between the foreign currency account and the expense
>> accounts were recoreded in AUD as expected.  All other transactions were
>> recorded with CHF exchange rates, although no currency conversions were
>> called for.  The prices of two of the three securities were also
>> recorded in CHF.  SInce the GC UI shows all accounts in AUD (except for
>> the securities accounts, which IIRC, do not have a currency) it was
>> unclear to me which account(s) I should duplicate.
>>
>> @David,
>>
>> My default currency is CHF; I live in Switzerland.  Trading accounts are
>> turned on.  I have tried to fix this in the XML based on the information
>> you provided.  I tried to look up more information about the file
>> structure, but the guide
>> <https://www.gnucash.org/viewdoc.phtml?rev=2.6&lang=C&doc=guide> wasn't
>> much help, and the wiki <https://wiki.gnucash.org/wiki/> appears to be down.
>>
>> IAC, I edited the price records for the two securities which had
>> incorrect price currencies, as well as all transactions which refenenced
>> any of the three securities accounts, changing the currencies from CHF
>> to AUD.  This appears to have fixed the problems, but I am keeping a
>> couple of pre-edit backups, as you recommend.
>>
>> Am I the only one to encounter problems like this?
>>
>> SInce the problem seems to have been with the (implicitly determined)
>> currencies of the prices and the transactions, would it be possible to
>> add an option to the price editor dialog for each price and to the
>> exchange rate editor for each transaction to change or select the
>> currency?  From what I have learned about the data structure, this looks
>> like the easiest way to provide a less dangerous way to fix such problems.
>>
>>> Date: Mon, 3 Feb 2020 10:31:58 -0600
>>> From: Adrien Monteleone <adrien.monteleone at lusfiber.net>
>>> To: Gnucash Users <gnucash-user at gnucash.org>
>>> Subject: Re: [GNC] Account Currency Screwed Up Again!
>>> Message-ID: <821D9637-24EE-4C33-A1BF-792FE55E893C at lusfiber.net>
>>> Content-Type: text/plain;	charset=utf-8
>>>
>>> Would creating a new duplicate account with the proper assigned currency, then deleting the old account and choosing
>>> to transfer all transactions to the new currency work?
>>>
>>> That would be significantly easier than manual editing.
>>>
>>> I?m not sure how the use of Trading Accounts affects this, or if it should or should not be turned on.
>>>
>>>
>>> Regards,
>>> Adrien
>>>
>>>> On Feb 3, 2020 w6d34, at 4:45 AM, David Cousens <davidcousens at bigpond.com> wrote:
>>>>
>>>> Have you checked the default currency used Edit->Preferences, Accounts tab,
>>>> Default Currency. It is normally set to locale. I guess you are in Australia
>>>> so it should be AUD. If not, diid you possibly create the book originally
>>>> while overseas or the locale on your computer was not set to Australia.
>>>> AFAIK it is not possible to edit the currency of an account once it is
>>>> created in the Edit Account dialog only in the New Account dialog.
>>>>
>>>> It should be possible to edit the file this but fraught with possible
>>>> problems.  Work on a copy of your original file and don't save it over the
>>>> original until you are really sure that the problem is fixed and other
>>>> problems haven't been created. I would keep a copy of the original even
>>>> after you reach this point just to be sure.
>>>>
>>>> Your XML file will have to be uncompressed Edit ->Preferences General Tab
>>>> and uncheck the box which says compress files. You may then have to force a
>>>> Save. I did this by creating a dummy transaction in a register then hitting
>>>> the Save button in the toolbar, deleting the dummy transaction and clicking
>>>> Save again.
>>>>
>>>> The section of the XMLfile which contains the commodity information for an
>>>> account is reproduced below for a SGD account I have.
>>>> <gnc:account version="2.0.0">
>>>>    <act:name>TravelMoney_CC_SGD</act:name>
>>>>    <act:id type="guid">*7db63ffa514d478191dea006748d7bd6*</act:id>
>>>>    <act:type>LIABILITY</act:type>
>>>>    <act:commodity>
>>>>      <cmdty:space>CURRENCY</cmdty:space>
>>>>      *<cmdty:id>SGD</cmdty:id>*
>>>>    </act:commodity>
>>>>
>>>> You will have to have the commodity you wish to change the account to
>>>> specified earlier in the file in a section like
>>>> <gnc:commodity version="2.0.0">
>>>>    <cmdty:space>CURRENCY</cmdty:space>
>>>>    <cmdty:id>SGD</cmdty:id>
>>>>    <cmdty:get_quotes/>
>>>>    <cmdty:quote_source>currency</cmdty:quote_source>
>>>>    <cmdty:quote_tz/>
>>>> </gnc:commodity>
>>>>
>>>> Each transaction to the account will have the commodity specified in a
>>>> <trn:currency> tag set. You would need to find all of these and change the
>>>> commodity similarly
>>>> <gnc:transaction version="2.0.0">
>>>>    <trn:id type="guid">4ec8a9416b31469bb1eeaf79c5557092</trn:id>
>>>>    <trn:currency>
>>>>      <cmdty:space>CURRENCY</cmdty:space>
>>>>      <cmdty:id>SGD</cmdty:id>
>>>>    </trn:currency>
>>>>    <trn:date-posted>
>>>>      <ts:date>2019-03-17 10:59:00 +0000</ts:date>
>>>>    </trn:date-posted>
>>>>    <trn:date-entered>
>>>>      <ts:date>2019-05-14 03:25:18 +0000</ts:date>
>>>>    </trn:date-entered>
>>>>    <trn:description>XX 1897  BP-BOOKSACTUALLY</trn:description>
>>>>    <trn:slots>
>>>>      <slot>
>>>>        <slot:key>date-posted</slot:key>
>>>>        <slot:value type="gdate">
>>>>          <gdate>2019-03-17</gdate>
>>>>        </slot:value>
>>>>      </slot>
>>>>    </trn:slots>
>>>>    <trn:splits>
>>>>      <trn:split>
>>>>        <split:id type="guid">2bf944df822042849e1cbd5a2b6db3c8</split:id>
>>>>        <split:reconciled-state>n</split:reconciled-state>
>>>>        <split:value>6100/100</split:value>
>>>>        <split:quantity>6654/100</split:quantity>
>>>>        <split:account
>>>> type="guid">7be97b5f738386fd2272881dc33a0272</split:account>
>>>>      </trn:split>
>>>>      <trn:split>
>>>>        <split:id type="guid">4ef42d767cc748f79ce09d6d8f2d3043</split:id>
>>>>        <split:reconciled-state>y</split:reconciled-state>
>>>>        <split:reconcile-date>
>>>>          <ts:date>2019-05-14 13:59:59 +0000</ts:date>
>>>>        </split:reconcile-date>
>>>>        <split:value>-6100/100</split:value>
>>>>        <split:quantity>-6100/100</split:quantity>
>>>>        <split:account
>>>> type="guid">*7db63ffa514d478191dea006748d7bd6*</split:account>
>>>>      </trn:split>
>>>>    </trn:splits>
>>>> </gnc:transaction>
>>>>
>>>> Make sure the guid for the account matches up (bolded in the above examples)
>>>> in the transaction splits and the account you have modified the currency
>>>> for.
>>>>
>>>> AFAIK they are the only places you should need to modify the currency but I
>>>> only have a passing acquaintance with the file XML format and the data
>>>> structures so I cannot be sure.
>>>>
>>>> The price database was the other section which had records containing the
>>>> currency
>>>>    <price>
>>>>      <price:id type="guid">5b56177651ba48ffa9ce7410e4f3f355</price:id>
>>>>      <price:commodity>
>>>>        <cmdty:space>CURRENCY</cmdty:space>
>>>>        <cmdty:id>AUD</cmdty:id>
>>>>      </price:commodity>
>>>>      <price:currency>
>>>>        <cmdty:space>CURRENCY</cmdty:space>
>>>>        <cmdty:id>SGD</cmdty:id>
>>>>      </price:currency>
>>>>      <price:time>
>>>>        <ts:date>2019-03-21 14:00:00 +0000</ts:date>
>>>>      </price:time>
>>>>      <price:source>user:price</price:source>
>>>>      <price:value>70000/77059</price:value>
>>>>    </price>
>>>>    <price>
>>>>
>>>> It may be easier edit that with the edit facilities in the Tools->Price
>>>> Database if necessary. None of this is really recommended but proceed with
>>>> extreme caution lots of backups and good luck.
>>>>
>>>> David Cousens
>>>
>>>
>>>
>>>
>> _______________________________________________
>> gnucash-user mailing list
>> gnucash-user at gnucash.org
>> To update your subscription preferences or to unsubscribe:
>> https://lists.gnucash.org/mailman/listinfo/gnucash-user
>> If you are using Nabble or Gmane, please see https://wiki.gnucash.org/wiki/Mailing_Lists for more information.
>> -----
>> Please remember to CC this list on all your replies.
>> You can do this by using Reply-To-List or Reply-All.

More information about the gnucash-user mailing list