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/
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 (non-VBA)
Navigate to DATA tab > From Web
Enter the website http://www.x-rates.com/table/ , and press Go
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.
Select a Cell to import, and then click on Properties button
In this Properties box, you can set how long the Excel table will refresh and get the latest data from website.
Now the exchange rate table is inserted and constantly updates
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”
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://download.finance.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.
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.
