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 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.
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|
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.