Котировки ммвб в excel

Ранее уже писал про получение данных с Московской биржи через формулы Google Таблиц. Однако остался вопрос — можно ли получать эти же данные при локальном использовании Microsoft Excel или его свободного аналога LibreOffice Calc? Без использования скриптов или ручного копирования.

Microsoft Excel с формулами получения данных с Мосбиржи

И на этот вопрос можно дать положительный ответ. Это даже более удобно, поскольку не приходится ожидать загрузки результатов работы функции IMPORTXML в Гугл Таблицах.

Аналогом этой функции в Excel и Calc выступает связка формул: WEBSERVICE (ВЕБСЛУЖБА) + FILTERXML (ФИЛЬТР.XML).

При работе с Microsoft Excel есть некоторые нюансы:

• Эти функции доступны только в Excel 2013 и более поздних версиях для Windows.
• Эти функции не будет возвращать результаты на компьютере Mac.

Для LibreOffice Calc подобных ограничений меньше:

Собрал работоспособный пример «API Мосбиржи в Microsoft Excel.xlsx» с функциями, которые описаны в программном интерфейсе к информационно-статистическому серверу Московской Биржи (ИСС / ISS). Плюс добавил некоторые функции, которые были найдены читателями первой части статьи про гугл таблицы.

Протестировал этот файл в Microsoft Excel 2019 под Windows 10 и в LibreOffice Calc 6.4 под Linux Mint 19.3. Под Mac OS у меня возможности протестировать не было.

API Московской биржи в формулах MS Excel

В общем виде все запросы, которые можно отправить к API Мосбиржи есть в справочнике. Но лично для меня этот справочник до сих пор не особо понятен.

Идентификатор режима торгов

В API Московской биржи очень многое зависит от параметра «Идентификатор режима торгов» (primary_boardid), который можно посмотреть прямо у них на сайте через форму поиска.

Идентификатор режима торгов для акций Тинькофф

Также этот идентификатор можно посмотреть через обычный HTTP-запрос к API:

https://iss.moex.com/iss/securities.xml?q=ТУТ ПИШЕМ НАЗВАНИЕ ИНСТРУМЕНТА ИЛИ ЕГО ЧАСТЬ&iss.meta=off&securities.columns=name,emitent_inn,isin,secid,primary_boardid

Поиск через 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 Московской биржи предоставляет широкие возможности, которые гораздо шире чем описанные в данной статье. Это статья своеобразная шпаргалка для долгосрочного частного инвестора, который ведёт учёт в локальном файле на собственном компьютере.

Также хочу отметить, что я никак не связан с Московской биржей и использую ИСС Мосбиржи только в личных интересах.

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

21 апреля 2020 г.

Microsoft Excel или LibreOffice Calc для локального использования?

Проголосовали 90 пользователей.

Воздержались 11 пользователей.

ВНИМАНИЕ! КОММЕНТАРИИ ПЕРВОГО УРОВНЯ В ВОПРОСАХ УПОРЯДОЧИВАЮТСЯ ПО ЧИСЛУ ПЛЮСИКОВ, А НЕ ПО ВРЕМЕНИ ПУБЛИКАЦИИ.

Только зарегистрированные и авторизованные пользователи могут оставлять ответы.

Залогиниться

Зарегистрироваться

Файлы к уроку:

• Для спонсоров Boosty
• Для спонсоров VK

Ссылки:

• Страница курса
• Плейлист YouTube
• Плейлист ВК

Описание

В этом уроке мы научимся получать прямо в Excel котировки разных ценных бумаг с сайта Московской биржи.

Решение

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

Если мы введем такую ссылку в Power Query, создав запрос из интернета, то получим интересующие нас котировки прямо в Excel.

Вот ссылка на сайте Московской биржи https://iss.moex.com/iss/reference/ На этой странице вы можете увидеть какую именно информацию вы можете получить с сайта Московской биржи. Котировками дело не ограничивается.

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

Наша ссылка будет выглядеть так — https://iss.moex.com/iss/history/engines/stock/markets/shares/securities/SBER/securities.xml

В том месте, где написано SBER нужно вставить тикер интересующей вас ценной бумаги. Далее, развернув таблицу, вы получите таблицу с котировками, но только 100 строк. Это максимальное количество, которое позволено выгружать. Нам нужно что-то придумать, чтобы выгружать больше строк.

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

Примененные функции

• Xml.Tables
• Web.Contents
• List.Numbers
• Table.FromList
• Splitter.SplitByNothing
• ExtraValues.Error
• Table.AddColumn
• Text.From
• Table.SelectColumns
• Table.ExpandTableColumn
• Table.TransformColumnNames
• Text.AfterDelimiter

Код

Пример получения 100 строк:

let
   source = Xml.Tables(
      Web.Contents(
         "http://iss.moex.com/iss/history/engines/stock/markets/shares/securities/SBER/securities.xml?start=300"
      )
   ){0}[Table]{0}[rows]{0}[row]
in
   source

Пример получения 4000 строк:

let
   source = List.Numbers(1, 40, 100),
   to_table = Table.FromList(
      source,
      Splitter.SplitByNothing(),
      null,
      null,
      ExtraValues.Error
   ),
   table_add_col = Table.AddColumn(
      to_table,
      "Пользовательская",
      each Xml.Tables(
         Web.Contents(
            "http://iss.moex.com/iss/history/engines/stock/markets/shares/securities/SBER/securities.xml?start="
               & Text.From([Column1])
         )
      ){0}[Table]{0}[rows]{0}[row]
   ),
   cols_select = Table.SelectColumns(table_add_col, {"Пользовательская"}),
   col_expand_table = Table.ExpandTableColumn(
      cols_select,
      "Пользовательская",
      {
         "Attribute:BOARDID",
         "Attribute:TRADEDATE",
         "Attribute:SHORTNAME",
         "Attribute:SECID",
         "Attribute:NUMTRADES",
         "Attribute:VALUE",
         "Attribute:OPEN",
         "Attribute:LOW",
         "Attribute:HIGH",
         "Attribute:LEGALCLOSEPRICE",
         "Attribute:WAPRICE",
         "Attribute:CLOSE",
         "Attribute:VOLUME",
         "Attribute:MARKETPRICE2",
         "Attribute:MARKETPRICE3",
         "Attribute:ADMITTEDQUOTE",
         "Attribute:MP2VALTRD",
         "Attribute:MARKETPRICE3TRADESVALUE",
         "Attribute:ADMITTEDVALUE",
         "Attribute:WAVAL"
      },
      {
         "Attribute:BOARDID",
         "Attribute:TRADEDATE",
         "Attribute:SHORTNAME",
         "Attribute:SECID",
         "Attribute:NUMTRADES",
         "Attribute:VALUE",
         "Attribute:OPEN",
         "Attribute:LOW",
         "Attribute:HIGH",
         "Attribute:LEGALCLOSEPRICE",
         "Attribute:WAPRICE",
         "Attribute:CLOSE",
         "Attribute:VOLUME",
         "Attribute:MARKETPRICE2",
         "Attribute:MARKETPRICE3",
         "Attribute:ADMITTEDQUOTE",
         "Attribute:MP2VALTRD",
         "Attribute:MARKETPRICE3TRADESVALUE",
         "Attribute:ADMITTEDVALUE",
         "Attribute:WAVAL"
      }
   ),
   types = Table.TransformColumnNames(
      col_expand_table,
      each Text.AfterDelimiter(_, ":")
   )
in
   types

Этот урок входит в курс Веб-запросы в Power Query

Программа предназначена для импорта котировок в Excel с сервера РБК.

Загружаются котировки ММВБ СЭЛТ (realtime) по активным инструментам.

Поскольку штатными средствами Excel (веб-запрос) загрузить данные в Excel не удаётся, — и был написан этот макрос.

Кроме того, преимущество макроса (программной загрузки данных с сайта) перед веб-запросом — отсутствие уведомлений об ошибке доступа
(если сайт недоступен, или доступ в интернет осуществляется с перебоями)

Программа берёт данные не с самой веб-страницы, а выполняет запрос напрямую к серверу
(ответ приходит в формате JSON)

Макрос анализирует ответ сервера, формирует таблицу, и выводит её на лист,
заменяя ранее вставленные данные.

Обновление котировок происходит автоматически, с интервалом в 30 минут
(время можно задать в настройках программы)

Большинство из нас используют 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.

let
    Источник = List.Numbers(1,1000,100),
    #"Преобразовано в таблицу" = Table.FromList(Источник, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Измененный тип" = Table.TransformColumnTypes(#"Преобразовано в таблицу",{{"Column1", type text}}),
    dt_from = DateTime.ToText(Excel.CurrentWorkbook(){[Name="Date_Parametr"]}[Content]{0}[Date From],"yyyy-MM-dd"),
    #"Добавлен пользовательский объект" = Table.AddColumn(#"Измененный тип", "Пользовательская", each Xml.Tables(Web.Contents("https://iss.moex.com/iss/history/engines/stock/markets/shares/securities.xml?date="&dt_from&"&..."), null){0}[Table]{0}[rows]{0}[row]),
    #"Удаленные ошибки" = Table.RemoveRowsWithErrors(#"Добавлен пользовательский объект", {"Пользовательская"}),
    #"Развернутый элемент Пользовательская" = Table.ExpandTableColumn(#"Удаленные ошибки", "Пользовательская", {"Attribute:BOARDID", "Attribute:TRADEDATE", "Attribute:SHORTNAME", "Attribute:SECID", "Attribute:NUMTRADES", "Attribute:VALUE", "Attribute:OPEN", "Attribute:LOW", "Attribute:HIGH", "Attribute:LEGALCLOSEPRICE", "Attribute:WAPRICE", "Attribute:CLOSE", "Attribute:VOLUME", "Attribute:MARKETPRICE2", "Attribute:MARKETPRICE3", "Attribute:ADMITTEDQUOTE", "Attribute:MP2VALTRD", "Attribute:MARKETPRICE3TRADESVALUE", "Attribute:ADMITTEDVALUE", "Attribute:WAVAL", "Attribute:TRADINGSESSION"}, {"Пользовательская.Attribute:BOARDID", "Пользовательская.Attribute:TRADEDATE", "Пользовательская.Attribute:SHORTNAME", "Пользовательская.Attribute:SECID", "Пользовательская.Attribute:NUMTRADES", "Пользовательская.Attribute:VALUE", "Пользовательская.Attribute:OPEN", "Пользовательская.Attribute:LOW", "Пользовательская.Attribute:HIGH", "Пользовательская.Attribute:LEGALCLOSEPRICE", "Пользовательская.Attribute:WAPRICE", "Пользовательская.Attribute:CLOSE", "Пользовательская.Attribute:VOLUME", "Пользовательская.Attribute:MARKETPRICE2", "Пользовательская.Attribute:MARKETPRICE3", "Пользовательская.Attribute:ADMITTEDQUOTE", "Пользовательская.Attribute:MP2VALTRD", "Пользовательская.Attribute:MARKETPRICE3TRADESVALUE", "Пользовательская.Attribute:ADMITTEDVALUE", "Пользовательская.Attribute:WAVAL", "Пользовательская.Attribute:TRADINGSESSION"}),
    #"Измененный элемент Пользовательская" = Table.TransformColumnTypes(#"Развернутый элемент Пользовательская",{{"Пользовательская.Attribute:BOARDID", type text}, {"Пользовательская.Attribute:TRADEDATE", type date}, {"Пользовательская.Attribute:SHORTNAME", type text}, {"Пользовательская.Attribute:SECID", type text}, {"Пользовательская.Attribute:NUMTRADES", type number}, {"Пользовательская.Attribute:OPEN", type number},{"Пользовательская.Attribute:LOW", type number}, {"Пользовательская.Attribute:HIGH", type number}, {"Пользовательская.Attribute:LEGALCLOSEPRICE", type number}, {"Пользовательская.Attribute:WAPRICE", type number}, {"Пользовательская.Attribute:CLOSE", type number}, {"Пользовательская.Attribute:VOLUME", type number}, {"Пользовательская.Attribute:MARKETPRICE2", type number}, {"Пользовательская.Attribute:MARKETPRICE3", type number}, {"Пользовательская.Attribute:ADMITTEDQUOTE", type number},  {"Пользовательская.Attribute:MP2VALTRD", type number}, {"Пользовательская.Attribute:MARKETPRICE3TRADESVALUE", type number},{"Пользовательская.Attribute:ADMITTEDVALUE",type number}, {"Пользовательская.Attribute:WAVAL",type number}, {"Пользовательская.Attribute:TRADINGSESSION", type number}},"en-GB"),
    #"Другие удаленные столбцы" = Table.SelectColumns(#"Измененный элемент Пользовательская",{"Пользовательская.Attribute:BOARDID", "Пользовательская.Attribute:TRADEDATE", "Пользовательская.Attribute:SHORTNAME", "Пользовательская.Attribute:SECID", "Пользовательская.Attribute:NUMTRADES", "Пользовательская.Attribute:VALUE", "Пользовательская.Attribute:OPEN", "Пользовательская.Attribute:LOW", "Пользовательская.Attribute:HIGH", "Пользовательская.Attribute:LEGALCLOSEPRICE", "Пользовательская.Attribute:WAPRICE", "Пользовательская.Attribute:CLOSE", "Пользовательская.Attribute:VOLUME", "Пользовательская.Attribute:MARKETPRICE2", "Пользовательская.Attribute:MARKETPRICE3", "Пользовательская.Attribute:ADMITTEDQUOTE", "Пользовательская.Attribute:MP2VALTRD", "Пользовательская.Attribute:MARKETPRICE3TRADESVALUE", "Пользовательская.Attribute:ADMITTEDVALUE", "Пользовательская.Attribute:WAVAL", "Пользовательская.Attribute:TRADINGSESSION"}),
    Пользовательская1 = Table.TransformColumnNames(#"Другие удаленные столбцы", each Text.AfterDelimiter(_,":"))
in
    Пользовательская1