empenoso Apr 21 2020 at 12:22

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

3 min

91K

API*Data Mining*Algorithms*Finance in IT

Comments 74

empenoso Apr 21 2020 at 20:38

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

kernelconf Apr 21 2020 at 21:27

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

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

empenoso Apr 22 2020 at 05:55

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

empenoso Apr 22 2020 at 06:01

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

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

Sonnenwendekind Apr 22 2020 at 09:15

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

empenoso Apr 22 2020 at 09:39

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

Sonnenwendekind Apr 22 2020 at 10:06

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

empenoso Apr 22 2020 at 10:11

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

empenoso Apr 22 2020 at 09:39

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

kostarev_v Apr 22 2020 at 17:28

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

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

empenoso Apr 22 2020 at 17:30

Хорошее дополнение

empenoso Apr 22 2020 at 17:31

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

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

empenoso Apr 22 2020 at 19:00

А вот кстати мне на канале t.me/RationalAnswer_Chat что ответили на счёт FACEVALUE:

Тут дело вот в чём. Если не фильтровать узлы, то замечал ли, что там есть узел securities, узел marketdata и ещё какие-то?

Так вот, securities — это _статические_ данные они неизменны в течение дня. (Чтоб их было достаточно один раз выкачать, а потом не указывать в последующих запросах.)

Если нужны какие-то динамические данные: текущая цена, исторические данные, etc. то они точно не будут в узле securities. То есть надо смотреть аналог колонки FACEVALUE в других узлах (history, или как он там, не помню точно).

Поэтому, скажем, «стабильная» цена PREVADMITTEDQUOTE находится в securities, а «переменная» цена LCURRENTPRICE в marketdata, например:

https://iss.moex.com/iss/engines/stock/markets/shares/boards/TQTF/securities.json?iss.meta=off&iss.only=marketdata,securities&marketdata.columns=SECID,LCURRENTPRICE&securities.columns=SECID,PREVADMITTEDQUOTE,FACEVALUE&securities=FXRL

empenoso Apr 22 2020 at 19:04

Просто я пользуюсь history для получения facevalue по ОФЗ-ИН и там вроде как всё работало

Да, я и говорю, нужно взять FACEVALUE из history, не из securities.

https://iss.moex.com/iss/history/engines/stock/markets/bonds/boards/TQOB/securities/SU52001RMFS3?history.columns=TRADEDATE,FACEVALUE&from=2020-01-10

kostarev_v Apr 22 2020 at 19:53

Да, по этой ссылке выдаёт верную информацию.
Я пользовался вот этой https://iss.moex.com/iss/statistics/engines/stock/markets/bonds/bondization/RU000A0JVSY3
для получения купонов и амортизаций и от туда же FACEVALUE тянул… Вот там оно всегда текущее, не смотря на даты.

kostarev_v Apr 22 2020 at 19:18

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

Почему-то цена не обновляется автоматически. Как её обновить?
Очень сложная и не универсальная формула… Т.е. надо для каждой бумаги вручную узнать её 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, но мне кажется это возможно сделать…
Если в формулу ВЕБСЛУЖБА забивать не заранее подготовленный адрес, а сформированный из значений ячеек.

kostarev_v Apr 22 2020 at 19:34

Всё, нашёл решение:
В ячейку А1 записываем тикер бумаги (например SBER)
Во второй столбец записываем BOARDID. Просто вставляем формулу
=ПОДСТАВИТЬ(ФИЛЬТР.XML(ВЕБСЛУЖБА("https://iss.moex.com/iss/securities/"&A1&".xml?iss.meta=off&iss.only=boards&boards.columns=secid,boardid,is_primary");"//document//data//rows//row[@is_primary=1]/@boardid");".";",")
В третий столбец пишем цену, просто вставляем формулу
=ПОДСТАВИТЬ(ФИЛЬТР.XML(ВЕБСЛУЖБА("https://iss.moex.com/iss/engines/stock/markets/shares/securities/"&A22&".xml?iss.meta=off&iss.only=marketdata&marketdata.columns=SECID,BOARDID,LAST");"//document//data//rows//row[@BOARDID='"&B1&"']/@LAST");".";",")
Дальше просто протягиваем эти формулы вниз по всем тикерам и получаем все цены.
Я молодец, и ты молодец… мы молодцы.

Вопрос с обновлением цены при открытии файла для меня остаётся открытым.

kostarev_v Apr 22 2020 at 19:35

ошибка во второй формуле вместо A22 должно A1 быть.

empenoso Apr 23 2020 at 12:54

Перезаписал файл «API Мосбиржи в Microsoft Excel.xlsx» вместе с Вашим решением.

kostarev_v Apr 23 2020 at 13:01

Клёво, только с облигациями это не сработает, формулу редактировать придётся.
Вместо shares ставить bonds во второй формуле… При желании конечно и это можно автоматизировать.

krllp May 6 2020 at 15:46

Может кому пригодится, еще есть вариант использовать вместо:

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

такой вариант:

https://iss.moex.com/iss/engines/stock/markets/shares/boardgroups/57/securities.xml?iss.dp=comma&iss.meta=off&iss.only=securities&securities.columns=SECID,PREVADMITTEDQUOTE

В первом варианте необходимо по каждому борду проверять, а во втором по группе бордов. Группа 57 объединяет весь основной режим Т+, скорее всего там будут все нужные вам акции и одним запросом, точно также, думаю и для облигаций можно

empenoso May 6 2020 at 15:47

Спасибо! Интересно.

empenoso May 6 2020 at 15:51

Группа 57 объединяет весь основной режим Т+, скорее всего там будут все нужные вам акции и одним запросом, точно также, думаю и для облигаций можно

Но для облигаций это не работает или ссылка должна быть другая:
https://iss.moex.com/iss/engines/stock/markets/bonds/boardgroups/57/securities.xml?iss.dp=comma&iss.meta=off&iss.only=securities&securities.columns=SECID,PREVADMITTEDQUOTE

В общем виде ведь ссылка

https://iss.moex.com/iss/engines/stock/markets/${markets}/boards/${boardID}/securities.json?iss.meta=off&iss.only=${section}&${section}.columns=${columns}

krllp May 6 2020 at 19:10

Да, покопался тоже, с облигациями чуть сложнее, как я понял основные группы по облигациям две(T0 и T+ основных режимов), их id: 7 и 58, соответственно ссылки:
https://iss.moex.com/iss/engines/stock/markets/bonds/boardgroups/7/securities.xml?iss.dp=comma&iss.meta=off&iss.only=securities&securities.columns=SECID,PREVADMITTEDQUOTE
https://iss.moex.com/iss/engines/stock/markets/bonds/boardgroups/58/securities.xml?iss.dp=comma&iss.meta=off&iss.only=securities&securities.columns=SECID,PREVADMITTEDQUOTE

empenoso May 6 2020 at 20:08

Спасибо, это ценная информация.

olegpoltoraname Jun 30 2020 at 01:18

Ваша ссылка показывает ту сумму, которую отображает сбербанк-инвестиции.
Но как ее подключить в экселе? Ведь там уже идет не xml и нужен другой обвес…

empenoso Jun 30 2020 at 06:10

В Экселе если не xml на самом деле не очень. Просто кроме него возможны и другие варианты.

kostarev_v Jun 30 2020 at 06:55

В адресную строку добавь .xml и выдаст в .xml

kostarev_v Apr 23 2020 at 10:30

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

empenoso Apr 23 2020 at 12:54

Перезаписал файл «API Мосбиржи в Microsoft Excel.xlsx».

kostarev_v Apr 23 2020 at 15:14

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

kostarev_v Apr 24 2020 at 16:40

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

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

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

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

empenoso Apr 24 2020 at 17:45

Пробовал применить функцию ПОДСТАВИТЬ до функции ФИЛЬТР.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 Apr 24 2020 at 20:15

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

empenoso Apr 24 2020 at 20:37

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

protorus Apr 30 2020 at 08:31

а если где-то в структуре xml-ответа будет такое значение, как value=«1.05», то такой способ все сломает

empenoso Apr 30 2020 at 08:32

Да, это костыль, но Вы можете предложить решение?

protorus Apr 30 2020 at 10:54

Грамотного ничего, но можно костыль подправить, если совсем просто, то например так, =ФИЛЬТР.XML(ЗАМЕНИТЬ(ПОДСТАВИТЬ(ВЕБСЛУЖБА(«xxx»);".";",");17;1;".");«xxx»)

empenoso Apr 30 2020 at 11:08

Не могу понять а 17 — это откуда?

protorus Apr 30 2020 at 11:21

это позиция точки в строке

<?xml version="1.0" encoding="UTF-8"?>

empenoso Apr 30 2020 at 11:54

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

kostarev_v May 9 2020 at 20:15

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

Crypto Sep 20 2021 at 17:48

Столкнулся с этой же проблемой и выяснил, что это результат неправильной интерепретации функцией ФИЛЬТР.XML значения как... даты.

Возьмем пример значения 7.95.

Если просто ввести это число в ячейку Excel, то в ячейке появится текст июл.95, а в поле редактирования будет стоять 01.07.1995. Это потому, что в Excel сработал механизм автоматического "угадывания" формата значения, и он решил, что пользователь ввёл дату.

Теперь, если поменять формат ячейки на "Общий" (там сначала будет стоять (все форматы)), то в поле появится... целое число 34881.

Именно это происходит в функции ФИЛЬТР.XML для некоторых котировок (т.е. когда сервис MOEX возвращает, например, LAST="7.95"). Функция возвращает неправильно интерпретируемое значение уже в общем формате, как целое число.

К сожалению, с этим целочисленным результатом ничего не получится сделать, изменение формата ячейки не помогает. Как заставить саму функцию ФИЛЬТР.XML НЕ интерпретировать подобные значения как даты, я пока так не нашёл. Временно прикрутил "костыль", похожий на предложенный вами - заменяю точки на запятые в исходном xml-респонсе, безопасно обходя version="1.0":

=ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(A2;"version=""1.0""";"%version%");".";",");"%version%";"version=""1.0""")(A2 содержит xml-респонс сервиса)

kostarev_v Apr 25 2020 at 07:58

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

empenoso Apr 26 2020 at 04:48

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

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

Lubiviy_Alexander May 30 2020 at 01:07

Очень занимательная статья.
Было бы интересно почитать похожую статью про получение финансовых прогнозов с рбк и технического анализа с investing com.

Также интересно почитать как вы применяете данные знания для аналитики: позволяет ли это анализировать акции «на потоке», возможно образовались какие-нибудь удачные паттерны анализа?

empenoso May 30 2020 at 12:21

Через пару недель опубликую статью про поиск облигаций на Мосбирже.

empenoso May 30 2020 at 12:21

И планирую написать про получение любых данных с яху финанс через API.

empenoso May 30 2020 at 12:22

Лично я применяю данные знания для учета активов. Как здесь, например.

rick1177 Jul 1 2020 at 00:53

Подскажите, пожалуйста, при запросе бумаге вот по такому коду 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

empenoso Jul 1 2020 at 05:56

Ответ апи корректный:

<row SECID="LSNG" PREVADMITTEDQUOTE="6.03"/>

Формула ещё заменяет точку на запятую. А какая у вас локаль установлена?

rick1177 Jul 1 2020 at 13:51

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

empenoso Jul 1 2020 at 14:02

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

rick1177 Jul 1 2020 at 15:19

Именно это он и делал. Причём делал он это на этапе применения функции «ФЛЬТР.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

protorus Jul 7 2020 at 16:44

у тебя порядок другой, сначала выполняется ФИЛЬТР.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")

olegpoltoraname Aug 31 2020 at 12:52

Недавно МосБиржа запустила торги акций иностранных эмитентов из индекса S&P 500.
Но я не могу через API получит доступ к курсу бумаг.
Использовал и board FQBR и другие — Мосбиржа знает о бумаге (MSFT-RM), но данные по торгам пустые. Возможно не там ищу.
Если кто разбирался, подскажите, пожалуйста.

empenoso Aug 31 2020 at 12:55

Приведите полную ссылку — где искали?

olegpoltoraname Aug 31 2020 at 13:22

Тут я узнал про board FQBR и что биржа в принципе знает о бумаге и ее тикет верный:
iss.moex.com/iss/securities.xml?q=MSFT-RM

Использовал board:
iss.moex.com/iss/engines/stock/markets/shares/boards/FQBR/securities.xml?
но тут поля rows пустые, хотя другая информация присутствует.

Для интереса прошелся по некоторым board из:
iss.moex.com/iss/engines/stock/markets/shares
но в них так же поле rows пустое.

empenoso Aug 31 2020 at 20:41

Непонятно, да. Может быть в другой секции: iss.moex.com/iss/engines
А может быть информация доступна только по подписке.

Путь вроде правильный, но там пусто: iss.moex.com/iss/engines/stock/markets/shares/boards/FQBR/securities

Две бумаги кстати находит: iss.moex.com/iss/securities.xml?q=MSFT

olegpoltoraname Aug 31 2020 at 21:35

Там почти все из топ10 S&P500 есть. Странно, что не сам топ, а выборочно.
К концу года обещают 30, и каждый квартал добавлять, но без api не представляю что делать.

empenoso Sep 1 2020 at 07:53

Вообще, можно использовать другой апи — например вот тоже бесплатный.

empenoso Sep 1 2020 at 09:55

Проглядели мы с вами. Всё у них есть в новой секции foreignshares:
iss.moex.com/iss/engines/stock/markets/foreignshares/boards/FQBR/securities

А была тут информация: iss.moex.com/iss/engines/stock/markets

olegpoltoraname Sep 1 2020 at 18:10

«foreignshares»!
Спасибо! Пишу коммент благодарности, т.к. не могу проголосовать за ответ.

olegpoltoraname Sep 1 2020 at 18:56

Странно, что api не знает об apple.
В S&P500 Apple на первом месте с 7.3%
В документе Мосбиржи «Презентация „Торги иностранными ценными бумагами“» apple есть — fs.moex.com/files/21231

empenoso Sep 1 2020 at 19:02

Может быть по ним торги ещё не начались на Мосбирже?

olegpoltoraname Sep 4 2020 at 19:06

А как подобное сделать и для СПб? Там ведь больше представлено акций иностранных компаний. У меня только недавно интерес сместился от etf к «прямым» акциям и сейчас ищу информацию об избежании двойного налогообложения и на Мосбирже и на СПб.
У вас есть интерес к СПб бирже?

empenoso Sep 4 2020 at 19:09

А у СПб разве есть апи?

olegpoltoraname Sep 5 2020 at 19:31

что-то есть, но я не ковырял его еще.

empenoso Sep 5 2020 at 19:39

Ссылкой поделитесь?

olegpoltoraname Sep 5 2020 at 20:20

spbexchange.ru/ru/otc_market/repository/api

kostarev_v Sep 10 2020 at 15:16

искал я в своё время способ добыть цены, свечи, дивиденды по бумагам с спб биржи… Так ни чего и не нашёл… Этот апи по ссылке тоже видел, но не осилил. В итоге сделал всё на yahoo finance api. Оно вроде как не официальное и без документации, но вполне работоспособное, если покапаться
query1.finance.yahoo.com/v8/finance/chart/AAPL?symbol=AAPL&period1=1560193200&period2=1585150003&interval=1d&includePrePost=true&events=div%7Csplit%7Cearn&

empenoso Sep 10 2020 at 19:21

Писал про него: Все финансовые рынки мира в API Яху Финанс.

kostarev_v Sep 10 2020 at 19:30

Точно, а я как-то проглядел. Полезная статья

dbax Jul 28 2023 at 14:13

Насколько я понял ФИЛЬТР.XML использует функции операционной системы Windows, поэтому она не возвращает результаты на компьютере Mac. Увы.

На линуксах вроде работает.