This sheet demonstrates how you can use, modify and create a file that downloads a variety of selected currencies. The file works retrieving data from the internet and then putting the relevant data at the top of the sheet. You can download the file you can quickly extract different currencies and copy the data into an excel file after clicking a couple of check boxes. The file uses the workbooks.open method as well as multiple check boxes.
Introduction
The file that you can download below uses current exchange rates from https://transferwise.com/us/currency-converter/usd-to-EUR-rate. It formerly used the website http://www.xe.com/currencyconverter/convert/?From=USD&To=CHF but this website no longer allows you to scrape anything.
In the completed file below, you can select different exchange rates and press a button (Read Currencies in List) to get current updates of the exchange rate. I have used the technique to use check boxes with TRUE and FALSE and Match as well as a macro that turns off all of the TRUE’s in all of the checkboxes.
If you download the file below, all you have to do is click on the currencies you want with the check boxes. Then click the macro box and all of your exchange rates are ready to be copied into any other file that you want.
Instructions for Using the Currency File
When you open the file, you may see something like what is in the screenshot below. This screen shot has some existing data from a prior run. I had begun clicking some currencies that I was interested in downloading. Note the final currencies are included in the grey box with the date that you have downloaded the data. This data in the grey box can be copied and pasted as values to your file.
The second screenshot shows how all the checkboxes are unclicked after pressing the button name “Set to False.” You can see that all of the boxes are blanks. In this case no currencies are selected.
The third screenshot shows how after selecting Sterling and Euro, you get a new summary. You just press the button named “Read Currencies in List”. Technical details that explain how to create the macro to scrape the data that are part of the macro assigned to this button are shown in the next section.
The fourth screenshot shows how you can copy the data into your spreadsheet after you have completed the download.
Technical VBA Details
In this section I have copied the VBA code that uses the workbooks.open technique to get the data. The file works like other files in that you separate and make new URL’s and use the INDEX function. Once you have the URL, the loop works through each currency, reads the currency from the internet. The row for 1 USD is found and the entire row is copied to the base file.
.
.
Public row_output_number As Single
Public currency_number As Single
Public base_sheet As String
Sub all_currencies()
Application.DisplayAlerts = False ' dont ask when close files
Application.ScreenUpdating = False ' dont show the screens
current_calc = Application.Calculation
Application.Calculation = xlCalculationSemiautomatic
For Sheet = Sheets.Count To 1 Step -1
If Sheets(Sheet).Name = "Break Sheet" Then
Exit For
End If
Worksheets(Sheet).Delete
Next Sheet
Application.Calculation = xlCalculationSemiautomatic
base_sheet = ActiveSheet.Name
date_output = DateValue(Now) ' if you want to show the date somewhere
Range("date_out") = date_output ' put the title date at to
Range("date_out").Select
Selection.NumberFormat = "d-mmm-yy"
Range("title_1") = "Currencies for "
row_output_number = 200
Range("output").Clear
Range("total_currencies").Calculate
MsgBox " This puts out beginning in row 200 " & Chr(13) & Chr(13) & " Reading " & Range("total_currencies") & " currencies"
For i = 1 To Range("total_currencies") ' from counta
Range("row_num") = i
row_output_number = row_output_number + 1
read_a_currency
Next i
Application.StatusBar = False ' put the url name at the bottom
Sheets(base_sheet).Select
Application.Calculation = current_calc
End Sub
Sub read_a_currency() ' Subroutine that reads each link separately
Dim test_value, test_criteria As String
close_file = Range("close_file") ' Read option from sheet
Application.DisplayAlerts = False ' dont ask when close files
current_book = ActiveWorkbook.Name ' record the existing location
current_sheet = ActiveSheet.Name
current_cell = ActiveCell.Address
Application.StatusBar = Range("url_name") ' put the url name at the bottom
url_name = Range("url_name") ' each url name in the range name
Workbooks.Open (url_name) ' read the file
temp_book_name = ActiveWorkbook.Name ' define the current sheet for closing later on
Cells.Select ' copy the sheet without ads etc
Selection.Copy
Sheets.Add ' add in the same sheet
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
test_value = "1 USD" ' What to Look For
test_criteria = "1 USD" ' What to Look For
evaluation = StrComp(test_value, "1 USD", vbTextCompare) ' evaluation is zero when equal
evaluation = StrComp(test_value, test_criteria, vbTextCompare) ' evaluation is zero when equal
For Row = 1 To 150 ' look for the exchange rate
test_value = Range("A:A").Cells(Row, 1) ' Look in Column A
test_value = Left(test_value, 5)
evaluation = StrComp(test_value, test_criteria, vbTextCompare)
If evaluation = 0 Then ' Zero is when found in the STRCOMP Function
match_truncated = Row ' Retain the Row Number
Exit For
End If
Next Row
row_number = Row ' find while in temp sheet
row_name = row_number & ":" & row_number ' row name for copying
Range(row_name).Select ' Select entire row
Selection.Copy ' Copy the entire row
If row_output_number = 0 Then row_output_number = 200
target_cell = "A" & row_output_number ' where to place the result
target_cell = row_output_number & ":" & row_output_number ' where to place the result
Workbooks(current_book).Activate ' return to base sheet and copy
Sheets(base_sheet).Activate
Range(target_cell).Select
'
' Paste the single row from the currency URL
'
Sheets(base_sheet).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
If close_file Then
Workbooks(temp_book_name).Close ' close the file from the URL
Else
Workbooks(temp_book_name).Activate
Cells.Select ' copy the sheet without ads etc
Selection.Copy
Workbooks(current_book).Activate ' return to base sheet and copy
Sheets.Add ' add in the same sheet
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
On Error Resume Next
ActiveSheet.Name = Range("currency")
Workbooks(temp_book_name).Close ' close the file from the URL
ActiveSheet.Move After:=Sheets(Sheets.Count)
End If
Range(current_cell).Select
End Sub
Sub ListBox2_Change()
base_cell = Selection.Address
For i = 1 To 10 ' decide how many possible items on the graph
Range("list_out1").Cells(i, 1).Clear ' clear out the range name that will be re-filled
Next i ' note this output is named list_out2; it can be renamed
ActiveSheet.Shapes.Range(Array("List Box 2")).Select ' change this name to the number of your box
Row = 1 ' Make a row counter to put output
For i = 1 To Selection.ListCount ' This is the number in the list box
If Selection.Selected(i) = True Then ' Only keep the selected items
Range("list_out1").Cells(Row, 1) = Selection.List(i)
Row = Row + 1 ' Assign to the range name
End If
Next i ' Finish loop around the listbox items
Range(base_cell).Select
Range(base_cell).Activate
End Sub
Video Explanation of the Macro and Using the File
The video below shows you how to create a macro that finds the correct place in the file that is downloaded from the internet with the workbooks.open statement and how to set-up the file to download multiple exchange rates.