Income Statement" aka "Profit/Loss Statement" with unrealized gain due to changes in stock and curre

751 views
Skip to first unread message

Chary Chary

unread,
Feb 26, 2019, 3:31:07 PM2/26/19
to Beancount
Hello everybody!

I stumbled on Beancount in my search for finance bookkeeping system, as Excel does not really satisfy all my needs

I first tested gnucash but I was told it can't do what I need. So I am just wondering whether Beancount can do this:

Sorry, if express what I need in gnucash terms, I am just not familiar with Beancount  terminology yet

What I need is following:

======================================

I am looking for Income Statement" aka "Profit/Loss Statement"  report which would show unrealized gain due to changes in stock and currency exchange rate

Say at the beginning of the year I have produced a Balance Sheet report. 
This report shows how much assets I have, which liabilities and delta 
between them. Suppose at begging of the year I had checking accounts in USD 
and Euro and some stock. 
Throughout the year I was getting salary, paying expenses, at the same time 
exchange rate was changing, stock price was changing etc. My be I sold some 
stock, moved money between USD and Euro accounts 

Now at the end of the year I have produced a new balance sheet. 

So, I want to be able to explain a delta between a balance sheet at the 
beginning of the ear and at the end. 

E.g.: 

I got so much salary 
I paid so much costs 

But also: 
Lost so much due to exchange rate change 
Gained to much due to stock price increase etc. 

So, effectively I need to be able to drill down in the delta in Balance 
Sheet report 

Regards. 
Chary 

Martin Blais

unread,
Feb 27, 2019, 12:28:32 AM2/27/19
to Beancount
On Tue, Feb 26, 2019 at 3:31 PM Chary Chary <char...@gmail.com> wrote:
Hello everybody!

I stumbled on Beancount in my search for finance bookkeeping system, as Excel does not really satisfy all my needs

I first tested gnucash but I was told it can't do what I need. So I am just wondering whether Beancount can do this:

Depends what you mean.
On the positive side, Beancount can store all the data you need to compute your year-to-date profits, if you stored in your file
1. your positions with their cost basis
2. the price at the beginning of the year
3. the recent price

 

Sorry, if express what I need in gnucash terms, I am just not familiar with Beancount  terminology yet

What I need is following:

======================================

I am looking for Income Statement" aka "Profit/Loss Statement"  report which would show unrealized gain due to changes in stock and currency exchange rate

Say at the beginning of the year I have produced a Balance Sheet report. 
This report shows how much assets I have, which liabilities and delta 
between them. Suppose at begging of the year I had checking accounts in USD 
and Euro and some stock. 
Throughout the year I was getting salary, paying expenses, at the same time 
exchange rate was changing, stock price was changing etc. My be I sold some 
stock, moved money between USD and Euro accounts 

Now at the end of the year I have produced a new balance sheet. 

So, I want to be able to explain a delta between a balance sheet at the 
beginning of the ear and at the end. 

It's possible to compute that, but I don't think any of the reports there by default do it now.
You might want to look into Fava, which may do offer this on the balance sheet.

With queries you can do something like this, replacing the date to get the amount at each date:
bean-query $L "select convert(units(sum(position)), 'USD') from close on 2018-03-01 where account ~ 'Assets:US:Broker:Main:VTI' "

You can issue this for a set of accounts by loosening the regexp in the where clause.
You'd have to run multiple queries, and please note that Beancount only renders prices that you stored in the input file, so if you want prices close to those dates, you'll have to fetch them and insert them. The bean-price tools provides some support for automating that some. It's not perfect.




E.g.: 

I got so much salary 
I paid so much costs 

But also: 
Lost so much due to exchange rate change 

re. exchange rate; it's possible to compute but you'll have to write some code for that.

I think you could write a script to do all that you've described (and that's what I would do).
Beancount allows you to store all the information you need for this.
Needs a bit of code for this specific report though.

Enjoy,

 

Gained to much due to stock price increase etc. 

So, effectively I need to be able to drill down in the delta in Balance 
Sheet report 

Regards. 
Chary 

--
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 post to this group, send email to bean...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/beancount/2cef9db8-20b9-49d3-b91b-ec3591e10a3b%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

Chary Chary

unread,
Feb 27, 2019, 4:37:33 AM2/27/19
to Beancount
Martin,

ok, thanks. I will look at Fava (looks cool) and possibly will see whether I can do some extra development myself there.


But I must say, I still have a sneaky feeling, that I may be missing something, because I just don't understand how people who deal with several currencies and who have stock can possibly live without such report, which would show unrealized gains.

Without report with unrealized gains you have a situation, that you have some balance at the beginning, balance at the end, but you have no report, which would show how you got from balance at the beginning to balance at the end. Even though all your individual transactions are balanced, if you add them all together they will not produce a delta between balance sheets, in case you deal with  more then one currency or have stock / gold (anything).

In a very simple situation:

Say I want to report in EUR, but I keep money in USD. Say I have 1000 USD and exchange rate was 1:1 at the beginning.
So, starting balance sheet will say, that I have 1000 EUR
Then exchange rate changed and now 1 USD costs 2 EUR. So, all of a sudden balance sheet at the closing will show, that I now have 2000 EUR.
But how did happen?
I would then expect some line, saying something like: "unrealized gain due to USD/EUR" exchange rate changes"  - 1000 EURO. 

The same applies to stock changes.

Am I talking some nonsense? How do people manage their finance without such analysis in our modern world? 

P.S. buy the way, please accept congratulation with a very thorough documentation, on beancount. Very, very well done.

Regards.

Chary

Martin Blais

unread,
Feb 27, 2019, 9:10:16 PM2/27/19
to Beancount
On Wed, Feb 27, 2019 at 4:37 AM Chary Chary <char...@gmail.com> wrote:
Martin,

ok, thanks. I will look at Fava (looks cool) and possibly will see whether I can do some extra development myself there.


But I must say, I still have a sneaky feeling, that I may be missing something, because I just don't understand how people who deal with several currencies and who have stock can possibly live without such report, which would show unrealized gains.

They don't. Most people invest domestically and don't think about other currencies.


Without report with unrealized gains you have a situation, that you have some balance at the beginning, balance at the end, but you have no report, which would show how you got from balance at the beginning to balance at the end. Even though all your individual transactions are balanced, if you add them all together they will not produce a delta between balance sheets, in case you deal with  more then one currency or have stock / gold (anything).

An elegant way to handle this is to use the Selinger trading accounts method.
It would be easy to write a Beancount plugin to do this automatically.

 

In a very simple situation:

Say I want to report in EUR, but I keep money in USD. Say I have 1000 USD and exchange rate was 1:1 at the beginning.
So, starting balance sheet will say, that I have 1000 EUR
Then exchange rate changed and now 1 USD costs 2 EUR. So, all of a sudden balance sheet at the closing will show, that I now have 2000 EUR.
But how did happen?
I would then expect some line, saying something like: "unrealized gain due to USD/EUR" exchange rate changes"  - 1000 EURO. 

The same applies to stock changes.

Am I talking some nonsense? How do people manage their finance without such analysis in our modern world? 

They ignore the currency effects.
Even many large companies sometimes don't break down the currency impact from foreign reserves.

 

P.S. buy the way, please accept congratulation with a very thorough documentation, on beancount. Very, very well done.

Thank you!

 

Callum Makkai

unread,
Feb 27, 2019, 9:14:13 PM2/27/19
to bean...@googlegroups.com

On 2/27/19 9:10 PM, Martin Blais wrote:
An elegant way to handle this is to use the Selinger trading accounts method.
It would be easy to write a Beancount plugin to do this automatically.

Jumping in here ... Martin, I was reading about the Selinger method the other day. Do you know if ledger or hledger implements this method? Or it's totally absent from all plain text packages?

Callum

Martin Blais

unread,
Feb 27, 2019, 9:17:56 PM2/27/19
to Beancount
I believe it's absent.
In Beancount, what would be required is basically to sum up the balances of each currency group and insert new accounts.
It's not difficult, though it's not cheap, some optimizations might be required.






--
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 post to this group, send email to bean...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/beancount/73b3eb60-39b3-3d64-37e4-0b82543fe40e%40gmail.com.

Martin Blais

unread,
Feb 27, 2019, 11:45:58 PM2/27/19
to Beancount
Here, I finally did implemented a prototype:
I guess it took me two hours.

It's not entirely trivial; there are a few issues that keep this at the level of "prototype":

1. the price annotations get stripped out, which means that if you used the implicit_prices plugin AFTER this one, your prices might disappear. Other plugins, such as sellgains, would also have to run BEFORE this one in order to take effect. Custom scripts also would not see price annotations. This could be fixed by changing the booking code to ignore the price annotation for modified postings (identified via flag of the presence of metadata). In other words, instead of removing the price annotations, keep them but make them "lame" by inserting some metadata and requiring collaboration with the booking code (it's a little bit ugly, but I'd be okay with it).

2. In some cases - a transaction with multiple currency groups, at least one price annotation, and some non-zero residual - a currency trading account posting will be inserted in lieu of a residual posting. This messes with the rounding feature that automatically inserts postings for residuals (maybe it's not that big a deal). I think it could be improved further by conditioning the insertion on multiple required currency account postings.

For more about these limitations, see the test file (look for FIXME).

I like this plugin. There's no need to change any of your input to use it (it worked on my ledger without modification). Maybe I should finesse it and this could become there by default in a future version. It works on my personal large input file and does adds only an insignificant amount of overhead. This is the most natural way to deal with conversions.

See this file for usage instructions:

Chary Chary

unread,
Mar 3, 2019, 5:03:50 PM3/3/19
to Beancount
Martin,

just for my understanding. 

Q1) Why did you have to implement the currency_accounts plugin if you already have beancount.plugins.unrealized

Isn't the latter supposed to do exactly  what I asked for in my starting topic?

In the description, of the beancount.plugins.unrealized you write that 

This will create a synthetic transaction at the date of the last of directives, that reflects the unrealized P/L. 

This was my understanding of exactly how Peter Selinger method is supposed to work

Q2) You write that 

"beancount.plugins.unrealized" "Unrealized" will create a synthetic transaction at the date of the last of directives, that reflects the unrealized P/L. It books one side as Income and the other side as a change in Asset.

Is my understanding correct, that this will be a virtual transaction, kept only in memory and not to be seen in a text format anywhere?

Martin Blais

unread,
Mar 3, 2019, 5:12:57 PM3/3/19
to Beancount
On Sun, Mar 3, 2019 at 5:03 PM Chary Chary <char...@gmail.com> wrote:
Martin,

just for my understanding. 

Q1) Why did you have to implement the currency_accounts plugin if you already have beancount.plugins.unrealized

Isn't the latter supposed to do exactly  what I asked for in my starting topic?

Because profits generated as a result of assert appreciate (above cost basis), within their denominated currency, are distinct from (usually accidental) profits generated from divergence of the investment's currency over the user's home currency.


In the description, of the beancount.plugins.unrealized you write that 

This will create a synthetic transaction at the date of the last of directives, that reflects the unrealized P/L. 

Yes.

In terms of reporting, it's also the wrong thing to do... it was implemented as a quick experiment to see if this could be used to include unrealized profits on the balance sheet. 
Turns out it sort-of works, but the transactions that should be inserted depend on the reporting period, so really, it's a function of the reporting parameters.
Don't use it.
The right thing to do is for the reporting code to calculate the produce the unrealized gain/loss at the time (usually end of period) of reporting.



This was my understanding of exactly how Peter Selinger method is supposed to work

The Selinger doc explains one method for dealing with accidental profits due to holding a portfolio of currencies without a cost basis.
Not handling gains/losses in investments.
These are two very different things and should be treated distinctly.

It's a long and complicated topic, but in short:
- Treat your investmnets in their local currency, tracking their original cost basis in their home currency
- If you want to track profits due to currency movements, in general, use the Selinger method (with or without my plugin)


Q2) You write that 

"beancount.plugins.unrealized" "Unrealized" will create a synthetic transaction at the date of the last of directives, that reflects the unrealized P/L. It books one side as Income and the other side as a change in Asset.

Is my understanding correct, that this will be a virtual transaction, kept only in memory and not to be seen in a text format anywhere?

Yes.
But don't use that plugin.
Use a report rendering thing like Fava instead, which should be render add unrealized gains for you, depending on the reporting  period you seek to view.




Chary Chary

unread,
Mar 3, 2019, 7:41:18 PM3/3/19
to Beancount
Ok, thanks.
I will have to do some more reading to be able to fully understand your answer

Martin Blais

unread,
Mar 3, 2019, 8:34:21 PM3/3/19
to Beancount
Examples: 
- If you live in the USA, convert dollars to Euros, and the value of the Euro goes up, when you look at it from a US perspective, you experienced a gain (if you looked at it from a European perspective you made a loss). That's not an investment profit; that's a gain from a currency conversion. Usually this is not called a profit (1). That conversion also didn't carry a cost basis along with it, e.g. those Euros, you didn't keep a memory of the rate at the moment of conversion.
- On the other hand, if you convert dollars to units of  stock in a US company, and the value of the company shares goes up, you turned out an investment profit. That's a real profit, and that's 99.99% of the cases where people talk about tracking gains/losses. The cost basis is attached to the units you bought, and Beancount allows you to unambiguously book these so you can keep track of how much cost basis is locked into those shares you hold. This matters because your local tax authority bases their taxes on this.
- Finally, if you buy shares in a foreign company whose shares are denominated in a foreign currency (2), first converting your local currency and then buying the shares, then you will incur two sources of change in value: the change in value relative to the native (foreign) currency due to the growth of the company, and a change in value due to the variation in the exchange rate. Right now there is no easy way to untangle these two separately.

(1) For technical pedants: Yes, that's not the case for people trading currencies, but in any case, those doing that would do it all on margin anyway, e.g. they wouldn't convert currencies that they own, they would instead enter a long/short collateralized (i.e., protected) by a margin in their home currency. That's not what I'm talking about here. And nobody who's clued in actually converts nominal amounts for the purpose of speculating.
(2) Usually this requires a conversion and special access to those markets, features which only relatively sophisticated brokers will offer (e.g. IBKR, or buying US shares from a Canadian account).

(For the entire list)
This brings up an interesting point, one which I had never given much though before: if we wanted to solve this problem - and I think it may be worthwhile to do so - couldn't we just (optionally) store the original exchange rate at the time the lot was converted, with the lot (i.e., Position object) itself? That is, each lot would store:
- units (amount, currency)
- cost (amount, currency, acquistion-date, label, rate*)
(*) This being the new bit.

Given those rates, we should be able to compute the current value of the cost basis in the local currency, and that against how much it cost us originally, should tell us the proportion of gain/loss due solely to currency appreciation/depreciation (ignore the effect relative interest differences between the two currencies).





To view this discussion on the web visit https://groups.google.com/d/msgid/beancount/939fe47e-382a-4272-94e6-dd29f660d519%40googlegroups.com.

Chary Chary

unread,
Mar 27, 2019, 12:37:11 PM3/27/19
to Beancount
Martin,

I think I have now done enough reading and testing (both beancount and ledger), that I can continue conversation without sounding too stupid (forgive me, if it is still  the case)

So, effectively what I think I want

Step 1: I run a report, which shows my Net Worth, converted to single currency at certain date, using all exchange rates valid for that date. Let us call it Day A Net Worth Report 

I think in beancount query language such report will look like this

select account, SUM(convert(position, 'CurrencyCode',<Date A>)) from close on <Date A> WHERE account ~'Assets' OR account ~'Liabilities'

This query will give me some output, similar to:

       account             sum_convert
---------------------- -----------------
Assets:US:PaymentUSD        12300.0 EUR
Assets:Broker:stock:XYZ      2100.0 EUR
Liabilities:DE:creditCard   -3000.0 EUR      

So my net worth is 11400.00 Euro (I didn't find a way how to make beancount query to put a total)



Step 2 I run the same report, but for day B (which is after day A).

Step 3: And now I want a single report, which will show how I got from Day A net Worth  to Day B Net Worth.  I want to have a set of numbers, which, when I add them together, will give me exactly the difference in Net Worth between pints A and B
 

I even have written a document, which describes how I think this can be achieved, document contains example spreadsheet with formulas, for very simple case

I think it shall work like this:


  1. Expenses and Incomes are tracked at the historical exchange rate (e.i. at the exchange rate, available during transaction). When exchange rate changes, Expenses and Income do not get re-calculated.  

This is believed to be inline with conventional wisdom , that if you have consumed pizza for 100 USD, there is no reason to re-calculate how much this pizza is worth now in EUR, when USD/EUR exchange rate changed. It only makes sense to know how much that pizza was worth in EUR, when you consumed it.


  1. Assets and Liabilities on the other side are recalculated with every event of exchange rate change.  

This is also inline with conventional wisdom. If instead of consuming 100 USD in a form of pizza,  you put them in a bank account and all of a sudden this USD currency became more expensive vs EUR, this means you actually got reacher in terms on EUR and you want to see it.


  1. The exchange rate changes are taken care in the following way:

A special Commodity Revaluation transaction is logged. Following the principle of double-entry accounting

  • one shoulder of transaction is logged to corresponding Asset and / or Liability accounts to adjust these accounts to new exchange rate.

another shoulder is logged to special Gain due to commodity price change (trading account, using Peter Selinger’s terminology).


In my example I do not distinguish realized and unrealized gain. I just create some virtual income account "Gain due to commodity price change". this term shall cover everything: gain due to stock price change as well as due to currency exchange rate.  This is because from one side, the term "realized gain" is closely linked to the term "taxable gain", hence depends on local legislation. From the other side, for the gain first becomes unrealized and only then, one can realize it (say by selling the stock).   Gain due to commodity price change the same.

So, using example from beancount documentation converting unrealized gain to realized shall look like this

2014-02-17 * "Selling some IBM"
 Assets:US:ETrade:IBM                             -3 IBM {160.00 USD} @ 170.00 USD
 Assets:US:ETrade:Cash                              500.05 USD
 Expenses:Financial:Commissions                       9.95 USD
 Income:GainDueToCommodityPriceChange:realized         -30 USD
 Income:GainDueToCommodityPriceChange:unrealized        30 USD





Does it make any sense?


Once again, this is all driven by idea to have an explanation on how I got from Day A net Worth to Day B Net Worth. 
I must say I still don't understand how people who do trading and spend and get paid in different currencies live without such report. I just wouldn't trust calculation, if I can't see how I got from A to B.


Note: In my document you can see, that I have tested ledger, and  It looks like ledger does calculation as per desired calculation method, however it seems not to be possible to produce a single income statement-like report. One would have to query ledger is different ways and paste together different parts of different reports, to get the situation as presented in Appendix A

It has been later found however, that on more complex example, ledger produces results with unrealized gains, which appear to be wrong.  See more details here.





Martin Blais

unread,
Apr 9, 2019, 10:35:35 PM4/9/19
to Beancount
On Wed, Mar 27, 2019 at 12:37 PM Chary Chary <char...@gmail.com> wrote:
Martin,

I think I have now done enough reading and testing (both beancount and ledger), that I can continue conversation without sounding too stupid (forgive me, if it is still  the case)

So, effectively what I think I want

Step 1: I run a report, which shows my Net Worth, converted to single currency at certain date, using all exchange rates valid for that date. Let us call it Day A Net Worth Report 

I think in beancount query language such report will look like this

select account, SUM(convert(position, 'CurrencyCode',<Date A>)) from close on <Date A> WHERE account ~'Assets' OR account ~'Liabilities'

This query will give me some output, similar to:

       account             sum_convert
---------------------- -----------------
Assets:US:PaymentUSD        12300.0 EUR
Assets:Broker:stock:XYZ      2100.0 EUR
Liabilities:DE:creditCard   -3000.0 EUR      

So my net worth is 11400.00 Euro (I didn't find a way how to make beancount query to put a total)

Just remove the "account, " target from your query. The presence of "select account" infers an implicit group-by clause, as a convenience.

In a distant future, I'd like to extend the supported SQL syntax  to support aggregates rendered at the bottom of each column, e.g.

  select account, sum(...) as amt  bottomline amt as sum

or something better than that, that would tell the rendered how to aggregate all the values in a column and render them at the bottom as a special row, because it's common.


Step 2 I run the same report, but for day B (which is after day A).

Step 3: And now I want a single report, which will show how I got from Day A net Worth  to Day B Net Worth.  I want to have a set of numbers, which, when I add them together, will give me exactly the difference in Net Worth between pints A and B

Easy: if it's a one-off, export the two queries to CSV and write a script that will read that and compute the difference.
In Python, you can do it all at once by using the code in beancount.query.query.
Or even just filter the postings through a loop and accumulate the filtered positions in an Inventory instance, and finally convert (see beancount.core.inventory, beancount.core.convert).



I even have written a document, which describes how I think this can be achieved, document contains example spreadsheet with formulas, for very simple case

I think it shall work like this:


  1. Expenses and Incomes are tracked at the historical exchange rate (e.i. at the exchange rate, available during transaction). When exchange rate changes, Expenses and Income do not get re-calculated.  

This is believed to be inline with conventional wisdom , that if you have consumed pizza for 100 USD, there is no reason to re-calculate how much this pizza is worth now in EUR, when USD/EUR exchange rate changed. It only makes sense to know how much that pizza was worth in EUR, when you consumed it.


  1. Assets and Liabilities on the other side are recalculated with every event of exchange rate change.  

This is also inline with conventional wisdom. If instead of consuming 100 USD in a form of pizza,  you put them in a bank account and all of a sudden this USD currency became more expensive vs EUR, this means you actually got reacher in terms on EUR and you want to see it.


Nothing is "tracked".
What you want is simply to convert your balance sheet to another currency at different points in time - with the rate at the time - and compute the difference.
You can do that as you describe above.

Here's a possible method: collect your two balance sheets, say, at time A and B, and then for each line that you compare, you gather the exchange rate at time A and B, as well as the inflows/outflows of capital to that account, compute the component that's from the exchange rate, and split them out from the converted totals at time B.


 

  1. The exchange rate changes are taken care in the following way:

A special Commodity Revaluation transaction is logged. Following the principle of double-entry accounting

  • one shoulder of transaction is logged to corresponding Asset and / or Liability accounts to adjust these accounts to new exchange rate.

another shoulder is logged to special Gain due to commodity price change (trading account, using Peter Selinger’s terminology).

#3 could essentially be just a transaction where you affect the trading accounts to recognize the aggregate profit/loss that they're accumulated.


In my example I do not distinguish realized and unrealized gain. I just create some virtual income account "Gain due to commodity price change". this term shall cover everything: gain due to stock price change as well as due to currency exchange rate.  This is because from one side, the term "realized gain" is closely linked to the term "taxable gain", hence depends on local legislation. From the other side, for the gain first becomes unrealized and only then, one can realize it (say by selling the stock).   Gain due to commodity price change the same.

So, using example from beancount documentation converting unrealized gain to realized shall look like this

2014-02-17 * "Selling some IBM"
 Assets:US:ETrade:IBM                             -3 IBM {160.00 USD} @ 170.00 USD
 Assets:US:ETrade:Cash                              500.05 USD
 Expenses:Financial:Commissions                       9.95 USD
 Income:GainDueToCommodityPriceChange:realized         -30 USD
 Income:GainDueToCommodityPriceChange:unrealized        30 USD





Does it make any sense?

There's a lot to read and I have too little time to get deep into the weeds, but I did notice that your transaction above does not balance.
Don't forget  that the first posting weights for 3 x 160, not 3 x 170

Typically - without your special currency consideration - you would write something like this:

2014-02-17 * "Selling some IBM"
 Assets:US:ETrade:IBM                             -3 IBM {160.00 USD} @ 170.00 USD
 Assets:US:ETrade:Cash                              500.05 USD
 Expenses:Financial:Commissions                       9.95 USD
 Income:Gains                                          -30 USD 

If I understand what you're trying to do, it is that rather than recognize a 30 USD income, you want to recognize that in EUR, but you want to split the gain into two postings: one for the USD gain, and one for the difference in currency (both in EUR).
Note that you *could* write a plugin that post-processed your transactions and hits the price table to fetch the rates and split up  all those capital gains postings automatically.



 
Once again, this is all driven by idea to have an explanation on how I got from Day A net Worth to Day B Net Worth. 
I must say I still don't understand how people who do trading and spend and get paid in different currencies live without such report. I just wouldn't trust calculation, if I can't see how I got from A to B.

People ignore the currency components, and think in terms of their home currency (folding in the change in currency into their P/L).


Note: In my document you can see, that I have tested ledger, and  It looks like ledger does calculation as per desired calculation method, however it seems not to be possible to produce a single income statement-like report. One would have to query ledger is different ways and paste together different parts of different reports, to get the situation as presented in Appendix A

It has been later found however, that on more complex example, ledger produces results with unrealized gains, which appear to be wrong.  See more details here.

Ledger doesn't book specific sales against purchases. 
You have to match all your costs manually.
In practice it's effectively impossible not to input errors like that.

So much detail, so little time... 


 






--
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 post to this group, send email to bean...@googlegroups.com.

Chary Chary

unread,
Apr 14, 2019, 7:34:47 AM4/14/19
to Beancount




2014-02-17 * "Selling some IBM"
 Assets:US:ETrade:IBM                             -3 IBM {160.00 USD} @ 170.00 USD
 Assets:US:ETrade:Cash                              500.05 USD
 Expenses:Financial:Commissions                       9.95 USD
 Income:GainDueToCommodityPriceChange:realized         -30 USD
 Income:GainDueToCommodityPriceChange:unrealized        30 USD





Does it make any sense?

There's a lot to read and I have too little time to get deep into the weeds, but I did notice that your transaction above does not balance.
Don't forget  that the first posting weights for 3 x 160, not 3 x 170

Typically - without your special currency consideration - you would write something like this:

2014-02-17 * "Selling some IBM"
 Assets:US:ETrade:IBM                             -3 IBM {160.00 USD} @ 170.00 USD
 Assets:US:ETrade:Cash                              500.05 USD
 Expenses:Financial:Commissions                       9.95 USD
 Income:Gains                                          -30 USD 

If I understand what you're trying to do, it is that rather than recognize a 30 USD income, you want to recognize that in EUR, but you want to split the gain into two postings: one for the USD gain, and one for the difference in currency (both in EUR).


OK, I see my mistake. In beancount model you show gain once you sell commodity.  In fact it does say this in your documentation (and I am sure I read about this): "The answer is often surprising to many users: the price is not used by the balancing algorithm if there is a cost basis;  the cost basis is the number used to balance the postings."

And in my model gain happens when commodity price goes up. When one sells commodity gain only changes from unrealized to realized, but the total amount of gain stays the same. I need to think how to work around this

 
Note that you *could* write a plugin that post-processed your transactions and hits the price table to fetch the rates and split up  all those capital gains postings automatically.


Is there a way to write plugin, which converts all postings to one single commodity at a historical price? I am thinking if I could do such conversion and then add extra transactions, related to gains due to commodity price changes then I could use all logic and reporting of beancount to further process it. I guess to achieve this I would need
  • some provision in current design to convert posting to another commodity
  • preferably function to pull commodity price from price database? Do you have some function like  get_relative_commodity_price(commodity, referenced_commodity, date))
 
 
Once again, this is all driven by idea to have an explanation on how I got from Day A net Worth to Day B Net Worth. 
I must say I still don't understand how people who do trading and spend and get paid in different currencies live without such report. I just wouldn't trust calculation, if I can't see how I got from A to B.

People ignore the currency components, and think in terms of their home currency (folding in the change in currency into their P/L).


I understand, that people ignore currency components. But even if you do investments in stock you still want to know how you got from A to B, when you measure both A and B and B-A in one single currency.

 

Martin Blais

unread,
Apr 14, 2019, 9:59:15 AM4/14/19
to Beancount
On Sun, Apr 14, 2019 at 7:34 AM Chary Chary <char...@gmail.com> wrote:




2014-02-17 * "Selling some IBM"
 Assets:US:ETrade:IBM                             -3 IBM {160.00 USD} @ 170.00 USD
 Assets:US:ETrade:Cash                              500.05 USD
 Expenses:Financial:Commissions                       9.95 USD
 Income:GainDueToCommodityPriceChange:realized         -30 USD
 Income:GainDueToCommodityPriceChange:unrealized        30 USD





Does it make any sense?

There's a lot to read and I have too little time to get deep into the weeds, but I did notice that your transaction above does not balance.
Don't forget  that the first posting weights for 3 x 160, not 3 x 170

Typically - without your special currency consideration - you would write something like this:

2014-02-17 * "Selling some IBM"
 Assets:US:ETrade:IBM                             -3 IBM {160.00 USD} @ 170.00 USD
 Assets:US:ETrade:Cash                              500.05 USD
 Expenses:Financial:Commissions                       9.95 USD
 Income:Gains                                          -30 USD 

If I understand what you're trying to do, it is that rather than recognize a 30 USD income, you want to recognize that in EUR, but you want to split the gain into two postings: one for the USD gain, and one for the difference in currency (both in EUR).


OK, I see my mistake. In beancount model you show gain once you sell commodity.  In fact it does say this in your documentation (and I am sure I read about this): "The answer is often surprising to many users: the price is not used by the balancing algorithm if there is a cost basis;  the cost basis is the number used to balance the postings."

And in my model gain happens when commodity price goes up. When one sells commodity gain only changes from unrealized to realized, but the total amount of gain stays the same. I need to think how to work around this

FWIW, I played with this idea a while back, converting unrealized to realized gains:

(I've come to the conclusion that it's so dependent on the various time periods / reports one generates, that it's best to defer that to reporting code logic and not reify those realizations as transactions at all.)


Note that you *could* write a plugin that post-processed your transactions and hits the price table to fetch the rates and split up  all those capital gains postings automatically.


Is there a way to write plugin, which converts all postings to one single commodity at a historical price? I am thinking if I could do such conversion and then add extra transactions, related to gains due to commodity price changes then I could use all logic and reporting of beancount to further process it. I guess to achieve this I would need
  • some provision in current design to convert posting to another commodity
  • preferably function to pull commodity price from price database? Do you have some function like  get_relative_commodity_price(commodity, referenced_commodity, date))
I think that's doable (but again, I wouldn't recommend it, if you need more fancy reporting around gains, I'd implement that in some reporting code instead).


Once again, this is all driven by idea to have an explanation on how I got from Day A net Worth to Day B Net Worth. 
I must say I still don't understand how people who do trading and spend and get paid in different currencies live without such report. I just wouldn't trust calculation, if I can't see how I got from A to B.

People ignore the currency components, and think in terms of their home currency (folding in the change in currency into their P/L).


I understand, that people ignore currency components. But even if you do investments in stock you still want to know how you got from A to B, when you measure both A and B and B-A in one single currency.

 

--
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 post to this group, send email to bean...@googlegroups.com.
Reply all
Reply to author
Forward
0 new messages