Блог им. ArtemKuznecov_45f

Получение котировок акций в Excel

До недавнего времени получал котировки в таблицы Google с помощью формулы
=IMPORTxml(«iss.moex.com/iss/engines/stock/markets/shares/boards/TQBR/securities.xml?iss.meta=off&iss.only=securities&securities.columns=SECID,PREVADMITTEDQUOTE», concatenate("//row[@SECID='",A2,"']/@PREVADMITTEDQUOTE"))

Сейчас она перестала работать.

Кто-нибудь может подсказать, как исправить формулу, чтобы она работала сейчас?
★14
60 комментариев
Какое отношение Excel имеет к таблице Google?
Где все-таки расположена неработающая формула? В каком табличном редакторе?
Что творится в ячейке А2?

Много, в общем, вопросов.
avatar
Алексей Федоров
Алексей Федоров, а в Excel такие же формулы, если не ошибаюсь.

В A2 находится название тикера.
avatar
Артём Кузнецов
+2
они в iss какую то трансформацию провели, что на днях все перестало работать. Надо ссылки перестраивать. Еще не разбирался
avatar
Андрей К
Андрей К,  в (бондовом, по крайней мере) споте все по-старому (в части структуры url-ов), и все старые баги с расчетными параметрами, увы, на своих местах
avatar
flextrader
flextrader, у нас все послетало нафиг из специфичной инфы. Но свечи работают стабильно ) решил уже на выхах позаниматься.
avatar
Андрей К
+1
iss.moex.com/iss/engines/stock/markets/shares/boards/TQBR/securities.xml?iss.dp=comma&iss.meta=off&iss.only=marketdata&marketdata.columns=SECID,LAST
avatar
Доктор Ливси
Доктор Ливси, не работает или что-то не туда пишу
avatar
Артём Кузнецов
+2
Доктор Ливси, Замените в своей формуле URL на мой и поменяйте парсинг в формуле concatenate("//row[@SECID='",A2,"']/@PREVADMITTEDQUOTE" на concatenate("//row[@SECID='",A2,"']/@LAST" И имейте ввиду, что мосбиржа выдает котировки с задержкой примерно 15 мин. для запросов без платной подписки.
avatar
Доктор Ливси
Доктор Ливси, так и делал. Только Loading пишет и всё.
avatar
Артём Кузнецов
Доктор Ливси, а можете файл на гугл диск выложить с примером?
avatar
Артём Кузнецов
+4
Доктор Ливси, =importxml("https://iss.moex.com/iss/engines/stock/markets/shares/boards/TQBR/securities.xml?iss.dp=comma&iss.meta=off&iss.only=marketdata&marketdata.columns=SECID,LAST";"//document//data//rows//row[@SECID='AFKS']/@LAST")
avatar
Доктор Ливси
Доктор Ливси, друзья, выложите файл с примером плс а ?  уже неск человек просят.
avatar
Socol
Доктор Ливси, перепробовал все формулы, везде пишет ошибка синтаксическая. Только в вашей формуле выводит значение. Но как только меняю на название тикера на ячейку, снова пишет ошибку(Нет данных для импорта). Где ошибка в формуле? Пробовал вписывать ячейку и с запятыми и с кавычками...
=importxml(«iss.moex.com/iss/engines/stock/markets/shares/boards/TQBR/securities.xml?iss.dp=comma&iss.meta=off&iss.only=marketdata&marketdata.columns=SECID,LAST»;"//document//data//rows//row[@SECID='C4']/@LAST")
avatar
gorushek
gorushek, добрый день.

=FILTERXML(WEBSERVICE(«iss.moex.com/iss/engines/stock/markets/shares/boards/TQBR/securities.xml?iss.dp=comma&iss.meta=off&iss.only=marketdata&marketdata.columns=SECID,LAST»),"//document//data//rows//row[@SECID='"&C4&"']/@LAST")
avatar
Доктор Ливси
Доктор Ливси, к сожалению, теперь синтаксическая ошибка… А в настройках гугл таблиц ничего не надо настроить?
avatar
gorushek
gorushek, 

Это была формула для MS Excel.
Для Гугла так:

=IMPORTXML(«iss.moex.com/iss/engines/stock/markets/shares/boards/TQBR/securities.xml?iss.dp=comma&iss.meta=off&iss.only=marketdata&marketdata.columns=SECID,LAST»;"//document//data//rows//row[@SECID='"&C4&"']/@LAST")

avatar
Доктор Ливси

Доктор Ливси, снова синтаксическая ошибка.

Изменил кавычки два штриха, теперь это — Ошибка Ссылка iss.moex.com/iss/engines/stock/markets/shares/boards/TQBR/securities.xml?iss.dp=comma&iss.meta=off&iss.only=marketdata&marketdata.columns=SECID,LAST недействительна

Как будто проблема настройках гугла...

avatar
gorushek
Доктор Ливси, Здесь для проверки указан тикер AFKS, работает. Поменяйте у себя на подстановку тикера из требуемой ячейки.
avatar
Доктор Ливси
Доктор Ливси, хмм… Работает только в новой таблице.
avatar
Артём Кузнецов
Доктор Ливси, Спасибо большое, очень помогли.
avatar
Артём Кузнецов
+1
У меня работает: IMPORTxml(«iss.moex.com/iss/engines/stock/markets/shares/boards/TQBR/securities.xml?iss.meta=off&iss.only=marketdata&marketdata.columns=SECID,LAST»
avatar
hdd
+1
hdd, а можете файл на гугл диск выложить с примером?
avatar
Артём Кузнецов
тупо, конечно, но вставлю свои пять копеек. у меня excel после обновы перестал адрес показывать как «A2». Вместо этого теперь красуется «R2C1», может дело в названии?
avatar
Akreg
Akreg, я читал, что надо PREVADMITTEDQUOTE заменить на PREVLEGALCLOSEPRICE. Но у меня не срабатывает.
avatar
Артём Кузнецов
Akreg, стиль ссылок в параметрах поменяйте. Галку снять надо
avatar
puz
Заметил, что у вас в ссылке https, а у меня http. Я менял в свое время, помогало, попробуйте
avatar
hdd
+1
=IMPORTxml(«iss.moex.com/iss/engines/stock/markets/shares/boards/TQBR/securities.xml?iss.meta=off&iss.only=marketdata&marketdata.columns=SECID,LAST», СЦЕПИТЬ("//row[@SECID='",B15,"']/@LAST"))

у меня все работает!
avatar
МихаилРостовПапа
у меня тоже через раз грузятся данные. 
avatar
Константин Ермаков
+2
Если кому нужно, могу выложить вечером пример для LibreOffice Calc — у меня все работает — получение котировок с Мосбиржи по API бесплатному.
avatar
Alexide
Alexide, скажите, а можно как-то автоматом выгружать данные по открытым позициям юров/физов в ексель, те, которые ещё каждые 5 минут обновляются? Что надо вообще прописать?
avatar
Xomyak147
Xomyak147, это вроде платный сервис Мосбиржи. Бесплатно они только вчерашние данные показывают. Я не умею извлекать такие данные.
avatar
Alexide
+1
Alexide, не, это бесплатные данные, просто проходите регистрацию и они доступны будут
avatar
Xomyak147
Alexide, Конечно выкладывайте! У меня Либре Офис на линуксе 
avatar
Максим
+1
можно попросить код для получения котировки золота? 
=IMPORTXML(«iss.moex.com/iss/engines/currency/markets/selt/securities/GLDRUB_TOM.xml», "/document/data[@id="«marketdata»"]/rows/row[@BOARDID=«CETS»]/@LAST")
Так пробовал не получается
avatar
Дмитрий Sh
Дмитрий Sh, =ПОДСТАВИТЬ(@ ФИЛЬТР.XML(ВЕБСЛУЖБА(«iss.moex.com/iss/engines/currency/markets/selt/securities/GLDRUB_TOM.xml?iss.dp=comma&iss.meta=off&iss.only=marketdata&marketdata.columns=LAST»);"//document//data//rows//row/@LAST");".";",")
avatar
Алексей Заказников
Алексей Заказников, Подскажи, пожалуйста, код для получения курса евро и доллара.
в приведенном выше коде пробовал менять GLDRUB_TOM на USDRUB_TOM, не получается
avatar
posetitel123
=ПОДСТАВИТЬ( ФИЛЬТР.XML(ВЕБСЛУЖБА(«iss.moex.com/iss/engines/stock/markets/shares/boards/TQBR/securities.xml?iss.dp=comma&iss.meta=off&iss.only=marketdata&marketdata.columns=SECID,LAST»);"//document//data//rows//row[@SECID='SBER']/@LAST");".";",")

Вот этот код для ячейки LibreOffice Calc возвращает котировку для Сбербанка например.

! Только двойные кавычки елочкой (вокруг URL внутри( замените на обычные двойные кавычки, иначе работать не будет

avatar
Alexide
Alexide, =ПОДСТАВИТЬ(ФИЛЬТР.XML(ВЕБСЛУЖБА(«iss.moex.com/iss/engines/stock/markets/shares/boards/TQBR/securities.xml?iss.dp=comma&iss.meta=off&iss.only=marketdata&marketdata.columns=SECID,LAST»);"//document//data//rows//row[@secid='C2']/@last");".";",") Не работает не подскажешь почему?
avatar
Maxim Snitko

Maxim Snitko, уточню — этот скрипт только для LibreOffice (для Excel вероятно нужна поправка).
2. Скопируйте еще раз аккуратно весь текст в моей цитате выше, каждый символ.
3. Кавычки елочкой замените на обычные двойные кавычки. Без этой правки работать не будет. Это Смартлаб исказил текст.
4. Еще вероятно появится всплывающая подсказка где-то вверху LibreOffice где нужно кликнуть и разрешить загрузку скрипта с внешнего сайта (Мосбиржи).

avatar
Alexide

Да что за WTF? Ежедневно вношу какие-то изменения в ссылки в Google Sheets. Начинает работать, на след день открываешь рабочий файл, — опять ошибки. Че происходит-то
 

 

avatar
Михаил Глазков
Гугл таблицы: ранее работало =importxml(«iss.moex.com/iss/engines/stock/markets/shares/boards/TQBR/securities.xml?iss.dp=comma&iss.meta=off&iss.only=marketdata&marketdata.columns=SECID,LAST»,"//document//data//rows//row[@SECID='",C5,"']/@LAST")
где С5 это Тикер.
Коллеги, перепробовал, что выше написали — не получается!
Выложите пожалуйста рабочую гугл таблицу, с рабочей формулой!
avatar
Гена Петров
Гена Петров, подскажите от чего зависит как выводится в ячейке значение? По Вашему скрипту отображается или через точку, или через запятую. Рандомно для разных эмитентов. Но дальнейшее вычисление суммы в первом случае не высчитывается — ошибку показывает, во втором — нормальный расчет.
avatar
Максим Малахов

Максим Малахов, нужно ли что еще настроить в гугл таблице для получения котировок? 

Вставил в ячейку, пишет Ошибка синтаксическая

avatar
gorushek

Для таблиц Google:
=ImportXML(CONCATENATE("https://www.moex.com/iss/engines/stock/markets/shares/boards/TQBR/securities/",C5,".xml?iss.meta=off&iss.only=marketdata&securities.columns=LAST"),"/document/data/rows/row/@LAST")

где С5 — это тикер.

Для Excel:
=ФИЛЬТР.XML(ВЕБСЛУЖБА(СЦЕПИТЬ("https://www.moex.com/iss/engines/stock/markets/shares/boards/TQBR/securities/";C5;".xml?iss.meta=off&iss.only=marketdata&securities.columns=LAST"));"//document//data//rows//row/@LAST")

avatar
mir-vam
Дайте совет, как сделать, чтобы котировки в экселе обновлялись автоматически? Приходится нажимать F2+Enter. Параметр автоматического расчета для формул включен.
Значение в ячейке такое: =ПОДСТАВИТЬ( ФИЛЬТР.XML(ВЕБСЛУЖБА(«iss.moex.com/iss/engines/stock/markets/shares/boards/TQBR/securities.xml?iss.dp=comma&iss.meta=off&iss.only=marketdata&marketdata.columns=SECID,LAST»);"//document//data//rows//row[@SECID='SBER']/@LAST");".";",")
avatar
Владимир Пудовкин
Гугл-таблица:
сегодня до 10-00 мск не работало, а потом заработало:
=ImportXML(CONCATENATE(«www.moex.com/iss/engines/stock/markets/shares/boards/TQBR/securities/»,C6,".xml?iss.meta=off&iss.only=marketdata&securities.columns=LAST"),"/document/data/rows/row/@LAST")
и
=importxml(«iss.moex.com/iss/engines/stock/markets/shares/boards/TQBR/securities.xml?iss.dp=comma&iss.meta=off&iss.only=marketdata&marketdata.columns=SECID,LAST»,"//document//data//rows//row[@SECID='YNDX']/@LAST")
avatar
Гена Петров
А можно попросить (буду крайне благодарен), как сделать запрос из Эксель на получение котировки по натуральному газу например NGK3
avatar
O J
Здравствуйте у меня в exel работает эта формула для получения котировок облигаций: 
=ФИЛЬТР.XML(ВЕБСЛУЖБА(СЦЕПИТЬ(«www.moex.com/iss/engines/stock/markets/bonds/boards/TQOB/securities/»;E4;".xml?iss.meta=off&iss.only=marketdata&securities.columns=LAST"));"//document//data//rows//row/@LAST")  Подскажите пожалуйста формулу для получения НКД?
avatar
Lendo Calendo
ВСЕ САМ РАЗОБРАЛСЯ ПОМОЩЬ НЕ НУЖНА ВОТ ФОРМУЛА НКД    =ФИЛЬТР.XML(ВЕБСЛУЖБА(«www.moex.com/iss/engines/stock/markets/bonds/boards/TQOB/securities/»&E4&"/securities.xml?iss.meta=off&iss.only=securities&securities.columns=SECID,ACCRUEDINT");"//document//data//rows//row/@ACCRUEDINT")
avatar
Lendo Calendo
Пришлите пожалуйста актуальную формулу. И нужно ли настроить сами гугл таблицы?

avatar
gorushek
Все формулы пробовал на форуме. Пишет синтаксическая ошибка… Что не так?

avatar
gorushek
Подскажите, получаю по облигациям в excel доходность (EFFECTIVEYIELD), НКД (ACCRUEDINT) из 20 облигаций в некоторых (не системно) вместо НКД, например 30,58 выдает 42256. Тоже самое с доходностью. Открываю по прописанной в excel ссылке XML, там все правильно, а в excel пятизначные числа. 
avatar
morsm
morsm, здравствуйте, столкнулся с такой же проблемой по облигациям доходность нормально не выводиться как я понял если после точки меньше двух знаков то выводиться какая-то чушь, получилось разобраться?
avatar
Nik
Nik, добрый день, нет, даже не знаю куда копать
avatar
morsm
morsm, нашел решение у одного доброго человека который свой файл выложил в общи доступ. Решение: 

Файл -> Параметры -> Дополнительно -> убрать галочку с «Использовать системные разделители».
Если не поможет, то поменяйте в этом параметре точку на запятую или наоборот.

avatar
Nik
Nik, Это из за того что используется запятая в системе, а надо чтоб использовалась точка как разделитель.
avatar
Nik
Nik, Спасибо большое, помогло замена на точку как разделитель. Только excel пользуюсь и по работе. Можно забыть поменять ). Пробую в гугл таблицах.
avatar
morsm

полезные записи за 24 часа

★14 5 Как купить биткоин в России за рубли: Полное руководство
★11 16 Для тех кто хочет разместить капитал в LQDT на новогодние 👇
★10 9 ЦБ поднимет ставку до 23% или выше. С чем он на самом деле борется?
★9 3 10 российских компаний с наибольшим индексом стабильности дивидендов DSI (декабрь 2024)
★8 38 Годовая инфляция в самогоне: 11%
★8 3 🌞 РусГидро: как долг стал основой, без которого невозможно выжить?
★7 68 Налетела грусть... Ну что ж пойду пройдусь... (С)
★7 14 Лайфхак для уменьшения налога на брокерском счету — сальдирование убытков
★7 21 📈 Топ-тренды Смартлаба 2024: что читали больше всего? 🚀
★7 21 Топ-3 акции от каждого аналитика Mozgovik. Конспект звонка с аналитиками сегодня
+229 178 Что сказал Путин на итогах года по прямой линии
+217 152 Ситуация на текущий момент
+155 68 Налетела грусть... Ну что ж пойду пройдусь... (С)
+127 80 Азия. Четверг.
+122 38 Годовая инфляция в самогоне: 11%
+108 21 Топ-3 акции от каждого аналитика Mozgovik. Конспект звонка с аналитиками сегодня
+96 17 В.Путин поддержал фондовый рынок 🔥Акции и инвестиции
+95 16 Для тех кто хочет разместить капитал в LQDT на новогодние 👇
+92 101 Индекс МБ сегодня
+76 21 📈 Топ-тренды Смартлаба 2024: что читали больше всего? 🚀

самые обсуждаемые сегодня

178к Что сказал Путин на итогах года по прямой линии
152к Ситуация на текущий момент
113к Думаю что многие здесь не пишут бомбических идей, только потому, что их сразу все воспримут в штыки.
113к Метод Геллы. Мечты сдуваются (газром)
101к Индекс МБ сегодня
80к Азия. Четверг.
53к Газпром всё
35к Китай стал монстром-автогигантом
34к YouTube на мобилах все?
32к Почему я считаю, что рынок акций РФ скоро начнёт расти!

теги блога Артём Кузнецов

....все тэги



UPDONW
Новый дизайн