Google Sheets: The Easy Way to Convert Currencies

Hey there, besties! Today, I’m going to show you how to effortlessly convert currencies in Google Sheets using the amazing GOOGLEFINANCE function.

What is the GOOGLEFINANCE function?

Working with finances can be a real pain, especially with constantly fluctuating exchange rates. But fear not! Google Sheets has a nifty function called GOOGLEFINANCE, specifically designed to handle financial calculations.

That’s what we’ll be using for currency conversion.

The GOOGLEFINANCE function retrieves real-time or historical currency information and exchange rates from the Google Finance website. This saves you time and effort that you would have spent importing exchange rates from another source.

Using GOOGLEFINANCE to Convert Currencies in Google Sheets

The GOOGLEFINANCE function is the perfect tool for currency conversion in Google Sheets. It allows you to fetch real-time exchange rates (well, almost real-time).

You won’t have to search through countless databases for the current exchange rates. All you need is the correct formula for this powerful currency converter in Google Sheets. The formula uses currency symbols to indicate the source and target currencies.

Syntax of the GOOGLEFINANCE function

So, what makes up the currency converter function in Google Sheets? Here’s the basic syntax of the GOOGLEFINANCE function for currency conversion:

=GOOGLEFINANCE("CURRENCY:<source_currency_symbol><target_currency_symbol>")

Please note that this is different from the typical syntax of GOOGLEFINANCE, which is also used to retrieve stock information. The syntax for that looks like this:

=GOOGLEFINANCE(ticker, [attribute])

Now, let’s dive a little deeper into the currency conversion function.

If you’re familiar with functions in Google Sheets, you’ll understand that this one has two important aspects. You’ll need to input this information for the currency converter to work. Luckily, it’s pretty straightforward to enter the information you want to extract from Google Finance. Here’s what you’ll need to input:

• "source_currency_symbol" is a three-letter code for the currency you want to convert from.
• "target_currency_symbol" is a three-letter code for the currency you want to convert to.

As promised, I’ll give you a concrete example. Let’s say we want to convert US Dollars to Indian Rupees. In this case, I would write the formula as:

=GOOGLEFINANCE("CURRENCY:USDINR")

Note that there is no space between the two currency codes. That’s a common mistake, but easily avoidable.

Three-Letter Currency Codes in Google Finance

If you want to convert other currencies, you’ll need to know the codes. Just like in the example above, you’ll replace the three-letter code with the currencies you want to convert.

Here are some commonly used currency codes with GOOGLEFINANCE:

• US Dollar (USD)
• Japanese Yen (JPY)
• Canadian Dollar (CAD)
• Indian Rupee (INR)
• Iranian Rial (IRR)
• Russian Ruble (RUB)
• Euro (EUR)
• Singapore Dollar (SGD)
• Hong Kong Dollar (HKD)
• British Pound (GBP)

How to Use GOOGLEFINANCE for Real-Time Currency Conversion

Ready for another concrete example? I’ve got screenshots below to show you how to convert dollars into three other currencies. I’ve included the currencies in column B. Please note that I’m using this format to convert currencies from column A to those in column B:

Here’s how to convert a currency in Google Sheets to get the exchange rate from dollars to the three currencies in column B:

1. Select the first cell in the column where you want the results to appear (C2).
2. Enter the formula: =GOOGLEFINANCE("CURRENCY:USDINR")
3. Press the Enter key.

You should see the current exchange rate for converting USD to INR in cell C2.

Alternatively, you can even include references to cells in the formula by combining them with the & operator. Here’s how it works:

1. Enter the formula: =GOOGLEFINANCE("CURRENCY:"&A2&B2)
2. Press the Enter key.
3. Double-click the fill handle of cell C2 to copy the formula to the rest of the cells in column C.

You should now see the conversion rates for USD to the three currencies listed in the sheet.

Note: The currency codes in Google Finance are identical to the abbreviated codes used on international stock exchanges.

How to Convert USD to INR with GOOGLEFINANCE

The previous steps only give you the exchange rates between two currencies in Google Sheets, but they don’t actually convert money from one currency to another. Let’s say we have the following list of prices in dollars and we want to convert them to INR.

To convert the money in the table above from USD to INR, follow these steps:

1. Select the first cell in the column where you want the results to appear (B2).
2. Enter the formula: =GOOGLEFINANCE("CURRENCY:USDINR")*A2
3. Press the Enter key.
4. Double-click the fill handle of cell B2 to copy the formula to the rest of the cells in column C.
5. You should now see column B filled with prices in INR.

Notice that we simply multiplied the result of the GOOGLEFINANCE function by the value in the cell in column A to convert the price to INR.

Entering your parameters with just the general GOOGLEFINANCE function is enough to get an accurate conversion rate.

However, there are other optional parameters that the GOOGLEFINANCE function allows you to input to get exactly what you need. For example, you can use it to also display historical exchange rates.

How to Use GOOGLEFINANCE to Retrieve Historical Exchange Rates

You can make some modifications to the GOOGLEFINANCE function to retrieve historical exchange rates over a time period, instead of just one day. This is useful when you want to create more advanced spreadsheets.

Please note that the syntax of the GOOGLEFINANCE function becomes a bit more complex here. You’ll need to add additional variables. To retrieve historical exchange rates, the GOOGLEFINANCE function can be customized according to the following syntax:

GOOGLEFINANCE("CURRENCY:<source_currency_symbol><target_currency_symbol>", [attribute], [start_date], [end_date|number_of_days], [interval])

Here’s an example of the formula in action. We’ll explain it in more detail in the next section:

In the above syntax, all the parameters mentioned within square brackets are optional. Here’s what they mean:

• The attribute parameter specifies the type of data you want to retrieve. It’s a string value, and its default value is “price”. This means that we want to retrieve real-time price quotes from Google Finance. We’ve provided a list of attribute values and what they mean at the end of this tutorial.
• The start_date parameter specifies the date from which we want the historical data to start.
• In the fourth parameter, you can either specify the end date for historical data or the number of days from the start date for which you want the historical data.
• The interval parameter specifies the frequency of the returned data. It can be either “DAILY” or “WEEKLY” depending on your needs.

How to Use GOOGLEFINANCE to Retrieve Exchange Rates over a Time Period

Let’s take an example to understand how the GOOGLEFINANCE function can be used to retrieve exchange rates (USD to INR) from October 10, 2020, to October 20, 2020.

1. Select a cell from where you want to start displaying the exchange rates. You don’t need to add column headers as the function automatically adds column headers.
2. Enter the formula: =GOOGLEFINANCE("CURRENCY:USDEUR", "price", DATE(2020,10,10), DATE(2020,10,20), "DAILY")
3. Press the Enter key.

You should now see two new columns automatically inserted starting from the cell where you entered the formula.

The first column contains the date for each day between October 10, 2020, and October 20, 2020. The second column contains the closing exchange rate from Google Finance for each day.

If you want to display weekly rates instead of daily rates, you can simply replace the interval parameter in the currency conversion function with “WEEKLY”.

How to Retrieve Exchange Rates for the Previous Week with GOOGLEFINANCE

If you want to dynamically display exchange rates for the previous week based on the day the sheet is opened, you can use the TODAY function instead of the DATE function.

Here’s an example where we want to dynamically display exchange rates for the past 10 days, regardless of the day the sheet is opened.

Follow these steps:

1. Select a cell from where you want to start displaying the exchange rates.
2. Enter the formula: =GOOGLEFINANCE("CURRENCY:USDEUR", "price", TODAY()-10, TODAY(), "DAILY")
3. Press the Enter key.

You should now see two new columns automatically inserted starting from the cell where you entered the formula.

The first column contains the date for each day, starting from the 10 days preceding the current date. The second column contains the closing exchange rate for each day.

In this case, the TODAY() function returns the current date the file is opened. So, whenever it’s opened, this function will refresh and give a new value.

Some Key Points to Remember About Currency Conversion in Google Sheets

Here are a few important points to know about the GOOGLEFINANCE function:

• When we talk about real-time exchange rates, expect a delay of up to 20 minutes.
• For real-time exchange rates, the function returns a single value. However, for historical exchange rates, the function returns an array with column headers.
• If you provide no date parameters, GOOGLEFINANCE assumes you only want real-time results. If you provide a date parameter, the query is considered a request for historical data.

Attribute Values for the GOOGLEFINANCE Function in Google Sheets

Here are some commonly used attribute values for the GOOGLEFINANCE function:

For real-time data:

• “priceopen” – We want the opening price of the current day.
• “high” – We want the highest price of the current day.
• “low” – We want the lowest price of the current day.
• “volume” – We want the trading volume of the current day.
• “marketcap” – We want the stock’s market capitalization.

For historical data:

• “open” – We want the opening price for the specified dates.
• “close” – We want the closing price for the specified dates.
• “high” – We want the highest price for the specified dates.
• “low” – We want the lowest price for the specified dates.
• “volume” – We want the volume for the specified dates.
• “all” – We want all the above-mentioned information.

There are other attributes you can use as well. You can refer to the official Google Sheets documentation to learn more.

FAQs on Currency Conversion with Google Sheets

Here are some of the most commonly asked questions about the currency converter (and the GOOGLEFINANCE function) in Google Sheets. If you have a question I haven’t covered yet, please leave a comment below (or on my YouTube channel).

How can I use the GOOGLEFINANCE function to track stocks?

I’ve created a video that shows you how to create a stock tracking sheet in under a minute on Google Sheets. Basically, all you have to do is list the stocks, ETFs, or cryptocurrencies you want to track and use the GOOGLEFINANCE function to automatically fetch their values. It’s my favorite way to track stock prices.

Can Google Sheets convert currencies?

Yes, you can use the GOOGLEFINANCE function with the currency codes you want to convert as the second argument, like this:

=GOOGLEFINANCE("Currency:GBPAUD")

Why isn’t the GOOGLEFINANCE function working?

The most common reason the GOOGLEFINANCE function doesn’t return any results is that you forgot to include quotation marks around your symbol. I once got a #NAME? error and realized I simply forgot to include the quotation marks. Adding the quotation marks solved the problem!

How do I convert pounds to dollars in Google Sheets?

You can use the GOOGLEFINANCE function for currency conversions in Google Sheets. You can get the current exchange rate between GBP and USD with the following formula:

=GOOGLEFINANCE("Currency:GBPUSD")

Then, multiply the two cells

Alternatively, you can directly convert the currency by combining the GOOGLEFINANCE formula with the MULTIPLY formula or the * sign.

In our example, you can use the formula:

=MULTIPLY(A4,GOOGLEFINANCE("Currency:GBPAUD"))

or

=GOOGLEFINANCE("Currency:GBPUSD")* A4

How do I change the currency format in Google Sheets from pounds to dollars?

You can also change the currency format you use in Google Sheets. Here’s how to change the currency format in Google Sheets:

1. Select the cells you want to change.
2. Click on Format > Number.

3. Click on Currency.

4. Click Apply.

This works for euros or any other global currency, as well as some cryptocurrencies.

How do I convert BTC to USD in Google Sheets?

BTC works the same way as any other currency conversion using GOOGLEFINANCE in Google Sheets. You just need to use BTC as the first currency argument. For example, if you want to get the current rate, you can use the following formula:

=GOOGLEFINANCE("Currency:BTCUSD")

Can I retrieve cryptocurrency prices in Google Sheets?

Yes, you can retrieve cryptocurrency prices in Google Sheets. Unfortunately, only major cryptocurrencies are supported. For example, to retrieve the current exchange rate from Ethereum to USD, you can use the following formula:

=GOOGLEFINANCE("Currency:ETHUSD")

Is there a delay when GOOGLEFINANCE retrieves values?

The delay for updating exchange rates in Google Sheets can be up to 20 minutes for current values.

Conclusion on Currency Conversion with Google Finance

In this tutorial, I showed you how to use the GOOGLEFINANCE function to retrieve real-time and historical exchange rates. I included screenshots with concrete examples.

I also showed you how to use the obtained rates for currency conversion in Google Sheets. Was this helpful to you? Please let me know if you have any other questions in the comments!

Other Google Sheets tutorials that might interest you:

• The REGEXMATCH function in Google Sheets
• How to use the IMPORTRANGE function in Google Sheets
• How to convert Excel to Google Sheets
• Converting formulas to values in Google Sheets
• How to automatically refresh Google Sheets (update every 1 minute)