Группы и рассылки:

Вы здесь

Пишем функцию Excel для получения курса валют на указанную дату

Версия для печати

В этом уроке мы напишем на языке VBA пользовательскую функцию, которая по заданным параметрам будет получать с сайта Центр-Банка РФ актуальные курсы валют, установленные на определенную дату.

О том, что такое пользовательские функции в Excel, Вы можете почитать в этом уроке. «Тело» функции будет реализовано на встроенном языке VBA(языке макросов). Если Вы не знаете, что такое «макросы», то можете почитать здесь, а также, как они создаются – здесь.

Возвращаемся к теме урока.

Если Ваша сфера деятельности тесно связана с курсом валют, или у Вас имеются инструменты(отчеты) в Excel, в которых используется конвертация рублей в некоторую валюту… Да или Вы просто любите «поиграть на курсах валют», Вам нужен инструмент, который будет автоматически актуализировать курс заданной валюты.

Как нам узнать текущий курс валюты?

В этом нам поможет официальный сайт Центрального банка Российской Федерации(www.cbr.ru). На данном сайте можно посмотреть установленные курсы Валют на определенную дату. Конечно, обновление данных происходит не в режиме онлайн, а раз в сутки, за исключением выходных и праздничных, но такая периодичность для многих задач сгодится!

Итак, чтобы увидеть таблицу курсов, нам необходимо перейти на страницу: https://www.cbr.ru/currency_base/daily.aspx?date_req=08.03.2018 где мы увидим следующую таблицу:

vbaimg_11.03.2018_12:06:30

Теперь заглянем на «внутренности» отображенной страницы т.е. на ее HTML-код:

vbaimg_11.03.2018_20:52:41

Как мы видим, очень много букв из которых нам нужно получить нужную информацию, а нужна нам следующая информация:

  • Буквенный код – этот код будет использоваться как входной параметр для поиска курса нужной валюты;
  • Единиц – этот параметр потребуется для расчета стоимости одной единицы т.к. некоторые валюты продаются по сто единиц;
  • Курс – ну здесь собственно в рублях.

Как же нам получить из всего этого набора тегов, скриптов, ссылок и т.д. нужную информацию?!

Для этого необходимо найти некоторую закономерность или метки, которые послужат сигналами к тому, что именно эта информация нам нужна и воспользуемся встроенными функциями в 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 дней, построенный на нашей функции.

vbaimg_11.03.2018_23:41:05

Файл прикреплен в конце урока.

Переходим к кодингу:

  1. Создаем новую книгу;
  2. Открывем редактор VBA (Alt+F11);
  3. Добавляем модуль к проекту с любым именем.

В этот модуль пишем следующий код:

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”)»

Более подробно смотрите в приложенном файл-примере

Автор: 
Deys
Категории: 

Добавить комментарий

Plain text

  • HTML-теги не обрабатываются и показываются как обычный текст
  • Адреса страниц и электронной почты автоматически преобразуются в ссылки.
  • Строки и параграфы переносятся автоматически.
Разместить свое видео на сайте
Вы можете разместить свои 
видео-уроки на сайте
БЕСПЛАТНО!


Подробнее>>>