Мой доклад на 35-й конференции Смартлаба в Москве: «Парсинг котировок в Microsoft Excel и Google Таблицы с любого сайта»

Бывает, что частные инвесторы не доверяют сервисам для ведения портфеля ценных бумаг и ведут учет своих инвестиций в «Экселе» или «Гугл Таблицах».

Если количество ценных бумаг не так велико, то подобное использование таблиц оправдано:

• не требуется платить кому-либо за хранение данных;
• никто не удалит ваш файл, например, за неактивность;
• отчеты можно сделать такие, как вам нравится.

Но у такого метода учета есть и свои минусы, главным образом связанные с необходимостью ручного обновления котировок. Если раз в квартал сделать это несложно и вручную, но чтобы поддерживать актуальность чаще, потребуется много времени: нужно зайти на сайт, где опубликованы текущие котировки, найти нужную цену, скопировать ее и вставить в ячейку таблицу. И так для каждой ценной бумаги в портфеле. Печально и долго.

Зачем вообще нужны актуальные цены в таблицах:

• Инвесторам — для эффективного управления портфелями и рисками.
• Трейдерам — для оптимизации решений о покупке и продаже с максимальной прибыльностью.
• Аналитикам — чтобы лучше понимать рыночные тенденции и повышать точность своих прогнозов и отчетов.

В статье расскажу, как автоматизировать процесс скачивания котировок. Ниже будут представлены готовые файлы с примерами получения цен, которые можно скопировать и с небольшими изменениями вставить в вашу таблицу.

Проблемы получения котировок в любые таблицы

Хотя возможность автоматического получения котировок в «Эксель» или «Гугл Таблицы» упрощает ведение портфелей, существует несколько технических препятствий, с которыми можно столкнуться при парсинге или скрапинге (в общем виде это автоматический сбор данных из интернета, в таблицах работает через формулы или скрипты):

• Динамическая загрузка контента: современные веб-сайты часто используют JavaScript для загрузки текущий цен уже после первоначальной загрузки страницы. Это создает проблему для базовых методов парсинга.
• Ограничения API: некоторые веб-сайты и финансовые учреждения предлагают общедоступные API (например, Московская биржа или Банк России), но и они имеют свои ограничения. А бывает, что можно найти АПИ, например для investing.com, но чтобы воспользоваться им потребует поиск альтернативных методов — имитация человека для того чтобы получить данные — использование автоматизации браузера.

Скачиваем котировки в в Microsoft Excel

Еще недавно автозагрузка котировок для некоторых иностранных акций была доступна прямо в «Экселе» — с подпиской Microsoft 365, но с марта 2024 это не работает.

Легко масштабируемый способ это VBA — это внутренний язык программирования Microsoft Office. С его помощью придется написать макрос, мини-программу, которая выполняет сразу несколько действий. Я уже написал код — достаточно будет заменить в нем пару строк под ваши потребности и вставить в таблицу.

Хотя такие инструменты, как Power Query, также могут достигать аналогичных результатов, VBA обеспечивает большую гибкость и масштабируемость.

Для извлечения российских котировок можно использовать API, предоставляемый Московской биржей. Интегрировав этот API с Excel можно извлекать цены на облигации или другие классы активов с 15и минутной задержкой непосредственно в свои электронные таблицы без ручных обновлений:

Для популярных иностранных бумаг написал VBA:

Для получения курсов от Центрального банка Российской Федерации тоже пришлось написать VBA скрипт, потому что формула недавно перестала работать:

⚠️Файл-пример скачивания котировок в Excel: Котировки любой бумаги в Excel (скачайте файл на компьютер, иначе он откроется в Гугл таблицах и будут одни ошибки).

Скачиваем котировки в Google Таблицы

Что касается Google Таблиц, для очень многих иностранных активов подойдёт встроенная функцию =GoogleFinance(), которая позволяет извлекать исторические и текущие цены на различные иностранные активы. Однако ограничением является полное отсутствие данных по российским акциям и облигациям, что является критическим пробелом:

Для российских бумаг:

Курсы валют Банка России:

⚠️Файл-пример скачивания котировок в Google Таблицы: Котировки в Гугл Таблицы

Если вы продвинутый пользователь, то сможете найти АПИ для любого популярного сайта, например для investing.com, и с помощью автоматизации браузера, например, библиотеки Puppeteer в Node.js можно обойти защиту Cloudflare, и автоматизировать извлечение котировок. Этот метод требует более глубокого понимания, но он открывает возможности для доступа к защищенным данным, с которыми простой скрапинг уже не справляется.

Ключевые выводы

Автоматизированная интеграция котировок — будь то международные или российские акции, автоматизация извлечения котировок экономит время и уменьшает количество ошибок в финансовом анализе.

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

Excel против Google Sheets: хотя обе платформы предлагают решения для извлечения рыночных данных, у каждой из них есть свои сильные стороны. VBA в Excel предоставляет мощные возможности настройки, тогда как простота Google Sheets усиливается за счет подключения к облаку.

Практические методы сбора данных могут помочь как новичкам, так и опытным пользователям лучше ориентироваться на финансовых рынках, быть в курсе событий и принимать своевременные решения. Независимо от того, создаете ли вы портфель, совершаете сделки или проводите углубленный анализ рынка, есть решение для парсинга, которое удовлетворит ваши потребности.

Автор:Михаил Шардин

21 октября 2024 г.