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. > On 2 May 2021, at 12:02, [email protected] <[email protected]> wrote: > > 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 > > > > On Sunday, May 2, 2021 at 3:37:29 AM UTC-7 [email protected] wrote: > 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! > > > >> On 2 May 2021, at 02:15, [email protected] <http://gmail.com/> >> <[email protected] <applewebdata://6FC07B9B-4F75-4957-ABE8-D056A4FA8FFD>> >> wrote: >> > >> 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. >> >> On Saturday, May 1, 2021 at 1:59:37 PM UTC-7 [email protected] >> <http://caesarschinas.com/> wrote: >> 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? >> >> >> On Sat, 1 May 2021 at 20:54, Caesar Schinas <[email protected] <>> >> wrote: >> 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. >> >> On Saturday, 1 May 2021 at 20:46:45 UTC+1 Caesar Schinas wrote: >> 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.) >> >> >> On Saturday, 1 May 2021 at 20:18:53 UTC+1 redst...@ <>gmail.com >> <http://gmail.com/> wrote: >> 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. >> >> On Wednesday, April 28, 2021 at 5:14:40 PM UTC-7 cae...@ <>caesarschinas.com >> <http://caesarschinas.com/> wrote: >> 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? >> >> -- >> 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 >> [email protected] <>. >> 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 >> [email protected] >> <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 > [email protected] > <mailto:[email protected]>. > 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 [email protected]. To view this discussion on the web visit https://groups.google.com/d/msgid/beancount/456E6F95-6BCB-4FD4-B8C1-EBD49A2C9FAD%40caesarschinas.com.
