Excel import exchange rates + nice formulas

0 Shares

Because Internet Explorer automation and data scraping with Excel is a topic that I like very much, I played with a Workbook in order to import Exchange Rates from a website considering the parameters input in the excel file (currency, start date and end date). For more fun, we also need to create a dynamic chart which will show the evolution in the given time interval of the selected Exchange rates. At the end of the post you can find the file for free download.

Steps to complete this task:

A) We want to take the values from this link: Exchange Rates Website, so we can start by inspecting the elements from the page using the “Inspect Element” browser feature:

We find out this information:

B) Then, we create the input parameters in Worksheet “Exchange_Rates”, cells “C3:C5”:

C) The next step is to write the macro which will import the data for the selected input values into columns “D:E”, when we press the “Import Data” button. This is the most interesting part and after some research the macro that we wrote is the following:

Sub take_data()

Dim i As Long
Dim IE As Object
Dim objElement As Object
Dim objCollection As Object
Dim lr1 As Long
Dim v() As String

'array with string months- for converting to numbers
v = Split("Ian,Feb,Mar,Apr,Mai,Iun,Iul,Aug,Sep,Oct,Noi,Dec", ",")

lr1 = 0
webPAGE = "http://www.cursbnr.ro/curs-valutar-bnr"
tableID = "tabel_curs_valutar_bnr"

'Create InternetExplorer Object
Set IE = CreateObject("InternetExplorer.Application")

'IE.Visible = True

IE.Navigate webPAGE

 ' Wait while IE loading...
    Do While IE.Busy
        Application.Wait DateAdd("s", 1, Now)
    Loop
    
'start date and end date
Set objCollection = IE.Document.getElementsByTagName("input")

ds = ActiveWorkbook.Worksheets("Exchange_Rates").Range("C4").Value
de = ActiveWorkbook.Worksheets("Exchange_Rates").Range("C5").Value

i = 0
    While i < objCollection.Length
        If objCollection(i).Name = "dataStart" Then
 
            ' Set text for search
            objCollection(i).Value = Format(DateSerial(Year(ds), Month(ds), Day(ds)), "dd/mm/yyyy")
 
        ElseIf objCollection(i).Name = "data" Then
 
            'Set text for search
            objCollection(i).Value = Format(DateSerial(Year(de), Month(de), Day(de)), "dd/mm/yyyy")
        
        Else
            If objCollection(i).Type = "submit" And _
               objCollection(i).Name = "butsub" Then
 
                ' "Search" button is found
                Set objElement = objCollection(i)
 
            End If
        End If
        i = i + 1
    Wend
  
'currency
Set objCollection = IE.Document.getElementsByTagName("select")

valuta = ActiveWorkbook.Worksheets("Exchange_Rates").Range("C3").Value

i = 0
    While i < objCollection.Length
        If objCollection(i).Name = "currency" Then
 
            ' Set text for search
            objCollection(i).Value = valuta
        End If
        
    i = i + 1
    Wend
    
    objElement.Click    ' click button to search
    
' Wait while IE loading...
    Do While IE.Busy
        Application.Wait DateAdd("s", 1, Now)
    Loop
    
'delete old data

Set sh = ActiveWorkbook.Worksheets("Exchange_Rates")

If sh.Range("D7") = "" And sh.Range("E7") = "" Then
lr1 = 7
Else
lr1 = sh.Cells(sh.Rows.Count, "D").End(xlUp).Row + 1
End If

sh.Range("D7:E" & lr1).ClearContents

a = 8


Application.ScreenUpdating = False
'copy data from table
9
            On Error Resume Next
            Set TRs = Nothing
            Set TRs = IE.Document.getElementbyID(tableID).getElementsByTagName("tbody")(0).getElementsByTagName("tr")
            On Error GoTo 0
            If TRs Is Nothing Then Application.Wait Now + 0.00001: GoTo 9
            
            If TRs.Length > 0 Then
                For Each TR In TRs
                    Set TDs = TR.getElementsByTagName("td")
                    'Debug.Print TDs(0).Innertext
                    
                    Data1 = TDs(0).Innertext
                    
                    Curs = TDs(1).Innertext
                    
                If IsNumeric(Curs) Then
                
                    Curs = Curs * 1             'exchange rate
                
                anul = Right(Trim(Data1), 4)    'year
                'create month and day
                For b = 1 To Len(Data1)
                    
                    If Not IsNumeric(Mid(Data1, b, 1)) Then
                         start_luna = b + 1
                         nr_zi = b - 1
                        Exit For
                    End If
                Next b
            
                luna = Application.Match(Mid(Data1, start_luna, 3), v, False)      'month
                ziua = Left(Data1, nr_zi)                                          'day
                
                   Else: GoTo nexti
                
                End If
                    
                    sh.Range("D" & a) = DateSerial(anul, luna, ziua)
                    sh.Range("E" & a) = Curs
                    
                    a = a + 1
                    
                    'MsgBox Data1 & "   " & Curs
nexti:
                Next
                
            
            End If
        
        sh.Range("D7") = "Date"
        sh.Range("E7") = "Exchange Rate: " & valuta & " converted to RON"
            

Application.ScreenUpdating = True
    
    Set TR = Nothing
    Set TDs = Nothing
    Set TRs = Nothing
    lr1 = 0

IE.Quit

    Set IE = Nothing
    
    MsgBox "Exchange Rates Imported !", vbInformation
    
End Sub

D) After we have completed the import of desired data we can start creating the dynamic chart. For this we first create some named ranges. Go to “Formulas”, then “Name Manager”, “New” and create the following two named ranges:

chart_Data: =OFFSET(Exchange_Rates!$E$8,0,0,COUNTA(Exchange_Rates!$E:$E)-1,1)

chart_label: =OFFSET(Exchange_Rates!$D$8,0,0,COUNTA(Exchange_Rates!$D:$D)-1,1)

Explanation: The Counta function counts the number of not empty cells into a range and the Offset function creates the named range starting from cell D/E8 until the last used cell in every column.

Now input the named ranges as data source for the chart:

For the Chart Title we can create a formula in Sheet “Lists” in order to have it dynamically changed considering the selected currency, the starting date and the end date of the imported data:

* at the end of this post you will find also a video where I explained detailed how this formula works.

Now the chart is automatically refreshed after new data is imported no matter how many rows of data we have:

I also created two videos to complete this post, you can watch them below (I am not the best presenter and I need to work more at this, so please don’t judge too hard the quality of those first two videos. Maybe in the future I will create better ones) :