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:
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 = 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:
'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.
See Also:
- Excel VBA Drop Down Lists.
- Retrieving Data From a Website Using a Query Table
- VBA Excel String Processing and Manipulation
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