Converting primary currency to regional currencies in spreadsheet budgets

Other Questions Google Sheets
1

I’m managing a master NZD budget in Sheets using QUERY. How can I auto-convert amounts to local currencies? For example:

=FILTER('RegionalSheet', "SELECT * WHERE region='UK'", 0)
2

I’ve tackled similar challenges in international budgeting. One effective method is using a combination of QUERY and VLOOKUP with a separate exchange rate table.

Begin by creating a dedicated sheet for exchange rates, for instance named ‘Rates’. Then, in your main sheet, apply the following formula:

=QUERY(ARRAYFORMULA('RegionalSheet'!A:F * VLOOKUP('RegionalSheet'!G, Rates!A:B, 2, FALSE)), "SELECT * WHERE Col1='UK'", 0)

This formula assumes that column G contains country codes that correspond to your rate table. The VLOOKUP retrieves the appropriate rate, and ARRAYFORMULA applies it across all rows. This approach allows for easy updates and supports multiple currencies, as long as your rate table remains current.

3

As someone who’s dealt with multi-currency budgets, I can offer some insights. Instead of using FILTER, I’d recommend leveraging QUERY with ARRAYFORMULA and GOOGLEFINANCE for automatic currency conversion. Here’s a approach that’s worked well for me:

=ARRAYFORMULA(
  QUERY(
    {'RegionalSheet'!A:E, 
     'RegionalSheet'!F:F * GOOGLEFINANCE("CURRENCY:NZDGBP")},
    "SELECT * WHERE Col1 = 'UK'", 0
  )
)

This assumes your amount column is F. The GOOGLEFINANCE function fetches real-time exchange rates. You’ll need to adjust the currency pair (NZDGBP) for each region. It’s more efficient than individual conversions and updates automatically. Just be aware that exchange rates fluctuate, so you might want to consider using a fixed rate for budgeting purposes if that’s a concern.

4

hey claire, try this:

=ARRAYFORMULA(QUERY(‘RegionalSheet’!A:F * VLOOKUP(‘RegionalSheet’!A:A, {“UK”,GOOGLEFINANCE(“CURRENCY:NZDGBP”)}, 2, 0), “select *”, 0))

it’ll auto-convert NZD to GBP for UK rows. just change currency pair for other regions. lemme know if u need help!