Balance and Stocks in foreign currency

[AmigaPhil]

>>> All I have to do is to create a separate "Commission" account (Expense)
>>> in USD.
>>
>> I've just did that, and the problem of a difference in balance
>> I had is GONE !
>>
>>> So I'm going to ensure that any incoming/outgoing money in a stock account
>>> in USD will be coming from (and going to) another account in USD as well.
>
> I think I shouted "Hurray" too early: I'm still having some balance woes.
> (I wonder if it's worth hitting my head against the wall for such
>  a small difference, but I'm puzzled and curious...)
>
>
> Let's start with an example again:
>
> On 10th November 2008, I bought 30 shares of XYZ at 15 USD.
> My bank statement told me that the commission fee was 30.58 EUR,
> the exchange rate was 1.3115 USD (1 EUR = 1.3115 USD), and the
> total debited from my bank account was 373.70 EUR.
>
> Based on this statement, I created a price for USD-EUR with the
> Price Editor to 0.762475 EUR (1/1.3115) for 2008-11-10.
> I also created a price for EUR-USD (just in case...) to
> 1.311518 USD (1/0.762475) for the same date.
>
> Then I tried the following methods to register the Stock purchase:
>
>
> Method 1:
> =========
>
>   Bank A    -->  Bank B   -->  Stock XYZ
>   (bank)         (bank)         (stock)
>    EUR            USD             USD
>     |
>     |
>     ----------> Commission
>                  (expense)
>                     EUR
>
> - PROS:
>   - Amounts are accurate
>   - Balance is right (Debits = Credits)
>
> - CONS:
>   - The Advanced Portofolio does not know about the Brokerage Fee
>
>
> Method 2:
> =========
>
>   Bank A    -->  Bank B   -->  Stock XYZ   --> Commission
>   (bank)         (bank)         (stock)         (expense)
>    EUR            USD             USD              EUR
>
> - PROS:
>   - The Brokerage Fee is right in the Advanced Portfolio report
>
> - CONS:
>   - Both the Chart of Accounts and the Balance Sheet report
>     are showing a difference (That's the begining of this thread.)
>
>
> Method 3:
> =========
>
>   Bank A    -->  Bank B   -->  Stock XYZ   --> Commission
>   (bank)         (bank)         (stock)         (expense)
>    EUR            USD             USD             *USD*
>
> -PROS:
>  - Changing the Commission account in EUR to a commission
>    account in USD did the trick: the Balance Sheet report
>    was right (Debits was equal to Credits again)
>    (I then had a 0.01 EUR difference when I tried to correct
>     the amounts of the transaction, probably something I have
>     introduced, like modifying the exchange rate calculation.)
>
> - CONS:
>  - The Advanced Portfolio report (and other reports) are not
>    showing the Brokerage Fee I DID payed in EUR (30.58).
>    (Showing 29.61 EUR instead.)
>
>
> Method 4:
> =========
>
>   Bank A    -->  Bank B   -->  Stock XYZ   --> Commission
>   (bank)         (bank)         (stock)         (expense)
>    EUR            USD             USD              USD
>                                                     |
>                                                     |
>                                      Commission  <---
>                                       (expense)
>                                          EUR
>
> - PROS:
>   - The ammount I *DID* payed for the brokerage fee is now
>     fixed (to 30.58 EUR)
>
> - CONS:
>   - The Advanced Portfolio report is still not showing the
>     right ammount in EUR for the brokerage fee
>   - Both the Chart of Accounts and the Balance Sheet report
>     are again showing a difference.
>
>
>     Taken from the balance sheet - with the defaulted "Average
>     costs" as quote source - I can see that the XYZ stock is
>     valued at 348.49 EUR, which imply a 0.774422 USD exchange
>     rate (348.49/450).
>
>     Shouldn't THAT exchange rate be: 0.762475 USD, as it is the
>     ONE and ONLY exchange rate used for the XYZ stock purchase
>     transaction (so is the AVERAGE COSTS) ?
>
>
>
> Bottom line: I think I will revert back to Method 2, as at least,
> the Advanced Portfolio is showing the right brokerage fee...
>
> (Using GnuCash 2.2.7 and Finance::Quote 1.15)


I did step back to Method 2 (that is registering the brokerage fee
for the purchase of a stock in USD straight to an expense account
in EUR, instead of using an intermediate expense account in USD).
And I was wrong in believing that would "fix" the brokerage fee
reported in Advanced Portfolio: nothing has changed (the problem
lies elsewhere).


Question: does Gnucash handle properly the exchange rate to use
when money is transfered from a Stock account to an Expense or
Income account ?

In the Advanced Portfolio, both the "Realized gains" and the
"Brokerage fee" columns show "29.61 EUR", which is wrong.
The brokerage fee I DID pay was 30.58 EUR.  This is something
realized, done once for all, and that value shouldn't change
other the time.  With a transaction to an expense account like
this one, the Advanced Portfolio should apply the exchange rate
from the transaction date (or the closest available one), AND
NOTHING ELSE (regardles the option setting for "quote source").
So with the example above:

   Stock XYZ  ----> Commission
    (stock)     |    (expense)
   40.11 USD    |    30.58 EUR
                |

           exchange rate
           PriceDB (USD)
           on 20081110 : 0.762475

With more than 1 stock purchase transaction, the total brokerage
fees should be:

  xx.xx USD        xx.xx USD
  * exch. rate  +  * exch. rate  +  ...
  at date1         at date2



In my previous post, I reported the Balance Sheet report valued
my XYZ shares to 348.49 EUR., which is NOT the right converted
ammount at the exchange rate from the date of purchase (450 USD
* 0.762475).  As a matter of fact, this (wrong) ammount does not
balance with the debit of my brokerage account (- 373.70 EUR) and
the credit of my Commissions account (+ 30.58 EUR).


(I think the same "exchange rate" problem also affect the total
 shown in the Chart of Account, but that's more difficult to track.)



AmigaPhil