Import of NBU exchange rates in Excel (NBU_RATE)

A ready-made function for those who often have to use NBU exchange rates in Excel. Helps to automatically insert currency values by its code and date from NBU website . Dedicated to bank employees of Ukraine.

Open the menu Service - Macros - Visual Basic Editor, insert the new module (menu Insert - Module) and copy the text of this function there:

Function NBU_RATE(sCurr$, iiDate As Date)
 Dim sURI As String
 Dim oHttp As Object
 Dim htmlcode As String
 Dim C As Range
 Dim Q As Long
 Dim iP As Long, z As Long
 Dim S As String, S1 As String, iOnlyTable As String
 Dim b As Object
 Dim massive(30, 5)
 Dim iDatas As Date
 sURI = "" & iiDate & "&execute"
 On Error Resume Next
 Set oHttp = CreateObject("MSXML2.XMLHTTP")
 If Err.Number <> 0 Then
 Set oHttp = CreateObject("MSXML.XMLHTTPRequest")
 End If
 If oHttp Is Nothing Then Exit Function
 On Error GoTo ConnectionError
 oHttp.Open "GET", sURI, False
 On Error GoTo ConnectionError
 htmlcode = oHttp.responseText
 iP = InStr(1, htmlcode, "України<"&"br/>встановлює")
 htmlcode = Mid(htmlcode, iP, 10000)
 iP = InStr(1, htmlcode, "<"&"td class=") + 6 + 11 '">
 iDatas = Mid(htmlcode, iP, 10)
 iOnlyTable = Mid(htmlcode, InStr(100, htmlcode, "<"&"table cellpadding="), InStr(1000, htmlcode, "<"&"/table>") - _
 InStr(100, htmlcode, "<"&"table cellpadding=") + 10)'">
 Set Doc = CreateObject("HTMLFile")
 Doc.Write iOnlyTable
 Set b = Doc.all.tags("TABLE")
 For Each uTableElement In b
 iRows = uTableElement.Rows.Length
 iCells = uTableElement.Cells.Length
 j = 0
 For k = 1 To iRows
    For l = 1 To 5
        j = j + 1
        massive(k, l) = uTableElement.Cells(j - 1).innerHTML
        If j = uTableElement.Cells.Length Then GoTo 1
    Next l
Next k
 Next uTableElement
 For k = 2 To iRows
    For l = 1 To 5
        If massive(k, l) = sCurr Then
            NBU_RATE = Replace(massive(k, 5), ".", ",")
            NBU_RATE = NBU_RATE / massive(k, 3)
        End If
     Next l
Next k

Set oHttp = Nothing
Exit Function
End Function

Close Visual Basic Editor and return to Excel.

Now, in Function wizard in category User definded you can find our function NBU_RATE and use it. The syntax of this function is as follows:

=NBU_RATE ( currency code ; date )

We will have the following result: