alexei.dolgolyov
0cdea8d00e
Document fetch strategy (MSXML2/WinHttp), UTF-8 decoding via ADODB.Stream, HTML parsing markers, and ChrW() Cyrillic encoding. Co-Authored-By: Claude Opus 4.6 <noreply@anthropic.com>
253 lines
11 KiB
Markdown
253 lines
11 KiB
Markdown
# Marathon Stats — VBA парсер live-статистики
|
||
|
||
VBA-модуль для Excel, который загружает live-статистику спортивных событий с сайта
|
||
[Marathon Bet (BY)](https://www.marathonbet.by/su/live/45356) и выводит результаты
|
||
в отформатированную таблицу.
|
||
|
||
## Возможности
|
||
|
||
- Загрузка HTML-страницы через встроенные COM-объекты (без внешних зависимостей)
|
||
- Корректная обработка кириллицы (UTF-8) через `ADODB.Stream`
|
||
- Парсинг HTML по DOM-маркерам Marathon: лиги, команды, счёт, время, коэффициенты
|
||
- Профессиональное оформление Excel: цветовые схемы, группировка по лигам, замороженные панели
|
||
|
||
## Быстрый старт
|
||
|
||
### Из Excel
|
||
|
||
1. Открыть Excel, нажать `Alt+F11` (редактор VBA)
|
||
2. **File → Import File** → выбрать `MarathonStats.bas`
|
||
3. Закрыть редактор, нажать `Alt+F8` → выбрать `FetchMarathonStats` → **Run**
|
||
|
||
### Из PowerShell (автоматизация)
|
||
|
||
```powershell
|
||
powershell.exe -ExecutionPolicy Bypass -File RunMarathon.ps1
|
||
```
|
||
|
||
> **Требование:** В Excel должен быть включён доступ к объектной модели VBA:
|
||
> `File → Options → Trust Center → Trust Center Settings → Macro Settings →
|
||
> Trust access to the VBA project object model`
|
||
|
||
## Структура файлов
|
||
|
||
| Файл | Описание |
|
||
|---|---|
|
||
| `MarathonStats.bas` | Основной VBA-модуль (импортируется в Excel) |
|
||
| `RunMarathon.ps1` | PowerShell-скрипт для автоматического запуска |
|
||
| `MarathonStats.xlsm` | Результат — Excel-книга с данными |
|
||
|
||
---
|
||
|
||
## Подробное описание кода
|
||
|
||
### Точка входа
|
||
|
||
```vba
|
||
Public Sub FetchMarathonStats()
|
||
```
|
||
|
||
Главная процедура. Последовательно выполняет:
|
||
|
||
1. Создаёт/очищает лист `LiveStats`
|
||
2. Загружает HTML-страницу (`FetchPage`)
|
||
3. Парсит события (`ParseHTML`)
|
||
4. Записывает данные и форматирует таблицу
|
||
|
||
---
|
||
|
||
### Загрузка страницы (Fetch)
|
||
|
||
Ключевая проблема: сервер Marathon отправляет ответы в **gzip-сжатии** и разрывает
|
||
соединение с клиентами, которые не умеют его обрабатывать. Решение — двухуровневая
|
||
стратегия с двумя COM HTTP-объектами.
|
||
|
||
#### `FetchPage(url As String) As String`
|
||
|
||
Диспетчер — пробует два метода по очереди:
|
||
|
||
```
|
||
FetchPage
|
||
├─ FetchWithXMLHTTP() ← основной
|
||
└─ FetchWithWinHTTP() ← резервный
|
||
```
|
||
|
||
#### Основной метод: `FetchWithXMLHTTP(url)`
|
||
|
||
Использует **`MSXML2.XMLHTTP.6.0`** — клиентский COM-объект (на базе WinInet, как IE).
|
||
Автоматически распаковывает gzip, что критично для Marathon.
|
||
|
||
```vba
|
||
Set http = CreateObject("MSXML2.XMLHTTP.6.0")
|
||
http.Open "GET", url, False
|
||
http.setRequestHeader "User-Agent", "Mozilla/5.0 ..."
|
||
http.setRequestHeader "Accept-Language", "ru-RU,ru;q=0.9"
|
||
http.Send
|
||
```
|
||
|
||
Заголовки имитируют браузер Chrome:
|
||
- **User-Agent** — без него сервер может отклонить запрос
|
||
- **Accept-Language: ru-RU** — для получения русскоязычной версии страницы
|
||
|
||
**Декодирование UTF-8** (критически важно для кириллицы):
|
||
|
||
```vba
|
||
Dim bodyBytes() As Byte
|
||
bodyBytes = http.responseBody ' сырые байты ответа
|
||
|
||
Dim stream As Object
|
||
Set stream = CreateObject("ADODB.Stream")
|
||
stream.Type = 1 ' adTypeBinary
|
||
stream.Open
|
||
stream.Write bodyBytes ' записать байты
|
||
stream.Position = 0 ' перемотать в начало
|
||
stream.Type = 2 ' adTypeText
|
||
stream.Charset = "UTF-8" ' указать кодировку
|
||
FetchWithXMLHTTP = stream.ReadText ' прочитать как текст
|
||
stream.Close
|
||
```
|
||
|
||
Почему нельзя просто использовать `http.ResponseText`:
|
||
- VBA работает в кодировке Windows-1251 (ANSI)
|
||
- `ResponseText` может неверно интерпретировать UTF-8 байты
|
||
- `ADODB.Stream` гарантирует корректное преобразование UTF-8 → Unicode
|
||
|
||
#### Резервный метод: `FetchWithWinHTTP(url)`
|
||
|
||
Использует **`WinHttp.WinHttpRequest.5.1`** с заголовком `Accept-Encoding: identity`,
|
||
который просит сервер отправить **несжатый** ответ. Обходит проблему gzip на уровне
|
||
протокола.
|
||
|
||
```vba
|
||
http.setRequestHeader "Accept-Encoding", "identity"
|
||
```
|
||
|
||
Декодирование UTF-8 — аналогичное через `ADODB.Stream`.
|
||
|
||
#### Почему не работают другие подходы
|
||
|
||
| Метод | Результат | Причина |
|
||
|---|---|---|
|
||
| `MSXML2.ServerXMLHTTP.6.0` | Соединение разорвано | Не обрабатывает gzip автоматически |
|
||
| `.NET HttpWebRequest` | Соединение разорвано | Аналогичная проблема |
|
||
| `WinHttp` без `identity` | Мусор вместо текста | Получает gzip-байты как строку |
|
||
| `InternetExplorer.Application` | Пустая страница | Контент загружается через JavaScript |
|
||
|
||
---
|
||
|
||
### Парсинг HTML
|
||
|
||
#### `ParseHTML(html As String)`
|
||
|
||
Последовательный однопроходный сканер. Ищет ближайший из трёх маркеров и обрабатывает
|
||
его в зависимости от типа:
|
||
|
||
```
|
||
Маркеры в HTML Marathon:
|
||
─────────────────────────────────────────────────────────
|
||
"category-label simple-live" → название лиги (в <h2>)
|
||
"cl-left red" → счёт матча (в <div>)
|
||
"data-member-link="true">" → имя команды (в <span>)
|
||
"data-selection-price="X"" → коэффициент ставки
|
||
"data-sport-type="Basketball" → вид спорта
|
||
"green bold nobr" → игровое время
|
||
─────────────────────────────────────────────────────────
|
||
```
|
||
|
||
Алгоритм на каждой итерации:
|
||
1. Находит позиции всех трёх основных маркеров через `InStr()`
|
||
2. Выбирает ближайший (минимальная позиция)
|
||
3. Извлекает данные в зависимости от типа маркера:
|
||
- **Лига** → сохраняет как текущую лигу для последующих событий
|
||
- **Счёт** → извлекает счёт и время матча
|
||
- **Команда** → читает пару команд, затем находит 2 коэффициента и сохраняет событие
|
||
|
||
#### Извлечение счёта: `ExtractScore(html, startPos)`
|
||
|
||
Особая обработка — нужно отсечь блок `time-description`, который идёт после счёта
|
||
в том же `<div>`:
|
||
|
||
```html
|
||
<div class="cl-left red">51:43 (29:17, 22:26)
|
||
<span class="time-description">Кон.</span>
|
||
</div>
|
||
```
|
||
|
||
Функция ищет маркер `"time-description"`, затем **откатывается назад** до символа `<`,
|
||
чтобы отрезать тег `<span>` целиком, оставив только текст счёта.
|
||
|
||
#### Извлечение коэффициентов: `ExtractOddsPair(html, afterPos, odds1, odds2)`
|
||
|
||
Ищет два ближайших атрибута `data-selection-price="X"` после позиции второй команды,
|
||
но **не дальше** следующего события (ограничение через `boundary`). Это предотвращает
|
||
захват коэффициентов от соседнего матча.
|
||
|
||
---
|
||
|
||
### Обработка кириллицы
|
||
|
||
В VBA-файлах (.bas) невозможно напрямую хранить кириллические литералы — при
|
||
экспорте/импорте они теряются. Поэтому все русские строки записаны через `ChrW()`:
|
||
|
||
```vba
|
||
' "Лига" = ChrW(1051) & ChrW(1080) & ChrW(1075) & ChrW(1072)
|
||
' "Команда" = ChrW(1050) & ChrW(1086) & ChrW(1084) & ChrW(1072) & ChrW(1085) & ChrW(1076) & ChrW(1072)
|
||
' "Счёт" = ChrW(1057) & ChrW(1095) & ChrW(1105) & ChrW(1090)
|
||
' "Время" = ChrW(1042) & ChrW(1088) & ChrW(1077) & ChrW(1084) & ChrW(1103)
|
||
```
|
||
|
||
| Код | Символ | | Код | Символ |
|
||
|-----|--------|-|-----|--------|
|
||
| 1040–1071 | А–Я | | 1072–1103 | а–я |
|
||
| 1105 | ё | | 8212 | — (тире) |
|
||
|
||
Название вида спорта определяется из атрибута `data-sport-type` и переводится
|
||
на русский через `Select Case`:
|
||
|
||
```vba
|
||
Case "basketball": ' → Баскетбол
|
||
Case "football": ' → Футбол
|
||
Case "icehockey": ' → Хоккей
|
||
Case "tennis": ' → Теннис
|
||
```
|
||
|
||
#### `StripTags(s)` и `CleanText(s)`
|
||
|
||
Две функции очистки текста, извлечённого из HTML:
|
||
|
||
- **`StripTags`** — удаляет HTML-теги (`<...>`), заменяет управляющие символы
|
||
(chr 10, 13, 9) пробелами, схлопывает множественные пробелы
|
||
- **`CleanText`** — дополнительная нормализация: замена `vbCrLf`, `vbCr`, `vbLf`,
|
||
`vbTab` на пробелы с последующим схлопыванием
|
||
|
||
Обе функции необходимы, потому что HTML Marathon содержит переносы строк и табуляции
|
||
внутри тегов со счётом и названиями лиг.
|
||
|
||
---
|
||
|
||
### Выходная таблица Excel
|
||
|
||
Лист `LiveStats` содержит:
|
||
|
||
```
|
||
Строка 1: Заголовок — "Marathon Bet — Баскетбол Live"
|
||
Строка 2: URL страницы
|
||
Строка 3: Дата/время загрузки
|
||
Строка 5: Шапка таблицы (закреплена)
|
||
Строка 6+: Данные
|
||
|
||
Столбцы:
|
||
# | Лига | Команда 1 | Команда 2 | Счёт | Время | П1 | П2
|
||
```
|
||
|
||
Форматирование:
|
||
- Шапка: тёмный фон, белый текст, жирный шрифт
|
||
- Чередование строк: белый / светло-серый
|
||
- Первая строка новой лиги: голубой фон, жирное название
|
||
- Счёт: крупный жирный шрифт, по центру
|
||
- Рамки: тонкие серые линии по всей таблице
|
||
|
||
## Лицензия
|
||
|
||
MIT
|