В этом уроке мы напишем на языке VBA пользовательскую функцию, которая по заданным параметрам будет получать с сайта Центр-Банка РФ актуальные курсы валют, установленные на определенную дату.
О том, что такое пользовательские функции в Excel, Вы можете почитать в этом уроке. «Тело» функции будет реализовано на встроенном языке VBA(языке макросов). Если Вы не знаете, что такое «макросы», то можете почитать здесь, а также, как они создаются – здесь.
Возвращаемся к теме урока.
Если Ваша сфера деятельности тесно связана с курсом валют, или у Вас имеются инструменты(отчеты) в Excel, в которых используется конвертация рублей в некоторую валюту… Да или Вы просто любите «поиграть на курсах валют», Вам нужен инструмент, который будет автоматически актуализировать курс заданной валюты.
Как нам узнать текущий курс валюты?
В этом нам поможет официальный сайт Центрального банка Российской Федерации(). На данном сайте можно посмотреть установленные курсы Валют на определенную дату. Конечно, обновление данных происходит не в режиме онлайн, а раз в сутки, за исключением выходных и праздничных, но такая периодичность для многих задач сгодится!
Итак, чтобы увидеть таблицу курсов, нам необходимо перейти на страницу: где мы увидим следующую таблицу:
Теперь заглянем на «внутренности» отображенной страницы т.е. на ее HTML-код:
Как мы видим, очень много букв из которых нам нужно получить нужную информацию, а нужна нам следующая информация:
- Буквенный код – этот код будет использоваться как входной параметр для поиска курса нужной валюты;
- Единиц – этот параметр потребуется для расчета стоимости одной единицы т.к. некоторые валюты продаются по сто единиц;
- Курс – ну здесь собственно в рублях.
Как же нам получить из всего этого набора тегов, скриптов, ссылок и т.д. нужную информацию?!
Для этого необходимо найти некоторую закономерность или метки, которые послужат сигналами к тому, что именно эта информация нам нужна и воспользуемся встроенными функциями в VBA для работы с текстом.
Строим алгоритм!
Смотрим код, и видим что область нужных нам данных начинается с тега table с классом “data”, а за ним сразу следует тег <tbody>!
Отлично! Конец уже легко найти, это первый встретившийся тег </tbody> после стартовой позиции т.е. конец тела таблицы. Выбираем эти данные из общей массы.
Но! У нас еще слишком много не нужной информации, смотрим еще и видим, что заголовок таблицы имеет теги <th></th> — столбцы, а в области данных столбцы разделены тегами <td></td>. Вот тут то и можно зацепиться, а именно взять нужную строку в которой содержится буквенный код искомой валюты.
Например, мы ищем курс конечно же по «Доллару США»… Буквенный код валюты «USD». Находим из выбранного ранее, первое вхождение подстроки “USD”, это и будет стартом для извлечения информации, а остановкой будет простой тег </tr> т.е. конец строки таблицы. Таким образом мы сразу получаем кол-во единиц и курс (результат выглядит вот так: “ USD</td><td>1</td><td>Доллар США</td><td>56,8011</td></tr>”).
Ну собственно осталось отчистить от лишних тегов. В этом нам поможет функция “Replace”
Функция позволяет заменить в тексте определенный набор символов, новым набором или пустотой.
Заменяем теги:
- <td> = “”(пусто);
- </td> = “;”(разделитель);
- <tr> = “”(пусто);
В результате получим строку «USD;1;Доллар США;56,8011». Все вполне пригодная строка данных.
Остается ее преобразовать в массив строк разделив на элементы с помощью функции «Split» и уже взять отдельные элементы массива для расчета и вернуть в функцию. Конец алгоритма!
Теперь это все реализуем в коде.
Для примера я набросал вот такой анализатор курса за последние 30 дней, построенный на нашей функции.
Файл прикреплен в конце урока.
Переходим к кодингу:
- Создаем новую книгу;
- Открывем редактор VBA (Alt+F11);
- Добавляем модуль к проекту с любым именем.
В этот модуль пишем следующий код:
Function ПОЛУЧИТЬКУРСРУБ(ByVal vDate As Date, ByVal Валюта As String)
Dim strURL As String
Dim dt As String ' Переменная дата в виде строки
Dim tmp As String 'Здесь храним наши выборки текста
Dim posSt As Long, posEnd As Long ' Переменные запоминают стартовую позицию в тексте нужной инфы и ее конец
'Проверяем, если дата введена не корректно или пуста, то ставим текущую дату
If IsDate(vDate) And Not IsEmpty(vDate) Then dt = CStr(vDate) Else dt = CStr(Date)
strURL = "https://www.cbr.ru/currency_base/daily.aspx?date_req=" & dt 'наша ссылка, а в конце добавляем дату, на которую хотим получить данные.
tmp = GetHTML(strURL) 'Запрашиваем код страницы(обратите внимание, здесь требуется доступ в инет, так что убедитесь что этот доступ не режут файрволы
'Начинаем поиски
posSt = InStr(1, tmp, "table class=""data""")
posSt = InStr(posSt, tmp, "<tbody>") + Len("<tbody>")
posSt = InStr(posSt, tmp, "</tr>") + Len("</tr>")
posEnd = InStr(posSt, tmp, "</tbody>")
tmp = Mid(tmp, posSt, posEnd - posSt) 'выбираем массив с курсами валют
posSt = InStr(1, tmp, Валюта) 'Ищем в массиве валюту
posEnd = InStr(posSt, tmp, "</tr>")
tmp = Mid(tmp, posSt, posEnd - posSt) 'выбираем строку данных
'Замена тэгов
tmp = Replace(tmp, "<td>", "")
tmp = Replace(tmp, "</td>", ";")
tmp = Replace(tmp, "<tr>", "")
mas = Split(tmp, ";") 'Разбиваем на массив
ПОЛУЧИТЬКУРСРУБ = mas(3) / mas(1) 'находим стоимость одной единицы валюты и возвращаем результат
End Function
Function GetHTML(URL As String) As String 'Получаем исходный код страницы в виде текста
Dim oHttp As Object
On Error Resume Next
Set oHttp = CreateObject("MSXML2.XMLHTTP")
If Err.Number <> 0 Then
Set oHttp = CreateObject("MSXML.XMLHTTPRequest")
End If
On Error GoTo 0
If oHttp Is Nothing Then
GetHTML = "err"
Exit Function
End If
oHttp.Open "GET", URL, False
oHttp.Send
GetHTML = oHttp.ResponseText
Set oHttp = Nothing
End Function
Ну и теперь осталось только вызвать функцию, а вызывается она как обычная встроенная функция Excel. Находится наша функция в категории «Определенные пользователем» и зовется «ПОЛУЧИТЬКУРСРУБ».
Пример вызова: «=ПОЛУЧИТЬКУРСРУБ(“09.03.2018”, “USD”)»
Более подробно смотрите в приложенном файл-примере