How to use the Google Finance Currency Function
Updated: Aug 5, 2023
Have you ever Googled a currency exchange rate or asked Google to convert one currency value to another?
If you search for something like “exchange rate for euros to pounds”, you’ll get a result something like this:
Did you know that Google has a function built into Google Sheets called the GOOGLEFINANCE function that returns current and historical financial data?
You can use this Google Finance function within a formula to provide real-time stock market prices, international exchange rates and currency conversions, to name a few.
If you're looking to save some time, I have a pre-built Currency Converter Google Sheets Template here.
Let’s take a look at how this works in a spreadsheet. To start, just type and equals sign = followed by the function name GOOGLEFINANCE and open a parenthesis to begin.
Google Finance uses the three letter currency codes arranged in this fashion to convert the former to the latter.
“Currency:EURGBP”
You can see in the example below, to convert EUR to GBP, the complete formula should be entered as follows:
=GOOGLEFINANCE("Currency:EURGBP")
Making this more dynamic is possible by using the “&” to concatenate the cells to the left.
Below I’ve highlighted just part of the formula to show you what this looks like, and the keen-eyed amongst you will have noticed that the result was the same as before when we did the first example without the dynamic part.
"Currency:EURGBP"
The complete formula should be entered as follows:
=GOOGLEFINANCE("Currency:"&A2&B2)
You could change the three-letter currency code in columns A and B to switch different currencies as required.
Then it’s a simple case of multiplying the ‘from’ value by the rate to get the ‘to’ value. So in the example below, our EUR value is €15; therefore, we just multiple 15.00 by the rate we got earlier for EUR to GBP to get £12.69.
I always use this in my work as it’s a real-time-saver once everything is set up. Remember that this function updates automatically every 20 minutes or so or every time you refresh or reopen the sheet. If you don’t want this to keep updating, you’ll have to retrieve the result, copy the rate, and paste it as values only (Cmd+Shift+V on a Mac or Ctrl+Shift+V on a PC).
Give it a try; it’s a handy function to know.
Σχόλια