Free Open Exchange Rates to BigQuery Connector by OWOX: Take Full Control of Your Currency Data

Running financial analysis is one thing. But automating live currency data flows into BigQuery without hitting API limits, tool fees, or brittle pipelines? That’s where it gets tricky.

Forget CSV uploads, copy-paste hacks, or expensive ETL tools.

With this free, open-source connector from OWOX Data Marts (Community Apps Script Edition), you can sync real-time exchange rate data from Open Exchange Rates directly into Google BigQuery – no code, no fees, just transparent, scalable FX data pipelines built for analysts.

Why We Made This Free Connector

Currency data powers everything from pricing models to international revenue reporting – but piping live exchange rates into BigQuery in a clean, analyst-ready format isn’t always straightforward.

At OWOX, we believe analysts and finance teams shouldn’t have to rely on clunky exports, overpriced ETL tools, or locked-down integrations just to access FX data. That’s why we built this free Open Exchange Rates to BigQuery connector as part of the OWOX Data Marts (Community Apps Script Edition).

No subscriptions. No usage caps. Just full transparency and control over your currency data – streaming directly into BigQuery for modeling, enrichment, and automation.

Whether you’re aligning pricing to global markets, tracking rate shifts over time, or joining FX data with sales or spend pipelines, this connector keeps you in charge.

Join the Open-Source Movement

We’re not just releasing a free connector, we’re inviting you to help shape the future of open marketing analytics tools.

At OWOX, we believe that data access and transparency should be a right, not a luxury. That’s why this connector is 100% open-source, giving you full control over how it works, what it does, and how far it can go.

If you like what we do, please ⭐ star our GitHub repo to show your support – it helps us reach more analysts and grow the number of available connectors.

Also, feel free to:

• Explore the code and customize it to fit your needs.
• Contribute to this data connectivity ecosystem: suggest new connectors, pull new connectors, and / or provide documentation to support the wider community.
• Join our community & share your feedback.

We’ve built the core, the foundation; now it’s your turn to take it further.

Why Send Open Exchange Rates Data to BigQuery?

Exchange rate data is vital for accurate forecasting, budgeting, and multi-currency reporting. But Open Exchange Rates doesn’t offer a native integration with Google BigQuery, which means you're often stuck with fragmented workflows and manual effort.

❌ No direct pipeline to BigQuery
❌ CSV exports that don’t scale or automate
❌ No way to blend exchange rates with spend, sales, or marketing data in real time

This connector changes that.

✅ Automate exchange rate imports straight into your BigQuery tables
✅ Define your own start dates and target currencies
✅ Get clean, structured FX data ready for modeling, reporting, or joining with other datasets

Instead of wrestling with workarounds or rigid platforms, analysts can now pull live Open Exchange Rates data into BigQuery with full transparency and control, without writing a single line of code.

Google BigQuery

Data sources

Free Open Exchange Rates Connector to Google BigQuery

Download template

Why Choose the OWOX Connector for Open Exchange Rates to BigQuery

The OWOX connector gives you more than a simple data transfer–it offers full control over your exchange rate data directly in BigQuery. Unlike paid ETL platforms that limit access, enforce rigid schemas, or lock features behind premium tiers, this solution is fully open-source and powered by Google Apps Script.

• Built with Google Apps Script – deploys directly from your spreadsheet, no middleware or hosted environments required
• Transparent by design – review and modify the script logic to suit your data model and analytics workflow
• Fast setup – configure your base currency, symbols, and historical range in just a few clicks
• 100% free – no pricing tiers, no quotas, no feature restrictions

Whether you’re feeding exchange rates into financial models, normalizing currency fields for eCommerce data, or building multi-market dashboards, the OWOX connector lets you automate Open Exchange Rates imports into BigQuery without compromise.

Explore More Free Connectors:
Already using Open Exchange Rates in BigQuery? Expand your workflow with these free, no-code integrations:
‍
Open Exchange Rates → Sheets
Microsoft Ads → BigQuery
Criteo Ads → BigQuery
LinkedIn Ads → BigQuery
‍
No subscriptions. No technical barriers. Just fast, flexible access to all your data.

How the Connector Works

We built the OWOX Open Exchange Rates to BigQuery connector to make currency data integration seamless – no paid tools, no CSVs, and no manual syncing. It’s powered by OWOX Data Marts (Community Apps Script Edition) and runs directly from Google Sheets, sending clean, structured data straight into your BigQuery tables.

Here’s what it does under the hood:

• Uses native Google Apps Script to operate securely within your Google BigQuery environment.
• Connects directly to the Open Exchange Rates API using your App ID, with no third-party intermediaries.
• Lets you set a custom start date and select specific currency symbols for targeted imports.
• Automatically sends both historical and current exchange rates into BigQuery for scalable storage and SQL-based analysis.
• Includes optional cleanup settings to manage table size and data retention based on your needs.

With transparent logic and flexible control, this connector delivers accurate exchange rate data into BigQuery, making it ideal for financial modeling, pricing strategy, multi-market dashboards, and more.

Step-by-Step: Import Open Exchange Rates Data into BigQuery  with OWOX Community Apps Script Edition

Streaming currency data into BigQuery is simple with the OWOX connector. In just a few quick steps, you’ll have structured exchange rate data flowing directly into your BigQuery project, ready for analysis, modeling, and reporting.

Step 1: Copy the BigQuery Template ( ~1 min)

To streamline the setup process, use our pre-configured BigQuery template – no need to build from scratch.

1. Access the Template

• Open the “Open Exchange Rates → BigQuery. Template” from OWOX.

2. Create a Personal Copy
   
   • Go to File → Make a copy to save it to your Google Drive.

Master Open Exchange Rates→ BigQuery Reporting With Our Free Template!

Say goodbye to CSV exports and complex APIs – this plug-and-play BigQuery template lets you pull Open Exchange Rates effortlessly.

Download your FREE template today!

Step 2: Configure the Template ( ~1 min)

Once you’ve copied the Open Exchange Rates BigQuery template, head to the Config tab to configure key import parameters.

1. Set the Start Date

• Select the start date for retrieving historical exchange rate data into BigQuery.

Note: A wide date range may result in slower imports, depending on API limits.

2. Define Symbols

• Enter the currency symbols you want to track (e.g., EUR, GBP, JPY). You can include multiple symbols, separated by commas.
•  Note: The base currency is fixed to USD by default. A full list of supported currency symbols and names can be found here.

3. Configure the BigQuery destination fields:

• Destination Dataset ID in the format: projectid.datasetid
• Destination Location matching your BigQuery region (e.g., US, EU)
• Note: If the specified dataset doesn't exist, it will be created automatically.

You may also adjust the following fields in the Config tab:

• ReimportLookbackWindow: Choose how many past days to include when refreshing data.
• MaxFetchingDays: Max number of days to fetch per run 
• CleanUp To Keep Window: Define how many days of data you’d like to retain in your sheet to keep it clean and manageable.

Step 3: Get Your App ID for Open Exchange Rates ( ~1 min)

To start importing data, you first need to generate an App ID from Open Exchange Rates to authorize the connector.

1. Log in to Your Account

• Visit  https://openexchangerates.org and sign in or create an account.

2. Go to the App IDs Section

•  Navigate to the Integration → App IDs  section from the dashboard.

3. Create a New App ID

• Enter a name for your app and click  Generate New App ID.

4. Copy Your App ID

•  Once generated, copy the App ID, as you’ll use it when setting up the connector in BigQuery.

Step 4: Enter Your Credentials in the Template ( ~10 seconds) 

Once you’ve generated your App ID, connect it to the BigQuery template.

1. Open the Credentials Menu

• In your BigQuery Template, go to the top menu → OWOX → Manage credentials.

2. Add Your App ID

• Paste the App ID you generated from the Open Exchange Rates dashboard into the prompt that appears.
• Click OK.

You’re now ready to start importing exchange rate data into BigQuery.

Step 5: Run the Connector & Import Your Data ( ~1 min)

With your App ID in place, you're ready to fetch live exchange rate data into your spreadsheet.

1. Run the Import
In your BigQuery template, go to the top menu → OWOX → Import New Data.

2. Wait for Completion

• Once the log shows "Import is finished", your exchange rate data will then be available in the Data sheet.

• The data will be written to the BigQuery dataset you specified earlier.

Step-by-Step: Import Open Exchange Rates Data to BigQuery with the OWOX Community Edition

Set up a fully self-managed, SaaS-free pipeline to import Open Exchange Rates data directly into BigQuery using the OWOX Community Edition and a local server.

Step 1: Install Node.js and OWOX CLI ( ~2 min)

To run OWOX Data Mart locally, you’ll need to install Node.js and the OWOX CLI. This setup allows you to start a local server and work with the Community Edition effectively.

To get started, install the LTS version of Node.js from nodejs.org and use a version manager like nvm or nvm-windows to avoid permission issues. Once Node.js is set up, you can install the OWOX CLI globally and run ‘owox serve’ to launch the local server. 

For detailed setup instructions and troubleshooting, check the official documentation.

Step 2: Connect Google BigQuery and Set Up Your Data Mart ( ~3 min)

Set up BigQuery as your destination and link it to a new Open Exchange Rates data mart in OWOX.

1.  Open your browser and go to http://localhost:3000.

2. Create Storage in the Data Mart
   
   • Click ‘New Storage’

• Choose ‘Google BigQuery’

3. Fill in the following: 

• Title: e.g., “Open Exchange Rates Storage”
• Project ID: Your Google Cloud project ID
• Region: Must match your BigQuery dataset region

4. Create and paste your Service Account JSON Key.

• Go to Google Cloud Console → IAM → Service Accounts

5. Create a new service account or choose an existing one:

• Click Create Service Account
• Fill in the Service Account Name, ID, and optionally a Description

6. Assign the following roles to the service account:

• BigQuery Data Editor
• BigQuery Job User

7. Generate a new authentication key:

• On the Service Accounts page, select your service account
• Go to the Keys tab
• Click Add Key → Create new key
• Select JSON format
• Click Create to download the file
• Open the downloaded JSON file in a text editor (e.g., Notepad, VS Code)
• Copy the entire contents and paste it into the ‘OWOX Service Account JSON Field’
• Don’t forget to click Save

8. Create your first Data Mart
   
   • Once storage is added, click on the Data Mart Tab, then on ‘New Data Mart’

• Now create the Data Mart, name the title as ‘Open Exchange Rates Data’, as the data source
• Choose the storage named ‘Open Exchange Rates Storage’ you just created
• Click ‘Create Data Mart’.

Now we will do the Data Setup

9. Setup your Data Mart
   
   • Go to the Data Setup Tab and then select Connector

• Click on Setup Connector and Choose Open Exchange Rates Connector

Step 3: Get the Open Exchange Rates App ID ( ~1 min)

To connect your Open Exchange Rates account, you’ll need an App ID. ‍

1. Get the App ID

‍Repeat the steps from Step 3 of the Community Apps Script setup to generate your App ID, or use the same one if it's still valid.

Step 4: Configure Open Exchange Rates Connector and Run Your First Import ( ~2 min)

Now that you’ve retrieved your Open Exchange Rates App ID, it’s time to configure the connector in OWOX.

1. Paste the App ID.

2. Start Date:
   
   • Set the reporting start date (e.g., 2025-08-01). This determines how far back data will be fetched.

• Currency Symbols: Set the currency symbol (e.g., GBP), used to represent monetary values in the fetched data.

3. Further Specify the Fields with the Following Parameters:

• ReimportLookbackWindow: Days to look back when reimporting data (default: 2)
• MaxFetchingDays: Max number of days to fetch per run (default: 31)
• EndDate: The final date up to which data should be fetched.
• Base Currency: Specifies the primary currency in which all financial metrics (like spend or cost) should be standardized. 
• MaxFetchRetries: Number of retry attempts allowed if a data fetch fails.
• InitialRetryDelay: Time delay before making the first retry attempt after a failure.

4. Click Next
5. Endpoint:
   
   • Choose the following valid endpoints: Historical exchange rates data

6. Choose Fields: 
   
   • Select all available fields or choose specific metrics like date, base, rate, and currency based on your reporting needs.

7. Name the Dataset
   
   • Enter the BigQuery dataset name where the imported data should be stored.

Note: If the dataset doesn't exist, OWOX will create it automatically during the import process.

8. Click Finish

In the Data Setup Tab

9.  Click 'Save'.

10. Click 'Publish'.

11. Click 'Manual Run' or use the 3-dot menu → 'Run Now'.

12. Go to 'Run History' and confirm the message: 'Import is finished'.

Step 5: View Imported Data in BigQuery ( ~1 min)

Check that your Open Exchange Rates data is now available in BigQuery.

1.  Go to https://console.cloud.google.com/bigquery.

2. Open the specified project and dataset.

3. Click the Hamburger Menu in the top-left corner of Google Cloud Console to open the navigation panel. 
4. From there, go to Cloud Overview and then proceed to Welcome → BigQuery.

5. Click the table that was created.
6. Use the 'Preview' tab or run SQL queries to inspect your data.

Step 6: Schedule Automated Imports ( ~1 min)

Set up a trigger to pull data on a recurring schedule.

1. Go to the 'Triggers' tab in the new Data Mart
   
   • Click 'Add Trigger'.

2. Configure.

• Trigger Type: Connector Run
• Schedule Settings: Daily / Weekly / Monthly / Interval
• Time Settings: hour, minute, second
• Days of the week
• Time zone

3. Click 'Create Trigger'. Your data will now refresh automatically.

4. The trigger is now active and scheduled to run automatically on the defined days and at the defined time.

📊 Real-Time Reporting with OWOX Reports

Transforming exchange rate data into BigQuery is only the first step. The real value emerges when you visualize that data to spot trends, shifts, and insights instantly.

With the OWOX Reports Extension for Google Sheets, you can instantly pull your Open Exchange Rates data from BigQuery and turn it into dynamic charts and pivot tables – no SQL, no formatting effort.

Use OWOX AutoCharts to:
📊 Visualize currency fluctuations over time
📈 Compare exchange rates across selected currencies
🔍 Spot spikes, drops, and anomalies in historical trends
🔁 Keep your dashboards updated automatically as new data loads

Whether you're managing pricing models, FX reporting, or international cost tracking, AutoCharts brings speed and clarity to your workflow.

What’s Next?

This connector is part of OWOX Data Marts (Community Apps Script Edition) – our open-source collection of tools built for analysts who need speed, transparency, and control.

Explore more resources:
🔗 More free connectors – like Facebook Ads and Twitter Ads to BigQuery, designed for flexible data ownership
🎥 Guided video tutorials – to walk you through setup, configuration, and custom use cases
📊 Ready-made dashboards – to help you visualize BigQuery data faster and with less effort

Explore the full list of tools on our GitHub repo, drop us a ⭐ star, and help shape the future of open analytics with OWOX Connectors.

Google BigQuery

Data sources

Free Open Exchange Rates Connector to Google BigQuery

Download template