PRO. Получаем данные с MOEX. Часть 2
@exstudy
Термины
MOEX — Moscow Exchange или Московская биржа, крупнейший организатор торгов в РФ.
ИСС — информационно-статистический сервер Московской биржи. База данных биржи, откуда мы хотим выгрузить данные в Excel.
http — протокол передачи данных. Фактически каждый адрес сайта в строке браузера — это http-запрос.
Посылая запрос (адрес сайта) через браузер, в ответ вы получаете отображение сайта (ответ сервера). Простая схема: запрос — ответ, запрос — ответ.
Работа с ИСС построена на этом же принципе: создаем http-запрос (адрес), получаем ответ (данные).
XML — формат передачи данных. Это как с документами: есть документы в формате pdf, а есть таблицы в формате .xlsx.
XPath — язык запросов к XML. Позволяет забирать определенные данные из множества данных в формате XML.
Ранее в цикле PRO-материалов мы пробовали подключаться к Московской бирже (MOEX) и выгружать курс доллара. Освежить память можно здесь: https://t.me/exstudy/237
В этом материале копнем поглубже и попробуем получить в старый добрый Excel цены на российские акции из ИСС Московской биржи.
1. Создаем запрос
Как и в прошлый раз, сначала конструируем запрос к базе биржи. Перейдем на https://iss.moex.com/iss/engines.
Нам интересен фондовый рынок — stock. В запрос добавляем в конце /stock/markets.
Получится так https://iss.moex.com/iss/engines/stock/markets. Нажимаете Enter и переходите дальше.
Нам интересны акции, поэтому дописываем /shares. Нажимаете Enter и переходите дальше.
Дальше в таблице boards нам нужен режим TQBR. Дописываем /boards/tqbr и добавляете в конец securities.xml. Если нажать Enter, то получите вот такое полотно данных. Его хорошо бы сжать до минимума.
Убираем лишнее
В конец запроса добавляете знак вопроса "?", который обозначает что далее пойдут доп. параметры:
-
— отключить метаданные в заголовке. Они вам ни к чему. — рассматриваем только блок securities. — из всех данных забираем столбцы с тикером акции (SECID) и ценой (PREVPRICE).
- Каждый из этих 3-х блоков скрепляется между собой знаком &.
В результате получится запрос:
При нажатии Enter в браузере, вы получите аккуратную таблицу с тикерами акций и последними ценами на день назад.
С этим запросом и будем работать в Excel.
2. Получаем данные в Excel
Вставляем наш запрос в ячейку А1 для удобства использования.
Нам нужно из таблицы получать цены по какому-либо из тикеров акции. Своего рода ВПР. Такую выборку можно сделать через запрос XPath, который используется в функции ФИЛЬТР.XML:
Он означает: смотри в строки (row) и забирай цену (PREVPRICE), только для тех акций (SECID), что имеют название SBER — он же Сбер банк.
Вставляем его в ячейку А2.
Пробуем выгрузить цену акций SBER с помощью связки функций ФИЛЬТР.XML + ВЕБСЛУЖБА.
Цену на вчерашний день для SBER получили. Но это не очень интересно, т.к. название акции у нас жестко зашито в запросе.
Попробуем усложнить задачу — нужно собрать цены по списку акций.
В таком случае формулу меняем на:
С помощью конкатенации (знаки &) мы сцепляем текст запроса + значение из ячейки А5 + завершение запроса.
Протягиваем формулу вниз и получаем цены по каждой из акций. Результат:
На этом материал завершен. В резюме отмечу, что непросто осознать структуру XML + запросы XPath, однако это знание поможет автоматизировать рутинные процессы.
По всем вопросам и интересным предложениям пишите сюда: @excelstudybot
Как подтянуть котировки акций в эксель
Чтобы вставить цену акций в Excel, сначала преобразуем текст в тип данных Stocks (Акции). Затем вы сможете извлечь в другой столбец нужные сведения из этого типа данных, такие как цены акций или их изменения.
Примечание: Тип данных Stocks (Акции) доступен толькоMicrosoft 365 или с бесплатной учетной записью Майкрософт. В языковых параметрах Office также должен быть добавлен английский, французский, немецкий, итальянский, испанский или португальский язык редактирования.
Введите текст в ячейки. Например, в каждой ячейке введите тикер, название компании или название фонда.
Затем выберем ячейки.
Хотя это необязательно, рекомендуем создать таблицу Excel. Это упростит получение сведений из Интернета. Чтобы создать таблицу, выберите Вставка > Таблица.
Выбирая ячейки, перейдите на вкладку Данные и нажмите кнопку Акции.
Если Excel обнаружит совпадение между текстом в ячейках и веб-источниками, текст будет преобразован в тип данных Stocks. Вы будете знать, что они преобразованы, если у них есть значок акций: 
.
Выберите одну или несколько ячеек с типом данных, и появится 
добавить столбец. Нажмите эту кнопку, а затем щелкните имя поля, чтобы извлечь дополнительные сведения. Например, для акций можно выбрать Price.
Нажмите кнопку Добавить столбец еще раз, чтобы добавить дополнительные поля. Если вы используете таблицу, введите имя поля в строке заглавных строк. Например, введите Change (Изменить) в строке колонок акций, и в столбце цен появится изменение. Или введите другие имена полей, такие как Last Trade Time (Время последней торговли), Previous Close(Предыдущее закрытия) и Exchange .
Чтобы увидеть все поля, доступные для компании или фонда, щелкните значок акций ( ) или выберите ячейку и нажмите CTRL+SHIFT+F5.
Если вы видите alt=»Значок вопросительного знака» />вместо значка, Excel не удается сопоставить текст с данными в веб-источниках. Исправьте орфографические ошибки и нажмите клавишу ВВОД, чтобы повторить попытку. Кроме того, вы можете щелкнуть alt=»Значок вопросительного знака» />, чтобы открыть область выделения. Выполните поиск по ключевым словам, выделите нужные данные, а затем нажмите кнопку Выбрать.
Вы также можете писать формулы, ссылаясь на типы данных, или использовать функцию STOCKHISTORY.
Данные об акциях задерживаются, предоставляются «как есть» и не являются торговыми целями или рекомендациями. Дополнительные сведения см. в сведениях об источниках данных.
Как получить котировки акций в Excel
Excel
Большинство из нас используют Microsoft Excel в повседневной жизни по-разному. Мы используем его для отслеживания задач, которые должны быть выполнены с использованием цветовой кодировки и людей, занимающихся бизнесом, используем его для отслеживания продуктов, которые были привезены и проданы, и каждый использует их тем или иным способом. Функции Excel облегчают нашу работу по минимизации выполняемой нами задачи. Существует множество встроенных формул, и вы даже можете создавать свои собственные пользовательские функции для расширения функциональности. Помимо использования его для обычных вещей, вы даже можете получить или получить котировки акций в Excel. Итак, давайте посмотрим, как получить котировки акций в Excel .
Получить котировки акций в Excel
Чтобы получить котировки акций в Excel, вам не нужно устанавливать какие-либо дополнительные дополнения к вашему листу Excel. Вы можете использовать MSN MoneyCentral Investor Stock Quotes, встроенное соединение с Excel и получить котировки акций. Я проведу вас через шаги, которым нужно следовать, чтобы достичь этого.
Откройте лист Excel и нажмите на вкладку «Данные». Затем нажмите «Соединения», после чего откроется «Соединения с книгой» и нажмите кнопку «Добавить».
Выберите «Котировки акций MSN MoneyCentral Investor» и дважды щелкните по нему.
Нажмите «Свойства», чтобы открыть всплывающее окно «Свойства подключения». Установите флажок «Включить фоновое обновление» и любые другие параметры, которые вам требуются. Нажмите «ОК» и «Закрыть».
Нажмите на вкладку «Данные» еще раз и нажмите «Существующие подключения». Выберите соединение, которое вы только что добавили, и нажмите кнопку «Открыть», или вы можете дважды щелкнуть по соединению.
Он открывает всплывающее окно «Импорт данных» и спрашивает вас, куда поместить данные. По умолчанию в качестве начальной ячейки отображается $ A $ 1 (ячейка as A1 ’). Вы даже можете выбрать диапазон ячеек, просто перетаскивая мышь над ячейками и нажимая кнопку «ОК».
Он попросит вас «ввести значение параметра» и добавить котировки акций, разделенные запятой. Установите флажки «Использовать это значение/ссылку для будущих ссылок» и «Обновлять автоматически при изменении значения ячейки».
Чтобы обновить данные, выберите ячейку с данными и нажмите на вкладку «Данные». Выберите «Обновить все», а затем «Обновить».
Он работает с Microsoft Excel 2007 и более поздними версиями, включая Excel 2013. Это простой способ получить котировки акций в Excel с помощью MSN Money. Вы даже можете просматривать и график и новости, касающиеся каждой котировки акций. Нет необходимости создавать какие-либо макросы или добавлять сторонние надстройки. Просто добавьте встроенное соединение, и оно просто тянет котировки акций в Excel.
Известно ли вам об этом методе получения биржевых котировок в Excel?
Теперь посмотрим, как можно добавить калькулятор Windows на панель быстрого доступа Excel.
Free API Мосбиржи в формулах Microsoft Excel
Ранее уже писал про получение данных с Московской биржи через формулы Google Таблиц. Однако остался вопрос — можно ли получать эти же данные при локальном использовании Microsoft Excel или его свободного аналога LibreOffice Calc? Без использования скриптов или ручного копирования.
Microsoft Excel с формулами получения данных с Мосбиржи
И на этот вопрос можно дать положительный ответ. Это даже более удобно, поскольку не приходится ожидать загрузки результатов работы функции IMPORTXML в Гугл Таблицах.
Аналогом этой функции в Excel и Calc выступает связка формул: WEBSERVICE (ВЕБСЛУЖБА) + FILTERXML (ФИЛЬТР.XML).
При работе с Microsoft Excel есть некоторые нюансы:
- Эти функции доступны только в Excel 2013 и более поздних версиях для Windows.
- Эти функции не будет возвращать результаты на компьютере Mac.
- Требуется LibreOffice 4.2 и выше.
- Нет ограничений на используемую ОС. Работает под:
Windows Linux Mac OS - Файл Excel .xlsx открывается и работоспособен, но визуально форматирование может быть нарушено.
Протестировал этот файл в Microsoft Excel 2019 под Windows 10 и в LibreOffice Calc 6.4 под Linux Mint 19.3. Под Mac OS у меня возможности протестировать не было.
API Московской биржи в формулах MS Excel
В общем виде все запросы, которые можно отправить к API Мосбиржи есть в справочнике. Но лично для меня этот справочник до сих пор не особо понятен.
Идентификатор режима торгов
В API Московской биржи очень многое зависит от параметра «Идентификатор режима торгов» (primary_boardid), который можно посмотреть прямо у них на сайте через форму поиска.
Идентификатор режима торгов для акций Тинькофф
Также этот идентификатор можно посмотреть через обычный HTTP-запрос к API:
Поиск через HTTP-запрос к API Мосбиржи по слову Пермь
Автоматическое получение имени акций, облигаций и ETF
Очень удобно, что можно получить полное или краткое наименование инструмента. Для облигаций полное название особенно понятно.
Файл « API Мосбиржи в Microsoft Excel.xlsx » с примерами автоматического получения имени для разных классов активов. Корректно работает и в LibreOffice Calc
Автоматическое получение текущих цен
На этой вкладке представлены актуальные примеры для получения цен акций, облигаций и ETF с Московской биржи.
Цена предыдущего дня берётся через PREVADMITTEDQUOTE, а не LAST с 15 минутной задержкой, поскольку по некоторым низко ликвидным инструментам через LAST цены может просто не быть.
Файл « API Мосбиржи в Microsoft Excel.xlsx » с примерами автоматического получения имени для разных классов активов. Корректно работает и в LibreOffice Calc
Автоматическое получение дивидендных выплат для акций
Очень удобная функция Мосбиржи, которая позволяет получать не только значение текущей выплаты, но и историю выплат дивидендов вместе с датами и значениями.
Файл « API Мосбиржи в Microsoft Excel.xlsx » с примерами автоматического получения имени для разных классов активов. Корректно работает и в LibreOffice Calc
Автоматическое получение облигационных выплат
По облигациям (не только корпоративным, но также ОФЗ и еврооблигациям) можно автоматически получать дату выплаты следующего купона и его значение.
Файл « API Мосбиржи в Microsoft Excel.xlsx » с примерами автоматического получения имени для разных классов активов. Корректно работает и в LibreOffice Calc
Автоматическое получение дат оферт
Удобно планировать собственные финансы, получая даты оферт (дата, в которую инвестор или эмитент имеют право досрочно погасить облигацию по цене номинала) автоматически.
Файл « API Мосбиржи в Microsoft Excel.xlsx » с примерами автоматического получения имени для разных классов активов. Корректно работает и в LibreOffice Calc
UPD. Пользователь mixei подсказывает, что автоматическое обновление настраивается через Параметры — Центр управления безопасностью — вкладка Внешнее содержимое — там надо поставить все флажки где не рекомендуется 🙂 Но это на страх и риск пользователей.
API Московской биржи предоставляет широкие возможности, которые гораздо шире чем описанные в данной статье. Это статья своеобразная шпаргалка для долгосрочного частного инвестора, который ведёт учёт в локальном файле на собственном компьютере.
Также хочу отметить, что я никак не связан с Московской биржей и использую ИСС Мосбиржи только в личных интересах.