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  http://www.x-rates.com/table/

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.

http://finance.yahoo.com/currency-investing/majors

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 http://www.x-rates.com/table/ , 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", "http://quote.yahoo.com/d/quotes.csv?s=" & currency1 & currency2 & "=X&f=l1"
    yahooHTTP.send
    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.

http://finance.yahoo.com/currency-converter/

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
Wyman is a Business Analyst based in Hong Kong, specialized in business analysis, project management, and also creating custom Function and Sub solutions, and is proficient in report automation with Access.

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

Leave a Reply

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