Entering stock transactions

[Fred Bone]

On 06 October 2008 at 15:05, Charles Day said:

> On Mon, Oct 6, 2008 at 8:34 AM, Fred Bone <Fred.Bone at bcs.org.uk> wrote:
> 
> > On 22 Sep 2008 at 11:02, Charles Day said:
> >
> > > On Mon, Sep 22, 2008 at 10:19 AM, Fred Bone
> > > <Fred.Bone at dial.pipex.com>wrote:
> > [...]
> > > > After a great deal of experimentation, I have narrowed my problem
> > > > down
> > > to
> > > > the Euro transactions. Unfortunately I have quite a few, spread
> > > > around
> > > a
> > > > number of accounts. However, the balance is not zero except in one
> > > case
> > > > (where an Expenditure account got zeroed to Equity; deleting that
> > > > transaction has no effect on the "imbalance").
> > > >
> > > > My main currency is GBP, and I have one Euro account under Assets
> > > > (Assets:Currency:Euro), two under Equity (Equity:Opening
> > > Balances:Euro
> > > > and Equity:Year end:2007:Euro), and four under Expenditure. I buy
> > > Euros
> > > > (cash) from my bank Current account, and deleting all the cash-
> > > > expenditure transactions makes no difference.
> > > >
> > > > What does make a difference is deleting the four transactions where
> > > I
> > > > purchased Euros, and three where I charged Euro transactions to my
> > > (GBP)
> > > > credit card.
> > [...]
> > > > Deleting a single EUR purchase and re-entering the same amount with
> > > the
> > > > same exchange rate yields the same "imbalance", so it's not a case
> > > > of
> > > bad
> > > > data in the file.
> > > >
> > > > The last such transaction was on 2008-05-20, when I bought EUR305.00
> > > for
> > > > GBP250.29. If I delete this transaction, my "imbalance" changes from
> > > > 4218.29 to 8.10. If instead I create another one (dated today or
> > > some
> > > > arbitrary other date, makes no difference), the "imbalance" becomes
> > > > 288.97.
> > > >
> > > > FWIW, the other bank-to-cash transactions are
> > > > 2007-02-20 EUR290.00 bought for GBP201.07
> > > > 2007-07-11 EUR290.00 bought for GBP202.68
> > > > 2007-10-07 EUR280.00 bought for GBP200.70
> > > > and the credit card transactions are
> > > > 2007-10-10 EUR258.00 charged at GBP183.78
> > > > 2008-06-01 EUR85.50 charged at GBP69.55
> > > > 2008-06-06 EUR55.10 charged at GBP45.29
> > > >
> > > > I cannot make any sense of what is going on. Especially the huge
> > > variance
> > > > in the effect of duplicating or removing a single transaction.
> > > >
> > >
> > > I think things will be easier to track down if you run the trial
> > > balance
> > > with your price source set to "average cost" (available starting in
> > > 2.2.6) . The default price source of "weighted average" seems to be
> > > more volatile due to the way it is calculated.
> > >
> > > I see you saying that GBP is being sold to buy EUR. Does the EUR cash
> > > later get transferred into accounts that are not also denominated in
> > > EUR?
> >
> > No. And btw, I've been running "average cost" all along.
> >
> > Some very strange things are happening.
> >
> > If I delete the earliest EUR purchase, the "imbalance" reduces from
> > GBP4218.29 to GBP49.78.
> >
> > If I then re-enter it from the (GBP-denominated) bank account side, the
> > "imbalance" is again shown as GBP4218.29, as noted previously.
> >
> > However, if instead I re-enter it from the "Cash - Euro" side, the
> > "imbalance" reduces further, to GBP5.42.
> >
> > OTOH, if I create a new file and enter all my Euro transactions, I can't
> > create an imbalance whether I enter the purchases from the GBP or the
> > Euro end.
> >
> > It may be worth noting that the PriceDB holds an entry obviously derived
> > from the first GBP/EUR transaction, showing an exchange rate of "1 +
> > 8893/20107" (which is the GBP-per-EUR rate; all the others show the EUR-
> > per-GBP rate, as I would expect). Deleting this makes no difference to
> > the "imbalance"; I'm not sure whether it ought to (i.e. whether the
> > Trial Balance makes any use at all of PriceDB entries).
> >
> > I've tried saving the "before" and "after" versions of the (real) file
> > uncompressed and running a diff. Here's the entire output (for the one
> > where I re-entered from the Euro side). I cannot see anything that I
> > would expect to affect the calculation of exchange rates.
> >
> 
> I've taken a look at the differences. I take it the intention is to buy
> 290 EUR at a cost of 201.07 GBP. The important tags are <cmdty:id>,
> <split:value>, and <split:quantity>.
> 
> The <cmdty:id> tag tells the currency of the transaction, and it will be
> GBP if you enter a new transaction in the GBP-denominated account's
> register, or EUR if you use the EUR-denominated account's register. Since
> you used a GBP-denominated register in one file, and a EUR-denominated
> register in the other file, the observed difference makes sense.
> 
> The <split:value> tag tells the amount of the split in terms of the
> transaction currency (<cmdty:id> tag).  In your GBP-denominated
> transaction, the <split:value> lines are both 201.07, so that looks OK. In
> the EUR-denominated transaction, both are 290.00, so that looks good too.
> 
> The <split:quantity> tag tells the value of the split in terms of that
> split's account's currency. Here is where you would see the effect of the
> exchange rate. The problem is that there seems to be no difference in the
> <split:quantity> in the two files. That means the exchange rate is wrong
> in one of them.
> 
> Could you verify whether the transaction looks correct from all four
> registers (two in each file)? I would suspect that in one file the
> exchange rate is 1 and either the "290.00" figure or the "201.07" figure
> cannot be found.

Well, I can only "Edit exchange rate" on the Euro (debit) split in either 
register. In the Euro register it showsa rate of 20107/20900 (EUR/GBP), 
and in the GBP register it's "1 + 8893/20107" (GBP/EUR). These are 
consistent. Asking to "edit exchange rate" on the other split does 
nothing - is there some trick I'm missing?

I've prepared a cut-down version of the file, by deleting everything 
except the GBP/EUR exchange transactions and most of the now-empty 
account registers. It still shows the same "imbalance", with the Trial 
Balance's idea of totals being 5371.65 debit vs 1153.36 credit, 
attributable to nonsensical EUR/GBP conversions. For example, Euro Cash 
totals EUR1165, which shows on the Accounts page as GBP932.00 (having 
cost GBP854.74 in several transactions), but Trial Balance shows it at 
GBP4287.55.

Once I've checked that I've cleaned out all the private data (bank 
account numbers, ...) I'll post it somewhere you can see it.

I'm concluding that there is something bad that an earlier version of 
GnuCash left in there, but I don't know how to either (a) find it and 
clean it out, or (b) export all the good data to re-import to a fresh 
file.

Many thanks for your responses so far.