How to record and keep track of stock share ?

[Christophe Schockaert]

Hi all,


First of all, I have to say that I find ledger very convenient, and looks like
what I was looking for as accountancy software: the ability to just write on
the flight is really great !

Before going further, I have no real skills in accountancy, so I learned with
documentation from GnuCash and Ledger.

I got an understanding for daily transactions, but I can't get it the way I
want it for keeping track of a few stocks. It might also be because I don't
know the "right way" to handle "in the accountancy way".

Maybe, you'll have some hints, I would appreciate :)

So, I made a sample file, close to what's in the Ledger documentation.
I run ledger "3.0.0-20130529" from the Debian archives.

My first try (case A), looks like:

; Initial openings at 2014/01/01
2014-01-01 Opening Balance
Assets:Bank:Check Account 5000,00 €
Equity:Opening Balance

; Details of shares at 2014/01/01
2014-01-01 Details for shares
Assets:Investments:Stocks:Total Value
Assets:Investments:Stocks:Share A 15 ShareA @ 20€
Assets:Investments:Stocks:Share B 5 ShareB @ 100€

Then, I run "ledger --real -B -V balance", which returns:
: 5000,00 € Assets
: 5000,00 € Bank:Check Account
: 0 Investments:Stocks
: 300,00 € Share A
: 500,00 € Share B
: -800,00 € Total Value
: -5000,00 € Equity:Opening Balance
: --------------------
: 0

That's fine. I understand that doing this, only the gains and loss will end in
the assets. I put the "Total Value" entry in order to see at a glance the value
of all shares.

In my second try (case B), I wish to see the contribution of my stocks within
the assets (I don't know if that's a valid practice but it gives me an overview
of the whole money available a time "t").

; Initial openings at 2014/01/01
2014-01-01 Opening Balance
Assets:Bank:Check Account 5000,00 €
Equity:Opening Balance

; Details of shares at 2014/01/01
;P 2014-01-01 00:00:00 ShareA 20€
;P 2014-01-01 00:00:00 ShareB 100€
2014-01-01 Details for shares
Equity:Opening Balance:Initial Investments
Assets:Investments:Stocks:Share A 15 ShareA @ 20€
Assets:Investments:Stocks:Share B 5 ShareB @ 100€


In that case, the same commands returns:
: 5800,00 € Assets
: 5000,00 € Bank:Check Account
: 800,00 € Investments:Stocks
: 300,00 € Share A
: 500,00 € Share B
: -5800,00 € Equity:Opening Balance
: -800,00 € Initial Investments
: --------------------
: 0

That's still fine, and in this case, I get the global value of the stocks in my
assets.

The, I sell some shares of stock B at a higher price, in both cases.
This is were problems rises for me.
; Selling 2 ShareB at 2014/04/01
2014-04-01 Selling 2 ShareB
Assets:Bank:Check Account 240,00 €
Assets:Investments:Stocks:Share B -2 ShareB {100€} @ 120€
Income:Investments Gains


In "case A", the result is:
5000,00 € Assets
5240,00 € Bank:Check Account
-240,00 € Investments:Stocks
300,00 € Share A
260,00 € Share B
-800,00 € Total Value
-4960,00 € Equity
40,00 € Capital Gains
-5000,00 € Opening Balance
-40,00 € Income:Investments Gains
--------------------
0

In "case B", the result (hopefully) looks similar:
5800,00 € Assets
5240,00 € Bank:Check Account
560,00 € Investments:Stocks
300,00 € Share A
260,00 € Share B
-5760,00 € Equity
40,00 € Capital Gains
-5800,00 € Opening Balance
-800,00 € Initial Investments
-40,00 € Income:Investments Gains
--------------------
0

I can see that I sold 2 stocks for 240€, and I can see my gain which is
2*(120-100)=40€, that's great.

There are a few things that bother me:
1) The presence of the "Equity:Capital Gains", which I understand balances the
entry, but it is automatically added and I can't choose its name. I thought
the balance would go to the "Income:Investments Gains" in fact.

I found this discussion in the archives:
https://groups.google.com/forum/#!msg/ledger-cli/g9879yRsTBo/WQQDO7zCkWQJ

Which ends-up saying it cannot be handled, however I found this commit in the
older repository:
https://gitorious.org/ledger/ledger/commit
/edc971b49370ec12f904a946f4be09f9e7005b52,
and I could find the code in the current GitHub.

So, what's the status for this question ?

2) The left-over for "Share B" shows 260€, which does not render the fact that
I still have 3 plain stocks, which would at least value 300€ (from their
initial value), or 360€ for the current value.

In the documentation example, everything was sold at one time, so we are not in
the same situation.

3) I would like to have a view of the left-over value of the stocks, at time
"t". In "case A", the total still show "800€" which is the initial value,
and in "case B", I see 560€, which reflects the "800€-240€", but not the
sum of the 10 ShareA and the 3 remaining ShareB.

I understand that it shows what it has to show, but I would like to know how to
set things up, so that I can see what I described there.

I also tried to play with lines like:
P 2014-01-01 00:00:00 ShareB 100€
P 2014-04-01 00:00:00 ShareB 120€

In that case, I can toggle the display of share or values with the option "-V",
which gets close to what I want.

However, I run into other issues, mainly that it only uses the last value, even
for the initial value, and if I mix the notation in "plain shares" (-2 ShareB)
and the notation with attributed values (-2 ShareB {100€} @ 120€), they don't
get along very well.

So, I prefered to narrow things down and start with my first two examples.

Thank you for having the patience to read that all way through.

Do you have any clues or advices so I can get a clear view of stocks in my
files, in accountancy terms, and with ledger in particular ?

Any help will be appreciated,
Thanks in advance,

Christophe S.

[Martin Michlmayr]

* Christophe Schockaert <R3vL...@citadels.be> [2014-06-11 22:10]:

> ; Details of shares at 2014/01/01
> 2014-01-01 Details for shares
> Assets:Investments:Stocks:Total Value

This is incorrect (see below).

> Assets:Investments:Stocks:Share A 15 ShareA @ 20€
> Assets:Investments:Stocks:Share B 5 ShareB @ 100€
>
> Then, I run "ledger --real -B -V balance", which returns:
> : 5000,00 € Assets
> : 5000,00 € Bank:Check Account
> : 0 Investments:Stocks
> : 300,00 € Share A
> : 500,00 € Share B
> : -800,00 € Total Value
> : -5000,00 € Equity:Opening Balance
> : --------------------
> : 0
>
> That's fine. I understand that doing this, only the gains and loss will end in
> the assets. I put the "Total Value" entry in order to see at a glance the value
> of all shares.

This "Total Value" entry is incorrect. It's not the total value, but
the cost. You have share A worth 300 and share B worth 500. How did
you pay for those shares? You either have to reduce cash, or your
case, use Equity:Opening Balance.

What you want is something like this:

2014-01-01 Details for shares

Assets:Investments:Stocks:Share A 15 ShareA @ 20€
Assets:Investments:Stocks:Share B 5 ShareB @ 100€

Equity:Opening Balance

Also, -B -V doesn't make much sense together. -B is the cost (what
you paid), -V is the current value (what it's worth now). Look here:

2014-01-01 Details for shares

Assets:Investments:Stocks:Share A 15 ShareA @ 20€
Assets:Investments:Stocks:Share B 5 ShareB @ 100€

Equity:Opening Balance

P 2014-02-01 ShareA 30€

bal 'Assets:Investments:Stocks:Share A' -B
€300 Assets:Investments:Stocks:Share A
bal 'Assets:Investments:Stocks:Share A' -V
€450 Assets:Investments:Stocks:Share A

> In my second try (case B), I wish to see the contribution of my stocks within
> the assets (I don't know if that's a valid practice but it gives me an overview
> of the whole money available a time "t").

I don't know what you're trying to say exactly, but in any case, this
is the correct accounting practice to use.

> The, I sell some shares of stock B at a higher price, in both cases.
> This is were problems rises for me.

...

> There are a few things that bother me:
> 1) The presence of the "Equity:Capital Gains", which I understand balances the
> entry, but it is automatically added and I can't choose its name. I thought
> the balance would go to the "Income:Investments Gains" in fact.

These Equity entries ledger generates are wrong. This is bug
http://bugs.ledger-cli.org/show_bug.cgi?id=712
The good news is that John and I agreed on a fix recently. You can
try the 713-costs branch and see if it does what you expect.

> So, what's the status for this question ?

There's a fix. We're just waiting for more people to test it.

> 2) The left-over for "Share B" shows 260€, which does not render the fact that
> I still have 3 plain stocks, which would at least value 300€ (from their
> initial value), or 360€ for the current value.

This is http://bugs.ledger-cli.org/show_bug.cgi?id=713
which should be fixed on the 713-costs branch.

> 3) I would like to have a view of the left-over value of the stocks, at time
> "t". In "case A", the total still show "800€" which is the initial value,
> and in "case B", I see 560€, which reflects the "800€-240€", but not the
> sum of the 10 ShareA and the 3 remaining ShareB.

I'm not entirely sure I understand, but with the 713-costs branch,
you should get the correct result:

bal 'Assets:Investments:Stocks' -B
600,00 € Assets:Investments:Stocks
300,00 € Share A
300,00 € Share B
--------------------
600,00 €

bal 'Assets:Investments:Stocks' -V
660,00 € Assets:Investments:Stocks
300,00 € Share A
360,00 € Share B
--------------------
660,00 €

-B is the cost: you originally paid 20 for A and 100 for B: 3 * 100 + 15*20 = 600

-V is the current value. A is worth 20, B is worth 120: 3 * 120 + 15*20 = 660

> However, I run into other issues, mainly that it only uses the last value, even
> for the initial value, and if I mix the notation in "plain shares" (-2 ShareB)
> and the notation with attributed values (-2 ShareB {100€} @ 120€), they don't
> get along very well.

I'm not sure I understand.

BTW, you use Assets:Investments:Stocks:Share A and Assets:Investments:Stocks:Share B
as account names. General practice on this list appears to be not to
use too many subaccounts. I'd just use Assets:Investments:Stocks for
both. If you want to see what Share A is worth only, you can do
-l "commodity == 'ShareA'"

--
Martin Michlmayr
http://www.cyrius.com/

[Martin Blais]

Christophe,

I was going to reply with the detail to this email, but I decided to write it up in my cookbook instead:

http://furius.ca/beancount/doc/cookbook

I've been fantasizing for a while about writing up detailed examples for all the things I've discovered for myself about bookkeeping over the years, and waiting to finish the whole document just hasn't served me well, might as well deliver it piecemeal. So here I wrote up much of the "Stock Trading" section for you, and I hope you will find it explains well how this is meant to be booked in our systems, Ledger or Beancount the same (I think).

Michael: I don't understand how it works in your example. You're entering the positions with price conversion, using the @ sign:

2014-01-01 Details for shares

   Assets:Investments:Stocks:Share A    15 ShareA @ 20€
   Assets:Investments:Stocks:Share B    5 ShareB @ 100€

   Equity:Opening Balance

But his sale is using the "cost" syntax (with {}):

2014-04-01 Selling 2 ShareB
  Assets:Bank:Check Account             240,00 €
  Assets:Investments:Stocks:Share B     -2 ShareB {100€} @ 120€
  Income:Investments Gains

How does this work under the covers?

I'm not sure how Ledger works in this regard actually, I'd love to know, I should probably dig in the source code. In my system, these are two completely different kinds of events. The first one is a "price conversion", such that no memory is maintained regarding the cost of the units converted. The price/rate is used to balance the transaction but the units are deposited in the account without associated cost. This is what you would do, for instance, if you were making a withdrawal in a foreign country, e.g. in JPY and then spend it locally: you never think of these yen in your wallet as "being held at such and such" exchange rate, once converted, they have no "cost" to them anymore, all the bills are just "yen". (In fact, if you're homed in multiple countries and hold assets in many places, holding currencies at cost creates incredible unnecessary complexities, I'm quite certain this is not what's happening). These conversion events are relatively rare in practice.

The semantics I attach to the second one is explained in a good amount of detail in the link to my document above. But it requires that when I enter a position, I say "this is held at cost" by virtue of using the {} syntax. It then attaches the cost to the lot as it accumulates balances for each posting of an account.

If someone could provide some insight, that would be interesting.

Finally, without the fix you provided in the github ticket, I don't understand how you've been using the system to track capital gains until now. With virtual transactions? How did this use to work?

Thank you,

--

---
You received this message because you are subscribed to the Google Groups "Ledger" group.
To unsubscribe from this group and stop receiving emails from it, send an email to ledger-cli+...@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

[Martin Michlmayr]

* Martin Blais <bl...@furius.ca> [2014-06-12 02:05]:

> I don't understand how it works in your example. You're entering
> the positions with price conversion, using the @ sign:
>
> 2014-01-01 Details for shares
> Assets:Investments:Stocks:Share A 15 ShareA @ 20€
> Assets:Investments:Stocks:Share B 5 ShareB @ 100€
> Equity:Opening Balance
>
> But his sale is using the "cost" syntax (with {}):

...

> How does this work under the covers?

When you specify a cost with @, ledger internally keeps this cost
around. You'll have 15 ShareA {€20}. You can see this with
--lot-prices:
15 ShareA {€20} Share A
5 ShareB {€100} Share B

So when you sell, what you do by saying 5 ShareB {€100} @ €120 is that
you're selling the 5 particular ShareB with a cost of €100.

Consider you bought 5 ShareB at 100€ and 5 at 110€. Then you'd have:

5 ShareB {€100}
5 ShareB {€110} Share B

So which one are you selling? You indicate that by using the {..}
syntax.

> The price/rate is used to balance the transaction but the units are
> deposited in the account without associated cost. This is what you
> would do, for instance, if you were making a withdrawal in a foreign
> country, e.g. in JPY and then spend it locally: you never think of
> these yen in your wallet as "being held at such and such" exchange
> rate, once converted, they have no "cost" to them anymore, all the
> bills are just "yen".

I think I actually disagree with this particular example. When I get
JPY out of an ATM, I'd say that a very clear exchange rate is attached
to those JPY (because you know exactly how much you paid in your local
currency).

But let's imagine you travel to Japan twice and you still have some
JPY left over from the previous trip. And now you go to the ATM
again. Then you have, let's say:

20000 JPY {$0.0090}
10000 JPY {$0.0098} Assets:Cash

So now things get complicated. The best solution would probably be to
just calculate the average cost and use that.

I suspect most people don't handle this correctly (me included :)
When you use --lot-prices, you actually see what's going on. I
suspect most people do something like this:

2014-03-01 * Buy JPY
Assets:Cash 10000 JPY @@ $98
Assets:Cash

2014-03-02 * Spend JPY
Expenses:Foo 10000 JPY
Assets:Cash -10000 JPY

And now ledger will say: ledger -f ~/e bal ets:cash
$-98 Assets:Cash

So everything *looks* great. But if you add --lot-prices, you see
that it's a mess:

$-98
-10000 JPY
10000 JPY {$0.0098} Assets:Cash

You have -10000 JPY and 10000 JPY {$0.0098}. This is because you
didn't specify the cost. You should have done:

2014-03-02 * Spend JPY
Expenses:Foo 10000 JPY {$0.0098}
Assets:Cash -10000 JPY {$0.0098}

But who does that?

> (In fact, if you're homed in multiple countries and hold assets in
> many places, holding currencies at cost creates incredible
> unnecessary complexities, I'm quite certain this is not what's
> happening).

Yes, I agree. Unfortunately, this situation applies to me and I
struggle with this all the time.

> Finally, without the fix you provided in the github ticket, I don't
> understand how you've been using the system to track capital gains until
> now. With virtual transactions? How did this use to work?

Fortunately, I don't have a lot of realized capital gains, so I was
mostly able to avoid the problem.

[Martin Blais]

On Thu, Jun 12, 2014 at 10:27 AM, Martin Michlmayr <t...@cyrius.com> wrote:

* Martin Blais <bl...@furius.ca> [2014-06-12 02:05]:

> I don't understand how it works in your example. You're entering
> the positions with price conversion, using the @ sign:
>
> 2014-01-01 Details for shares
>    Assets:Investments:Stocks:Share A    15 ShareA @ 20€
>    Assets:Investments:Stocks:Share B    5 ShareB @ 100€
>    Equity:Opening Balance
>
> But his sale is using the "cost" syntax (with {}):

...

> How does this work under the covers?

When you specify a cost with @, ledger internally keeps this cost
around.  You'll have 15 ShareA {€20}.  You can see this with
--lot-prices:
     15 ShareA {€20}    Share A
     5 ShareB {€100}    Share B

So when you sell, what you do by saying 5 ShareB {€100} @ €120 is that
you're selling the 5 particular ShareB with a cost of €100.

Consider you bought 5 ShareB at 100€ and 5 at 110€.  Then you'd have:

     5 ShareB {€100}
     5 ShareB {€110}    Share B

So which one are you selling?  You indicate that by using the {..}
syntax.

This is just like Beancount, except that Ledger price conversions are the same as "at-cost conversions."  That's what I thought: if I understand correctly, Ledger *always* tracks converted units with their cost, even when they're just price conversions. I think this will cause problems down the road.  Beancount treats these two cases differently on purpose; I believe this reflects the semantics of the operation closer and it allows me to solve the multi-currency conversion problem (more detail below about this).

> The price/rate is used to balance the transaction but the units are
> deposited in the account without associated cost. This is what you
> would do, for instance, if you were making a withdrawal in a foreign
> country, e.g. in JPY and then spend it locally: you never think of
> these yen in your wallet as "being held at such and such" exchange
> rate, once converted, they have no "cost" to them anymore, all the
> bills are just "yen".

I think I actually disagree with this particular example.  When I get
JPY out of an ATM, I'd say that a very clear exchange rate is attached
to those JPY (because you know exactly how much you paid in your local
currency).

It is true that this is a choice. Let's say you like in the US but you infrequently travel to Europe. You *could* attempt to book all your EUR expenses to lots at a particular cost. In practice, if you had to specify the particular lot each time, this would be a impractical and a major PIA, not even taking into account that that's not how you think about these euros (they're just EUR when you spend them, you never think "oh I'll spend my euros from the first withdrawal because I got a better rate on them". The bills all look the same in your wallet. And they are indeed fungible.). I'll argue that even if you build in an easy way to automatically choose a lot - say you don't specify which lot of your EUR you're spending when you subtract from an account - you really could build in a mechanism that selects a random lot by default, or a specific method, such as FIFO, it's still confusing to do so. What if you have 3 or more currencies?

But let's imagine you travel to Japan twice and you still have some
JPY left over from the previous trip.  And now you go to the ATM
again.  Then you have, let's say:

 20000 JPY {$0.0090}
 10000 JPY {$0.0098}  Assets:Cash

So now things get complicated.  The best solution would probably be to
just calculate the average cost and use that.

The best solution IMHO models how you think about the world.  They're just JPY to me.

What would do if you have accounts in two countries, in two different currency units, and you make withdrawals in JPY from these two accounts? Now you have "JPY held in USD" and "JPY held in EUR", for instance. Madness. And what happens if you make a conversion in the other direction? I do this all the time: I live in the US but have some remaining non-liquid assets in Canada. For a while I was doing transfers out from CAD to USD, but sometimes, in order to pay a tax liability to the Canadian govt, I might have a cash flow problem, I might have to make some temporary transfers the other way around from USD to CAD in order to make the payment. So now what... I have "USD in CAD" and "CAD in USD"?  No way... that's just not how I think about it and that's also not how I want to track it.

I suspect most people don't handle this correctly (me included :)
When you use --lot-prices, you actually see what's going on.  I
suspect most people do something like this:

2014-03-01 * Buy JPY
   Assets:Cash                10000 JPY @@ $98
   Assets:Cash

2014-03-02 * Spend JPY
   Expenses:Foo               10000 JPY
   Assets:Cash               -10000 JPY

And now ledger will say: ledger -f ~/e bal  ets:cash
                $-98  Assets:Cash

So everything *looks* great.  But if you add --lot-prices, you see
that it's a mess:

                $-98
          -10000 JPY
 10000 JPY {$0.0098}  Assets:Cash

This is scary confusing to me. Imagine you have a realistic scenario with 10's of transfers in all directions? How can you check that the numbers even make any sense?

 

You have -10000 JPY and 10000 JPY {$0.0098}.  This is because you
didn't specify the cost.  You should have done:

2014-03-02 * Spend JPY
   Expenses:Foo               10000 JPY {$0.0098}
   Assets:Cash               -10000 JPY {$0.0098}

But who does that?

Nobody (I hope).

> (In fact, if you're homed in multiple countries and hold assets in
> many places, holding currencies at cost creates incredible
> unnecessary complexities, I'm quite certain this is not what's
> happening).

Yes, I agree.  Unfortunately, this situation applies to me and I
struggle with this all the time.

So I believe I've cracked that problem in my code rewrite last year, and it took me a long, long while to figure out an elegant solution that works without a currency home. My solution - and i'm always open to new ideas for a better one, a better one *may* exist, I'm not claiming that this is the only solution - is to treat the two types of conversions differently. The price conversions (using @) do not attach cost to the units, only conversions at cost, (i.e., using {}). The cost is forgotten in the resulting balance (the transaction still has the price attached to it, of course). The resulting balancing algorithm for a transaction is simple:

https://hg.furius.ca/public/beancount/file/0a5f9bba3085/src/python/beancount/core/balance.py#l21

The problem that arises with this solution is that if you sum up the balance of all the transactions, such as you do when you compute balances for a balance sheet, the total amounts does not necessarily balance to zero. Instead, what you'll find is the inverse of the sum of all price conversions. That is, if you convert all of these amounts to a single currency in order to zero them out, the rate at which you would have to do so is the weighted average weight of all the conversion amounts (there's something elegant about that fact actually).

So how do I zero out the balance sheet so that it is *precisely* exact? Like everything else in Beancount: I add a transaction. This is an automatically inserted transaction that shows up in the journal, BTW, the user can see it, and it books to Equity:Conversions. I had a design choice to make here: Let's see I have 3 conversion amounts to zero out, in 3 different currencies. I could have (a) chosen one currency as "special" and converted all the other amounts to this currency at some set of rates which makes the whole transaction balance. This has the advantage that it results in a regular-looking transaction, albeit all to Equity accounts. (Note that figuring out the rates to do this is not a trivial problem given the general problem of N currencies to zero out - it admits many solutions - but I think it's possible to come up with one solution using the history of rate conversions and an optimization routine.) Or, (b) refuse to identify any of the legs as "special" and balance all of these legs at a degenerate "0 exchange rate."  (B) is a bit ugly, because in a sense it is really just patching up the balance to sum up to zero. "We want it to be zero so let's make it zero." Not super nice, but...  this is the one and only place where such a funny kludge appears in the balance calculation, and it holds the advantage that the resulting system truly is currency-agnostic and I don't have to play tricks to find workable rates. I like that better.

So I chose solution (b).  It's here:

https://hg.furius.ca/public/beancount/file/0a5f9bba3085/src/python/beancount/ops/summarize.py#l187

In practice, it has never failed me and does not even cause weirdness. If you draw out a balance sheet, you see two conversion entries: one to account for all the conversions occurring before the beginning of the exercise, and another one to account for the conversions during the period of exercise. I'm even finding these entries useful now, because they report on the sum total of the flow of currencies between all accounts during those periods. The numbers produced are useful.

This is a complicated topic. I'd be really interested to hear about other alternatives, perhaps better solutions.

[Rick F]

The method used in the cookbook, is not correct.

The cookbook uses the example of buying 10 shares of IBM at $160 and then selling those shares at $170.  Without commissions, that would amount to a realized gain of $100, $1700 (the sales price) - $1600 (the cost basis).  With commissions, however, the reportable gain is really $1700 (the sales price) - $9.95 (commission on the sale) - $1600 (basis) - $9.95 (commission on purchase) = $80.10.  The method in the cookbook only accounts for the sale commission when figuring the capital gain.

So what's the right way to do this, accounting for commission on purchase.  Note that if only part of the purchased shares are sold the commission is prorated, so the actual basis is $1609.95 or $160.995 per share.

[Martin Blais]

On Fri, Jun 13, 2014 at 9:01 AM, Rick F <ri...@farnbach.com> wrote:

The method used in the cookbook, is not correct.

That's not exactly true: it's correct if your definition of capital gain does not take into account commissions. I used the definition that excludes capital gains in order to keep things simple (the level of discussion I chose in that doc is that congruent with trying to explain P/L clearly, intro level). Whether you should be able to exclude commissions or not from your gains is a matter tax law, and it depends on your own situation.

Thanks for bringing up the topic, though, I admit I did forget to explain it. I'll add a separate section about commissions. I'm well aware of this.

At the moment, I simply subtract the commissions from the gains for reporting. The gains and commissions get counted in separate accounts (which is mostly fine, except for the prorating detail you bring up below). You can subtract the total commissions from total gain for a good approximation of gains-without-commissions. Most of my trading accounts already provide a suitable 1099 so I use my own accounting on these accounts for tax planning and cross-checking against their calculations, and I use the 1099 forms for tax reporting.

The cookbook uses the example of buying 10 shares of IBM at $160 and then selling those shares at $170.  Without commissions, that would amount to a realized gain of $100, $1700 (the sales price) - $1600 (the cost basis).  With commissions, however, the reportable gain is really $1700 (the sales price) - $9.95 (commission on the sale) - $1600 (basis) - $9.95 (commission on purchase) = $80.10.  The method in the cookbook only accounts for the sale commission when figuring the capital gain.

Actually, that's incorrect, the method I described does exclude the commission from the gain on BOTH sides and will thus calculate a gain of 100$ (no commissions at all).

So what's the right way to do this, accounting for commission on purchase.  Note that if only part of the purchased shares are sold the commission is prorated, so the actual basis is $1609.95 or $160.995 per share.

That's a great question.

Again, how it should be done is a matter of tax law, it's a choice, but this is indeed a sensible one in many cases. Simply subtracting the sum of the commissions - you could easily track them per account, e.g. using Expenses:US:ETrade:Commissions instead of Expenses:Financial:Commissions - from the capital gains provides a good approximation of the gains as you describe it, but as you point out, not a perfectly accurate one, especially if you hold positions across reporting periods (taxation years). It is appropriate to note that folding in the purchase commission on the cost basis is but one method to automatically pro-rate that commission into the gain - we could track it separately, per-lot - but it is one that is elegant and leads to an unambiguous implementation. I would like to be able to support it.

I don't have a good solution for doing this at the moment, but I want to think of one. 

In particular, we should design a method that leads to easy entry by a user, with a correspondingly nice syntax.

I'll be thinking about this; any ideas welcome.

[Martin Blais]

On Fri, Jun 13, 2014 at 9:57 AM, Martin Blais <bl...@furius.ca> wrote:

On Fri, Jun 13, 2014 at 9:01 AM, Rick F <ri...@farnbach.com> wrote:

The method used in the cookbook, is not correct.

That's not exactly true: it's correct if your definition of capital gain does not take into account commissions. I used the definition that excludes capital gains in order to keep things simple (the level of discussion I chose in that doc is that congruent with trying to explain P/L clearly, intro level). Whether you should be able to exclude commissions or not from your gains is a matter tax law, and it depends on your own situation.

Thanks for bringing up the topic, though, I admit I did forget to explain it. I'll add a separate section about commissions. I'm well aware of this.

At the moment, I simply subtract the commissions from the gains for reporting. The gains and commissions get counted in separate accounts (which is mostly fine, except for the prorating detail you bring up below). You can subtract the total commissions from total gain for a good approximation of gains-without-commissions. Most of my trading accounts already provide a suitable 1099 so I use my own accounting on these accounts for tax planning and cross-checking against their calculations, and I use the 1099 forms for tax reporting.

The cookbook uses the example of buying 10 shares of IBM at $160 and then selling those shares at $170.  Without commissions, that would amount to a realized gain of $100, $1700 (the sales price) - $1600 (the cost basis).  With commissions, however, the reportable gain is really $1700 (the sales price) - $9.95 (commission on the sale) - $1600 (basis) - $9.95 (commission on purchase) = $80.10.  The method in the cookbook only accounts for the sale commission when figuring the capital gain.

Actually, that's incorrect, the method I described does exclude the commission from the gain on BOTH sides and will thus calculate a gain of 100$ (no commissions at all).

So what's the right way to do this, accounting for commission on purchase.  Note that if only part of the purchased shares are sold the commission is prorated, so the actual basis is $1609.95 or $160.995 per share.

That's a great question.

Again, how it should be done is a matter of tax law, it's a choice, but this is indeed a sensible one in many cases. Simply subtracting the sum of the commissions - you could easily track them per account, e.g. using Expenses:US:ETrade:Commissions instead of Expenses:Financial:Commissions - from the capital gains provides a good approximation of the gains as you describe it, but as you point out, not a perfectly accurate one, especially if you hold positions across reporting periods (taxation years). It is appropriate to note that folding in the purchase commission on the cost basis is but one method to automatically pro-rate that commission into the gain - we could track it separately, per-lot - but it is one that is elegant and leads to an unambiguous implementation. I would like to be able to support it.

I don't have a good solution for doing this at the moment, but I want to think of one. 

In particular, we should design a method that leads to easy entry by a user, with a correspondingly nice syntax.

I'll be thinking about this; any ideas welcome.

Some more thinking about this: one possible method would be to allow the user to
indicate that a particular leg of the transaction is meant to be folded into the
cost of another leg's, something I would implement like the following.

First let's make up an example of a transaction the way they're currently done,
with the full capital gain, that is, one that unfairly (to you) includes
commissions:

2014-06-13 * "Transactions with Commissions"
  Assets:US:Invest:Cash            -5009.95 USD                  ;  5009.95 USD
  Expenses:US:Invest:Commissions       9.95 USD                  ;     9.95 USD
  Assets:US:Invest:GOOG               10.00 GOOG {500.00 USD}    ;  5000.00 USD

2014-06-20 * "Transactions with Commissions"
  Assets:US:Invest:GOOG              -10.00 GOOG {500.00 USD}    ; -5000.00 USD
  Expenses:US:Invest:Commissions       9.95 USD                  ;     9.95 USD
  Assets:US:Invest:Cash             5090.05 USD                  ;  5090.05 USD
  Income:US:Invest:PnL              -100.00 USD                  ;  -100.00 USD

This is what we need to fix, we want the gain to become 100 USD - 2 x 9.95 USD.

So now let's introduce a flag into the cost syntax (there's already a flag
available on each posting):

2014-06-13 * "Transactions with Commissions"
  Assets:US:Invest:Cash              -5009.95 USD                  ;  5009.95 USD
  + Expenses:US:Invest:Commissions       9.95 USD                  ;     0.00 USD (+)
  Assets:US:Invest:GOOG                 10.00 GOOG {500.00 USD +}  ;  5009.95 USD =  10.00 GOOG {500.995 USD}

2014-06-20 * "Transactions with Commissions"
  Assets:US:Invest:GOOG                -10.00 GOOG {2014-06-13 -}  ; -5009.95 USD = -10.00 GOOG {500.995 USD}
  - Expenses:US:Invest:Commissions       9.95 USD                  ;     0.00 USD (-)
  Assets:US:Invest:Cash               5090.05 USD                  ;  5090.05 USD
  Income:US:Invest:PnL                 -80.10 USD                  ;   -80.10 USD

When a flag is encountered in the cost, this says, add to the total cost the
amount from postings marked with that flag. In the case of the buy, this
means (500.00 x 10 + 9.95) is the new total cost and the cost per unit is now
500.995 USD. This works to give you a capital gain as you desired.

However, here are some drawbacks:

- You cannot use the cost basis of the lot to identify it anymore (the lot is
  now at a different cost basis, 500.995 USD instead of 500.00 USD, and that
  amounts shows up nowhere, we cannot realistically expect the user to know it).

  That may not be a bad thing though, I never liked identifying lots by their
  cost in the first place, I prefer a method that uses a label or the date to
  disambiguate. When reducing a position, the purpose of that cost really is
  just to identify which lot we should be using to reduce the position. I
  already have a proposal to make this better, by using labels or dates instead
  of cost (see sale transaction).

- A bigger problem, however, and one that is preventing me from fully going
  ahead with this idea just yet, is that the expenses are gone. The expenses leg
  just cannot post to the commissions account, because its amount has been
  incorporated into the cost of the position. Allowing the cost to show up in
  both places would break the accounting equation; this transaction must balance.

Maybe we should add an Income leg to absord the Expenses amount and post it
anyhow?

2014-06-13 * "Transactions with Commissions"
  Assets:US:Invest:Cash              -5009.95 USD                  ;  5009.95 USD
  + Expenses:US:Invest:Commissions       9.95 USD                  ;     0.00 USD (+)
  + Income:US:Invest:Commissions-Rebate
  Assets:US:Invest:GOOG                 10.00 GOOG {500.00 USD +}  ;  5009.95 USD =  10.00 GOOG {500.995 USD}

I'm not quite sure how to solve this yet.

Ideas welcome,

[Martin Blais]

I documented in more detail the problem that Rick brought up in a section in the cookbook document I'm building:

https://docs.google.com/document/d/1WjARst_cSxNE-Lq6JnJ5CC41T3WndEsiMw4d46r2694/edit#heading=h.gy32bzj5jx1w

(Feel free to comment.)

[Jostein Berntsen]

Would it be useful with a python function here, something like this?

https://gist.github.com/kevin3/5119111

Jostein
 

[Martin Blais]

Jostein, 

The challenge is not calculating the commission; the challenge is designing a scheme, a recipe for how to enter this as a DE transaction, for putting down the various legs of the transaction in a way that automatically accumulates the correct capital gain - in this case, the capital gain without commissions and other costs, with the acquisition commissions pro-rated to the number of shares sold. Requirements of the design are:

- The user should not have to calculate anything manually, all numbers needed to book the transaction should be readily available from a typical confirmation statement

- The transaction's postings have to balance according to some set of universal rules for balancing postings; right now Beancount uses this rule:

https://hg.furius.ca/public/beancount/file/181d0d299c87/src/python/beancount/core/complete.py#l20

The code you quoted is something that in Beancount you would have you put into a plugin for yourself, to make your own data entry easier. That is, your plugin would go through all the transactions after the parsing stage, detecting those that match your criteria for trades on these platforms (e.g. "one leg is in account Asssets:SG:StandardChartered"), and transforming them by generating new ones that have the commissions leg added in. This would make your data entry easier. Ledger has such a transformation mechanism in its expression language; Beancount takes the route of allowing you to process all your entries in Python instead of providing a custom expression parser. 

But for both systems, the problem that remains is in how to book the gains right in the most elegant way.

I'll make the unfortunate observation here that if you relax the requirement that a transaction balance by allowing, for example, virtual transactions, you can solve the problem, but then you can't draw a balance sheet anymore (you break the accounting equation). This is not a solution IMO: you can "solve" any problem by allowing any arbitrary counting, which is what virtual transactions are, but you lose what I think of as an essential property of the counting method).

[Jostein Berntsen]

On 11.06.14,22:10, Christophe Schockaert wrote:
> Hi all,
>

....

Can you try this way to do it?

dat file:

; Initial openings at 2014/01/012014-01-01 Opening Balance

Assets:Bank:Check Account 5000,00 €
Equity:Opening Balance


; Details of shares at 2014/01/01
;P 2014-01-01 00:00:00 ShareA 20€
;P 2014-01-01 00:00:00 ShareB 100€
2014-01-01 Details for shares
Equity:Opening Balance:Initial Investments

Assets:Investments:Stocks:ShareA 15 ShareA @ 20€

Assets:Investments:Stocks:ShareB 5 ShareB @ 100€

; Selling 2 ShareB at 2014/04/01
2014-04-01 Selling 2 ShareB

Assets:Investments:Stocks:ShareB -2 ShareB @ 100€
Assets:SoldStocks:SoldB 2 ShareB @ 120€
Income:Investment Gains

; Selling 2 ShareB at 2014/04/01

2014-04-02 Selling 4 ShareA
Assets:Investments:Stocks:ShareA -4 ShareA @ 20€
Assets:SoldStocks:SoldA 4 ShareA @ 30€
Income:Investment Gains

Output:

jostein:~/Documents/Finans/ledger/testing$ ledger -B bal -f testshares.dat
5880,00 € Assets
5000,00 € Bank:Check Account
520,00 € Investments:Stocks
220,00 € ShareA
300,00 € ShareB
360,00 € SoldStocks
120,00 € SoldA
240,00 € SoldB

-5800,00 € Equity:Opening Balance
-800,00 € Initial Investments

-80,00 € Income:Investment Gains
--------------------
0

jostein:~/Documents/Finans/ledger/testing$ ledger -V bal -f testshares.dat
6050,00 € Assets
5000,00 € Bank:Check Account
690,00 € Investments:Stocks
330,00 € ShareA
360,00 € ShareB
360,00 € SoldStocks
120,00 € SoldA
240,00 € SoldB

-5800,00 € Equity:Opening Balance
-800,00 € Initial Investments

-80,00 € Income:Investment Gains
--------------------
170,00 €


Jostein

[Jostein Berntsen]

Martin: Does this seem right for you?

Jostein

[Martin Michlmayr]

* Jostein Berntsen <jbe...@broadpark.no> [2014-06-20 04:26]:

> > ; Selling 2 ShareB at 2014/04/01
> > 2014-04-01 Selling 2 ShareB
> > Assets:Investments:Stocks:ShareB -2 ShareB @ 100€
> > Assets:SoldStocks:SoldB 2 ShareB @ 120€
> > Income:Investment Gains
> >

> Martin: Does this seem right for you?

I'm not sure what Assets:SoldStocks:SoldB is for. If you're selling
two stocks for cash, you get cash; but in your example, you still have
those 2 stocks as an asset:

ledger -f qq bal Assets:SoldStocks:SoldB
2 ShareB Assets:SoldStocks:SoldB

What you want is this:

; Initial openings at 2014/01/01

2014-01-01 Opening Balance
Assets:Bank:Check Account 5000,00 €
Equity:Opening Balance

; Details of shares at 2014/01/01
;P 2014-01-01 00:00:00 ShareA 20€
;P 2014-01-01 00:00:00 ShareB 100€
2014-01-01 Details for shares
Equity:Opening Balance:Initial Investments
Assets:Investments:Stocks:ShareA 15 ShareA @ 20€
Assets:Investments:Stocks:ShareB 5 ShareB @ 100€

; Selling 2 ShareB at 2014/04/01
2014-04-01 Selling 2 ShareB

Assets:Investments:Stocks:ShareB -2 ShareB {100€} @ 120€
Assets:Cash 240€
Income:Investment Gains -40€

; Selling 2 ShareB at 2014/04/01
2014-04-02 Selling 4 ShareA

Assets:Investments:Stocks:ShareA -4 ShareA {20€} @ 30€
Assets:Cash 120€
Income:Investment Gains -40€

[Martin Blais]

On Fri, Jun 20, 2014 at 8:50 AM, Martin Michlmayr <t...@cyrius.com> wrote:

* Jostein Berntsen <jbe...@broadpark.no> [2014-06-20 04:26]:

> > ; Selling 2 ShareB at 2014/04/01
> > 2014-04-01 Selling 2 ShareB
> >   Assets:Investments:Stocks:ShareB     -2 ShareB @ 100€
> >   Assets:SoldStocks:SoldB                    2 ShareB @ 120€
> >      Income:Investment Gains
> >

> Martin: Does this seem right for you?

I'm not sure what Assets:SoldStocks:SoldB is for.  If you're selling
two stocks for cash, you get cash; but in your example, you still have
those 2 stocks as an asset:

ledger -f qq bal Assets:SoldStocks:SoldB
            2 ShareB  Assets:SoldStocks:SoldB

That's indeed incorrect.

You don't have the assets anymore, you surely don't want them to show up on the balance sheet.

What you want is this:

; Initial openings at 2014/01/01

2014-01-01 Opening Balance
  Assets:Bank:Check Account                      5000,00 €
  Equity:Opening Balance

; Details of shares at 2014/01/01
;P 2014-01-01 00:00:00   ShareA 20€
;P 2014-01-01 00:00:00   ShareB 100€
2014-01-01 Details for shares
  Equity:Opening Balance:Initial Investments
  Assets:Investments:Stocks:ShareA    15 ShareA @ 20€
  Assets:Investments:Stocks:ShareB    5 ShareB @ 100€

; Selling 2 ShareB at 2014/04/01
2014-04-01 Selling 2 ShareB

  Assets:Investments:Stocks:ShareB     -2 ShareB {100€} @ 120€
  Assets:Cash                                   240€
  Income:Investment Gains                       -40€

; Selling 2 ShareB at 2014/04/01
2014-04-02 Selling 4 ShareA

  Assets:Investments:Stocks:ShareA     -4 ShareA {20€} @ 30€
  Assets:Cash                                   120€
  Income:Investment Gains                       -40€

What I think has been confusing some people is that the 120E and 30E values above are [correctly] ignored (with your patch).

They are going to be ignored, other than for adding an entry to the price DB, right?

If not, how are they going to be used?

[Martin Michlmayr]

* Martin Blais <bl...@furius.ca> [2014-06-20 23:16]:

> > ; Selling 2 ShareB at 2014/04/01
> > 2014-04-02 Selling 4 ShareA
> > Assets:Investments:Stocks:ShareA -4 ShareA {20€} @ 30€
> > Assets:Cash 120€
> > Income:Investment Gains -40€
>
> What I think has been confusing some people is that the 120E and 30E values
> above are [correctly] ignored (with your patch).
>
> They are going to be ignored, other than for adding an entry to the price
> DB, right?
> If not, how are they going to be used?

They are not ignored - they are used to calculate the gain/loss.

In other woods, this would not balance:

Assets:Investments:Stocks:ShareA -4 ShareA {20€} @ 30€
Assets:Cash 120€

Because you have 4 * 30, but also the 4*(30-20) gain, so you need:

Assets:Investments:Stocks:ShareA -4 ShareA {20€} @ 30€
Assets:Cash 120€
Income:Investment Gains -40€

I actually ran into something that's not ideal the other day. Since
ledger just caculates and doesn't understand what's going on, you
could happily do:

Assets:Investments:Stocks:ShareA -4 ShareA {20€} @ 30€

Assets:Cash 80€

This still balances, but doesn't represent the nature of the
transaction correctly -- it's exactly what you'd get without the
capital gain.

I'm not sure what to do about this... one solution is just to hope
people will get it right. Another would be new syntax to let people
give the name of an account the capital loss/gain should be booked to,
e.g.:

Assets:Investments:Stocks:ShareA -4 ShareA {20€} @ 30€ => Income:Capital gain
Assets:Cash 120€

Then Assets:Cash is 120 and Income:Capital gain is -40

How do you handle this in beancount?

[Martin Blais]

On Sat, Jun 21, 2014 at 9:22 AM, Martin Michlmayr <t...@cyrius.com> wrote:

* Martin Blais <bl...@furius.ca> [2014-06-20 23:16]:

> > ; Selling 2 ShareB at 2014/04/01
> > 2014-04-02 Selling 4 ShareA
> >   Assets:Investments:Stocks:ShareA     -4 ShareA {20€} @ 30€
> >   Assets:Cash                                   120€
> >   Income:Investment Gains                       -40€
>
> What I think has been confusing some people is that the 120E and 30E values
> above are [correctly] ignored (with your patch).
>
> They are going to be ignored, other than for adding an entry to the price
> DB, right?
> If not, how are they going to be used?

They are not ignored - they are used to calculate the gain/loss.

How?

 

In other woods, this would not balance:

   Assets:Investments:Stocks:ShareA     -4 ShareA {20€} @ 30€
   Assets:Cash                                   120€

If would not balance because -4x20 + 120 != 0, right?

So the 30E is ignored? How is it otherwise used?

Because you have 4 * 30, but also the 4*(30-20) gain, so you need:

   Assets:Investments:Stocks:ShareA     -4 ShareA {20€} @ 30€
   Assets:Cash                                   120€
   Income:Investment Gains                       -40€

What's the amount used to balance the first leg? -4x20, right?

Do you have an implicit deposit to some other account?

AFAIK -4x20 here is balanced against the other postings, 120 + -40, and THOSE are the ones really specifying the capital gain.

I actually ran into something that's not ideal the other day.  Since
ledger just caculates and doesn't understand what's going on, you
could happily do:

   Assets:Investments:Stocks:ShareA     -4 ShareA {20€} @ 30€

   Assets:Cash                                    80€

IMO that's a fine use case, I wouldn't worry about this. The transaction did not actually receive the gain his cash or other accounts, so there has been no gain materialized. The reality of these data entries is that the cash amount deposited to your account will always be available from the CSV / OFX downloads, so you never make a "mistake" on it. The cash deposit should always be the source of truth to determine the realized capital gain.

This still balances, but doesn't represent the nature of the
transaction correctly -- it's exactly what you'd get without the
capital gain.

I'm arguing (1) that it does: if you did not get the money, then you did not actually realize the gain, and (2) as above, that the user will never make a mistake on it. I've entered 8 years worth of data this way with no errors, because when I import that data, the cash deposit amount is always there. It works.

I'm not sure what to do about this... one solution is just to hope
people will get it right.  Another would be new syntax to let people
give the name of an account the capital loss/gain should be booked to,
e.g.:

   Assets:Investments:Stocks:ShareA     -4 ShareA {20€} @ 30€ => Income:Capital gain
   Assets:Cash                                    120€ 

Then Assets:Cash is 120 and Income:Capital gain is -40

That's an interesting idea IMO, but I think goes against the spirit of Ledger, which is very liberal and allows you to even book against lots that don't exist in the account's inventory (see below). The syntax is irregular and long-ish, but it's a cool idea! To interpret your example in more general terms, you're saying that a posting with the syntax 

  ACCOUNT UNITS {COST} @ PRICE => GAINS-ACCOUNT

will 

1. balance against other postings at a value of UNITS x COST, and,

2. automatically insert a posting at UNITS x (PRICE - COST).

This way, the "Cash" leg in your example does not have to be 120E, it's more flexible. It could be like this, for example:

   Assets:Investments:Stocks:ShareA     -4 ShareA {20€} @ 30€ => Income:Capital gain

   Assets:Cash                                    110.05€ 

   Expenses:Commissions                9.95€ 

 

This would transform into a transaction like this (could even be done with pre-processing):

   Assets:Investments:Stocks:ShareA     -4 ShareA {20€}

   Income:Capital gain                              -4 @ 10€

   Assets:Cash                                    110.05€ 

   Expenses:Commissions                9.95€ 

This balances because you have -80 + -40 + 110.05 + 9.95 = 0

If you do implement this, if I were in your shoes I would run a transformation stage that transforms the affected transactions instead of complicating the balancing logic.

How do you handle this in beancount?

My approach has been to balance against the UNITS x COST and let the other legs naturally specify the capital gain, like this:

   Assets:Investments:Stocks:ShareA     -4 ShareA {20€}

   Income:Capital gain                            

   Assets:Cash                                    110.05€ 

   Expenses:Commissions                9.95€ 

I'm using the difference in the balance to automatically calculate the gain. I don't always care about the market price per share (it should be clear by now you don't need it), but when I do, I add a @ 30€ to the ShareA leg, and Beancount would add this to the price database -- that's the only thing it would do with it (it's optional in the syntax), it would not get used in balancing in any way.

http://memegenerator.net/instance/51434312

The fact of the matter is, you need a certain number of elements to compute the gain, the syntax you propose just uses the price in the equation to compute the gain instead of the cash deposit. That proposal is not harvesting the opportunity to let Ledger extrapolate the missing amount, it specifies all amounts, but that's okay, you're essentially saying "you'll always have settlement price, so we can compute all the legs, no need for extrapolation." In a sense, this is more strict (I like that).  But... I claim that you don't always have the exact price of the transaction in the downloads (usually comes with the paper/PDF confirmation statements later on in the form of a settlement price). Moreover, the settlement price that you transacted at may not reflect the actual market price that you'd want to show in your price database, there are many reasons for this, e.g., the offer price differs from what you got vs. average day or closing prices that you might want in your DB or pull from Yahoo/Google, or maybe you traded something off-market, and using the price in the gains computation as you propose would implicitly force an off-market price entry in the database.

But I think it's an interesting idea, still. Maybe some people find method easier to grok.

If I were you, I'd make the new syntax something like this:

   Assets:Investments:Stocks:ShareA     -4 ShareA {20€} @ 30€ +

   Income:Capital gain                          +

   Assets:Cash                                    110.05€ 

   Expenses:Commissions                9.95€ 

whereby the "+" indicate where to report the computed gain.

Something like that. I've been toying with the idea of using such "references," for computing the gains sans commissions, but I haven't convinced myself I have some good use cases for this yet.

In the meantime, on a related topic maybe you can explain this to me:

2014-03-01 Details for shares
  Assets:Investments:Stocks     2 GOOG @ 500 USD
  Assets:Investments:Cash   -1000 USD

; This does not generate an error... it should.
2014-03-02 Details for shares
  Assets:Investments:Stocks    -2 GOOG {505 USD} @ 510 USD
  Assets:Investments:Cash


This demonstrates that Ledger is not strict about which lots you can reduce... there are no "GOOG @ 505 USD" lots here, yet it allows us to do this. Moreover, it created 20 USD out of thin air... the sum of all accounts does not balance. This is one of many things I don't understand about it: I cannot conceive correctly entering my data without (1) strict inventory booking against existing lots, that is, you cannot reduce a position against a lot that does not exist, as in the above, and (2) the sum total of all transactions _must_ balance to 0 in all units. Beancount enforces both, always. Even when making transformations, such as filtering the entries to show a balance sheet and income statement and moving amounts from income accounts to equity's net income, I check that the sum is zero.

$ ledger -f badstock.lgr  bal
              10 USD  Assets:Investments:Cash
              10 USD  Equity:Capital Gains
--------------------
              20 USD


Here is the detail:

$ ledger -f test.lgr  bal --lots

2 GOOG {500 USD} [2014/03/01]
   -2 GOOG {505 USD}
              10 USD  Assets:Investments
              10 USD    Cash
2 GOOG {500 USD} [2014/03/01]
   -2 GOOG {505 USD}    Stocks
              10 USD  Equity:Capital Gains
--------------------
2 GOOG {500 USD} [2014/03/01]
   -2 GOOG {505 USD}
              20 USD

And talking about strict inventory booking, even when you try to book against an existing lot it doesn't work:

2014-03-01 Details for shares
  Assets:Investments:Stocks     2 GOOG @ 500 USD
  Assets:Investments:Cash   -1000 USD

2014-03-02 Details for shares
  Assets:Investments:Stocks    -2 GOOG {500 USD} @ 510 USD
  Assets:Investments:Cash


$ ledger -f test.lgr  bal --lots

2 GOOG {500 USD} [2014/03/01]
   -2 GOOG {500 USD}  Assets:Investments:Stocks
              20 USD  Equity:Capital Gains
--------------------
2 GOOG {500 USD} [2014/03/01]
   -2 GOOG {500 USD}
              20 USD

So now we have both 2 GOOG @ 500 USD and -2 GOOG @ 500 USD?

Is this a bug? The only way I could make it balance is by specifying the date along.

[Jostein Berntsen]

Thanks, I see that error. But to me this seems more correct after some testing:

; Initial openings at 2014/01/012014-01-01 Opening Balance

Assets:Bank:Check Account 5000,00 €
Equity:Opening Balance


; Details of shares at 2014/01/01
;P 2014-01-01 00:00:00 ShareA 20 €
;P 2014-01-01 00:00:00 ShareB 100 €
2014-01-01 Details for shares
Equity:Opening Balance:Initial Investments
Assets:Investments:Stocks:ShareA 15 ShareA @ 20 €
Assets:Investments:Stocks:ShareB 5 ShareB @ 100 €

; Selling 2 ShareB at 2014/04/01
2014-04-01 Selling 2 ShareB

Assets:Investments:Stocks:ShareB -2 ShareB @ 100 €

Assets:Cash 240 €
Income:Investment Gains

; Details of shares at 2014/04/01
P 2014-04-01 00:00:00 ShareA 30 €

P 2014-04-01 00:00:00 ShareB 120 €

Selling 2 ShareA at 2014/05/02
2014-04-02 Selling 4 ShareA
Assets:Investments:Stocks:ShareA -4 ShareA @ 20 €

Assets:Cash 120 €
Income:Investment Gains

; Details of shares at 2014/05/01
P 2014-04-01 00:00:00 ShareA 70 €

P 2014-04-01 00:00:00 ShareB 120 €

Jostein

[Rick F]

"Allowing the cost to show up in

  both places would break the accounting equation; this transaction must balance."

So I've been thinking about this and I think that is the fundamental problem.  Technically, commission (at least in the U.S.) isn't an expense, it's an asset.  It sounds strange, but think about it.  In the U.S., the commission counts toward the cost basis and the cost basis is recorded with the asset.  It's the whole depreciate vs. expense issue in accounting.

I'm not sure how, but I think the solution is somewhere down that road.  Maybe creating a fake asset that is the commission per share, maybe a sub-asset.

The thing is, this isn't a new problem.  This isn't a ledger problem so much as an accounting problem.  Accountants (in the U.S. at least) already have to deal with this situation.  Does anyone know how accountants deal with this currently?

Rick

[Martin Blais]

On Wed, Jun 25, 2014 at 1:08 AM, Rick F <ri...@farnbach.com> wrote:

"Allowing the cost to show up in

  both places would break the accounting equation; this transaction must balance."

So I've been thinking about this and I think that is the fundamental problem.  Technically, commission (at least in the U.S.) isn't an expense, it's an asset.  It sounds strange, but think about it.  In the U.S., the commission counts toward the cost basis and the cost basis is recorded with the asset.  It's the whole depreciate vs. expense issue in accounting.

Commission is definitely not an asset.

I think I see what you mean though, and I think what you mean is this (please correct me if I'm wrong):

  2014-06-25 * "Buying into a position"

    Assets:Investments:Cash      -1009.95 USD

    Assets:Investments:Stock      10 STOCK {100 USD}

    Expenses:Commissions               9.95 USD

  2014-06-25 * "Cost basis adjustment for commission"

    Assets:Investments:Stock      -10 STOCK {100 USD}

    Assets:Investments:Stock       10 STOCK {100.995 USD}

    Income:Investments:Rebates          -9.95 USD

Now, the way that I carried out the cost basis adjustment above looks a bit inconvenient: remove all shares and replace them by new ones at the adjusted cost. This is because it is just what is supported in Beancount/Ledger right now. In the example above, I use a "rebate" account to absorb the cost adjustment: you would _not_ declare this as taxable income, it's just there for balancing the transaction.

  2014-08-04 * "Selling haf of my position"

    Assets:Investments:Stock      -5 STOCK {100.995 USD}

    Assets:Investments:Cash       540.05 USD

    Expenses:Commissions             9.95 USD

    Income:Investments:Gains      -35.075 USD

On the sale you don't have to make an adjustment, because the shares have been converted to cash.

Does this make sense?

So, the lesson from your comment is that it might be useful to create a syntax dedicated to easily make a cost basis adjustment. That syntax should transform into this (copied from above):

  2014-06-25 * "Cost basis adjustment for commission"

    Assets:Investments:Stock      -10 STOCK {100 USD}

    Assets:Investments:Stock       10 STOCK {100.995 USD}

    Income:Investments:Rebates          -9.95 USD

The same syntax could be used to make other types of cost basis adjustments.

I'll be updating my proposal with this comment:

https://docs.google.com/document/d/1F8IJ_7fMHZ75XFPocMokLxVZczAhrBRBVN9uMhQFCZ4/

 

 

I'm not sure how, but I think the solution is somewhere down that road.  Maybe creating a fake asset that is the commission per share, maybe a sub-asset.

The solution you're implying is to make a cost-basis adjustment.

The thing is, this isn't a new problem.  This isn't a ledger problem so much as an accounting problem.  Accountants (in the U.S. at least) already have to deal with this situation.  Does anyone know how accountants deal with this currently?

I'm curious now... did anyone ever check how GnuCash or QuickBooks deals with it?

[Martin Blais]

On Wed, Jun 25, 2014 at 11:14 AM, Martin Blais <bl...@furius.ca> wrote:

On Wed, Jun 25, 2014 at 1:08 AM, Rick F <ri...@farnbach.com> wrote:

"Allowing the cost to show up in

  both places would break the accounting equation; this transaction must balance."

So I've been thinking about this and I think that is the fundamental problem.  Technically, commission (at least in the U.S.) isn't an expense, it's an asset.  It sounds strange, but think about it.  In the U.S., the commission counts toward the cost basis and the cost basis is recorded with the asset.  It's the whole depreciate vs. expense issue in accounting.

Commission is definitely not an asset.

I think I see what you mean though, and I think what you mean is this (please correct me if I'm wrong):

  2014-06-25 * "Buying into a position"

    Assets:Investments:Cash      -1009.95 USD

    Assets:Investments:Stock      10 STOCK {100 USD}

    Expenses:Commissions               9.95 USD

  2014-06-25 * "Cost basis adjustment for commission"

    Assets:Investments:Stock      -10 STOCK {100 USD}

    Assets:Investments:Stock       10 STOCK {100.995 USD}

    Income:Investments:Rebates          -9.95 USD

Now, the way that I carried out the cost basis adjustment above looks a bit inconvenient: remove all shares and replace them by new ones at the adjusted cost. This is because it is just what is supported in Beancount/Ledger right now. In the example above, I use a "rebate" account to absorb the cost adjustment: you would _not_ declare this as taxable income, it's just there for balancing the transaction.

  2014-08-04 * "Selling haf of my position"

    Assets:Investments:Stock      -5 STOCK {100.995 USD}

    Assets:Investments:Cash       540.05 USD

    Expenses:Commissions             9.95 USD

    Income:Investments:Gains      -35.075 USD

On the sale you don't have to make an adjustment, because the shares have been converted to cash.

Actually, I screwed up a bit in the sell example above, sorry about that. 

You would _also_ have to make an adjustment to the sale side, but you would adjust the gains there. Like this (tested, this time):

2014-01-01 open Assets:Investments:Cash
2014-01-01 open Assets:Investments:Stock
2014-01-01 open Expenses:Commissions
2014-01-01 open Income:Investments:Rebates
2014-01-01 open Income:Investments:Gains

2014-06-25 * "Buying into a position"
  Assets:Investments:Cash   -1009.95 USD
  Assets:Investments:Stock        10 STOCK {100 USD}
  Expenses:Commissions          9.95 USD

2014-06-25 * "Cost basis adjustment for commission"
  Assets:Investments:Stock       -10 STOCK {100 USD}
  Assets:Investments:Stock        10 STOCK {100.995 USD}
  Income:Investments:Rebates   -9.95 USD


2014-08-04 * "Selling half of my position"
  Assets:Investments:Stock        -5 STOCK {100.995 USD}
  Assets:Investments:Cash     540.05 USD
  Expenses:Commissions          9.95 USD
  Income:Investments:Gains

2014-08-04 * "Cost basis adjustment for commission"
  Income:Investments:Rebates   -9.95 USD
  Income:Investments:Gains

Resulting trial balance from Beancount:

|-- Assets                          
|   `-- Investments                 
|       |-- Cash         -469.90 USD
|       `-- Stock         504.98 USD
|-- Expenses                        
|   `-- Commissions        19.90 USD
`-- Income                          
    `-- Investments                 
        |-- Gains         -35.08 USD
        `-- Rebates       -19.90 USD

The method I suggest in the document just does the adjustment transaction in the same transaction as the sale.

They're equivalent.

What I like about your suggestion is the idea of making cost basis adjustments simpler.

Maybe something like this could be implemented eventually:

2014-06-25 * "Cost basis adjustment for commission"
  Assets:Investments:Stock        10 STOCK {100 USD -> 100.995 USD}
  Income:Investments:Rebates   -9.95 USD

Or even better, without having to specify the merged amount!

2014-06-25 * "Cost basis adjustment for commission"
  Assets:Investments:Stock       -10 STOCK {100 USD}   ; disambiguate
  Assets:Investments:Stock        10 STOCK {}          ; let system auto-fill
  Income:Investments:Rebates   -9.95 USD

[Bruce Schultz]

On 25 June 2014 3:08:19 PM AEST, Rick F <ri...@farnbach.com> wrote:
>"Allowing the cost to show up in
>
>both places would break the accounting equation; this transaction must
>balance."
>
>
>So I've been thinking about this and I think that is the fundamental
>problem. Technically, commission (at least in the U.S.) isn't an
>expense, it's an asset. It sounds strange, but think about it. In the
>U.S., the commission counts toward the cost basis and the cost basis is
>recorded with the asset. It's the whole depreciate vs. expense issue
>in accounting.
>
>
>I'm not sure how, but I think the solution is somewhere down that road.
>Maybe creating a fake asset that is the commission per share, maybe a
>sub-asset.
>
>
>The thing is, this isn't a new problem. This isn't a ledger problem so
>much as an accounting problem. Accountants (in the U.S. at least)
>already have to deal with this situation. Does anyone know how
>accountants deal with this currently?

I found this the other day. It details the flows between various accounts in a wide range of situations.

http://timriley.net/appahost/accountancy_model.pdf

I found it helpful so far in getting my head around fund accounting. Hopefully, others can get some value from it too.

Bruce


--
:B

[Rick Farnbach]

Great find. Breaks all financial transactions into a series of templates to be followed. I'll definitely use that book as a reference going forward. 

In regards to the current conversation, here's the relevant quote. 

Stock Cost = (Shares Purchased × Price Per Share) + Commissions + Other Transaction Fees

 Notice that the commission is capitalized, not expensed. That's clearly where we have been going wrong. 

Rick

Sent from my iPhone

[Jostein Berntsen]

On 24.06.14,22:08, Rick F wrote:
> "Allowing the cost to show up in
>
> both places would break the accounting equation; this transaction must balance."
>
>
> So I've been thinking about this and I think that is the fundamental problem. Technically, commission (at least in the U.S.) isn't an expense, it's an asset. It sounds strange, but think about it. In the U.S., the commission counts toward the cost basis and the cost basis is recorded with the asset. It's the whole depreciate vs. expense issue in accounting.
>
>
> I'm not sure how, but I think the solution is somewhere down that road. Maybe creating a fake asset that is the commission per share, maybe a sub-asset.
>
>
> The thing is, this isn't a new problem. This isn't a ledger problem so much as an accounting problem. Accountants (in the U.S. at least) already have to deal with this situation. Does anyone know how accountants deal with this currently?
>
>
> Rick
>
>
> On Saturday, June 14, 2014 10:50:32 AM UTC-7, Martin Blais wrote:
> >

Would this then be the best way to calculate it?

; Initial openings at 2014/01/012014-01-01 Opening Balance

Assets:Bank:Check Account 5000,00 €
Equity:Opening Balance

; Details of shares at 2014/01/01

;P 2014-01-01 00:00:00 ShareA 20 €
;P 2014-01-01 00:00:00 ShareB 100 €
2014-01-01 Details for shares
Equity:Opening Balance:Initial Investments

Assets:Investments:Stocks:ShareA 15 ShareA @ 20 €

Assets:Investments:Stocks:ShareB 5 ShareB @ 100 €

; Details of shares at 2014/04/01

P 2014-04-01 00:00:00 ShareA 30 €

P 2014-04-01 00:00:00 ShareB 120 €

; Selling 2 ShareB at 2014/04/01

2014-04-01 Selling 2 ShareB

Assets:Investments:Stocks:ShareB -2 ShareB @ 100 €

Assets:Commission -9,90 €
Assets:Cash 2 ShareB @ 120 €
Income:Investment Gains

; Details of shares at 2014/05/02
P 2014-05-01 00:00:00 ShareA 70 €
P 2014-05-01 00:00:00 ShareB 120 €

Selling 2 ShareA at 2014/05/01

2014-04-02 Selling 4 ShareA
Assets:Investments:Stocks:ShareA -4 ShareA @ 20 €

Assets:Commission -9,90 €
Assets:Cash 2 ShareA @ 120 €
Income:Investment Gains


Jostein

[Martin Blais]

On Fri, Jun 27, 2014 at 4:00 PM, Rick Farnbach <ri...@farnbach.com> wrote:

Great find. Breaks all financial transactions into a series of templates to be followed. I'll definitely use that book as a reference going forward. 

In regards to the current conversation, here's the relevant quote. 

Stock Cost = (Shares Purchased × Price Per Share) + Commissions + Other Transaction Fees

 Notice that the commission is capitalized, not expensed. That's clearly where we have been going wrong. 

There's no right or wrong, right is how you choose to look at it. Usually you want to look at it the same way the tax man does, but you may also want to be able to look at your PnL separately from its trading costs. Pulling it out of a book doesn't legitimize it one way or another.

[Martin Blais]

On Fri, Jun 27, 2014 at 5:36 PM, Jostein Berntsen <jbe...@broadpark.no> wrote:

On 24.06.14,22:08, Rick F wrote:
> "Allowing the cost to show up in
>
>   both places would break the accounting equation; this transaction must balance."
>
>
> So I've been thinking about this and I think that is the fundamental problem.  Technically, commission (at least in the U.S.) isn't an expense, it's an asset.  It sounds strange, but think about it.  In the U.S., the commission counts toward the cost basis and the cost basis is recorded with the asset.  It's the whole depreciate vs. expense issue in accounting.
>
>
> I'm not sure how, but I think the solution is somewhere down that road.  Maybe creating a fake asset that is the commission per share, maybe a sub-asset.
>
>
> The thing is, this isn't a new problem.  This isn't a ledger problem so much as an accounting problem.  Accountants (in the U.S. at least) already have to deal with this situation.  Does anyone know how accountants deal with this currently?
>
>
> Rick
>
>
> On Saturday, June 14, 2014 10:50:32 AM UTC-7, Martin Blais wrote:
> >

Would this then be the best way to calculate it?

Nope.

(Explanations below.)

; Initial openings at 2014/01/012014-01-01 Opening Balance

  Assets:Bank:Check Account                      5000,00 €
  Equity:Opening Balance

; Details of shares at 2014/01/01

;P 2014-01-01 00:00:00   ShareA 20 €
;P 2014-01-01 00:00:00   ShareB 100 €
2014-01-01 Details for shares
  Equity:Opening Balance:Initial Investments

  Assets:Investments:Stocks:ShareA    15 ShareA @ 20 €

  Assets:Investments:Stocks:ShareB    5 ShareB @ 100 €

That's correct, if you're just starting your Ledger.

Thereafter, instead of an Equity account you'd be using a cash account and likely there would be a commissions account.

 

; Details of shares at 2014/04/01

P 2014-04-01 00:00:00   ShareA 30 €

P 2014-04-01 00:00:00   ShareB 120 €

; Selling 2 ShareB at 2014/04/01

2014-04-01 Selling 2 ShareB

        Assets:Investments:Stocks:ShareB                   -2 ShareB @ 100 €

        Assets:Commission            -9,90 €
        Assets:Cash                        2 ShareB @ 120 €
     Income:Investment Gains

Why are you depositing "2 ShareB" in a Cash account?

A cash account can only contain cash.

You sell 2 ShareB at the 100 EUR lot, so that should be using the lot disambiguation "{}" syntax, not the price "@" syntax. You're selling two of the 100 EUR lots that you have in the account:

 

        Assets:Investments:Stocks:ShareB                   -2 ShareB {100 €}

The commission is incorrect too, a commission is an expense, not an asset and should be positive:

 

        Expenses:Commission            9,90 €

Assets are things you have. You can't resell your commissions; it makes no sense. A commission is clearly an expense.

Then you credit the account with the corresponding amount of cash, that is 2 x 120 = 240 - 9.90 = 230.10 EUR

        Assets:Cash                        230.10 €

That amount would be given to you by your import process or by you looking at your account statement (if you do it manually). You probably should not compute this yourself, it's best to use the actual amount reported from the statement from the institution--that's the real amount, and doing this provides a check on your data entry.

The legs above sum to: (-2) x 100 + 9.90 + 230.10 = 40 EUR, so in order to balance, the remaining gains leg should be:

 

     Income:Investment Gains      -40 € 

You can, of course, leave the amount off of this one and Ledger/Beancount will compute it automatically.

This does not capitalize the commissions.

I already outlined a method to achieve this and a proposal to extend Beancount and Ledger's semantics to be able do that in

https://docs.google.com/document/d/1F8IJ_7fMHZ75XFPocMokLxVZczAhrBRBVN9uMhQFCZ4/edit#heading=h.axc1rmv8ctni

In the meantime, in Ledger, you could probably hack a non-balancing transaction using virtual postings. Beancount does not support virtual postings, so if you really insist on the PnL without commissions you would have to enter the adjusted amounts manually (as in the doc) or just live with the PnL excluding commissions until the proposal is implemented.

; Details of shares at 2014/05/02
P 2014-05-01 00:00:00   ShareA 70 €
P 2014-05-01 00:00:00   ShareB 120 €

Selling 2 ShareA at 2014/05/01

2014-04-02 Selling 4 ShareA
    Assets:Investments:Stocks:ShareA     -4 ShareA @ 20 €

    Assets:Commission                   -9,90 €
    Assets:Cash                           2 ShareA @ 120 €
     Income:Investment Gains

This is wrong here too, it should be like this, follow the same prescriptions as above:

2014-05-01 Selling 4 ShareA
    Assets:Investments:Stocks:ShareA     -4 ShareA {20 €}

    Expenses:Commission                   9,90 €
    Assets:Cash                           270.10 €
     Income:Investment Gains

... and the gains would be: 270.10 + 9.90 + (-4) x 20 = -200 EUR

Read my doc, all the detail is already there. I made an effort to write it in simple terms that anyone could understand and took time to write this long discussion of P/L. If you don't understand a section, please highlight it and add a comment.