#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 2nd 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))

exRate1

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.

exRate2

The file can be downloaded here.

ExRate

Advertisements

One thought on “#Excel Exchange Rate UDF With Symbol Lookup by David Hager

  1. Pingback: # Excel Super Links #62 – shared by David Hager | Excel For You

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s