#Excel Exchange Rate UDF With Symbol Lookup by David Hager

A number of Excel UDFs used to be available which utilized the Yahoo Finance API. Then, Yahoo changed its protocol for that financial data source, which caused those procedures to stop working. However, Google still has its financial converters exposed. In particular, we are interested in obtaining the current exchange rate from one currency to another.

The main procedure for returning exchange rates from Google Finance came from this site.

http://www.codepal.co.uk/show/MS_Excel_Live_Currency_Converter

To use the UDF, under Tools, References in the VBE, scroll down the list and check:

Microsoft WinHttp Services, version 5.1

A table of currency codes and symbols was obtained from this web site.

http://investexcel.net/foreign-exchange-rate-function-in-excel/

The 2^nd argument of the UDF (DestCur) is used to lookup the currency symbol associated with the desired currency. The following lines of code illustrate how this is done. In the main module, the following variable is declared.

Global SymbolToLookup As String

This has to be a global variable since it is going to be used in an event procedure in a worksheet module.

The lookup table is on the CurrencySymbols worksheet. This code returns currency symbol to be applied.

SymbolToLookup = Application.WorksheetFunction.Index([CurrencySymbols!C2:C110], Application.WorksheetFunction.Match(DestCur, [CurrencySymbols!B2:B110], 0))

Note the syntax of the two ranges. This is a shorthand method of passing ranges to worksheet functions used in VBA.

Then, the SymbolToLookup variable is passed to the event procedure in the Convert worksheet module, which fires after the UDF is entered in a cell.

Private Sub Worksheet_Change(ByVal Target As Range)

Target.NumberFormat = “General” & SymbolToLookup

End Sub

The following figure shows the result using the UDF plus the event procedure.

The file can be downloaded here.

ExRate