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.