This tutorial demonstrates how to create and automatic currency converter in Excel and Google Sheets.
Excel is a powerful tool for calculations, often involving international currencies. Excel does not have a built-in currency conversion tool, but with a link to an external XML database, you can build your own conversion tool into Excel using Power Query.
Link to External XML Source File
The most user-friendly source file is one called Floatrates, a free XML feed that is updated daily.
- In a blank Excel worksheet, in the Ribbon, go to Data > Get Data > From File > From XML.
- In the File name field, type in the URL for the Floatrates file – www.floatrates.com/daily.usd.xml – and then click Open.
- Click Transform Data.
- This opens the Power Query Editor. Scroll across to the last item on the list, and then click the word Table. A table below the initial line of data is populated with the exchange rates.
- Click on the double-headed arrow to show the fields of this table, and then click OK.
- The Power Query changes to show the exchange rate columns as well as the initial columns.
- In the Ribbon, go to Home > Manage Columns > Choose Columns.
- Uncheck the columns you don’t need, and then click OK.
- Click Close & Load to return to Excel.
Create Currency Converter
- Create a new worksheet in the same workbook as the linked XML database and set up a table as shown below.
- Use the VLOOKUP Function to look up the currency rate for the British Pound against the US Dollar. In the relevant cell (for example C5), type in:
- Then highlight the relevant lookup data by going to the data sheet and selecting the information you need.
- Complete the VLOOKUP formula by typing in the column name and the word FALSE as the final argument for the formula.
- Press ENTER to enter the formula into Excel.
- Repeat the procedure to look up EUR from the data table.
- To get the value of 2 USD to 1 GBP or 1 EUR, copy the formula to the next row, and then multiply it by the value in Column B.
- Copy the formula down to populate the exchange rate converter.
- To create an exchange rate converter for the inverse rates, create another lookup – for the inverse rates in the data.
Automatic Currency Conversion in Google Sheets
Google Sheets has a built-in currency converter that makes use of existing up-to-date conversion rates on the internet.
- Set up a table in Google Sheets similar to the one set up in Excel above.
- In the selected cell (for example, C3), type in the formula (with the GOOGLEFINANCE Function):
- Google automatically looks up the exchange rate from USD (in B2) to GBP (in C2) and fills in the selected cell.
- Since cells B2 and C2 are locked (with $), you can copy this formula down to the next cell and then multiply the formula by the value in Column B.
- Copy the formula in this cell down to the remaining rows in the conversion table.
- Repeat the process to look up the EUR.
Other tutorials that use Power Query