• 131
• 255
• 14

Google Sheet get currency exchange rate with GOOGLEFINANCE formula

by bernt & torsten

Did you know that Google Sheet has a formula that allows you do get the latest currency rates and calculate currency conversion? For people in finance or for anyone that travels for work and needs to submit a travel expenses sheet. You can now use the sheet formula GOOGLEFINANCE  to get the exchange rate, you can also go back in time to see what the exchange rate was on a specific day. Here we go a few ways you can use the GOOGLEFINANCE sheet formula.

Get the currency conversion rate

In the Google Sheet, you would need to select a cell where you want the result of the currency conversion to show up, in that cell type this formula

=B1*GOOGLEFINANCE("CURRENCY:CADEUR")

In the example in the image below, in B1 enter the value you want to convert, the conversion is done between CAD to EUR. You can put your base currency in C2 – when done press the Enter key and the result will show up.

Get the currency rate between two currencies

In the Google Sheet, you would need to select a cell where you want the result of the calculation to show up. to calculate the currency rate between two currencies type this formula

=GOOGLEFINANCE("CURRENCY:"&$B$1&C1) 

(in the formula, $B$1&C1 reads the cell value that needs to be a currency code. When you entered the currency code press Enter key and the result will show.

Show the currency exchange rate between two currencies

In the Google Sheet, you would need to select a cell where you want the result of the calculation to show up. In the image example below enter the start date for the 7 days you want to show the currency exchange rate, enter this formula

=GOOGLEFINANCE("CURRENCY:CADEUR", "price", A3, A3+7, "DAILY")

in the formula, A3 is the first date of the week you need to enter a data in A3, A3+7 means to list the currency rates of the next 7 days, CADEUR means to convert CAD to EUR. When you are ready with the formula, press the Enter key and the result will show..

Conclusion

You can become very creative in Google Sheets, and build a sheet that you can use in your job function, to simplifies your work. Google Sheets is a great spreadsheet tool that integrates nicely with the Google Cloud Platform. The best thing is that you can collaborate in real-time on a Google Sheet, the days are gone when you send a spreadsheet file to colleagues to collaborate.