Excel VBA, Currency Exchange (Forex) Sheet, (Sample Code and Download)

In this article explain how you can write an excel application to retrieve currency exchange (forex) rates from the website http://finance.yahoo.com/. This application also allows users to select only the currency exchange (forex) rates he/she needs. You can download the codes and files related to this article here.

Below you can see the programs main interface. Currency 1 and Currency 2 are the currencies we are after:

This is the main interface of the program. By pressing Add you will get the amount of unit 2 currency which is equal to one unit of currency two

Each currency is chosen from a drop down list. The final number the application will show, is the amount of currency 2 which is equal to one unit of currency 1:Each currency is chosen from a drop down list

The source of the drop down lists are in sheet 2. In column A you can see the currency codes, and in column B the name of the country the currency is associated with. Column B is only for display purposes and is not used in the application. The drop down lists were created using data validation. For more information about creating drop down lists in Excel please visit Excel VBA Drop Down Lists.

Currency codes, drop down list source

After selecting the currency code from the drop down list, a worksheet_change event will be triggered. The event handler will modify  updated cell C2 based on the values chosen from the drop down lists:

Cell C2 is updated in the event handler of the worksheet_change event

Option Explicit
'this is a global variable. Determines if the current change in sheet1
'is because of user action or because of program action.
Dim flagProgram As Boolean
'this event is triggered when a cell in sheet1 is modified
Private Sub Worksheet_Change(ByVal Target As Range)
    'value in cell A2
    Dim str1 As String
    'value in cell B2
    Dim str2 As String
    'if the change is because of user action
    If flagProgram = False Then
        'all future changes will be due to program changes
        flagProgram = True
        'if the cells in question have been modified
        If (Target.Address = Range("A2").Address) Or _
        (Target.Address = Range("B2").Address) Then
         'if neither cell is empty
           If (Cells(2, 1) <> "") And (Cells(2, 2) <> "") Then
               'add the strings and print result in C2
               Cells(2, 3) = Cells(2, 1) + Cells(2, 2)
           End If
        End If
        'future changes are due to user action
        flagProgram = False
    End If
End Sub

By pressing the add  button the current value in cell C2 is printed to the list of desired currency exchange (Forex) rates:

By pressing add the current value in C2 is added to the desired forex rates

'adds the current value in C2 to the list of desire forex rates
Private Sub btnAdd_Click()
'the number of total values currently in the list
Dim intCurrentCount As Integer
intCurrentCount = getCount
Cells(intCurrentCount + 4, 1) = Cells(2, 3)
End Sub

'this function gets the total rows in column A of sheet1
'starting from row 4
Private Function getCount() As Integer
'a counter
Dim i As Integer
'determines if the current row is empty or not
Dim flag As Boolean
i = 1
flag = True
While flag = True
    'checks to see if the current row is empty
    If Sheet1.Cells(i + 3, 1) <> "" Then
        'if its no empty go on
        i = i + 1
    Else
        'if its empty stop
        flag = False
    End If
Wend
getCount = i - 1
End Function

Note that in this example there is 3 desired currency exchange (Forex) rates. AUD/USD (1 Australian Dollar is equal to how many US Dollars), CADUSD (1 Canadian Dollar is equal to how many US Dollars) and GBP/USD (1 Great Britain Pound is equal to how many US Dollars). By pressing the update button data queries will be created in sheet 3 form the website to get the exchange rates. For more information about using data queries to get data from websites please visit Excel VBA, Retrieving Data From a Website Using a Query Table. Depending on your internet speed it might take a while for the query to complete:

Data Query Results

Currency Exchange (Forex) Rates

'retrieves the exchage rates from the webiste
'http://finance.yahoo.com
Private Sub btnUpdate_Click()
'the number of desired exchange rates to retrieve
Dim intCurrentCount As Integer
'counter
Dim i As Integer
Dim strConnection As String
Dim rngDesination As Range
Dim strName As String

intCurrentCount = getCount
'makes sheet3 the active sheet so you can see
'the queries being created
Sheet3.Activate
'erases all previous data from sheet3
Sheet3.Cells.Clear
'iterates the currency codes in sheet1
For i = 1 To intCurrentCount
    'this is all generated by the macor recorder
    strConnection = "URL;http://finance.yahoo.com/q?s=" + _
        Sheet1.Cells(i + 3, 1) + "=x"
    Set rngDesination = Sheet3.Range(Sheet3.Cells(1, i * 2), _
        Sheet3.Cells(1, i * 2))
    strName = "q?s=" + Cells(i + 3, 1) + "=x_1?"
    With Sheet3.QueryTables.Add(Connection:= _
    strConnection, Destination _
        :=rngDesination)
       
    .Name = strName
    .FieldNames = True
    .RowNumbers = False
    .FillAdjacentFormulas = False
    .PreserveFormatting = True
    .RefreshOnFileOpen = False
    .BackgroundQuery = True
    .RefreshStyle = xlInsertDeleteCells
    .SavePassword = False
    .SaveData = True
    .AdjustColumnWidth = True
    .RefreshPeriod = 0
    .WebSelectionType = xlEntirePage
    .WebFormatting = xlWebFormattingNone
    .WebPreFormattedTextToColumns = True
    .WebConsecutiveDelimitersAsOne = True
    .WebSingleBlockTextImport = False
    .WebDisableDateRecognition = False
    .WebDisableRedirections = False
    .Refresh BackgroundQuery:=False
End With
'update sheet1 with the forex rate
Sheet1.Cells(i + 3, 2) = Sheet3.Cells(130, i * 2 + 1)
Next i
Sheet1.Activate
End Sub

The query table code above was generated by the macro recorder. The only parts i modified are the highlighted parts. The first highlighted part is the address of the website, which is in the form http://finance.yahoo.com/q?s=CADUSD=x for the CAD to USD to exchange rate, http://finance.yahoo.com/q?s=AUDUSD=x for the AUDUSD exchange rate … Using string manipulation you can modify this url to return data from the website you are looking for. For more information regarding string manipulation please visit VBA Excel String Processing and Manipulation.

See Also:

You can download the codes and files related to this article from the link below:

If you need assistance with your code, or you are looking to hire a VBA programmer feel free to contact me. Also please visit my website  www.software-solutions-online.com

Leave a Reply

Your email address will not be published. Required fields are marked *