Free API Мосбиржи в формулах Microsoft Excel +7




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


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

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

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

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

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

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

  • Требуется LibreOffice 4.2 и выше.
  • Нет ограничений на используемую ОС. Работает под:
    image Windows
    image Linux
    image Mac OS
  • Файл Excel .xlsx открывается и работоспособен, но визуально форматирование может быть нарушено.

Собрал работоспособный пример «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 контактирую довольно часто.

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


В 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

Итог


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

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

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

21 апреля 2020 г.

Только зарегистрированные пользователи могут участвовать в опросе. Войдите, пожалуйста.

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

  • 45,7%Microsoft Excel16
  • 34,3%LibreOffice Calc12
  • 14,3%Что-то другое5
  • 5,7%Мне всё равно2




Комментарии (29):

  1. empenoso
    /#21524420

    Предыдущие комментарии: https://habr.com/ru/post/486716/#comment_21227328

    • kernelconf
      /#21524624

      Скажите, Вы не видели в API что-нибудь чтобы хоть как-то связать инструменты срочного рынка с базовыми активами, если они торгуются на Мосбирже? В Квике в таблице «Текущие торги» есть «Базовый актив», но он совсем не совпадает с реальными тикерами. Такое впечатление, что на FORTS своя собственная база, впрочем, это и логично, так как базовым активом может быть что угодно.

      Контанго-бэквордацию хочу считать.

      • empenoso
        /#21525524

        Хороший вопрос, но немного не по теме статьи похоже.

      • empenoso
        /#21525532

        В API есть секция срочного рынка.
        Если Вы хотите делать подобные расчёты, то надо брать цены из разных секций.


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

      • Sonnenwendekind
        /#21525848 / +1

        QUIK — программа для суровых профи, они пользуются короткими кодами

        • empenoso
          /#21525912

          QUIK это нечто. Как 10 лет назад так и сейчас не особо поменялся.

          • Sonnenwendekind
            /#21525998 / +1

            Вы на Bloomberg Terminal гляньте ;) С другой стороны, всякие свистелки в профессиональном софте ни к чему.

            • empenoso
              /#21526012

              Слишком дорогой для частного лица :(

        • empenoso
          /#21525914

          У брокеров удобные мобильные приложения почти у всех появились.

  2. kostarev_v
    /#21528480 / +1

    Добавлю инфы:


    1. Если в запросе к API убрать расширение .xml, то результат выдаст в удобочитаемом html. Так же поддерживаются форматы .json и .csv
    2. Дивиденды мосбиржа выдаёт не все. Например по МРСК Урала история дивидендов у них пустая. По каким-то бумагам вроде не полная.
      3.В истории купонов и амортизаций не верно выдаёт значения номинала бумаги на определенную дату (FACEVALUE), во всех строках почему-то стоит текущее значение, а не то которое было на указанную дату.
      Короче говоря API у мосбиржи очень крутое, но как будто сырое.

    • empenoso
      /#21528506

      Короче говоря API у мосбиржи очень крутое, но как будто сырое.

      Но оно хотя бы без смс и регистрации доступно.

  3. kostarev_v
    /#21529092 / +1

    Мне из всего этого больше всего интересен вывод текущей цены в Excel
    Что мне не понятно или смущает:


    1. Почему-то цена не обновляется автоматически. Как её обновить?
    2. Очень сложная и не универсальная формула… Т.е. надо для каждой бумаги вручную узнать её board_id и подставить в запрос.
      Думаю можно сделать как-то универсальней. Например получать цену не по этой ссылке
      https://iss.moex.com/iss/engines/stock/markets/shares/boards/TQBR/securities?iss.dp=comma&iss.meta=off&iss.only=securities&securities.columns=SECID,PREVADMITTEDQUOTE
      где выдаются цены всех бумаг на доске TQBR, а по этой
      https://iss.moex.com/iss/engines/stock/markets/shares/securities/SBER/?iss.only=marketdata&marketdata.columns=SECID,BOARDID,LAST
      где выдаются цены по конкретной бумаге. Подразумеваем, что тикер SECID нам известен, осталось только выбрать данные по нужной нам доске TQBR. Это наверное тоже можно автоматизировать.
      Через запрос
      https://iss.moex.com/iss/securities/SBER?iss.only=boards&boards.columns=secid,boardid,is_primary
      выбрать все доски бумаги, и из полученного списка получить ту, у которой is_primary = 1
      Либо как в посте через ссылку
      https://iss.moex.com/iss/securities?q=sber&iss.meta=off&securities.columns=secid,primary_boardid
      Получить все бумаги по запросу q= и выбрать из результата ту, у которой SECID совпадает с нашим. Так или иначе мы можем получить primary_board_id и записать его в промежуточную ячейку, а потом уже из неё наверное можно взять значение, что бы отфильтровать предыдущую выборку с ценами.
      Я не разбираюсь в формулах Excel, но мне кажется это возможно сделать…
      Если в формулу ВЕБСЛУЖБА забивать не заранее подготовленный адрес, а сформированный из значений ячеек.

  4. kostarev_v
    /#21531074 / +1

    Нашёл отноительно простой способ обновления данных в формулах ВЕБСЛУЖБА
    (CTRL+ ALT + F9)
    Думаю можно добавить эту комбинацию в виде подсказки на листах

  5. kostarev_v
    /#21532518 / +1

    Настроил эту фишку у себя на сайте при экспорте в Excel. Теперь портфель в excel можно импортировать с этими формулами внутри. Кто хочет — затестите:
    https://izi-invest.ru/10 ссылка — Экспорт в Excel [Auto]

  6. kostarev_v
    /#21537406 / +1

    Столкнулся с проблемой, которую пока не знаю как решить.
    Иногда (что странно) функция ФИЛЬТР.XML не верно распознаёт значения цены с дробной частью. Некоторые значения распознаёт верно, некоторые заменяет каким-то произвольным большим целым числом.


    Проверка показала, что если бы API мосбиржи выдавало добрые числа с запятой, а не с точкой — проблема бы исчезла.


    Пробовал применить функцию ПОДСТАВИТЬ до функции ФИЛЬТР.XML, но тогда замена точек на запятую приводит xml в негодное для этой функции состояние.


    В инструкции к API мосбиржи сказано, что можно изменить знак разделителя, но только для csv формата. Короче я в тупике.

    • empenoso
      /#21537790


      Пробовал применить функцию ПОДСТАВИТЬ до функции ФИЛЬТР.XML, но тогда замена точек на запятую приводит xml в негодное для этой функции состояние.

      Нарушается структура xml, если менять все точки на запятые:
      Оригинал:
      <?xml version=""1.0"" encoding=""UTF-8""?>

      Если менять все точки на запятые:
      <?xml version=""1,0"" encoding=""UTF-8""?> 


      Вот решение:
      =ФИЛЬТР.XML( ПОДСТАВИТЬ( ПОДСТАВИТЬ(ВЕБСЛУЖБА("xxx");".";",");"1,0";"1.0");"ххх")

      • kostarev_v
        /#21538444 / +1

        Да, это сработало! Жаль только, что формула всё усложняется. Хотелось бы проще

        • empenoso
          /#21538544

          Это вряд-ли — обычно только всё усложняется со временем.

  7. kostarev_v
    /#21539552

    Вот ещё вопрос для размышления, может найдётся ответ когда-нибудь:
    Есть ли способ через API мосбиржи узнать, работает ли она сегодня… Или работала ли она в определённую дату? Ну или просто работает ли она прямо сейчас?
    Чтоб в выходные показывала, что не работает, и после закрытия дня тоже показывала что не работает…
    Если надётся ответ, отпишитесь

    • empenoso
      /#21542582

      Вот в телеграм канале подсказывают:


      Ну я рабочие дни брал по датам индекса Мосбиржи например (из iss/history). Это конечно не совсем то, но требуемый результат получите.