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

    Ранее уже писал про получение данных с Московской биржи через формулы 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 для локального использования?

    • 50,0%Microsoft Excel24
    • 29,2%LibreOffice Calc14
    • 14,6%Что-то другое7
    • 6,2%Мне всё равно3
    AdBlock похитил этот баннер, но баннеры не зубы — отрастут

    Подробнее
    Реклама

    Комментарии 54

      0

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

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

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

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

            0

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


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

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

                0

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

                  +1

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

                    0

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

                  0

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

              +1

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


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

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

              +1

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

                +1

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


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


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


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

                  0

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

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

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


                  Вот решение:
                  =ФИЛЬТР.XML( ПОДСТАВИТЬ( ПОДСТАВИТЬ(ВЕБСЛУЖБА("xxx");".";",");"1,0";"1.0");"ххх")
                    +1
                    Да, это сработало! Жаль только, что формула всё усложняется. Хотелось бы проще
                      0

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

                      +1
                      а если где-то в структуре xml-ответа будет такое значение, как value=«1.05», то такой способ все сломает
                        0
                        Да, это костыль, но Вы можете предложить решение?
                          +1
                          Грамотного ничего, но можно костыль подправить, если совсем просто, то например так, =ФИЛЬТР.XML(ЗАМЕНИТЬ(ПОДСТАВИТЬ(ВЕБСЛУЖБА(«xxx»);".";",");17;1;".");«xxx»)
                            0
                            Не могу понять а 17 — это откуда?
                              +1
                              это позиция точки в строке
                              <?xml version="1.0" encoding="UTF-8"?>
                                0

                                Более хитрый костыль :)

                                  +1
                                  я просто менял =«1,0» на =«1.0»
                                  ПОДСТАВИТЬ(«XXX»;"="«1,0»;"="«1.0»);
                    +1

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

                      0

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


                      Ну я рабочие дни брал по датам индекса Мосбиржи например (из iss/history). Это конечно не совсем то, но требуемый результат получите.
                      +1
                      Очень занимательная статья.
                      Было бы интересно почитать похожую статью про получение финансовых прогнозов с рбк и технического анализа с investing com.

                      Также интересно почитать как вы применяете данные знания для аналитики: позволяет ли это анализировать акции «на потоке», возможно образовались какие-нибудь удачные паттерны анализа?
                        0
                        Через пару недель опубликую статью про поиск облигаций на Мосбирже.
                          0
                          И планирую написать про получение любых данных с яху финанс через API.
                            0
                            Лично я применяю данные знания для учета активов. Как здесь, например.
                            +1
                            Подскажите, пожалуйста, при запросе бумаге вот по такому коду LSNG выводится ну что-то очень странное (цена не «цена» а что-то другое).

                            Формула выглядит так:
                            =ПОДСТАВИТЬ( ФИЛЬТР.XML(ВЕБСЛУЖБА("https://iss.moex.com/iss/engines/stock/markets/shares/boards/TQBR/securities.xml?iss.dp=comma&iss.meta=off&iss.only=securities&securities.columns=SECID,PREVADMITTEDQUOTE");"//document//data//rows//row[@SECID='"&A1&"']/@PREVADMITTEDQUOTE");".";",")

                            В ячейке A1 указывается LSNG
                              0
                              Ответ апи корректный:
                              <row SECID="LSNG" PREVADMITTEDQUOTE="6.03"/>

                              Формула ещё заменяет точку на запятую. А какая у вас локаль установлена?
                                +1
                                Локаль стоит русская. Попробовал сменить всё на английский — не изменилась ситуация.
                                Если подставить «GAZP» или «SBER» — всё корректно. Именно на этой бумаге почему-то не так. Причём название он её правильно определил, а в качестве цены выставил «43896». Вот файл: yadi.sk/i/dJ2I3hJRINEJBw
                                Не могу понять где зарылся ответ.
                                  0

                                  Возможно, что-то с форматами не так указано. На вид как будто дата. Как будто он распознаёт это число как дату.

                                    +1
                                    Именно это он и делал. Причём делал он это на этапе применения функции «ФЛЬТР.XML». На текущий момент костыльное решение выглядит так:
                                    =ПОДСТАВИТЬ(ФИЛЬТР.XML(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ВЕБСЛУЖБА("https://iss.moex.com/iss/engines/stock/markets/shares/boards/TQBR/securities.xml?iss.dp=comma&iss.meta=off&iss.only=securities&securities.columns=SECID,PREVADMITTEDQUOTE");".";"ю");"ю";".";1);"//document//data//rows//row[@SECID='"&A1&"']/@PREVADMITTEDQUOTE");"ю";",")*1
                                      +1
                                      у тебя порядок другой, сначала выполняется ФИЛЬТР.XML, затем ПОДСТАВИТЬ, а нужно наоборот, сначала нужно правильно заменить точки на запятые, затем уже ФИЛЬТР.XML
                                      Например так,
                                      A1 = LSNG
                                      A2 = ВЕБСЛУЖБА(«iss.moex.com/iss/engines/stock/markets/shares/boards/TQBR/securities.xml?iss.meta=off&iss.only=securities&securities.columns=SECID,PREVADMITTEDQUOTE»)
                                      A3 = ПОДСТАВИТЬ(A2;".";",")
                                      A4 = ЗАМЕНИТЬ(A3;17;1;".")
                                      A5 = ФИЛЬТР.XML(A4;"//document//data//rows//row[@SECID='"&A1&"']/@PREVADMITTEDQUOTE")

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

                            Самое читаемое