Hi, Ok, I see the sum is done after the conversion, thank you!
Best Regards Fabian > On 3. Aug 2025, at 22:56, Chary Ev2geny <[email protected]> wrote: > > 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 [email protected] > <http://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 [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] > <mailto:[email protected]>. > To view this discussion visit > https://groups.google.com/d/msgid/beancount/128fa223-6917-4cd1-b700-0031c46a52fan%40googlegroups.com > > <https://groups.google.com/d/msgid/beancount/128fa223-6917-4cd1-b700-0031c46a52fan%40googlegroups.com?utm_medium=email&utm_source=footer>. -- 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/230CE973-CC64-4511-8219-1556475A0E64%40googlemail.com.
