Get exchange rate using VBA Access Function

This tutorial demonstrates how to create a VBA custom function (UDF) in Microsoft Access in order to get the real time exchange rate.

You may also want to read

Get Foreign Exchange Rate in Excel

Get exchange rate using VBA Access Function

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

Open Access, press ALT+F11 > Tools > References > enable “Microsoft WinHTTP Services”

Insert a Module and then insert the below code. This Function contains two arguments, the first argument is the target currency, the second argument is the original currency.

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 exchange rate using VBA Access 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/

Suppose you have a staff list that contains employee salary, but the salary is in different currency. You want to standardize them in USD.

Employee Name	Monthly Salary	Currency
Amy	10000	USD
Cat	10000	CNY
Dorothy	10000	EUR
Susan	10000	JPY

Now create a Query and add an Expression called Salary in USD

Salary in USD: wConvertCurrency("USD",[Currency])*[Monthly Salary]

Run the Query and you get the below result.