In this article, we cover how to convert currency in Google Sheets. The method which we are about to cover would do away with the need to manually enter the converted currency rates. The function that can help us get our work done is GOOGLEFINANCE.
Not only just Currency conversion, with the help of GOOGLEFINANCE one can get information about various other securities as well. For the purpose of the article, we limit our discussion to just Currency conversion here.
The functions help us with the required financial data, in this case, currency related, from Google Finance.
Currency Conversion in Google Sheets
We can utilize GOOGLEFINANCE function in multiple ways. We cover each of those ways next.
I. The format to convert one currency to the next is:
=GOOGLEFINANCE("Currency:Currency1Currency2")
For instance, if we wish to convert USD to EUR then enter the following in one of the cells,
=GOOGLEFINANCE("Currency:USDEUR")
II. GOOGLEFINANCE not only can fetch current currency data but also historical as well. So, to get currency data for a specific date:
=GOOGLEFINANCE("Currency:Currency1Currency2", "price", DATE(YYYY,MM,DD))
For instance, to get the data for Date: 2023/01/09
=GOOGLEFINANCE("Currency:USDEUR", "price", DATE(2023,1,9), DATE(2023,2,9))
III. To get historical data for a specific date range:
=GOOGLEFINANCE("Currency:Currency1Currency2", "price", DATE(YYYY,MM,DD), DATE(YYYY,MM,DD))
For instance, to get currency data between January 09, 2023 and February 08, 2023:
=GOOGLEFINANCE("Currency:USDEUR", "price", DATE(2023,1,9), DATE(2023,2,9))
IV. Up till now, we have manually entered Currencies. But, we can also fetch them from the CELL entries directly. For instance, if cell A2 contains USD and cell B2 contains EUR then,
=GOOGLEFINANCE("Currency:"&A2&B2)
V. We have used the DATE() function above. If we want to display currency data dynamically then we can use the TODAY() function. This allows us to get currency data for the last n days. Let’s understand it with the help of an example:
=GOOGLEFINANCE("Currency:USDEUR", "price", TODAY()-9, TODAY())
For us, it displayed data from March 25, 2023 to April 02, 2023. One of the benefits of using the TODAY() function is that we don’t have to edit dates for the last 9 days. TODAY() function takes care of that on its own.
In conclusion, we have covered Currency Conversion in Google Sheets here.
