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 [email protected] 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 [email protected].
To view this discussion visit
https://groups.google.com/d/msgid/beancount/ca245749-eba8-49eb-b3cb-39c471ca65ban%40googlegroups.com.
