Skip to content

exchange_rate

exchange_rate(base, to)

Description

Get exchange_rate from base (e.g. USD) to to (e.g. EUR) from Yahoo Finance.

This functions uses yfinance python package and returns the average of the bid price and the ask price. If the data is not available in Yahoo Finance, will return null

Param Possible values
base
and
to		 Iso 4217 currency codes such as EUR or USD. See active codes on wikipedia

Usage

Call or Deploy exchange_rate ?
Call exchange_rate directly

The easiest way to use bigfunctions

  • exchange_rate function is deployed in 39 public datasets for all of the 39 BigQuery regions.
  • It can be called by anyone. Just copy / paste examples below in your BigQuery console. It just works!
  • (You need to use the dataset in the same region as your datasets otherwise you may have a function not found error)

Public BigFunctions Datasets

Region Dataset
eu bigfunctions.eu
us bigfunctions.us
europe-west1 bigfunctions.europe_west1
asia-east1 bigfunctions.asia_east1
... ...
Deploy exchange_rate in your project

Why deploy?

  • You may prefer to deploy exchange_rate in your own project to build and manage your own catalog of functions.
  • This is particularly useful if you want to create private functions (for example calling your internal APIs).
  • Get started by reading the framework page

Deployment

exchange_rate function can be deployed with:

pip install bigfunctions
bigfun get exchange_rate
bigfun deploy exchange_rate

Examples

To know how many euros a dollar is worth

select bigfunctions.eu.exchange_rate("USD", "EUR")

select bigfunctions.us.exchange_rate("USD", "EUR")

select bigfunctions.europe_west1.exchange_rate("USD", "EUR")

+---------------+
| exchange_rate |
+---------------+
| 0.910437      |
+---------------+

Use cases

A company sells products internationally and stores sales data in BigQuery. The sales data includes the transaction amount and the currency in which the transaction occurred. They need to report all sales in a single currency (e.g., USD) for financial analysis.

They can use the exchange_rate function to convert all transactions to USD in their reporting queries. For example, if they have a table called sales with columns transaction_amount and transaction_currency, they can write a query like this:

SELECT
    transaction_amount * bigfunctions.us.exchange_rate(transaction_currency, 'USD') AS transaction_amount_usd
  FROM
    `your-project.your_dataset.sales`

This query would calculate transaction_amount_usd by multiplying the original transaction_amount by the exchange rate returned by the exchange_rate function. This effectively normalizes all transaction amounts to USD. This makes it possible to aggregate and analyze sales data across different currencies, providing a consolidated view of the company's financial performance.

Need help or Found a bug?
Get help using exchange_rate

The community can help! Engage the conversation on Slack

We also provide professional suppport.

Report a bug about exchange_rate

If the function does not work as expected, please

  • report a bug so that it can be improved.
  • or open the discussion with the community on Slack.

We also provide professional suppport.

Show your ❤ by adding a ⭐ on