Investing USD and EUR with prices

[Fabian Braennstroem]

Hi,

I am trying to calculate profits/loss in EUR for investing in USD

My bean file looks like this at the moment. At the end I listed the prices; here for easy testing with simple numbers:

option "operating_currency" "USD"
option "operating_currency" "EUR"

; Accounts
1970-01-01 open Assets:Brokerage
1970-01-01 open Assets:Cash
1970-01-01 open Income:CapitalGains
1970-01-01 open Expenses:Commissions
2016-09-13 open Assets:Brokerage:AAPL

2022-01-01 * "Buy AAPL"
  Assets:Brokerage:AAPL         100 AAPL {100.00 USD} @ 100.00 USD
  Assets:Cash -10000.00 USD

2022-06-01 * "Sell AAPL"
  Assets:Brokerage:AAPL        -50 AAPL {} @ 200.00 USD
  Assets:Cash              10000.00 USD
  Income:CapitalGains
2022-12-01 * "Sell AAPL"
  Assets:Brokerage:AAPL        -50 AAPL {} @ 200.00 USD
  Assets:Cash              10000.00 USD
  Income:CapitalGains

;; for testing
2022-01-01 price USD 0.8 EUR
2022-06-01 price USD 0.5 EUR
2022-12-01 price USD 0.9 EUR


Running this query with python and pandas:
SELECT
  year(date) AS year, account, convert(sum(position), 'EUR') AS EUR
WHERE
  account ~ '^Income:CapitalGains'
GROUP BY
  year, account
ORDER BY
  year, account


results in:
====================================================================================================
 - USD - original currency
....................................................................................................
   year              account  sum(position) (USD)
0  2022  Income:CapitalGains             -10000.0
====================================================================================================
 - EUR - converted from USD
....................................................................................................
   year              account  eur (EUR)
0  2022  Income:CapitalGains    -9000.0



As you can see, this query takes only the last price exchange info. Though, I
would need a calculation, where for each trading day the conversion takes place.
So for this example each different price needs to be account for for each
different trading day.

I am sure, I am doing something wrong... but not sure, what, probably the query.

Do you have an idea, what I am doing wrong?

Thank you!

Fabian

[Chary Ev2geny]

I think this is what you need

===========

SELECT
  year(date) AS year, account, convert(sum(position), 'EUR', date) AS EUR

WHERE
  account ~ '^Income:CapitalGains'
GROUP BY
  year, account
ORDER BY
  year, account

[Fabian Braennstroem]

Hi,

thank you for your quick response!

Unfortunately, I get an error with this adjustment and I am not exactly sure what that means. The error is:

    raise CompilationError('mixed aggregates and non-aggregates are not allowed')
beanquery.compiler.CompilationError: mixed aggregates and non-aggregates are not allowed

Maybe you have an idea? I forgot to mention that I am using v3, if this is relevant here.

Thank you!

Fabian

[Chary Ev2geny]

Sorry,

my fault.

This query works

=======

SELECT
  year(date) AS year, account, sum(convert(position, 'EUR', date)) AS EUR

WHERE
  account ~ '^Income:CapitalGains'
GROUP BY
  year, account
ORDER BY
  year, account

=========

You can test it here:

https://colab.research.google.com/drive/1uaEVHVZ7bsPgXyjHYz0eMziToePltZZn?usp=sharing

[Fabian Braennstroem]

Hi,

Ok, I see the sum is done after the conversion, thank you!

Best Regards

Fabian

-- 
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 visit https://groups.google.com/d/msgid/beancount/128fa223-6917-4cd1-b700-0031c46a52fan%40googlegroups.com.