Rather than finding currency conversion rates manually, you can import up-to-date and historical exchange rates using GOOGLEFINANCE in Google Sheets. Using these rates, you can convert any currency to another currency in your spreadsheet.
As its name suggests, the GOOGLEFINANCE function takes advantage of the precise financial data available from Google itself.
Conversioncurrency in real time using GOOGLEFINANCE
The GOOGLEFINANCE function is capable of extracting current and real time data on the financial markets. This includes exchange rates which, when inserted into your spreadsheet using GOOGLEFINANCE, will be updated every 20 minutes.
The format of a GOOGLEFINANCE formula for finding the current exchange rate is = GOOGLEFINANCE ( "Currency: USDGBP "), where you can replace USD and GBP with other three letter currency codes .
L The example above shows the USD / GBP rate then in effect. In the example above, the currency codes are used in the formula itself, but you can also refer to them separately.
To do this, enter your currency codes in two individual cells (for example, "USD " in cell A1 and "GBP in cell B1).
In a third cell , type = GOOGLEFINANCE ( "Currency: " & A1 & A2), replacing A1 and A2 with the appropriate cell references for your spreadsheet.
These rates above show the exchange rates listed in column A in column B. The GOOGLEFINANCE formula used in C2, for example, displays the rate of US dollars in pounds.
This value (0.7691) shows a US dollar converted to pounds. To convert a higher monetary value, you can multiply the higher value relative to this rate.
For example, to convert $ 100 to British pounds, you multiply this figure ($ 100) by the value of the exchange rate (0.7691), generated using the GOOGLEFINANCE function.
The above example shows three different USD digits in column B converted to GBP. The GOOGLEFINANCE function is used in cells A3 to A5, returning the USD to GBP rate then in effect.
By multiplying the USD figure in column B relative to the USD to GBP exchange rate in column A , the converted GBP amount is returned in column C.
RELATED: How to multiply numbers in Google Sheets
Finding historical currency conversion rates
The GOOGLEFINANCE function can also be used to provide historical exchange rates.will display the rate at the end of each day, for a period you have specified. It can be for a single day, a week, a month or more.
To do this, click on an empty cell and type = GOOGLEFINANCE ( "Currency: USDGBP ", "price ", DATE (YYYY, MM, DD), DATE (YYYY, MM, DD), where the first nested DATE function is the start date and the second DATE function is the end date.
Replace YYYY with the year, MM with the month and DD with day for the two nested DATE functions. You will also need to replace the currency codes to match the currencies you wish to exchange.
If you only want to display one date, you can use = GOOGLEFINANCE ( "Currency: USDGBP ", "price ", DATE (YYYY, MM, DD) ) instead.
Using other functions like TODAY 'HUI instead of the DATE function, you can also have a drop-down list.indicates that your list will be updated daily. You can view the last seven days, for example.
To do this, you can use = GOOGLEFINANCE ( "Currency: USDGBP ", "price ", TODAY () - 7, TODAY ()).
The TODAY function is used as the end date, this which means your list is always updated to show exchange rates (in this case, USD to GBP) for the past seven days.
If you wanted to do thisover a longer (or shorter) period, simply change the number used with the first nested TODAY function from seven to another number.