Как автоматически скачивать котировки любой ценной бумаги в «Экселе»

Часто индивидуальные инвесторы не доверяют онлайн-сервисам и по старинке ведут учет собственных инвестиций в Microsoft Excel или его свободных аналогах вроде LibreOffice Calc.

Если бумаг не очень много, ведение учета в «Экселе» оправданно:

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

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

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

Сразу уточню: речь пойдет только о формулах для эксель-таблиц. Для учета в гугл-таблицах или в Numbers они не подойдут. Про учет в гугл-таблицах в Тинькофф Журнале есть отдельная статья.

А еще эти формулы будут работать только в «Экселе» для операционной системы Windows. К сожалению, с версией для macOS они несовместимы: в них есть функция ФИЛЬТР.XML, которая использует особенности Windows.

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

Во-вторых, это важно для финансового моделирования. Автоматическая загрузка цен дает пользователю возможность включить в свою модель актуальные рыночные данные.

Котировки публикуют на сайтах бирж и в финансовых агрегаторах, например на Мосбирже, Yahoo Finance или Investing.com. Мы писали, как пользоваться готовыми сервисами, если не хочется разбираться в формулах.

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

1. Цену российской акции «Лукойл» (тикер LKOH) публикуют на сайте Московской биржи.
2. Цену российской государственной облигации ОФЗ 26226 (код НРД SU26226RMFS9) также публикует Московская биржа.
3. Официальный курс доллара к рублю можно найти на сайте Центробанка.
4. Цену иностранной акции Alphabet (тикер GOOGL, ISIN US02079K3059) удобно смотреть на сайте Yahoo Finance.
5. Цену иностранного ETF iShares MSCI Europe ex-UK UCITS ETF (тикер IEUX, ISIN IE00B14X4N27) можно посмотреть на сайте Morningstar.

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

Есть несколько удобных способов сделать так, чтобы актуальные цены из этих источников сами подгружались в «Эксель». Расскажу о двух наиболее простых и эффективных — парсинге и API. Дальше в статье я на примерах покажу, как применить их для учета инвестиций, и поделюсь собственными формулами для тех, кто не хочет погружаться в программирование. Мои формулы можно брать и подставлять в любую таблицу.

Чтобы было понятнее, о чем я рассказываю, скачайте и откройте в «Экселе» вот эту мою таблицу.

Для каждой ценной бумаги я укажу в таблице две формулы. Первая подойдет, если хотите получить текущую цену — максимум с 15-минутной задержкой. Но когда биржа закрыта, формула будет выдавать ошибку. Вторая формула показывает цену всегда, в любое время, но только на момент закрытия последних торгов.

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

Формулы в этом блоке подходят только для котировок акций. Для ETF и других активов понадобятся другие формулы — они будут дальше в статье.

Например, получать сегодняшнюю цену акций «Лукойла» с Мосбиржи можно с такой формулой:

=ПОДСТАВИТЬ(@ ФИЛЬТР.XML(ВЕБСЛУЖБА("https://iss.moex.com/iss/engines/stock/markets/shares/boards/TQBR/securities.xml?iss.meta=off&iss.only=marketdata&marketdata.columns=SECID,LAST");"//document//data//rows//row[@SECID='"&A3&"']/@LAST");".";",")

В ячейке A3 в таблице надо указать тикер нужной вам акции.

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

Следующая формула более универсальна. С ее помощью можно скачать цену акций с Мосбиржи даже в нерабочее время — на момент ее закрытия. Не забудьте снова подставить тикер нужной вам акции:

=ПОДСТАВИТЬ(@ ФИЛЬТР.XML(ВЕБСЛУЖБА("https://iss.moex.com/iss/engines/stock/markets/shares/boards/TQBR/securities.xml?iss.meta=off&iss.only=securities&securities.columns=SECID,PREVLEGALCLOSEPRICE");"//document//data//rows//row[@SECID='"&A3&"']/@PREVLEGALCLOSEPRICE");".";",")

Аналогично акциям можно автоматически подгружать с Мосбиржи в свою таблицу и стоимость государственных облигаций.

Например, чтобы получать стоимость российской облигации ОФЗ 26226 (код НРД SU26226RMFS9), можно использовать такую формулу:

=ПОДСТАВИТЬ(@ ФИЛЬТР.XML(ВЕБСЛУЖБА("https://iss.moex.com/iss/engines/stock/markets/bonds/boards/TQOB/securities.xml?iss.meta=off&iss.only=marketdata_yields&marketdata_yields.columns=SECID,PRICE");"//document//data//rows//row[@SECID='"&A8&"']/@PRICE");".";",")

В ячейке A8 надо указать код нужного вам НРД — его тоже можно найти на сайте Мосбиржи.

Эта формула будет работать только для облигаций ОФЗ и только в рабочее время Московской биржи.

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

=ПОДСТАВИТЬ(@ ФИЛЬТР.XML(ВЕБСЛУЖБА("https://iss.moex.com/iss/engines/stock/markets/bonds/boards/TQOB/securities.xml?iss.meta=off&iss.only=securities&securities.columns=SECID,PREVLEGALCLOSEPRICE");"//document//data//rows//row[@SECID='"&A8&"']/@PREVLEGALCLOSEPRICE");".";",")

У Центробанка тоже есть удобный API, через который можно получать курсы любых валют. На сайте ЦБ опубликована подробная инструкция, как это сделать.

Так выглядит готовая формула, которая позволит автоматически загрузить в «Эксель» курс доллара США на сегодня:

=@ФИЛЬТР.XML(ВЕБСЛУЖБА("http://www.cbr.ru/scripts/XML_dynamic.asp?date_req1="&ТЕКСТ(СЕГОДНЯ();"дд.ММ.гггг")&"&date_req2="&ТЕКСТ(СЕГОДНЯ();"дд.ММ.гггг")&"&VAL_NM_RQ=R01235");"//Value")

Не у всех ресурсов есть открытый доступ к API, как у Московской биржи или Центрального банка. А для части иностранных инвестиций, например облигаций или фондов, вообще сложно найти котировки в открытом доступе.

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

Чтобы использовать VBA, сначала необходимо включить в «Экселе» режим разработчика:

1. Нажмите «Файл» → «Параметры».
2. Выберите «Настроить ленту» в левой боковой панели.
3. Установите флажок «Разработчик» в правом столбце.
4. Нажмите «ОК», чтобы применить изменения.

Парсинг Yahoo Finance. Получать котировки иностранных акций проще всего с помощью парсинга в сервисе Yahoo Finance. Покажу, как это делается, на примере акции Alphabet (тикер GOOGL, ISIN US02079K3059).

Чтобы настроить парсинг цен с сайта Yahoo Finance, придется выполнить несколько действий:

1. Откройте мою таблицу в «Экселе» в режиме разработчика — она содержит сценарии VBA.
2. «Эксель» предложит включить макросы — нажмите «Включить макросы» или «Включить контент», чтобы разрешить запуск сценариев VBA.
3. После этого выберите любую ячейку и воспользуйтесь функцией =GetQuoteFinanceYahooCom("GOOGL"). Котировка появится в этой же ячейке.

Для другой иностранной акции подставьте на место GOOGL нужный тикер.

Где искать котировки редких акций. Иногда в портфеле могут оказаться экзотические активы, цены которых особенно сложно отыскать. Можно воспользоваться поиском по ISIN, International Securities Identification Number, — международному идентификационному коду.

Например, эта формула позволит получить котировки не очень известного иностранного ETF iShares MSCI Europe ex-UK UCITSvETF (тикер IEUX, ISIN IE00B14X4N27) с сайта агентства Morningstar. Если хотите настроить получение цены с любого сайта, этот VBA-скрипт может стать образцом. Без кода VBA-скрипта формула ниже работать не будет:

=GetQuoteMorningstarCoUkETF(B9)

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

1. В «Экселе» можно автоматически скачивать котировки не только большинства российских активов, но и множества иностранных.
2. Часть котировок российских активов удобно скачивать с помощью формул из этой статьи. А для иностранных понадобится скрипт, который позволит загрузить котировки через кастомную формулу.
3. Внутренний язык программирования Microsoft Office дает возможность брать из интернета цены любых ценных бумаг, и они будут автоматически обновляться.