Google Sheets is a great tool for tracking currency conversions. You can create a table with all of your currency conversions, or you can use the Currency Converter to quickly and easily convert between different currencies. To start, open Google Sheets and create a new table. Name the table “conversions”. In the first column, enter the value of the currency you want to convert to. For example, if you want to convert Euros to dollars, you would enter “EUR” in the first column and “Dollar” in the second column. Next, in the third column, enter how many units of that currency you want to convert. For example, if you want to convert Euros to dollars but only want them converted into British pounds, you would enter “GBP” in this column and “USD” in the fourth column. Finally, in the fifth column, enter how much money you will be converting each unit of that currency. For example, if you are converting Euros into dollars but only want them converted into cents (and not vice versa), you would enter “$0.00” in this column. Now that your conversion table is complete, simply click on its name to open it up in a new window or tab. You can now start tracking your conversions!
Rather than finding currency conversion rates manually, you can import up-to-date and historical currency exchange rates using the GOOGLEFINANCE function in Google Sheets. Using these rates, you can convert any currency to another in your spreadsheet.
As the name suggests, the GOOGLEFINANCE function takes advantage of accurate financial data available from Google itself.
Real-Time Currency Conversion Using GOOGLEFINANCE
The GOOGLEFINANCE function is capable of pulling in current, real-time data about the financial markets. That includes exchange rates, which, when inserted into your spreadsheet using GOOGLEFINANCE, will update every 20 minutes.
The format for a GOOGLEFINANCE formula to find the current exchange rate is =GOOGLEFINANCE(“Currency:USDGBP”), where you can replace USD and GBP with other suitable three-letter currency codes.
The example above shows the then-current USD to GBP rate. In the example above, the currency codes are used within the formula itself, but you can also refer to these separately.
To do that, type your currency codes in two individual cells (for instance, “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 to column B. The GOOGLEFINANCE formula used in C2, for instance, shows the rate from U.S. Dollars to British Pounds.
This value (0.7691) shows one U.S. Dollar converted to British Pounds. To convert a larger currency figure, you can multiply the larger value against this rate.
For instance, to convert $100 to British Pounds, you would multiply that figure ($100) against the exchange rate value (0.7691), generated using the GOOGLEFINANCE function.
The example above shows three different USD figures in column B converted to GBP. The GOOGLEFINANCE function is used in cells A3 to A5, returning the then-current USD to GBP rate.
By multiplying the USD figure in column B against 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. It will list the rate at the end of each day, for a period specified by you. This could be for a single day, a week, a month, or longer.
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 the day for both nested DATE functions. You’ll also need to replace the currency codes to match the currencies you’re looking to exchange.
If you only want to display a single date, you can use =GOOGLEFINANCE(“Currency:USDGBP”, “price”, DATE(YYYY,MM,DD)) instead.
Using other functions like TODAY in place of the DATE function, you can also have a rolling list. This means your list will update every day. You can display the last seven days, for instance.
To do that, you can use =GOOGLEFINANCE(“Currency:USDGBP”, “price”, TODAY()-7, TODAY()).
The TODAY function is used as the end date, meaning your list always updates to show currency exchange rates (in this instance, from USD to GBP) for the past seven days.
If you wanted to do this over a longer (or shorter) period, simply change the number used with the first nested TODAY function from seven to another number.
