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/83075172-36cd-4ade-a25e-e0d11336004en%40googlegroups.com.
