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 need 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 were 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, in C1 enter the currency you want to convert from, EUR is the currency you want to convert to). When done press 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 this formula =GOOGLEFINANCE(“CURRENCY:CADEUR”, “price”, A3, A3+7, “DAILY”) in the formula, A3 is the first date of the week, A3+7 means to list the currency rates of next 7 days, CADEUR means to convert CAD to EUR. When you are ready with the formula, press Enter key and the result will show..
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.