Integrating Currency Exchange Rates into BigQuery

If you work with international data, you’ve likely encountered the headache of currency conversion in your analytics. Converting transactions from multiple currencies into a single reporting currency is essential for accurate financial analysis, but implementing a reliable currency conversion system directly in BigQuery can be challenging.

When operating globally, organizations frequently need to standardize financial data in a single currency for reporting and analysis. This is especially crucial when dealing with revenue streams, expenses, or transactions from different countries—each using their local currency. Without standardization to a common denominator (usually USD, EUR, or the company’s home currency), comparing performance across regions becomes nearly impossible, and consolidated financial reporting would show misleading results. This conversion process needs to be both accurate and consistent throughout your analytics pipeline, which is exactly what we’ll solve with this approach.

Fortunately, there’s an elegant solution that leverages Google Sheets as a bridge between live currency rates and your BigQuery environment. This approach is particularly powerful because it:

• Automatically updates with current exchange rates using Google Finance
• Requires minimal maintenance once established
• Integrates seamlessly with your existing BigQuery datasets
• Avoids complex API implementations and associated costs😍😍

In this guide, I’ll walk you through the entire process of setting up this currency conversion pipeline, from creating your Google Sheet to writing the final SQL queries that leverage your exchange rate data.

The Three-Step Process

Here’s the high-level approach we’ll be taking:

1. Create a Google Sheet that pulls live currency exchange rates using Google Finance functions
2. Connect BigQuery to the Sheet by creating an external table that references the currency data
3. Write SQL queries that join your transaction data with the exchange rate table

Let’s dive into each step in detail.

Step 1: Creating Your Currency Exchange Rate Google Sheet

Our first task is to create a Google Sheet that will serve as our exchange rate source. This sheet will use Google Finance functions to automatically pull the latest exchange rates for all the currencies you need. You can make your own version or to make a copy of the file I’ve already made.

Setting Up Your Google Sheet

1. Create a new Google Sheet (or copy my version)
2. Rename it to something descriptive like “Currency Exchange Rates for BigQuery”
3. A basic structure might include:
   • Base Currency (the currency you’re converting from)
   • Exchange Rate (the value of 1 unit of base currency in terms of the target currency)
   • Date (when the rate was retrieved)

Using Google Finance Functions

The real magic happens with the GOOGLEFINANCE() function, which retrieves current and historical financial data, including exchange rates. Here’s the basic syntax for fetching the exchange rate from USD to GBP:

=GOOGLEFINANCE("CURRENCY:USDGBP")

Now, let’s enhance this setup. Instead of hardcoding the origin currency, I moved it to a Variables tab. Additionally, rather than specifying GBP, I use a dynamic value from column C:

=GOOGLEFINANCE(“CURRENCY:C2&Variables!$B$1“)

And that’s the static value on the Variables tab.

Here, Variables!$B$1 contains the static origin currency.

However, Google Finance does not support some currencies, and in certain cases, it might throw an error. To handle this, we can use IFERROR() to return zero when an error occurs:

=IFERROR(GOOGLEFINANCE(“CURRENCY:” & C2 & Variables!$B$1),0)

Additionally, Google Finance may return an error when converting a currency to itself. To prevent this, we add a validation step:

=if(C2=Variables!$B$1,1,(IFERROR(GOOGLEFINANCE(“CURRENCY:” & C2 & Variables!$B$1),0)))

This ensures that if the target currency matches the base currency, the formula simply returns 1 instead of causing an error.

Expanding Your Currency Coverage

You’ll want to include all currencies relevant to your business. If you operate in markets using 20 different currencies, add all of them to your sheet. You can also create additional rows for cross-currency pairs if needed (e.g., EUR to GBP).

Step 2: Creating a BigQuery External Table

Now that we have our currency data in Google Sheets, we need to make it accessible to BigQuery. We’ll do this by creating an external table that reads directly from the Google Sheet.

Creating the External Table

Now we need to create the BigQuery table to “host” the data from Google Sheet.

1. In BigQuery, select your dataset (or create a new one)
2. Click “Create Table”
3. For “Source” select “Drive“
4. Enter the URL of your Google Sheet
5. Select “Google Sheets” as the file format
6. Specify the data range.
7. If missing, enter the Project & Dataset names.
8. Enter a name for the new table.

1. In Schema, choose Auto Detect
2. In Advanced Options, make sure to enter “1” in “Header rows to skip”
3. Click “Create Table”

Understanding External Table Behavior

It’s crucial to understand how external tables work in BigQuery:

• Data isn’t copied: The table is just a pointer to your Google Sheet
• Query-time fetching: Data is fetched only when you run queries
• Potential latency: There might be a slight delay when querying external tables
• No historical data: Unless explicitly stored in your sheet

Testing Your External Table

After creating the table, run a simple query to verify it’s working:

SELECT * 
FROM `your_project.your_dataset.currency_exchange_rates`
LIMIT 10;

You should see your currency exchange rates appear in the results.

Step 3: Writing SQL Queries With Currency Conversion

Now comes the fun part: using your exchange rate data in actual analytics queries. The basic pattern involves joining your transaction data with the currency table and applying the conversion.

Basic Currency Conversion Query

Here’s a simple example query that converts transaction amounts to USD:

SELECT
  t.transaction_id,
  t.transaction_date,
  t.amount,
  t.currency AS original_currency,
  c.exchange_rate,
  t.amount * c.exchange_rate AS amount_usd
FROM
  `your_project.your_dataset.transactions` t
JOIN
  `your_project.your_dataset.currency_exchange_rates` c
ON
  t.currency = c.base_currency
  AND c.target_currency = 'USD'
WHERE
  t.transaction_date = '2023-01-15'

Maintenance and Troubleshooting

Once your system is set up, it should run smoothly with minimal intervention. However, there are a few maintenance considerations:

Regular Checks

1. Verify Google Finance data: Occasionally check that your Google Sheet is still pulling accurate rates
2. Watch for schema changes: If you modify your Sheet structure, you’ll need to update your external table
3. Monitor query performance: External tables can be slower than native BigQuery tables

Common Issues and Solutions

Stale Exchange Rates

If your rates aren’t updating, check:

• Google Sheet formula cells are properly formatted
• You haven’t accidentally replaced formulas with static values
• The Google Finance API is functioning (rarely, it may have outages)

Performance Problems

If queries are running slowly:

• Consider materializing the exchange rate data in a regular BigQuery table
• Schedule a daily job to copy the latest rates from your external table to a native table

Materializing Exchange Rate History

Instead of relying solely on the current rates in your Sheet, you might want to build a historical record:

1. Create a scheduled query that runs daily
2. Have it append the current day’s rates to a historical table
3. Use this historical table for time-sensitive analytics

Happy converting!