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:
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:
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.
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:
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 = FalseThen '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:
'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:
'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.