Currency Converter

This is a desktop app developed with Visual Basic for Applications. The purpose of the project was to create a currency converter that works with real-time data fetched from the following website (currency API): https://www.xe.com/currencytables/?from=USD&date=2021-07-14

The app lets the user track the currency exchange behaviour and compare the converted values with a log of estimations and a graphical analysis. The user form is executed in runtime in Excel when the user clicks an action button in the main worksheet.

Layout

When the user form is loaded the conversion currencies are updated automatically and the current date is set as default:

When all input values are added the user must click on 'Update Currencies' to get the conversion rates according to the selected date and the currencies chosen in the combo boxes.

Update Currencies

Based on the input date, a visual basic method is executed to fetch the currency conversion rates directly from the web service. The following syntax creates the query connection using the necessary parameters to pull the dataset:

WebScrapingCurrencyConverter-Vb/On Click Actions/UpdateCurrencyRates.bas

Lines 30 to 72 in 0e43f18

	url = "URL;https://www.xe.com/currencytables/?from=USD&date=" & Format(converterForm.dateText.Text, "yyyy-mm-dd")
	'Ideally this URL should be parameterized and administered in a spreadsheet cell
	'Create the connection to start data scraping from the website
	With Worksheets("Sheet1").QueryTables.Add(Connection:=url, Destination:=Worksheets("Sheet1").Range("A1"))
	'Configure the query parameters to save the extracted data
	.Name = "My Query"
	.RowNumbers = False
	.FillAdjacentFormulas = False
	'Use native data format
	.PreserveFormatting = True
	.RefreshOnFileOpen = False
	.BackgroundQuery = False
	.RefreshStyle = xlOverwriteCells
	.SavePassword = False
	.SaveData = True
	.AdjustColumnWidth = True
	.RefreshPeriod = 0
	'Fetch the entire site metadata to avoid missing data
	.WebSelectionType = xlEntirePage
	.WebFormatting = xlWebFormattingNone
	'Store data in column format for easier manipulation
	.WebPreFormattedTextToColumns = True
	'Avoid large data groups separated by columns
	.WebConsecutiveDelimitersAsOne = True
	.WebSingleBlockTextImport = False
	'Treat dates as strings
	.WebDisableDateRecognition = False
	.WebDisableRedirections = False
	'Avoid query automatic updates
	.Refresh BackgroundQuery:=False
	End With

In order to handle any connection runtime error, a try-catch block triggers display messages if needed with an 'On Eror GoTo' form command. However, some dates may not be available if the website provider has internal constraints or fails to update the conversion metadata. In this case, the conversion button will not work and a error pop-up window will alert the user.

i.e

Convert Currency

The conversion output will be displayed in the 'Converted Amount' field when the user clicks the button. If the conversion rates haven't been updated ('Refresh Currencies' is not clicked) all currencies will be automatically updated forcing the previous method to be executed. The following syntax illustrates how the conversion is estimated using the fetched data:

WebScrapingCurrencyConverter-Vb/On Click Actions/ConvertCurrency.bas

Lines 87 to 114 in daa3b1f

	'Create index that will iterate over the web service conversion rates (data extracted)
	'"CONVERT FROM CURRENCY" name
	indexToConv = converterForm.ComboBox1.ListIndex
	'"CONVER TO CURRENCY" name
	indexConv = converterForm.ComboBox2.ListIndex
	'If the conversion rate is not available then display a user alert
	If ActiveCell.Offset(indexToConv, 2).Value = 0 Or ActiveCell.Offset(indexConv, 2).Value = 0 Then
	MsgBox "ONE OF THE CURRENCY RATES IS NOT AVAILABLE FOR THAT DATE, TRY A DIFFERENT YEAR "
	Sheets("Sheet1").Visible = False
	Exit Sub
	End If
	'Estimate the currency conversion and save the OUTPUT value that will be displayed in the form field
	'Converted Value = Amount * ( Conversion currency rate ) / ( Initial currency rate)
	converterForm.convertedOutput = converterForm.TextBox1 * (ActiveCell.Offset(indexConv, 2).Value / ActiveCell.Offset(indexToConv, 2).Value)
	Sheets("Sheet1").Visible = False

This result will be rendered in the user form as follows:

View Conversions Log

Every conversion request is saved in an internal log of changes and It is possible to view the selected currency behaviour of the last 30 days (previous to the input date) in a data plot. This will be automatically displayed in a spreadsheet when the user clicks 'Plot Last 30 Days' button.

i.e Using sample data for USD - GBP conversion behaviour over time:

In order to dynamically populate this plot with different conversions, a query connection is created recursively per daily rate. The following syntax partially illustrates a visual basic method that extracts the daily rates and iterates over the last 30 days estimating the conversions:

WebScrapingCurrencyConverter-Vb/On Click Actions/PlotCurrencyRateBehavior.bas

Lines 25 to 90 in 6bf2733

	'Iterate over the last 30 days (before selected date)
	For i = 30 To 1 Step -1
	'Save date per iteration and create X axis
	Range("A" & 30 - i + 1) = DateAdd("d", -i + 1, converterForm.dateText.Text)
	Next i
	'Iterate over the last 30 days (before selected date)
	For i = 1 To 30
	Sheets("Sheet1").Visible = True
	Sheets("Sheet1").Cells.Clear
	'Create query connection recursively using the ith index which represents each day to plot
	url = "URL;https://www.xe.com/currencytables/?from=USD&date=" & Format(Worksheets("Sheet3").Range("A" & i).Text, "yyyy-mm-dd")
	With Worksheets("Sheet1").QueryTables.Add(Connection:=url, Destination:=Worksheets("Sheet1").Range("A1"))
	'Set query parameters format that scraps daily conversion rates and populates Y axis
	.Name = "My Query"
	.RowNumbers = False
	.FillAdjacentFormulas = False
	.PreserveFormatting = True
	.RefreshOnFileOpen = False
	.BackgroundQuery = False
	.RefreshStyle = xlOverwriteCells
	.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
	'Fetch the data set that corresponds to the daily conversion rate (i index iteration), this will be used as the Y axis
	For j = 1 To 1000
	If Worksheets("Sheet1").Range("A1:A" & 1000).Cells(j, 1).Text = "USD" Then
	Sheets("Sheet1").Select
	Range("A" & j).Select
	Exit For
	End If
	Next j
	'Calculate converted amount and populate Y axis in the plot
	'Use combo list index logic for currency conversion
	indexToConv = converterForm.ComboBox1.ListIndex
	indexConv = converterForm.ComboBox2.ListIndex
	'Plot each conversion record
	Worksheets("Sheet3").Range("B" & i) = converterForm.TextBox1 * (ActiveCell.Offset(indexConv, 2).Value / ActiveCell.Offset(indexToConv, 2).Value)
	Next i

In order to handle any connection runtime error, a try-catch block launches display messages if needed with an 'On Eror GoTo' form command. However, some dates may not be available if the website provider has internal constraints or fails to update the conversion metadata. In this case, the conversion button will not work and a error pop-up window will alert the user.

.

Usage

Import the .bas files to a VB/VBA Excel developer editor (module1 and RibbonX customization are optional) and add a macro button to execute the user form. Add three worksheets without display name which will be automatically updated when the data is pulled from the XE website. After adding the .bas files to the project, import the .frm file and configure the display attributes in the form editor. The following parameters specify the display default settings:

WebScrapingCurrencyConverter-Vb/ConverterUserForm.frm

Lines 1 to 17 in bd35980

	VERSION 5.00
	Begin {C62A69F0-16DC-11CE-9E98-00AA00574A4F} converterForm
	Caption = "Currency Converter v1.0"
	ClientHeight = 6264
	ClientLeft = 108
	ClientTop = 456
	ClientWidth = 10560
	OleObjectBlob = "ConverterUserForm.frx":0000
	StartUpPosition = 1 'CenterOwner
	WhatsThisButton = -1 'True
	WhatsThisHelp = -1 'True
	End
	Attribute VB_Name = "converterForm"
	Attribute VB_GlobalNameSpace = False
	Attribute VB_Creatable = False
	Attribute VB_PredeclaredId = True
	Attribute VB_Exposed = False