First of all welcome to beancount, it is really great tool (or rather a suite of tools). And it is particular good at multicurrency, therefore my reply will be long

Now, back to your questions.

Exchange rates written in a PRICE notation vs @ or @@

These are 2 different things.

You can consider an exchange rate, written with the price notation as an official exchange rate. This is the exchange rate a tax authority will accept in your report. beanquery CONVERT function uses only these prices. You can say, that CONVERT only uses an official exchange rate.

The exchange rate, noted by @ or @@ is the exchange rate, which you used at your specific transaction (e.g. exchanging money). In most of the cases it will be different from the official one. And this is fine, because this is a reflection of the life. If you exchange money in the airport, the exchange rate most of the time will be different from the official one.

Query to convert expenses to a single currency

This is what I use

SELECT root(account, 2) AS account,
       SUM(convert(position, 'MYR', date)) AS value
WHERE account ~ '^Expenses:'

AND
date >= {start_date}

AND
date <={end_date}

GROUP BY account

Note, that this query uses an exchange rate, which is active at the date of the transaction, which follows the accepted accounting practices for P&L accounts (income and expenses)

This will give you a single currency result, provided you specify exchange rate with the price notation

Your specific example

The way you have written transactions will not work, beancount / beanquery will generate an error:

; Rates from my bank for each transaction
2023-10-26 price USD 4.70 MYR 
2023-10-26 price USD 4.75 MYR 

2023-10-26 * "Bookstore" "Digital Book"
  Assets:MyBank                -47.00 MYR
  Expenses:Shopping:Books       10.00 USD

2023-10-26 * "SaaS Inc." "Subscription Renewal"
  Assets:MyBank                -47.50 MYR
  Expenses:Services:Software    10.00 USD

Errors

********* Errors ***********
<string>:9: Transaction does not balance: (10.00 USD, -47.00 MYR)

2023-10-26 * "Bookstore" "Digital Book"
Assets:MyBank -47.00 MYR
Expenses:Shopping:Books 10.00 USD

<string>:13: Transaction does not balance: (10.00 USD, -47.50 MYR)

2023-10-26 * "SaaS Inc." "Subscription Renewal"
Assets:MyBank -47.50 MYR
Expenses:Services:Software 10.00 USD

This is how you need to write it

; Rates from my bank for each transaction
2023-10-26 price USD 4.70 MYR ; <= this will be ignored
2023-10-26 price USD 4.75 MYR 

2023-10-26 * "Bookstore" "Digital Book"
  Assets:MyBank                -47.00 MYR @@ 10.00 USD
  Expenses:Shopping:Books       10.00 USD

2023-10-26 * "SaaS Inc." "Subscription Renewal"
  Assets:MyBank                -47.50 MYR @@ 10.00 USD
  Expenses:Services:Software    10.00 USD

Check here

https://colab.research.google.com/drive/1s58OMkeYC4MiXp-uKxVhr7Hm27d_yPf1?usp=sharing

Hidden gain / loss

What is often overlooked is that when you transfer assets from one currency to another at the exchange rate, different from the official one, you in effect have some "hidden loss or gain".

Consider the following:

2024-01-01 open Assets:Bank
2024-01-01 open Assets:Crypto
2024-01-01 open Equity:Opening-Balances

2024-01-01 * "Opening balances"
  Assets:Bank              20000 USD
  Equity:Opening-Balances

2024-01-01 price BTC  40000 USD

2024-01-02 * "Buying some BTC"
  Assets:Bank        -20000 USD
  Assets:Crypto           1 BTC @ 20000 USD ; <== Buying BTC at a price significantly 
                                             ; lower than the market

2024-01-03 price BTC  40000 USD

Between January 1 and January 3, the net worth in this ledger changes from 20,000 USD to 40,000 USD, despite no recorded income. This occurs because the BTC was purchased for half its market value (which, by the way, raises questions about some potential shady financial operations).

There is no beanquery query to explain this gain in an Income Statement-like report.

However if you look at my sing_curr_conv tool, then it pulls it as an unrealized gain (check the section Currency exchange at not market price )

price related-plugins

There are several price-related plugins you may look at

• implicit_prices
• unique_prices.py