When you have transactions, invoices or bookings in multiple currencies, it’s important to convert these into a single currency before doing aggregations. For example you might want to calculate total revenue per month, with sales being done in multiple currencies.

The challenge is to convert currencies with the exchange rate of the day of the transaction.

Step 1: import exchange rates

Go to Connections, and add a connection to one of the currency exchange rates connectors, for example Norway Bank (Norges bank). This will sync a table into your account with exchange rates:

Step 2: join your transactions with the exchange rates

Write a query that joins your transactions on the currency exchange rates.

Make sure to perform a LEFT join and not an INNER JOIN, to make sure that no transactions would be left out because of a missing matching record in the currency exchange table.
Make sure to join on both the currency and the date of the transaction.
Add a column with the converted amount: transaction amount x rate from the currencies table

Example query:

SELECT 
	transactions.*,
  transactions.amount * rate AS converted_amount 
FROM transactions
LEFT JOIN currencies
ON transactions.currencycode = rate_currency AND transactions.date = rate_date

Query joining transactions with exchange rates

Step 3: check the result

Double check if any converted amounts are missing:

If OK, use this table going forward in your aggregations, charts etc.

⌘I

Basics

Advanced

For Partners

For AI Creators

Currency conversions

Step 1: import exchange rates

Step 2: join your transactions with the exchange rates

Step 3: check the result

​Step 1: import exchange rates

​Step 2: join your transactions with the exchange rates

​Step 3: check the result

Step 1: import exchange rates

Step 2: join your transactions with the exchange rates

Step 3: check the result