Difficulty accounting for stock sales in foreign currency

[Caesar Schinas]

I've been using beancount for a while for my everyday transactions, but am only recently getting around to trying to import my trading activity to my ledger as it seems much more complex.

I'm having trouble with accounting for profit/loss on shares which are priced in a currency different to the main currency of my trading account.

My trading account is in GBP, but I often buy shares priced in USD. My broker handles the conversion, and charges me an FX fee as a percentage of the value of the trade.

Here is an example:

2020-03-31 * "Buy 11 shares of Square"
  Assets:GB:Freetrade:GIA:Cash   -482.60 GBP @ 1.23390 USD
  Assets:GB:Freetrade:GIA:SQ          11 S_SQ {54.13456 USD}

; ...

2020-11-16 * "Sell 11 shares of Square"
  Assets:GB:Freetrade:GIA:Cash   1467.08 GBP @ 1.31820 USD
  Assets:GB:Freetrade:GIA:SQ         -11 S_SQ {54.13456 USD} @ 175.81000 USD
  Assets:GB:Freetrade:GIA:Cash     -6.60 GBP
  Expenses:Financial:Fees           6.60 GBP
  Income:Investments:PnL         -977.88 GBP @ 1.31820 USD

All the figures are from my broker's reports, except for the profit calculation.

For the profit I have simply subtracted the GBP purchase price from the GBP sale price, as this is the actual amount of profit I made (the actual net change in my account balance).

However, the second transaction does not balance:

Transaction does not balance: (49.3832800 USD)

I am struggling to understand where the extra 49.3832800 USD  came from (or went?), or how I can balance this transaction.

I'm quite new to this. Can anyone give me any tips?

[redst...@gmail.com]

Sale 11 175.81  1,933.91
Cost 11  54.135   595.49
                1,338.43
   
Proceeds 1467.08  1.3182   1,933.90
PnL    (1,015.34) 1.3182  (1,338.43)
                             595.48
   
So you should have -1015.34 GBP as your Income, not -977.88   

Of course, you would need an additional posting to absorb rounding errors.

[Caesar Schinas]

But that's the thing: my income wasn't 1015.34 GBP, it was 977.88 GBP.

That is the difference between the amount of 482.60 GBP that was debited from my account when I bought the shares, and the amount of 1467.08 GBP that was credited to my account when I sold the shares.

So something else is amiss. But I can't work out what. (Unless it's totally incorrect exchange rates from my broker. But their web interface shows the same figures as the CSV from which I extracted the above data, and I'm loath to 'massage' the rates just to make the numbers fit, without actually understanding what's wrong.)

[Ben Blount]

bean-doctor context <file> <line# of txn>

can really help. It prints resolved details from the transaction and should help you track down the imbalance.

--
You received this message because you are subscribed to the Google Groups "Beancount" group.
To unsubscribe from this group and stop receiving emails from it, send an email to beancount+...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/beancount/4d36b92d-1251-424e-874b-6c687f8aa0e5n%40googlegroups.com.

[Caesar Schinas]

Apologies, I made a minor error in my last message – my arithmetic doesn't add up because I omitted to account for the FX fee of 6.60 GBP.

The point remains: the much larger 'error' of 49.38 GBP is unaccounted for.

[Caesar Schinas]

I think I now understand what's going on here.

It's actually simple: because the commodity is priced in USD, Beancount is naturally calculating my profit in USD.

The result is 1338.43 USD – which, at the current exchange rate on the sale date, is 1015.35 GBP.

But because I actually made the transactions in GBP, and the exchange rate has changed in the meantime, my actual profit is different (977.88 GBP).

I'm not sure what the cleanest way is to resolve this. At present I can think of two options:

1. Use a 'false' FX rate for the profit/loss leg of the transaction, such as by using Beancount's @@ syntax.
Example:

  Income:Investments:PnL                      -977.88 GBP @@ 1338.43 USD

At first glance this seems quite elegant, but one disadvantage is that it will result in incorrect entries in the prices database for GBP/USD.

2. Add another leg for FX gains/losses (I'm not sure if Expenses is the right category)

Example:

  Income:Investments:PnL                      -977.88 GBP @ 1.31820 USD

  Expenses:Financial:FXGainLoss                -37.46 GBP @ 1.31820 USD

The problem with this is that it looks like I made a profit on the FX difference, when I actually made a loss. I'm not sure how to resolve that.

Does anyone have any better ideas?

--
You received this message because you are subscribed to a topic in the Google Groups "Beancount" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/beancount/3Bp4ZgnqOvY/unsubscribe.
To unsubscribe from this group and all its topics, send an email to beancount+...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/beancount/20b40ee8-50cd-436b-b402-e5e22e6ede11n%40googlegroups.com.

[redst...@gmail.com]

Setting aside beancount for a second,

a) what cost currency was the commodity held in? I.e., what currency was it purchased and sold against?

b) it seems like a good way to think about this sale is two transactions: a sale of S_SQ, and a subsequent currency conversion. Do you think that's a good model? If so, I'd recommend starting by splitting it into those two transactions in your ledger, which should lead to clarity.

[Caesar Schinas]

Interesting. I'll have to have a play with that idea and see what I can come up with that makes sense.

From my own accounting point of view the main requirements are:

1. My profit/loss must be in GBP, as that's the currency of the account and the currency I pay tax in.

2. The commodity price should be denominated in its native currency (USD) so that I can fetch prices and see my unrealized gains/losses (converted back to GBP at current rates if I so desire) over time.

Regarding your point (a): My account is in GBP and I never see the USD as they are converted by my broker as part of the purchase/sale transaction (at the time of the purchase, their accounting reports didn't even contain the USD price) – but obviously, it's a USD-denominated commodity and it doesn't make sense to try and denominate it in GBP in my ledger.

Accounting for the sale as two transactions is a very interesting idea. I'm struggling to mentally imagine how it would look (subject to my constraint 1 above), though maybe it will become clear if I play around with it a bit. Maybe I use an equity account for the temporary (imaginary?) USD holding? Where does the FX loss (or profit) get accounted for? At some point it needs to be offset against my P/L from selling the shares so I end up with the correct overall profit/loss in GBP.

Do you think you could share an example of how you envisage booking this as two separate transactions?

Thanks!

To view this discussion on the web visit https://groups.google.com/d/msgid/beancount/f0c5845b-793b-440f-be43-ca858d30256en%40googlegroups.com.

[redst...@gmail.com]

Here's an example. I've removed the commissions for simplicity (they can be added back with some thought, of course). These can all be merged into two transactions, but splitting them up provides clarity on what is happening. You end up with 40 USD in exchange rate gain, which makes sense ((482 GBP * 1.3xxx USD/GBP) - (482 GBP * 1.2xxx USD/GBP) == 40.6886)

plugin "beancount.plugins.auto_accounts"

2020-03-31 * "Buy 11 shares of Square"
  Assets:GB:Freetrade:GIA:Cash   -482.60 GBP @ 1.23390 USD
  Assets:GB:Freetrade:GIA:SQ          11 S_SQ {54.13456 USD}

  Equity:Rounding -0.00002 USD

2020-11-16 * "Sell 11 shares of Square"

  Assets:GB:Freetrade:GIA:Cash   1933.91 USD

  Assets:GB:Freetrade:GIA:SQ         -11 S_SQ {54.13456 USD} @ 175.81000 USD

  Income:Investments:PnL        -1338.43 USD

2020-11-16 * "Conversion"
  Assets:GB:Freetrade:GIA:Cash   1467.08 GBP @ 1.3182 USD

  Assets:GB:Freetrade:GIA:Cash

2020-11-16 * "Conversion"
  Income:Investments:PnL  -984.48 GBP @ 1.3182 USD
  Income:Investments:PnL

2020-11-16 * "Book exchange rate loss/gain"
  ; (482 GBP * 1.3xxx USD/GBP) - (482 GBP * 1.2xxx USD/GBP) == 40.6886
  Income:Investments:PnL   40.68846 USD

  Income:Exchange

beancount> balances
          account             sum_position
---------------------------- --------------
Assets:GB:Freetrade:GIA:Cash  984.48    GBP,    0.00514 USD

Assets:GB:Freetrade:GIA:SQ

Equity:Rounding                -0.00002 USD
Income:Exchange               -40.68846 USD
Income:Investments:PnL         -0.00000 USD, -984.48    GBP

[Caesar Schinas]

Thanks! That makes it much clearer to me what you’re suggesting, thanks for the time and effort to produce that concrete example.

The one thing that doesn’t make sense to me is the exchange rate “gain” – so far as I can see, it should be a loss. As far as the balance of the “Income:Investments:PnL” account goes, that loss is accounted for by your second “Conversion" entry, but it leaves me with that “profit” in “Income:Exchange” which is a profit I didn’t actually receive.

To explain what I mean, forgetting the share dealing parts of the transactions for a moment and just focussing on the FX aspect:

If I buy 100 GBP worth of USD at a rate of 1.2339 I will receive 123.39 USD.

If I then sell those USD at a rate of 1.3182 I will receive 93.60 GBP.

I will have made a loss of 6.40 GBP.

Looking at the FX aspect alone I’m sure there’s a clean way to resolve it, but I haven’t found it yet – I’ll have to do some more reading.

To view this discussion on the web visit https://groups.google.com/d/msgid/beancount/839a3167-c6de-4279-b065-6a14b5cbe468n%40googlegroups.com.

[James Cook]

Maybe Income:Exchange should instead be Equity:Exchange or something?

In my mind, that money is going into the same place the converted
amounts go when you write a posting like

Income:Investments:PnL -984.48 GBP @ 1.3182 USD

Doesn't that posting use "Equity:Conversions" or something like that
under the hood?

I have been thinking about how to handle a similar situation for
myself: I have bought and sold some stocks in CAD, but need to report
my profit/loss in USD to the IRS. I was thinking I'll just record the
costs in USD --- in the analogy to your situation, that would be GBP.
That doesn't meet your main requirement #2 but I think I'm okay with
that if it makes things simpler.

(I also need to report my gain/loss to Canada in CAD, but for that I
wrote a plugin that adds a separate Canadianese version of everything.)

> >> To unsubscribe from this topic, visit https://groups.google.com/d/topic/beancount/3Bp4ZgnqOvY/unsubscribe <https://groups.google.com/d/topic/beancount/3Bp4ZgnqOvY/unsubscribe>.

> >> To unsubscribe from this group and all its topics, send an email to beancount+...@googlegroups.com <>.

> >> To view this discussion on the web visit https://groups.google.com/d/msgid/beancount/20b40ee8-50cd-436b-b402-e5e22e6ede11n%40googlegroups.com <https://groups.google.com/d/msgid/beancount/20b40ee8-50cd-436b-b402-e5e22e6ede11n%40googlegroups.com?utm_medium=email&utm_source=footer>.

> >>
> >> --
> >> You received this message because you are subscribed to a topic in the Google Groups "Beancount" group.

> >> To unsubscribe from this topic, visit https://groups.google.com/d/topic/beancount/3Bp4ZgnqOvY/unsubscribe <https://groups.google.com/d/topic/beancount/3Bp4ZgnqOvY/unsubscribe>.
> >> To unsubscribe from this group and all its topics, send an email to beancount+...@googlegroups.com <applewebdata://6FC07B9B-4F75-4957-ABE8-D056A4FA8FFD>.
> >
> >> To view this discussion on the web visit https://groups.google.com/d/msgid/beancount/f0c5845b-793b-440f-be43-ca858d30256en%40googlegroups.com <https://groups.google.com/d/msgid/beancount/f0c5845b-793b-440f-be43-ca858d30256en%40googlegroups.com?utm_medium=email&utm_source=footer>.

> >
> >
> > --
> > You received this message because you are subscribed to a topic in the Google Groups "Beancount" group.

> > To unsubscribe from this topic, visit https://groups.google.com/d/topic/beancount/3Bp4ZgnqOvY/unsubscribe <https://groups.google.com/d/topic/beancount/3Bp4ZgnqOvY/unsubscribe>.
> > To unsubscribe from this group and all its topics, send an email to beancount+...@googlegroups.com <mailto:beancount+...@googlegroups.com>.
> > To view this discussion on the web visit https://groups.google.com/d/msgid/beancount/839a3167-c6de-4279-b065-6a14b5cbe468n%40googlegroups.com <https://groups.google.com/d/msgid/beancount/839a3167-c6de-4279-b065-6a14b5cbe468n%40googlegroups.com?utm_medium=email&utm_source=footer>.
>
> --
> You received this message because you are subscribed to the Google Groups "Beancount" group.
> To unsubscribe from this group and stop receiving emails from it, send an email to beancount+...@googlegroups.com.
> To view this discussion on the web visit https://groups.google.com/d/msgid/beancount/456E6F95-6BCB-4FD4-B8C1-EBD49A2C9FAD%40caesarschinas.com.

--
James

[Caesar Schinas]

Yes, I’ve been considering whether using an Equity account would solve the problem.

So far I haven’t really understood how they are supposed to be used.

I’m not completely sure if a negative value is a gain or a loss in an Equity account?

Beancount does seem to internally use Equity:Conversions for FX transactions, but I haven’t really worked it out yet – except that I end up with massive balances in that account, positive in some currencies and negative in others. I’m not sure if that’s how it’s supposed to be, or if it’s a symptom of me doing something wrong…

I’ve played with manually “converting” the currencies using “currency accounts” as described at https://beancount.github.io/docs/beancount_v3.html#currency-accounts-instead-of-a-single-conversion, but so far I haven’t managed to convert that currency loss to an actual loss instead of having it show up as a gain (negative expense or income value).

It sounds like you have the exact same problem as me, so let me know if you find a solution more elegant than just denominating all commodities in your primary currency.

Incidentally I’d be very interested in your "plugin that adds a separate Canadianese version of everything” – I also need to report to two different tax authorities in different countries and haven’t worked out how to do that properly yet. Have you published your plugin anywhere?

[James Cook]

On Sun, May 02, 2021 at 05:23:07PM +0100, Caesar Schinas wrote:
> Yes, I’ve been considering whether using an Equity account would solve the problem.
> So far I haven’t really understood how they are supposed to be used.
> I’m not completely sure if a negative value is a gain or a loss in an Equity account?
>
> Beancount does seem to internally use Equity:Conversions for FX transactions, but I haven’t really worked it out yet – except that I end up with massive balances in that account, positive in some currencies and negative in others. I’m not sure if that’s how it’s supposed to be, or if it’s a symptom of me doing something wrong…

I think that's normal. The description at
https://www.mscs.dal.ca/~selinger/accounting/tutorial.html might clear
it up if you have time to read it. But basically, I like to think of a
currency conversion like this:

2000-01-01 * "Trade"
Assets:Chequing:CA -100 CAD
Assets:Chequing:CA 70 USD
Equity:Trade 100 CAD
Equity:Trade -70 USD

I don't think it makes sense to interpret the current balance in
Equity:Trade. You could look at the net change in Equity:Trade over a
span of time if you want to understand your trading activity, but it
requires further interpretation if you want to think of it in terms of
profit or loss. NB I haven't tried to do this "further interpretation".


> I’ve played with manually “converting” the currencies using “currency accounts” as described at https://beancount.github.io/docs/beancount_v3.html#currency-accounts-instead-of-a-single-conversion <https://beancount.github.io/docs/beancount_v3.html#currency-accounts-instead-of-a-single-conversion>, but so far I haven’t managed to convert that currency loss to an actual loss instead of having it show up as a gain (negative expense or income value).

Can you show an example?

Continuing mine from before, I could imagine converting back at a worse
rate...

2000-01-01 * "Trade"
Assets:Chequing:CA -100 CAD
Assets:Chequing:CA 70 USD
Equity:Trade 100 CAD
Equity:Trade -70 USD

2000-01-01 * "Trade"
Assets:Chequing:CA 90 CAD
Assets:Chequing:CA -70 USD
Equity:Trade -90 CAD
Equity:Trade 70 USD

and then Equity:Trade would end up with a balance of 10 CAD, which
could be thought of as a loss.

> It sounds like you have the exact same problem as me, so let me know if you find a solution more elegant than just denominating all commodities in your primary currency.
>
> Incidentally I’d be very interested in your "plugin that adds a separate Canadianese version of everything” – I also need to report to two different tax authorities in different countries and haven’t worked out how to do that properly yet. Have you published your plugin anywhere?

See my earlier thread: "Tracking different cost basis methods for
different countries?"

Current version is at

https://hub.darcs.net/falsifian/misc-pub/browse/beancount_plugins/falsifian/parallel_average_cost.py

I hope the documentation explains what's going on, but I'm happy to
answer questions. The gist is that it just adds some postings that live
in a "parallel universe" with new accounts and currencies.

I'm curious what will happen if anyone else tries to
use it; I'm sure there are plenty of strange assumptions I'm making
that are true for my own ledger.

--
James

[Caesar Schinas]

On 2 May 2021, at 17:35, James Cook <fals...@falsifian.org> wrote:

I’ve played with manually “converting” the currencies using “currency accounts” as described at https://beancount.github.io/docs/beancount_v3.html#currency-accounts-instead-of-a-single-conversion <https://beancount.github.io/docs/beancount_v3.html#currency-accounts-instead-of-a-single-conversion>, but so far I haven’t managed to convert that currency loss to an actual loss instead of having it show up as a gain (negative expense or income value).

Can you show an example?

Hmm, well here’s what I’ve got so far… 

The `Equity:Exchange` entries are redundant except to be more specific about what’s going on, and could functionally be dropped if the commented exchange rates were uncommented.

Functionally the only change from where I started is the `Equity:FXGainLoss` entry.

option "inferred_tolerance_default" "GBP:0.005"

option "inferred_tolerance_default" "USD:0.005"

plugin "beancount.plugins.auto_accounts"

2020-03-31 * "Buy 11 shares of Square"

Assets:GB:Freetrade:GIA:Cash -482.60 GBP ;@ 1.23390 USD

Equity:Exchange:GBP 482.60 GBP

Equity:Exchange:USD -595.48 USD

Assets:GB:Freetrade:GIA:SQ 11 S_SQ {54.13456 USD}

2020-11-16 * "Sell 11 shares of Square"

Assets:GB:Freetrade:GIA:SQ -11 S_SQ {54.13456 USD} @ 175.81000 USD

Equity:Exchange:USD 1933.91 USD

Equity:Exchange:GBP -1467.08 GBP

Assets:GB:Freetrade:GIA:Cash 1467.08 GBP ;@ 1.31820 USD

Income:Investments:PnL -977.88 GBP @ 1.31820 USD

Equity:FXGainLoss -37.47 GBP @ 1.31820 USD

Note that I haven’t yet managed to drop the @ exchange rates on the last two postings, even with the `Equity:Exchange` accounts.

And I haven’t really decided if `Equity` is the right place for that `FXGainLoss` account, but it certainly doesn’t make sense to me anywhere else or it appears to one a profit, when in fact it’s a loss (albeit a loss which is already accounted for in the posting to `Income:Investments:PnL`, so all I’m really trying to do is ‘discard’ it to make the entry balance…)

Running `bean-doctor` on this example shows 

** Balances after transaction --------------------------------

  Assets:GB:Freetrade:GIA:SQ                                                     

* Equity:Exchange:USD                                                 1338.43 USD

* Equity:Exchange:GBP                                                 -984.48 GBP

* Assets:GB:Freetrade:GIA:Cash                                         984.48 GBP

* Income:Investments:PnL                                              -977.88 GBP

* Equity:FXGainLoss                                                    -37.47 GBP

I suppose one other possibility (if I do explicitly book the FX conversion to currency accounts, which I’m not too happy about having to do manually) would be to subtract that 37.47 GBP (49.39 USD) from the Equity:Exchange:USD account. But it seems a bit arbitrary…

It sounds like you have the exact same problem as me, so let me know if you find a solution more elegant than just denominating all commodities in your primary currency.

Incidentally I’d be very interested in your "plugin that adds a separate Canadianese version of everything” – I also need to report to two different tax authorities in different countries and haven’t worked out how to do that properly yet. Have you published your plugin anywhere?

See my earlier thread: "Tracking different cost basis methods for
different countries?"

Current version is at

https://hub.darcs.net/falsifian/misc-pub/browse/beancount_plugins/falsifian/parallel_average_cost.py

I hope the documentation explains what's going on, but I'm happy to
answer questions. The gist is that it just adds some postings that live
in a "parallel universe" with new accounts and currencies.

I'm curious what will happen if anyone else tries to
use it; I'm sure there are plenty of strange assumptions I'm making
that are true for my own ledger.

Thanks! I’ll have a look at that.

[Artem Tikhomirovs]

Keep in mind that the way your UK broker holds US commodities at cost in GBP is exactly how HMRC wants you to report your capital gains: https://www.gov.uk/hmrc-internal-manuals/capital-gains-manual/cg78310

I have an opposite problem. I mostly use a US broker, but still have to account for any gains using UK tax rules. 

On 2 May 2021, at 11:37, Caesar Schinas <cae...@caesarschinas.com> wrote:



You received this message because you are subscribed to the Google Groups "Beancount" group.
To unsubscribe from this group and stop receiving emails from it, send an email to beancount+...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/beancount/58F07E0D-F708-4607-9C15-A7A0F178A0ED%40caesarschinas.com.

[James Cook]

On Sun, May 02, 2021 at 06:45:40PM +0100, Caesar Schinas wrote:
>
>
> > On 2 May 2021, at 17:35, James Cook <fals...@falsifian.org> wrote:
> >
> >> I’ve played with manually “converting” the currencies using “currency accounts” as described at https://beancount.github.io/docs/beancount_v3.html#currency-accounts-instead-of-a-single-conversion <https://beancount.github.io/docs/beancount_v3.html#currency-accounts-instead-of-a-single-conversion>, but so far I haven’t managed to convert that currency loss to an actual loss instead of having it show up as a gain (negative expense or income value).
> >
> > Can you show an example?
>
> Hmm, well here’s what I’ve got so far…
> The `Equity:Exchange` entries are redundant except to be more specific about what’s going on, and could functionally be dropped if the commented exchange rates were uncommented.
> Functionally the only change from where I started is the `Equity:FXGainLoss` entry.
>
> option "inferred_tolerance_default" "GBP:0.005"
> option "inferred_tolerance_default" "USD:0.005"
> plugin "beancount.plugins.auto_accounts"
>
> 2020-03-31 * "Buy 11 shares of Square"
> Assets:GB:Freetrade:GIA:Cash -482.60 GBP ;@ 1.23390 USD
> Equity:Exchange:GBP 482.60 GBP
> Equity:Exchange:USD -595.48 USD
> Assets:GB:Freetrade:GIA:SQ 11 S_SQ {54.13456 USD}
>
> 2020-11-16 * "Sell 11 shares of Square"
> Assets:GB:Freetrade:GIA:SQ -11 S_SQ {54.13456 USD} @ 175.81000 USD
> Equity:Exchange:USD 1933.91 USD
> Equity:Exchange:GBP -1467.08 GBP
> Assets:GB:Freetrade:GIA:Cash 1467.08 GBP ;@ 1.31820 USD
> Income:Investments:PnL -977.88 GBP @ 1.31820 USD
> Equity:FXGainLoss -37.47 GBP @ 1.31820 USD
>
>
> Note that I haven’t yet managed to drop the @ exchange rates on the last two postings, even with the `Equity:Exchange` accounts.
> And I haven’t really decided if `Equity` is the right place for that `FXGainLoss` account, but it certainly doesn’t make sense to me anywhere else or it appears to one a profit, when in fact it’s a loss (albeit a loss which is already accounted for in the posting to `Income:Investments:PnL`, so all I’m really trying to do is ‘discard’ it to make the entry balance…)
>

I don't think I completely understand what you're doing. E.g. I'm not
sure where 977.88 GBP comes from.

I'm certainly no expert, but how I would account for this depends on
which country I'm doing my taxes for. (My plugin tries to do both at
once, at the cost of some added complexity.)

For the US, (I think) my PnL should be computed just using the USD
amounts. So it should come out to (175.81-54.13456)*11 = 1338.43 USD.
Here's a way to get that:

option "operating_currency" "GBP"

option "inferred_tolerance_default" "GBP:0.005"
option "inferred_tolerance_default" "USD:0.005"
plugin "beancount.plugins.auto_accounts"

2020-03-31 * "Buy 11 shares of Square"
Assets:GB:Freetrade:GIA:Cash -482.60 GBP

Equity:Exchange:GBP 482.60 GBP
Equity:Exchange:USD -595.48 USD
Assets:GB:Freetrade:GIA:SQ 11 S_SQ {54.13456 USD}

2020-11-16 * "Sell 11 shares of Square"
Assets:GB:Freetrade:GIA:SQ -11 S_SQ {54.13456 USD} @ 175.81000 USD
Equity:Exchange:USD 1933.91 USD
Equity:Exchange:GBP -1467.08 GBP
Assets:GB:Freetrade:GIA:Cash 1467.08 GBP

Income:Investments:PnL -1338.43 USD

The only changes from your example were to drop the Equity:FXGainLoss
posting, and change the Income:Investments:PnL posting to USD.

If you want to do taxes in GBP: well, you put in 482.60 GBP and got
back 1467.08 GBP, so your profit is 984.48 GBP. Here's how you can get
that:

option "operating_currency" "GBP"

option "inferred_tolerance_default" "GBP:0.005"
option "inferred_tolerance_default" "USD:0.005"
plugin "beancount.plugins.auto_accounts"

2020-03-31 * "Buy 11 shares of Square"
Assets:GB:Freetrade:GIA:Cash -482.60 GBP

Assets:GB:Freetrade:GIA:SQ 11 S_SQ {43.8727 GBP}

2020-11-16 * "Sell 11 shares of Square"

Assets:GB:Freetrade:GIA:SQ -11 S_SQ {43.8727 GBP} @ 133.3713 GBP

Assets:GB:Freetrade:GIA:Cash 1467.08 GBP

Income:Investments:PnL -984.48 GBP

I simply removed all mention of USD.

Neither of my examples include an "FXGainLoss" account. If you want to
include it, I think you will need to start be definining exactly what
it's supposed to measure. Personally I don't see why it's needed.

--
James

[James Cook]

I should emphasize I don't know for sure this is correct. I'm assuming
the US wants you to convert each buy and sell to USD at the time of
transaction, and similar for UK, because that makes sense to me.

(If I'm wrong about the US, someone should let me know soon...)

--
James

[Caesar Schinas]

On Tuesday, 4 May 2021 at 15:17:32 UTC+1 ar...@tikhomirovs.ru wrote:

Keep in mind that the way your UK broker holds US commodities at cost in GBP is exactly how HMRC wants you to report your capital gains: https://www.gov.uk/hmrc-internal-manuals/capital-gains-manual/cg78310

That's interesting. I hadn't thought about it as "holding US commodities at cost in GBP". (My broker's reports, such as they are, only actually give me the USD price and the exchange rate – not the GBP price – but I could easily calculate the equivalent GBP price from that.)

I suppose I could just do all my accounting with GBP prices (hold the commodities at cost in GBP, in other words), but I would loose the ability to fetch current prices from live data sources and calculate my unrealized gains/losses. Maybe I should focus instead on a better way to deal with that aspect.

 

I have an opposite problem. I mostly use a US broker, but still have to account for any gains using UK tax rules. 

The way I see it that's the same problem I have been trying to solve (holding at cost in USD but calculating P/L in GBP) – isn't it?

 

[Caesar Schinas]

On 7 May 2021, at 18:51, James Cook <fals...@falsifian.org> wrote:

I don't think I completely understand what you're doing. E.g. I'm not
sure where 977.88 GBP comes from.

Ha, I’m not sure I do either, which is why I posted here :-)

The 977.88 GBP is my profit (984.48 GBP by selling the shares, minus 6.60 GBP fee). It’s the actual profit I made – the amount that the purchase and subsequent sale altered my account balance.

It’s about the only invariant here: the profit is 977.88 GBP. That must be the outcome of whatever accounting method I use, as it is the amount of profit I actually made.

I'm certainly no expert, but how I would account for this depends on
which country I'm doing my taxes for. (My plugin tries to do both at
once, at the cost of some added complexity.)

For the US, (I think) my PnL should be computed just using the USD
amounts. So it should come out to (175.81-54.13456)*11 = 1338.43 USD.

That would make sense if my account balance was in USD and so that could be conceived as the amount of profit I had made. But it isn’t (see above) so that figure is somewhat illusory – the change in exchange rate wiped out a chunk of it.

If you want to do taxes in GBP: well, you put in 482.60 GBP and got
back 1467.08 GBP, so your profit is 984.48 GBP.

Yes, that’s what I want to achieve (minus the 6.60 GBP fee, making the real profit 977.88 GBP, but let’s ignore that for now for simplicity).

Here's how you can get that:

option "operating_currency" "GBP"
option "inferred_tolerance_default" "GBP:0.005"
option "inferred_tolerance_default" "USD:0.005"
plugin "beancount.plugins.auto_accounts"

2020-03-31 * "Buy 11 shares of Square"
 Assets:GB:Freetrade:GIA:Cash      -482.60 GBP
 Assets:GB:Freetrade:GIA:SQ             11 S_SQ {43.8727 GBP}

2020-11-16 * "Sell 11 shares of Square"
 Assets:GB:Freetrade:GIA:SQ            -11 S_SQ {43.8727 GBP} @ 133.3713 GBP
 Assets:GB:Freetrade:GIA:Cash      1467.08 GBP
 Income:Investments:PnL            -984.48 GBP

I simply removed all mention of USD.

Right, so holding the stocks in GBP instead of in USD. That’s probably what I’ll end up doing, because I can’t find a better solution – but it is in my opinion not an elegant solution because the commodity is priced on the market in USD, not GBP. So holding it in my accounts in GBP means I can’t compare the price (using Beancount and Fava) to current market prices, as the currencies are different.

Perhaps I can create a plugin to solve that issue, instead of trying to hold the stocks in USD.

Anyway, thanks for the input! :-)

[James Cook]

On Fri, May 07, 2021 at 07:24:58PM +0100, Caesar Schinas wrote:
> On 7 May 2021, at 18:51, James Cook <fals...@falsifian.org> wrote:
> > I don't think I completely understand what you're doing. E.g. I'm not
> > sure where 977.88 GBP comes from.
>
> Ha, I’m not sure I do either, which is why I posted here :-)
>
> The 977.88 GBP is my profit (984.48 GBP by selling the shares, minus 6.60 GBP fee). It’s the actual profit I made – the amount that the purchase and subsequent sale altered my account balance.
> It’s about the only invariant here: the profit is 977.88 GBP. That must be the outcome of whatever accounting method I use, as it is the amount of profit I actually made.
>
> > I'm certainly no expert, but how I would account for this depends on
> > which country I'm doing my taxes for. (My plugin tries to do both at
> > once, at the cost of some added complexity.)
> >
> > For the US, (I think) my PnL should be computed just using the USD
> > amounts. So it should come out to (175.81-54.13456)*11 = 1338.43 USD.
>
> That would make sense if my account balance was in USD and so that could be conceived as the amount of profit I had made. But it isn’t (see above) so that figure is somewhat illusory – the change in exchange rate wiped out a chunk of it.

That's a good point. I'm not sure how that interacts with US taxes,
though. My guess would be that (for the US) you're supposed to compute
it in USD anyway, and then if you like, you could also compute your
gain/loss on the currency exchange, separately. But I'm not sure.

Good luck!

--
James

[Caesar Schinas]

On Friday, 7 May 2021 at 19:39:49 UTC+1 James Cook wrote:

That's a good point. I'm not sure how that interacts with US taxes,
though. My guess would be that (for the US) you're supposed to compute
it in USD anyway, and then if you like, you could also compute your
gain/loss on the currency exchange, separately. But I'm not sure.

Ah, fair point. I'm not sure what the approved thing to do there would be.

Fortunately I don't have to deal with US taxes (beyond any deductions which are made automatically by my broker).

But I do have to deal with taxes in EUR as well as GBP – and introducing a third currency to this mix has to make things super complicated! I'm not sure what I'll do there but I might start but looking at your plugin for inspiration…

 

Good luck!

Thanks, I think I need it! 😆

[James Cook]

On Fri, May 07, 2021 at 07:24:58PM +0100, Caesar Schinas wrote:

I'm actually currently working on a plugin to convert all my cost bases
and capital gains computations to USD for the purposes of US taxes,
even if I enter them in CAD (because I actually did the trading in
CAD).

Hopefully the below test case illustrates how it's supposed to work.
It's a bit weird, because it expects the transaction-as-entered to
already have capital gains computed in USD. That kind of suits my own
use case, since I've been obsessing over making sure all my numbers add
up, and don't mind spending a bit of extra time filling in the capital
gains amounts.

Happy to share the current state if anyone is interested now. Otherwise
I was planning to wait until I've successfully converted my ledger to
use it, since I keep thinking of things to change.

(One planned change based on my experience so far: I need to add a work
around for the fact that prices and costs must be in the same currency.
I make liberal use of Norbert's gambit, so some of my postings make the
most sense (to me) as something like:

Assets:Brokerage -5 ACME {20.00 CAD} @ 15.00 USD

I'm thinking I'll just replace the "@ 15.00 USD" with metadata to be
interpreted by the plugin.)

class TestConvertAllCosts(cmptest.TestCase):

@loader.load_doc(expect_errors=False)
def test_simple_buy_sell(self, entries, _errors, _options_map):
"""
plugin "falsifian.convert_cost" "conversion_account=Equity:Cost-conversion"

2000-01-01 open Assets:Brokerage
2000-01-01 open Assets:Chequing
2000-01-01 open Income:Capital-gains
2000-01-01 open Equity:Cost-conversion

; Buy transactions should balance as entered. The conversion_account
; will be used to make sure they still balance after being converted.
2000-01-01 * "Buy"
Assets:Chequing -100.00 CAD
Assets:Brokerage 5 ACME {20.00 CAD}
convert_buy_cost_to: "USD"

; Sell transactions should be written to balance after being converted.
; The capital gain portion of the sale will be converted to
; target_currency, but the rest will be left alone. In this example,
; that means the Assets:Chequing posting can reflect the actual CAD
; balance deposited.
2000-02-01 * "Sell"
Assets:Chequing 200.00 CAD
Assets:Brokerage -5 ACME {20.00 CAD} @ 40.00 CAD
convert_sell_cost_to: "USD"
Income:Capital-gains -90.00 USD

2000-01-01 price CAD 0.70 USD
2000-02-01 price CAD 0.80 USD

; Irrelevant prices that should be ignored
2000-01-01 price CAD 100.00 AUD
2000-01-01 price AUD 100.00 USD
"""
self.assertEqualEntries("""
2000-01-01 open Assets:Brokerage
2000-01-01 open Assets:Chequing
2000-01-01 open Income:Capital-gains
2000-01-01 open Equity:Cost-conversion

2000-01-01 * "Buy"
Assets:Chequing -100.00 CAD
Assets:Brokerage 5 ACME {14.0000 USD}
Equity:Cost-conversion -70.0000 USD
Equity:Cost-conversion 100.00 CAD

2000-02-01 * "Sell"
Assets:Chequing 200.00 CAD
Assets:Brokerage -5 ACME {14.0000 USD} @ 32.0000 USD
Income:Capital-gains -90.00 USD
Equity:Cost-conversion -200.00 CAD
Equity:Cost-conversion 160.0000 USD

2000-01-01 price CAD 0.70 USD
2000-02-01 price CAD 0.80 USD
2000-01-01 price CAD 100.00 AUD
2000-01-01 price AUD 100.00 USD
""", entries)

--
James