"Average Cost" in this case doesn't mean what normal people would think. The function finds all of the splits in all of the non-trading accounts that involve currencies other than the parent transaction's currency and creates the scheme equivalent of a table that looks something like 
  comm1 comm2 quantity value
where comm1 is the transaction currency, comm2 is the split commodity, quantity is the total number of split-commodity units and value the total number of transaction-currency units.

Note that the transaction currency is determined by the account-register you start out in, so one would expect that if all of the transactions are initiated from the Checking Account register the table will look like
  USD  EUR 19999/100  21000/100

Fiddling around with the numbers shows that the resulting exchange rate is the trading gain divided by the residual amount in the EUR account, so that the value balance in the EUR account is always the trading gain. 

Unfortunately figuring out what gnc:get-exchange-cost-totals (https://github.com/Gnucash/gnucash/blob/master/src/report/report-system/commodity-utilities.scm#L624) is really doing exceeds my limited Scheme ability.

First of all a big thanks for looking into that at all.

> "...so that the value balance in the EUR account is always the trading gain."
That's exactly what I observed as well. Obviously it follows that in the total balance there is no trading gain/loss at all - since that is 'hidden' in the foreign currency account's value (1).

> "Average Cost in this case doesn't mean what normal people would think"
Is there a simple summary what "Average Cost" means instead? The documentation states: "Average Cost: The volume-weighted average cost of purchases".

From a naive stand-point I still think that this in my example translates to 1 USD = 1 EUR (there is only one 'purchase' of EUR with exactly this exchange rate)

In any case, from the description, I'd deduct that only 'purchase' transactions should contribute to determine the exchange rate (2) - which is obviously not the case here. Is there something horribly wrong in my logic or is that some kind of bug (at least in the description of that price source)?

Cheers
- Stefan



Remarks:
(1) This also leads to the interesting behaviour that all goes well if all EUR is sold (in 2nd transaction: replace 99.99 EUR by 100 EUR): Since there is no more EUR left, this accounts value must be zero => the trading gain/loss can longer be hidden, thus needs to be listed explicitly - which is done correctly in the report.

(2) I do see a catch there - what is the condition for 'purchase' after all, e.g. for transactions between two currencies which are both not the base currency? This might be tricky to define unambiguously for all possible situations...

btw:
> "...figuring out what gnc:get-exchange-cost-totals is really doing exceeds my limited Scheme ability."
Thanks for pointing me to the source. Unfortunately this hold true for me as well. I do have some limited programming skills in other languages, but that Scheme thing is pretty hard to understand for my untrained eyes...

Thanks to working through the code with Geert I think I understand what is the problem with the code. 
Here's what I think Christian (Stimming, the original author of this code) was trying to do:

The "Weighted Average" selection--which works, BTW--aggregates all transactions between two currencies and calculates a net exchange rate. It uses absolute values so that all transactions are added together regardless of direction. The recorded direction of transactions are set by the commodity of the first transaction involving a particular pair of commodities; any transactions of that commodity pair with the "other" commodity as the transaction commodity are swapped (amount->value, value->amount) and added to the summary. The resulting rate for each commodity pair is the total amount/total value.

The "Average Cost" selection was meant to look only at buy transactions of one commodity in the other, so only half of the transactions would be considered. What he actually wrote, though, computes the *net* of buys and sells, with the result that the rate is the ratio if the residual amounts in the two commodities. In order to accomplish what we think is Christian's original intent the table will need two rows for each pair, one for each direction, and the division function will need to select the right direction for the rate that it wants.

Now it seems to me that neither is likely to be what most users would find useful, so I'll also change the default to "nearest in time". It also seems to me that what most users would want for "average cost" would look only at the cost of what's currently in the account, but that's a slippery concept after a few buys-and-sells if the balance doesn't go to 0 after each sell.

Ok, thinking about it I also got some more clues:

As you stated already, the "Average Cost" is more complex than just 'average cost of purchases'. Although this is what I was looking for initially, I agree this is also not what I really wanted. It would give the expected results in the beginning, but after a few buys-and-sells tracing the whole history of buys gives some counter-intuitive results.
[Example: Buy EUR100 -> Sell EUR100 -> No EUR cash, wait a few years -> Buy EUR100: Intuitively I probably don't want to consider the first buy (since it is sold completely anyway), but looking at purchases only would do exactly that.]

> It also seems to me that what most users would want for "average cost" would
> look only at the cost of what's currently in the account
Agree, and I think this is what "Average Cost" tries to achieve. Now I think that I can see the logic behind that: Estimating the value of a commodity by "average cost" means that there even cannot be a gain or loss - since a gain/loss is caused only if the value of the commodity changes in comparison to the purchase cost.

Looking at it from this angle, it makes sense that the algorithm tries to drive the unrealized gain/loss to zero (by hiding it in the value of the commodity). Unfortunately it results in those strange values in my example. On the other hand I thinks it works if used properly: On each sell, I do actually _realize_ a gain/loss, so I should add a "Realized gain/loss" transaction to each sell. Doing so adapts the value of the remaining commodity accordingly and in fact, the exchange rate calculated in this case makes much more sense!

Follow-up:
Due to my laziness of not adding "realized gain/loss" transactions to each sell I was hoping to see an "unrealized gain/loss" being calculated automatically, with the amount being equal to exactly what I should enter in the "realized gain/loss" transaction. In fact I wanted to merge all realized gain/loss transactions of an accounting period into one and was hoping for Gnucash doing the math for me by showing the not-yet-booked gain/loss as unrealized gain/loss.

But that's not how things work, and Peter Selinger also makes this clear in his tutorial on trading accounts (http://www.mathstat.dal.ca/~selinger/accounting/tutorial.html):
> The important point is that realized gains and losses are not calculated
> implicitly by the accounting system, but must be explicitly stated by a
> bookkeeper or accountant.
The most important reason why Gnucash cannot calculate the gain/loss simply is that there not just one way of calculating it (there is FIFO, LIFO and whatever else, see also the "Lots" feature do that calculation)


To summarize - it seems that "Average Cost" is already doing what it should do, but needs to used properly. I think there should be some remark on that in the documentation. When I find the time I'd try to put something together to somewhat extend the documentation in that respect.

Note: There is also some interesting thread on the price source topic (weighted average vs. average cost):
http://gnucash.1415818.n4.nabble.com/Reporting-weighted-average-price-source-td1446540.html


And a final remark on changing the default price source to "Nearest in time":
I personally think that this would be a sane default but there might be catch, see Bug 340991.

Do you realize that it's searching the whole account tree for transactions involving the two currencies and summing everything it finds (except trading account entries)?

How exactly did you book the gain? The usual way when booking a gain in a non-currency commodity (e.g. a stock investment) doesn't affect the sale split and so wouldn't affect this problem with the average cost calculation. That isn't possible in currency-only transactions though because there's no way to have a value entry without an amount entry in currency registers. The alternative, forcing the price to be the same as the "buy" and adding an "Income:Currency Trading Gain/Loss" split, seems a bit error-prone and might be asking too much of the user. I'd prefer to modify the function so that it pays attention to the direction of the splits and sums e.g. EUR->USD and USD->EUR separately and then uses only the right one for the report. That's still not what most users would expect because it will span multiple accounts, but I think it better matches Christian's intent.

Christian's concern in Bug 340991 has been mostly resolved from version 2.6.10 on by creating entries in the price table for transactions. Users can still break "Nearest in Time" and "Most Recent" by deleting those price entries, but then it's their fault.

> How exactly did you book the gain?
Currently I don't have access to my GnuCash instance. To test this, I simply used the scrub account button of the "Show Lots" dialog to create a "Realized gains/losses" transaction. Without knowing exactly I'd guess that it should create a transaction that moves the gain/loss amount from the EUR trading account into a Gains/Losses account.

Now I'm somewhat confused as you say that transactions involving trading accounts are not considered in the calculation, still I saw a considerable difference when I added this transaction. I'd need to check how that transaction looked like, I'll come back on this as soon as possible.

> I'd prefer to modify the function so that it pays attention to the direction
> of the splits and sums
While this is still under investigation, my feeling is that the current implementation is reasonable if used correctly (see above). Is there a way to get Christian opinion on that? He probably had some reasons to do it the way it is now.

On the other hand I'd agree that your proposal might be reasonable as well and is probably particularly helpful if the used does not take care of explicitly stating realized gains/losses. Would it be possible to add that as an new price source, to have both options? In any case, the documentation of the price source(s) needs some more details...

> Christian's concern in Bug 340991 has been mostly resolved from version 2.6.10
Great! In that case I vote +1 to use "Nearest in time" as default price source

(In reply to Stefan Söffing from comment #8)
> > How exactly did you book the gain?
> Currently I don't have access to my GnuCash instance. To test this, I simply
> used the scrub account button of the "Show Lots" dialog to create a
> "Realized gains/losses" transaction. Without knowing exactly I'd guess that
> it should create a transaction that moves the gain/loss amount from the EUR
> trading account into a Gains/Losses account.

I just tried that, it didn't create any splits or transactions and didn't change the incorrect rate in the Balance Sheet report. I think it creates those only for STOCK and FUND splits.

> 
> Now I'm somewhat confused as you say that transactions involving trading
> accounts are not considered in the calculation, still I saw a considerable
> difference when I added this transaction. I'd need to check how that
> transaction looked like, I'll come back on this as soon as possible.

No, not transactions. Trading account *splits* are ignored.

(In reply to John Ralls from comment #9)
> I just tried that, it didn't create any splits or transactions and didn't
> change the incorrect rate in the Balance Sheet report. I think it creates
> those only for STOCK and FUND splits.

Ah, no, I tried again in the other (USD) account and this time it did create the G/L transaction, and the Balance Sheet report is, as you say, correct.

When I tried to reconstruct what I did before I realized just the same as you. In fact it creates the G/L in the USD account and fails to do it in the EUR account.

In my opinion this is a bug: Drawing the analogy to investment accounts: The Lots dialog will be used in the stock's account to determine the G/L of stock transactions.
So here (with EUR being the "foreign" currency, i.e. the traded item) it should also work in the EUR account.

Besides, if done in the USD account it seems that GnuCash somehow assume EUR to be the base currency and creates a G/L in EUR (but it's the USD amount). This seems to be related to the behavior reported here: http://gnucash.1415818.n4.nabble.com/Use-of-lots-for-gains-losses-when-using-multiple-currencies-tp4686888p4687872.html

I tried your workaround here as well (create another asset account with currency EUR as base account for EUR cash) but it didn't solve the problem here...

I've modified the Average Cost pricing option as I described in comment #4 as well as changing the default and I'm marking this fixed. Please file a new bug for the backwards behavior of the scrub lots function. That code is a horrid mess and we're not likely to be able to fix the bug until it can get rewritten, but at least having the bug will remind us to get that right when we do.

Did you consider my comment 5 to keep the current method as well? Now that I'm correctly using the realized G/L txns, I surprisingly do prefer the current implementation over a "pure" average cost function, since it suppresses rates from "older" buys ("old" in the sense that the corresponding amounts have been sold already).

Since "Average cost" is probably more appropriate to your new method, I'd suggest to rename Christian's implementation to something like "Adjusted average cost"; along with a tooltip description "Volume-weighted average cost of purchases, adjusted by sells on the basis of recorded realized gains/losses"
(feel free to rephrase, I'm not a native speaker).

Thanks!

Problem with "View Lots" submitted as bug 775903. Strange enough, while creating the sample gnucash file I noticed that occurrence of the issue depends on where the transaction is created - but see the bug report for more information..

I didn't, but I'll have another look at it after next week's release.

The new implementation of "Average Cost" calculation breaks the correct calculation of gains and losses and leads to an "Unrealized Gain / Loss" in the Balance Sheet, Portfolio and related reports. 

As Stefan has also requested, please bring back the earlier implementation of "Average Cost" even if its in a new name like "Adjusted Average Cost" or something. This is critical for me to be able to correct reconcile the gains and losses on my portfolio. Else, I'll be stuck on version 2.6.14.

Request you to please consider this - I re-iterate its very critical for me (and presumably many other people!)

Thanks & regards,
Nikhil.

Nikhil: Do your books have realized gains splits to account for your gains? If not, then the reports are correct and your books are out of balance. If you have realized the gains and they're also showing up as unrealized then please create a small set of books that illustrates the problem.

Created attachment 349225 [details]
Test GnuCash file to Illustrate Bug 775368 around Avg. Cost Calculation

Hi John,

My books do have realized gains / splits to account for them and they used to show completely perfectly till GnuCash 2.6.14

As requested, I've created a small test GnuCash file (GnuCash Test - Bug 775368.gnucash) to illustrate the problem:

1. The file basically contains a Mutual Fund (HDFC Top 200) which has an Opening Balance and then a Sell, followed by some SIP Buys & Dividends and ultimately a final Sell.

2. There are few additional accounts to manage the transactions like a Savings Account, Equity>Opening Balance, Capital Gains & Dividend Income, etc.

3. Generate the Balance Sheet report (with Price Source for Commodities as 'Average Cost' as of 31-03-2016 or 31-03-2015 or earlier and the Realized Gains/Losses show as non-zero. 

4. However, if you generate the Balance Sheet report for 31-03-2017 (post the last transaction in the mutual fund account), the Realized Gains/Losses become 0.

5. This was never the issue in the versions up till 2.6.14 and the algorithm produced the desired output (at least as desired by me). The Realized gains/losses was always 0 irrespective of when you generated the Balance Sheet for as long as the gains/losses splits were accounted for in the transactions.

Please do let me know in case you need any additional info. This is a very critical feature for me to be able to use GnuCash productively. Request your help to resolve this either by modifying the Average Cost algorithm or by re-introducing the older algorithm with a different name. 

Many thanks,
Nik.

Comment on attachment 349225 [details]
Test GnuCash file to Illustrate Bug 775368 around Avg. Cost Calculation

Wrong attachment - please delete.

Created attachment 349230 [details]
Test GnuCash file to Illustrate Bug 775368 around Avg. Cost Calculation

Please remove the earlier attachment - that was a wrong file and I'm unable to delete it.

No Bugzilla user can delete anything, but one can mark an attachment obsolete and that removes it from the attachment list. I've done that.

The problem you're seeing is that the "average price" is including the price of the sold shares and that's different from the average price of the shares currently in the account. I agree that's a problem.

(In reply to John Ralls from comment #22)
> No Bugzilla user can delete anything, but one can mark an attachment
> obsolete and that removes it from the attachment list. I've done that.
> 
> The problem you're seeing is that the "average price" is including the price
> of the sold shares and that's different from the average price of the shares
> currently in the account. I agree that's a problem.

Thanks for having a look and identifying the issue. What'll be the best way to tackle this:

(i) Modifying the new algorithm
OR
(ii) Adding the old algorithm as a 'Modified Average Cost' method?

Hi John,

I've provided the required data, but the bug status is still "NEED INFO". Request you to change the status and request an early resolution to the bug. 

Thanks & regards,
Nikhil.

You should be able to change the status yourself when you answer the question. I've done it for you this time.

Hi John, Nikhil

I'm happy not being alone with that issue. I'd like to mention again, that based on my comment #5 I'd rather withdraw my 'bug' report (comment #1). The original functionality can be desirable, as it is in Nikhil's and my use case.

@John, do you consider adding the previous method again as "Adjusted average cost"?

Me too, I'm unhappy with the current situation. I cannot reproduce balance sheets that I had exported some years ago, although I'm working on the same data set. I haven't done my 2015 and 2016 reports yet, since I'd need to break the way of calculation that was used before. [Stop ranting]

Anyway, I would try to re-add that option myself, but I'd be very happy if I don't have to. I'm sure you can do this much quicker than I could...

Thanks!
Stefan

Hi John,

Just wanted to request you for helping resolve this bug at the earliest. It is really affecting the ability to generate accurate Balance Sheet reports and hence having to continue to use gnucash 2.6.14.

Hoping for your kind attention on this bug soon.

Thanks!
Nikhil.

Nihil,

The retained earnings vs. realized & unrealized gains is unrelated to the pricing calculation. What's happening there is that the Balance Sheet report is sweeping the realized gain/loss into "Retained Earnings" unless you have an open position in the security. I don't know when that started and I don't have time to test exhaustively, but it worked the same with 2.6.11, the oldest version I have available.

(In reply to John Ralls from comment #28)
> Nihil,
> 
> The retained earnings vs. realized & unrealized gains is unrelated to the
> pricing calculation. What's happening there is that the Balance Sheet report
> is sweeping the realized gain/loss into "Retained Earnings" unless you have
> an open position in the security. I don't know when that started and I don't
> have time to test exhaustively, but it worked the same with 2.6.11, the
> oldest version I have available.

Ah, got the complaint backwards:
(In reply to Nikhil Arora from comment #16)
> The new implementation of "Average Cost" calculation breaks the correct
> calculation of gains and losses and leads to an "Unrealized Gain / Loss" in
> the Balance Sheet, Portfolio and related reports. 

It's not the rollup into retained earnings that you don't like, it's that the imputed unrealized gain is created because the average price is different from the average of the open positions.

That's right John. I believe when I'd earlier shared the test file with you, you'd figured the issue with the new algorithm that was causing this. The issue is indeed with the Unrealised gain that pops up due to an incorrect average price of the remaining securities.

Hi Nikhil,

since this issues is still bugging me, I tried this:

Luckily, John has nicely separated out his steps in modifying the code file, so to revert only the latest change that modifies the "Average cost" calculation, you need to get the commodity-utitlities.scm file from git:
https://raw.githubusercontent.com/Gnucash/gnucash/d9dbc3de04eb9518a688a9846e5276c3d2a2d05c/src/report/report-system/commodity-utilities.scm

backup and replace the existing file (path for my Ubuntu installation)
/usr/share/gnucash/scm/commodity-utilities.scm

Restart GnuCash and it should automatically recompile the reporting code. 

Be warned that this might be considered a dirty hack; anyway, it worked nicely for me to get my figures back for the time being.

@John: Looking forward to any further discussion on this topic!

Happy new year everyone!


- Stefan

Correction, I have to go back even to 5803c14 [1] to also revert the changes in get-match-commodity-splits, since the report did no longer recognize a realized gains transaction (neither created manually nor automatically by the scrub lots feature) when the commodity was not sold completely (Buy 200, Sell 125).
This is with trading accounts enabled.


[1] Get the file here: https://raw.githubusercontent.com/Gnucash/gnucash/5803c141c18a6ff75a7f49a9142f834a596857f5/src/report/report-system/commodity-utilities.scm

@John: Some more thoughts, maybe this is useful for later discussion:

I've been reading Peter Selinger's tutorial on trading accounts again and again. Finally I have the illusion of understanding something: 

1. Unfortunately, GnuCash's trading accounts use only one trading account for the base currency - it would be easier if there was one base currency account per traded commodity. If this was the case, the trading accounts would give the gains/losses basically for free - gains/losses are exactly equal to the trading account totals!

2. GnuCash does not provide that separation, therefore this simple scheme unfortunately does not work if more than one commodity is traded in the same account tree (unfortunately this is the expected case)

3. To work around that, your proposal might be the solution: Sum over all trading account splits within one account: The total gives exactly the gains/losses for transactions in that account (=> still need to consider how to deal with the situation of trading the same commodity in different accounts => have two different exchange rates for the commodity?)

4. Remarks 1-3 hold true for trading accounts enabled - how does it relate to the situation without trading accounts?
Instead of summing over all _trading splits_ in an account, it is equivalent to sum over all _non-trading splits_ involving a foreign currency. This needs to yield the same result, since trading splits are generated exactly such as to capture the "creation" and "annihilation" of commodities in multi-currency transactions.

5. And finally: Summing over all non-trading splits involving foreign currencies (net of buy/sells) is exactly what I understand the "Average cost" source did originally.

Conclusion: My feeling is, that the original calculation comes very close to what Peter Selinger had in mind. In his article, though, this is referred to as "adjusted cost base" [A] (he mentions "weighted average" as well, but this is certainly not the same as GnuCash's weighted average based on absolute values)

What was still missing in GnuCash original "Average cost", is a way to calculate the exchange rates on a per account basis - just as you proposed, John. In fact, Peter Selinger has already foreseen that as well, see his example in [B]!


Long story short: I'd be happy to have the old way back and maybe even improved ;-)

This collection of my thoughts has gotten somewhat lengthy now, anyway I hope it helps somehow...

- Stefan


[A] https://www.mathstat.dal.ca/~selinger/accounting/tutorial.html#5.3
[B] https://www.mathstat.dal.ca/~selinger/accounting/tutorial.html#4.4

Hi Stefan,

Thanks for taking the time out to find an easy workaround to the problem. It works perfectly. And, I have upgraded to the latest version (I was stuck on 2.6.14 ever since this issue cropped up), thanks to this workaround you've elegantly laid out.

Will wait now for John to help sort this issue - till then this is extremely helpful.

And, yes, a very Happy New year to everyone.

Best regards,
Nikhil.

Created attachment 366757 [details]
Screenshot showing transactions in a mutual fund with a fund merger

Hello,

I am on Mac OS Sierra v10.12.6. I am submitting this based on Jon Ralls' suggestion.

I upgraded to the latest version of GnuCash 2.6.19.

After running a preliminary test of some of the reports I use for tax reporting purposes, I noticed that the cost basis on one of my mutual funds has changed significantly (see attached screenshot for the transactions on that mutual fund account).

Some history on this fund. It used to be called Fidelity Flexi Gilt Fund and I had invested INR 850,000 and accumulated 70,362.427 shares as of 16-Nov-12. But on 23-Nov-2012, Fidelity sold its mutual fund business in India to L&T Mutual Fund and the latter decided to merge Fidelity’s gilt fund into its own - now called L&T Gilt Fund.

When this merger happened, I simply used the stock split assistant to reduce the no. of shares by 34,769.081 based on the account statement sent by L&T.

As of 2.6.6, the (average) cost basis on the balance sheet report correctly showed INR 850,000 even after the “stock split” transaction. But in the latest version 2.6.19, the balance sheet report shows the same cost basis as 429,978.69. I think it has reduced the cost basis by the cost of the shares reduced from the merger i.e., 34,769.081 shares.

This is causing such differences to show up as imbalance in my reports!

Odd thing though is that I have a no. of stocks that declared a stock split, but in those cases, the cost basis is correctly maintained even after the split. This behaviour is only seen in mutual fund shares (as far as I can tell).

Cheers,
Deva

I've reverted 98697a1 in maint, which puts it back to the way it was in 2.6.12 for the terminal release of 2.6.x., and merged that up into unstable. With that I'll look into more limited changes that can address the problem of determining the correct direction of a transaction and to limiting the selection of splits to a report's current account selection.

GnuCash bug tracking has moved to a new Bugzilla host. The new URL for this bug is https://bugs.gnucash.org/show_bug.cgi?id=775368. Please continue processing the bug there and please update any external references or bookmarks.