Блог им. Camarada

Получение котировок и других параметров нашего рынка в google spreadsheets

Для смартлабовца иногда важно вести какие-либо вычисления по бумагам в режиме лайв.
Да, есть DDE в Квике, но у меня например нет такой возможности на работе. А в гуглспредшитс можно заглянуть прямо с телефона. Гораздо мобильнее получается.

Про функцию GOOGLEFINANCE слышали многие, там можно например получить курсы валют или какие-то основные акции. Но например цены и прочие параметры облигаций там не получить.

Итак, у нас есть открытые данные Мосбиржи в виде древовидной структуры, которые лежат по адресу https://iss.moex.com/iss/engines/

Если тыркнуть по ссылке, увидим список основных площадок.

Как с этим работать

Допустим, нам нужны котировки какой-нибудь ОФЗ, например 26222.
Облигации — это у нас фондовый рынок или stock 
Открываем ссылку https://iss.moex.com/iss/engines/stock/markets/ там мы увидим все субрынки фондового рынка. Нам нужны bonds
Идем по адресу https://iss.moex.com/iss/engines/stock/markets/bonds, видим список площадок с различными режимами 
ОФЗ у нас соответствуют режиму «Т+: Облигации — безадрес.» или TQOB
Чтобы посмотрет все данные об облигациях https://iss.moex.com/iss/engines/stock/markets/bonds/boards/tqob/securities.xml
Вот отсюда мы и будем дергать данные с помощью функции с помощью функции IMPORTXML, которая позволяет искать по этому xml-документу с помощью языка XPath

Вот способы получить некоторые поля
Получение котировок и других параметров нашего рынка в google spreadsheets

Надеюсь схема понятна.
Можно например вывести все цены 
=IMPORTXML(«iss.moex.com/iss/engines/stock/markets/bonds/boards/TQOB/securities.xml», "//row/@MARKETPRICE")

Результат заполнит N ячеек вниз

Получение котировок и других параметров нашего рынка в google spreadsheets


P.S. Внимание!

Мне уже 2 человека пожаловались, что «не работает». Дело в том, что из-за того, что данные с iss.moex.com импортируются с точками в качестве разделителей для вычислений я переключил региональные настройки своей таблицы на американские. Поэтому, например, разделитель между аргументами функций таблиц изменился с точки запятой на запятую.
Переключить настройки можно в меню Файл->Настройки таблицы->Общие->Региональные настройки
Ну или можно использовать в качестве разделителя аргументов функций точку с запятой (тогда правда у вас могут быть сложности с дополнительными вычислениями, так как дробные числа не будут трактоваться как числа, поэтому рекомендую первый вариант)
★41 | ₽ 96
48 комментариев
а есть способ вытягивать данные со страниц смартлаба?
smart-lab.ru/q/NKNC/f/y/
От Лонга! Я тебя умоляю!, здесь нет такой жесткой структуры, не думаю, что есть какое-то готовое решение, но наверное написать несложно.
avatar
UnembossedName, а расширение Scraper для браузера Chrome пригодится?
От Лонга! Я тебя умоляю!, не пользовался. Да и зачем привязываться к браузеру.
avatar
UnembossedName, а разве будет привязка гугл-таблицы к браузеру?
это расширение просто облегчает выковыривание нужных данных из страницы
или я не прав?
От Лонга! Я тебя умоляю!, наверное прав, в топике все-таки о другом, о том, как получать котировки онлайн, а у вас задача просто распарсить/конвертировать данные в таблицу.
avatar
UnembossedName,
а у вас задача просто распарсить/конвертировать данные в таблицу.

дык если на сайте в таблице меняются какие-то циферки — то эти изменения тоже попадут гугл-таблицу
От Лонга! Я тебя умоляю!, если это автоматом происходит, то замечательно, пробуйте.
avatar
UnembossedName, Спасибо за информацию. У меня почему-то после .xml, (запятая) не работает, а с ; (с точкой запятой всё нормально). Не подскажите, как фильтры поставить, чтобы например импортировать ТОЛЬКО облигации с постоянным купонном? А цену закрытия, как вытащить?
avatar
DanVi, мой косяк, суть в том, что я региональные настойки поменял на пиндосские. Из-за того, что данные импортятся с точками в качестве разделителя дробных разрядов. Я так понимаю с такими настройками меняется и разделитель аргументов функций таблиц.
avatar
DanVi, насчет облигаций с постоянным купоном. Там есть параметр SECNAME
Для облигации из моего примера его значение «ОФЗ-ПД 26222 16/10/24», тогда как для 29006 например, он будет «ОФЗ-ПК 29006 29/01/25»

Можно выбрать только те, в которых этот параметр начинается на ОФЗ-ПД с помощью XPath
avatar
UnembossedName, Спасибо, так и сделал, через сводную таблицу, а потом с помощью функции SORT отсортировал по дате гашения. По другому, не смог заставить сортировать по дате.
avatar
DanVi, через сводную даже гибче, можно фильтровать, сортировать, как хошь
avatar
UnembossedName, Согласен. Еще раз спасибо за идею. Случаем не подскажите, можно ли таким же способом котировки акций с мосбиржы вытащить?
avatar
DanVi, конечно, я же описал путь до ОФЗ, таким же образом в разделе stocks найдете акции
avatar
UnembossedName, это понятно, где взять. перефразирую вопрос. не понятно как выдернуть определенную акцию и цену закрытия. как составить запрос.
avatar
DanVi, я думал, я довольно понятно разжевал.
Как получить список полей, я описал.
Как выдергивать определенную бумагу тоже описал.
Этого должно быть достаточно. Что у вас не получается?
avatar
Добавил P.S для тех, у кого не работает.
avatar
Чтобы не менять региональные настройки, можно подменить точку на запятую на лету.

=REPLACE(IMPORTXML(«iss.moex.com/iss/engines/stock/markets/bonds/boards/tqob/securities.xml»;"//row[@SECID="«SU26212RMFS9»"]/@MARKETPRICE");FIND(".";IMPORTXML(«iss.moex.com/iss/engines/stock/markets/bonds/boards/tqob/securities.xml»;"//row[@SECID="«SU26212RMFS9»"]/@MARKETPRICE"));1;",")
avatar
Turbo Pascal, да, можно и так, можно вообще макрос написать, который это сделает, вопрос зачем)
avatar
Turbo Pascal, У меня, к сожалению не получилось.




avatar
DanVi, 

«http://» поставьте перед обоими запросами. Тут преобпразует в ссылки, и эти символы пропадают.
avatar
А когда вы доходности ПИФов анализировали, тоже откуда-то xml-файлы аналогично брали? Можете ссылку привести?
Анастасия К, я брал данные с investfunds, там можно грузить csv файлы по расчетным стоимостям пая. Готовыми средствами это не разрулить. Писал программу отдельную для импорта.
avatar
Возможно кому нибуть пригодится:

=IMPORTXML(«iss.moex.com/iss/engines/stock/markets/shares/boards/tqbr/securities.xml», "//row[@SECID="«SBER»"]/@SHORTNAME")
avatar
Как часто обновляются котировки при таком импорте?
avatar
Andrey Alekseev, если вы хотите аналог котировок из торгового терминала, то вам явно не сюда. В документации ЕМНИП видел цифру 20 минут. Но можно форсировать обновление. Другое дело, что котировки все равно с 15 минутной задержкой. Поэтому такая штука хороша для учета портфеля например.
avatar
UnembossedName, нет, я не хочу аналог котировок из терминала. Мне надо было просто узнать задержку их обновления. Спасибо за информацию!
avatar

Спасибо, ОФЗ просто отлично парсит!

А вот если хочется корпы, то печалька :( При попытке импорта ...bonds/boards/EQOB/securities.xml гуглотаблицы выдают Resource at url contents exceeded maximum size.

avatar
telectron, жалко. Я не пробовал, но знаю человека, который сделал клевый спредшит для льготных корпов, если интересно, ссылку дам
avatar

UnembossedName, в принципе проблему решил — оказывается, можно парсить не общий xml, а одной конкретной бумаги! Например, .../bonds/boards/EQOB/securities/RU000A100998.xml", "//row[@SECID="«RU000A100998»"]/@MARKETPRICE"

А клевые спредшиты интересны всегда, дайте пожалуйста ссылку, если не трудно :)

avatar
telectron, держите. Ликвидные льготные корпоративные облигации
https://docs.google.com/spreadsheets/d/1k4EdwAiwVdok2z3CZoUk_q6Atdb1x7QK2PuRBni814E/edit?ouid=107135225539676699904&usp=sheets_home&ths=true
avatar
UnembossedName, спасибо!
avatar
telectron, ну и тогда держите от этого же автора еще по спредам и ликвидности ETF на Мосбирже
https://docs.google.com/spreadsheets/d/1aYKX8yR97A_r6smh3PW9wSyH4ecr8s-eDXU2gHwVbwY/edit?ouid=107135225539676699904&usp=sheets_home&ths=true
avatar
Спасибо за информацию! А есть где таким же образом достать наши ETF: SBMX, FXRB, VTBA и т.д.?
avatar
CrazyTrain, это все есть в другой секции. Я вроде специально уделил часть поста логике поиска бумаг.

ETF здесь https://iss.moex.com/iss/engines/stock/markets/shares/boards/TQTF/securities.xml
avatar
Как можно получить данные по EPS похожим запросом например из ссылки:

https://finance.yahoo.com/quote/TRNFP.ME?p=TRNFP.ME&.tsrc=fin-srch
avatar
VLDMR, вы разницу между веб-страницей и структурированным xml-файлом не видите? Пост о том, где взять эти структурированные файлы и как из них легко получить данные. Краткий курс по HTML я пожалуй воздержусь проводить в комментах.

А вообще некоторые eps можно получить через GoogleFinance
=GOOGLEFINANCE(«MCX:GAZP»;«eps»)
Но с Транснефтью не работает

По вашей странице можно например так
=VLOOKUP(«EPS (TTM)»;IMPORTHTML(«finance.yahoo.com/quote/TRNFP.ME?p=TRNFP.ME&.tsrc=fin-srch»;«table»;2);2;FALSE)

Для этого надо понимать, что на данный момент данные содержатся в таблице и эта таблица с EPS в документе вторая, поэтому и работает

Можно и через importxml
=IMPORTXML(«finance.yahoo.com/quote/TRNFP.ME?p=TRNFP.ME&.tsrc=fin-srch»;"//td[@data-test="«EPS_RATIO-value»"]")

Но тут тоже надо понимать, что может сломаться, надо понимать структуру HTML файла

avatar
Доброго дня! Подскажите, может знаете, как c moex забрать историю курсов валют? Желательно в разрезе open, low, up, close?
Максим Филенко, 
https://iss.moex.com/iss/history/engines/currency/markets/selt/boards/cets/securities/USD000UTSTOM?from=2020-02-15

Только количество строк ограничено
Нужно переставлять дату в ссылке.
Архивы целиком наверняка можно где-то найти в других местах
avatar
UnembossedName, Спасибо! То что надо, в имени инструмента был косяк...
я обычно забираю пакетами по 100 шт в диапазоне дат: https://iss.moex.com/iss/history/engines/currency/markets/selt/boards/cets/securities/USD000UTSTOM?from=2020-02-15&till=2020-02-25
 Странно, но формула =IMPORTHTML(«iss.moex.com/iss/history/engines/currency/markets/selt/boards/cets/securities/USD000UTSTOM.HTML?from=2020-02-15&till=2020-02-25»; «table»; 0) не видит данных. При этом в браузере все отражается нормально
Максим Филенко, я с IMPORTHTML мало опыта имею.
Вот так работает =IMPORTDATA(«iss.moex.com/iss/history/engines/currency/markets/selt/boards/cets/securities/USD000UTSTOM.csv?iss.dp=point&iss.delimiter=,&from=2020-02-15&till=2020-02-25»)
avatar
 нет, все таки загрузился по ссылке: =IMPORTHTML(«iss.moex.com/iss/history/engines/currency/markets/selt/boards/cets/securities/USD000UTSTOM/marketdata.html?from=2020-07-15&till=2020-07-29», «table», 0)
Похоже, что проблема с кэшэм была 
Доброго дня! Случаем не в курсе, как можно загнать результаты импорта в кэш, чтобы использовать их при вычислениях на различных листах и не запускать на каждой ячейке новый импорт?
avatar
Максим Филенко, тут уже макрос надо писать с запуском по таймеру
avatar

теги блога UnembossedName

....все тэги



UPDONW