Страницы: 1 2 3 След.
RSS
Извлечение курса валют Сбербанка на указанную дату, сделал, надо ускорить
 
Здравствуйте, Господа!

С помощью Fiddler вытащил курсы Сбербанк-Кострома на заданную дату.
Пример запроса на 01.05.2014: тыц.
Два вопроса:
1. Что такое inf_block=138? (при изменении на 128 больше валют, но цифры совсем другие)
2. Как ускорить UDF?
Буду благодарен, если кто-то поможет.

PS: Если выдаст ошибку, замените MSXML2.ServerXMLHTTP.6.0 на MSXML2.ServerXMLHTTP.4.0.
Изменено: Acid Burn - 09.05.2014 19:07:46
 
Сделал уже 3 версии... Файл обновил.
У кого какие идеи будут в плане ускорения парсинга полученных строк?
Изменено: Acid Burn - 08.05.2014 23:53:03
 
Цитата
Acid Burn пишет:
Пример запроса на 01.05.2014: тыц
Попробовал сгрузить в Excel с помощью Данные - Получить внешние данные - Из Веба.
Приходит строка HTML кода таблицы, но без тега . Если добавить к ней этот тег, а также тег кодировки, то получится HTML файл, который открывается в Excel, Chrome и IE как таблица. Его парсить гораздо приятнее. Вот файл - переименовать в .htm.
 
Казанский, что-то я не пойму, в чём плюсы.
Мне в конечном итоге нужна не таблица, а курс валют на любую дату.
Аналоги: тут и тут.

Исходные коды моей и Вашей таблиц одинаковые.
Я сделал 3 версии парсинга: формулы, RegEx и гибрид.
Время "вычисления" блока 2х7 ячеек:
1 - 2,35853523283004 с
2 - 2,26521527177329 с
3 - 2,20532386099330 с

Как распарсить быстрее?
Изменено: Acid Burn - 08.05.2014 08:40:51
 
Acid Burn, нет плюсов, мне просто интересно было, как работает этот "вебдваноль"  :)
Я думаю, из приведенного Вами времени 2 секунды занимает веб-запрос, а остальное время собственно парсинг. Так что оптимизацией парсинга многого не добьешься. Попробуйте сделать объекты статическими, чтобы не создавать их каждый раз:

Код
Function CBR_Rate3(ByVal iCy As String, iOp As String, iDt As Date, iPt As String, iCl As String) As Double
' Получение курса валют с сайта Сбербанк-Кострома
' http://www.sberbank.ru/kostroma/ru/quotes/currencies/

' iCy   - тип валюты        (USD, EUR, GBP, SEK - КАК ПОЛУЧИТЬ ОСТАЛЬНЫЕ???)
' iOp   - тип операции      (Купить / Продать)
' iDt   - дата              (дд.мм.гггг)
' iPt   - способ оплаты     (Cash - наличный, Non_cash - безналичный)
' iCl   - категория клиента (legal - юрик, natural - физик)

' ПОЛУЧЕНИЕ ДАННЫХ
' ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Static XMLHTTP As Object, objRegExp As Object
If XMLHTTP Is Nothing Then
  Set XMLHTTP = CreateObject("MSXML2.ServerXMLHTTP.6.0")
End If
If objRegExp Is Nothing Then
  Set objRegExp = CreateObject("VBScript.RegExp")
  With objRegExp
    .Global = True
    .IgnoreCase = True
    .MultiLine = False
    .Pattern = "(<.*?>)+"
  End With
End If
  
  URL = "http://www.sberbank.ru/common/js/quote_table.php"
  Post = "inf_block=138&date=" & iDt & "&payment=" & iPt & "&person=" & iCl
  XMLHTTP.Open "POST", URL
  XMLHTTP.setRequestHeader "Content-Type", "application/x-www-form-urlencoded"
  XMLHTTP.Send Post
  vStr = XMLHTTP.responseText
  
' ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
  Курс = objRegExp.Replace(Replace(Replace(vStr, "<tr>", ""), "<td", "  <td"), "")
  
  Set F = Application.WorksheetFunction
  Tmp = F.Substitute(F.Substitute(F.Trim(Right(Курс, Len(Курс) - F.Search(iCy, Курс) - 3)), " ", "<", 2), " ", ">", 2)
  Q = Left(Tmp, F.Search(" ", Tmp))
  Buy = F.Substitute(Mid(Tmp, Len(Q) + 1, F.Search("<", Tmp) - Len(Q) - 1), ".", ",") / Q
  Sell = F.Substitute(Mid(Tmp, F.Search(">", Tmp) + 1, F.Search(" (", Tmp) - F.Search(">", Tmp)), ".", ",") / Q
  If iOp = "Купить" Then CBR_Rate3 = Buy Else CBR_Rate3 = Sell

End Function
 
 
Когда мне за сеанс нужно было много раз запрашивать один и тот же курс (в таблице на много строк и много повторяющихся дат) - я собирал уже полученные курсы в словарь (публичный, или можно и в статичный) и сперва смотрел нет ли там уже нужного курса.
 
Казанский, спасибо за совет.
Согласен, что основное время занимает web-запрос.
Но хочется оптимально построить ИМЕННО парсинг (для будущих задач в том числе):
сделать его максимально "читабельным" и без использования Application.WorksheetFunction.
Поможете?
И почему, кстати, Вы выбрали именно этот вариант функции? Он реально лучше других?

Hugo, идея со словарём тоже мне интересна, хоть и не будет повторяющихся дат.
Поможете прикрутить?

Сам сейчас пытаюсь понять, что такое Info_blok (при запросе от 1 до 1000 получил 83 ответа).
Похоже на субъекты РФ согласно Конституции, без Крыма и Севастополя.
Сейчас попробую найти классификаторы Сбербанка и проверить.
Изменено: Acid Burn - 08.05.2014 09:52:54
 
У меня с работы не работает MSXML2.ServerXMLHTTP, так что словарь вслепую если только прикручивать... не хочу.
Может кто подскажет - что за библиотека нужна? Сходу не получилось инет на ответ раскрутить...
 
Hugo, попробуйте MSXML2.ServerXMLHTTP 6.0 заменить на 4.0. Дополнительно можно посмотреть ТУТ.
Скрытый текст
Код проверки broken references:
Код
Sub ReferenceProperties()
  For Each ref In ThisWorkbook.VBProject.References
    If ref.IsBroken = False Then
   Debug.Print "Имя: ", ref.Name
   Debug.Print "Путь: ", ref.FullPath
   Debug.Print "Версия: ", ref.Major & "." & ref.Minor
   Debug.Print "GUID: ", ref.GUID & Chr(10)
    Else
   Debug.Print "GUIDs of broken references:"
   Debug.Print ref.GUID
    End If
  Next ref
End Sub
И когда на форуме спойлеры заработают?
И когда пользователи кнопки видеть научатся? [МОДЕРАТОР]
Изменено: Acid Burn - 08.05.2014 16:37:37
 
Не, наврал - объект создаётся, не проходит XMLHTTP.Send Post - видать что-то админы режут, из дома работало.
 
Цитата
И когда на форуме спойлеры заработают?
а это что
 
V, спасибо!
У меня [spoiler]Вариант 1[\spoiler], [spoiler]Вариант 2[/spoiler] не работают
Упс
Изменено: Acid Burn - 08.05.2014 23:53:44
 
Для начала я бы запустил Developers Tools (есть в каждом браузере) и там посмотрел бы все внутренности в красиво оформленном виде (больше всех мне нравится DT в Opera). Далее используем две библиотеки: "Microsoft HTML Object Library" и "Microsoft XML, 6.0". Ответ от XMLHTTP пихаем в HTMLDocument и используем, например, getElementId, чтобы достать элемент по ID. И не надо никакого парсинга.  :)  

Код
Sub F()

    Dim doc As MSHTML.HTMLDocument
    Dim req As MSXML2.XMLHTTP60
    Dim iElement As MSHTML.IHTMLElement
    
    Set doc = New MSHTML.HTMLDocument
    Set req = New MSXML2.XMLHTTP60
    
    req.Open "GET", "http://www.yandex.ru", False
    req.send
    
    doc.body.innerHTML = req.responseText
    Set iElement = doc.getElementById("currency")

End Sub 
Изменено: Johny - 08.05.2014 12:44:41
There is no knowledge that is not power
 
Johny, звучит красиво, но пока мало понятно...
Буду пробовать.
Изменено: Acid Burn - 08.05.2014 12:09:44
 
Developers Tools в Opera.
There is no knowledge that is not power
 
Спасибо. Продолжу вечером.
 
Посмотрите  тему  Здесь
Юзайтевебсервис сбера
 
Так то cbr, это уже проходили. С сбером интереснее :)
 
Похоже продолжу уже завтра, сейчас нет времени.
Пока удалил все промежуточные версии файла, финальную добавил в первый пост.

Изменения:
1. Идентифицировал все возможные inf_block (как я и предполагал, это - регионы РФ)
2. Идентифицировал все возможные валюты
3. Теперь 2 банка на выбор: Сбербанк и ЦБ РФ (спасибо MCH и EducatedFool за их функцию)

4. Добавил режимы вывода "Таблица" и "Динамика" дополнительно к "Продать" и "Купить"
5. Прикрутил удобный календарик для выбора дат (спасибо неизвестному мне автору)
6. Добавил динамические ссылки и комментарии

Буду благодарен за помощь в доработке кода и новые идеи.  ;)
Изменено: Acid Burn - 09.05.2014 17:36:11
 
Если упаковать - всего 30кб.
Словарь  может за выходные помогу прикрутить, если раньше кто не поможет - но для данного примера словарь ничего не даст. Ну разве что если регионы туда-сюда менять - со словарём повторного запроса не будет.
 
Hugo, согласен. В формате 7zip и xlsb - ещё меньше.
Без упаковки и в старом формате сохранил специально, чтобы всем было удобнее.

Не вышло сделать:
1. Многопоточную загрузку данных в заданном диапазоне дат со сбором в словарь или на скрытый лист по нажатию кнопки "Start"
2. Обработчик ошибок вместо использования ЕСЛИОШИБКА()
3. Упростить формулы парсинга данных и тем более сбор данных без парсинга (вариант Jonny)

Need help!
 
Алгоритм словаря такой:
1. объявляем публичный объект
2. первым делом в UDF проверяем объект - если nothing, то создаём словарь
3. далее из входных параметров собираем ключ дата|валюта|регион  и возможно ещё добавить "закупка" или "наличка" (зависит как реализовать)
4. проверяем наличие ключа в словаре
4.1 - если есть, то извлекаем из item курс/курсы, подставляем в результат, готово.
4.2 - если нет, то идём на сайт, получаем курсы, пишем их в словарь, подставляем в результат, готово.

С работы (сегодня работаю) нет возможности зайти на майл.ру, да и не работает вообще эта UDF (выше упоминал).
 
Hugo, спасибо за совет и заинтересованность.
Но, чтобы грамотно реализовать те 3 пункта, о которых я говорил выше, лично у меня не хватает ни знаний, ни времени, ни сил.
К сожалению, я сделал всё, что мог.
Выкладываю последнюю версию файла в новом формате, всё "лишнее" прибрал в модуль Тест, внёс комментарии и т.п.
Очень надеюсь, что найдётся кто-то, кто сможет улучшить мою работу.
Изменено: Acid Burn - 10.05.2014 18:10:08
 
Всё же UDF жёстко виснет на больших массивах дат. Основную часть времени занимает web-запрос.
Кто-нибудь может подсказать быстрый общедоступный ресурс, где было бы можно разместить и ежедневно обновлять базу Сбербанка?
Ведь это вполне можно реализовать, вопрос - как это лучше сделать.

И как всё же прикрутить обработчик ошибок? - при отсутствии валюты в базе появляется #ЗНАЧ!, которая не обрабатывается IFERROR, ISERR и т.п.
 
Можно хранить данные в этой книге на скрытом листе - при открытии книги загружать их в словарь, при закрытии дополненный словарь выгружать на лист.
Только тогда вероятно вместо очистки словаря нужно делать что-то другое...
И есть вопрос по тем данным, где в этой версии пишется "нет курса" - грузить их каждый раз из сети долго (т.к. их много), не грузить повторно вообще тоже не годится...
Может добавить спецкнопку - по клику удалить из словаря текущие ключи
Банк
Область
Дата
Способ оплаты
Категория клиента
по всем валютам (т.е. ключ like t & "*")
с "нет курса" в Item и пересчитать лист? Так удалённые курсы/ключи загрузятся заново - может там появились данные.

Добавил в код словарь. Теперь если за сеанс запрашивать уже запрошенное - не запрашивает :)
Хотел ещё заменить Substitute на Replace и Search на Instr - но местами запутался и плюнул...  :(
Но кое-где заменил - результат аналогичен Вашему.
 
Да, про "многопоточность" не думал - но вот сейчас думаю, что лишнее. Хотя сделать можно - даже просто в цикле понаподставлять в ячейку даты этот диапазон дат, в конце вернуть исходную. Перед уходом на обед :)
 
Чуть добавил "многопоточности" (теперь за один раз тянет все виды сделок из сбербанка, но только текущую валюту) и ещё по мелочи ускорил.
Но конечно запрашивать за год долго...
Поигрался немного с статусбаром - показывает загрузку словаря. Пока ячейку не сменишь.
 
Думаю, что использовать Replace вместо Substitute не стоит из-за ограничения в 1024 символа.
И вообще Replace, как Вы сами видели, работает как-то иначе, нежели Substitute.
Фразу "нет курса" я всё же заменил на "".
За остальное спасибо.

Однако, тормозов при обработке новых запросов меньше не стало + файл стал ОЧЕНЬ сильно виснуть при открытии.
Идея со скрытым листом IMHO не реальна: не известно, кому какие данные потребуются; впихнуть всё на лист не выйдет, да и файл сильно "потяжелеет".
Идея со словарём - не панацея, ведь он очищается при закрытии книги.

Поэтому есть идея скачать всю базу Сбербанка, возможно конвертировать в XML и выложить куда-нибудь.
Проблема - как её обновлять и куда собственно выложить.
Изменено: Acid Burn - 10.05.2014 17:39:08
 
Ну и будут те же яйца - тянуть всю базу долго, а частями - те же яйца  :(  
Тормоза для новых запросов уменьшились только в части сбера - там сразу одним запросом на дату/валюту/регион тянутся  все

"Таблица"
"Купить"
"Продать"
"Динамика"


Так что быстрее стало!  :)
Ну а повторно ничего не запрашивается.
 
Но ведь запрос к ЦБ РФ проходит быстро, значит проблема в сайте Сбербанка.
Как это обойти?

а) создать свою базу аналогичную базе ЦБ РФ
б) прогрузить часть базы в несколько потоков и сохранить для последующей обработки (идея кнопки "Start")
Изменено: Acid Burn - 10.05.2014 18:01:30
Страницы: 1 2 3 След.
Наверх