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

On Sunday, August 3, 2025 at 11:23:16 PM UTC+3 fbraen...@gmail.com wrote:

> 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
> On Sunday, August 3, 2025 at 10:02:02 PM UTC+2 Chary Ev2geny wrote:
>
>> 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
>>
>> On Sunday, August 3, 2025 at 10:42:11 PM UTC+3 fbraen...@gmail.com wrote:
>>
>>> 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
>>>
>>>

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

Reply via email to