Get Foreign Exchange Rate in Excel (VBA and non-VBA)

This Excel tutorial explains how to get foreign exchange rate in Excel using VBA and non-VBA.

You may also want to read:

Get exchange rate using VBA Access Function

Excel currency converter template

Foreign Exchange Rate Websites

Excel allows you to create an external connection to website where you can get the data directly in the spreadsheet.

To begin with, look for a dynamic website that contains foreign exchange rate where the database constantly refreshes.

One example isĀ

Get Foreign Exchange Rate in Excel 01

Some people like using Yahoo Finance, but I personally believe Yahoo may change their interface more often and it may destroy the Excel connection. Anyway below is a link.

Get Foreign Exchange Rate in Excel 02

Get Foreign Exchange Rate in Excel (non-VBA)

Navigate to DATA tab > From Web

Get Foreign Exchange Rate in Excel 03


Enter the website , and press Go

Get Foreign Exchange Rate in Excel 04


The yellow arrows you see are the tables you can import into Excel. Click on the on the yellow arrow (it will turn green), then press Import button.

Get Foreign Exchange Rate in Excel 05


Select a Cell to import, and then click on Properties button

Get Foreign Exchange Rate in Excel 06


In this Properties box, you can set how long the Excel table will refresh and get the latest data from website.

Get Foreign Exchange Rate in Excel 07


Now the exchange rate table is inserted and constantly updates

Get Foreign Exchange Rate in Excel 08


Get Foreign Exchange Rate in Excel (VBA)

Yahoo Finance is designed in the way to facilitate you to get data. This time we establish a connection to Yahoo and directly get the exchange rate using User Defined Function.

Press ALT+F11 > Tools > References > enable “Microsoft WinHTTP Services”

Get Foreign Exchange Rate in Excel 09


Insert a Module and then insert the below code. The required code is simpler than you ever think.

Public Function wConvertCurrency(currency1, currency2)
 Dim yahooHTTP As New WinHttp.WinHttpRequest
 yahooHTTP.Open "GET", "" & currency1 & currency2 & "=X&f=l1"
 wConvertCurrency = CDbl(yahooHTTP.ResponseText)
End Function

Example – get Foreign Exchange Rate using custom Function

Before you start, check yahoo finance what the currency codes they use for exchange rate, such as EUR, AUD, GBP, USD.

In the below worksheet, column A and B are the currencies you need to convert. Column C is the formula of the result of column D.

Get Foreign Exchange Rate in Excel 10

This VBA Function has a limitation that you cannot refresh the result at specific time period. Instead, you can consider using Application.OnTime, Application.Volatile or worksheet change event to refresh the formula indirectly.


Wyman W
Compensation Survey Manager@Mercer
Wyman is human resources professional, specialized in business analysis, project management, data transformation with Access and Excel.

He is also a:
- Microsoft Most Valuable Professional (Excel)
- Microsoft Community Contributor
- Microsoft Office Specialist in Access / Excel
- Microsoft Specialist in MS Project
- Microsoft Technical Associate
- Microsoft Certified Professional
- IBM SPSS Specialist

You may drop me a message if you are interested in HR consultancy services from Mercer.

Leave a Reply

Your email address will not be published. Required fields are marked *