08 февраля 2023, 14:42

Получение котировок акций в 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"))

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

Кто-нибудь может подсказать, как исправить формулу, чтобы она работала сейчас?

торговые роботы

blog30mln

Артём Кузнецов

Новочебоксарск

1 165

с 4 июля 2020

60 Комментариев

Алексей Федоров
08 февраля 2023, 14:47
Какое отношение Excel имеет к таблице Google?
Где все-таки расположена неработающая формула? В каком табличном редакторе?
Что творится в ячейке А2?

Много, в общем, вопросов.
0
- Артём Кузнецов
  08 февраля 2023, 14:55
  Алексей Федоров, а в Excel такие же формулы, если не ошибаюсь.
  
  В A2 находится название тикера.
  0
Андрей К
08 февраля 2023, 14:52
они в iss какую то трансформацию провели, что на днях все перестало работать. Надо ссылки перестраивать. Еще не разбирался
+2
- flextrader
  09 февраля 2023, 07:56
  Андрей К, в (бондовом, по крайней мере) споте все по-старому (в части структуры url-ов), и все старые баги с расчетными параметрами, увы, на своих местах
  0
  - Андрей К
    09 февраля 2023, 15:09
    flextrader, у нас все послетало нафиг из специфичной инфы. Но свечи работают стабильно ) решил уже на выхах позаниматься.
    0
Доктор Ливси
08 февраля 2023, 14:57
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
+1
- Артём Кузнецов
  08 февраля 2023, 15:01
  Доктор Ливси, не работает или что-то не туда пишу
  0
- Доктор Ливси
  08 февраля 2023, 15:07
  Доктор Ливси, Замените в своей формуле URL на мой и поменяйте парсинг в формуле concatenate("//row[@SECID='",A2,"']/@PREVADMITTEDQUOTE" на concatenate("//row[@SECID='",A2,"']/@LAST" И имейте ввиду, что мосбиржа выдает котировки с задержкой примерно 15 мин. для запросов без платной подписки.
  +2
  - Артём Кузнецов
    08 февраля 2023, 15:15
    Доктор Ливси, так и делал. Только Loading пишет и всё.
    0
  - Артём Кузнецов
    08 февраля 2023, 15:31
    Доктор Ливси, а можете файл на гугл диск выложить с примером?
    0
- Доктор Ливси
  08 февраля 2023, 15:49
  Доктор Ливси, =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")
  +4
  - Socol
    08 февраля 2023, 18:19
    Доктор Ливси, друзья, выложите файл с примером плс а ? уже неск человек просят.
    0
  - gorushek
    09 ноября 2023, 23:33
    Доктор Ливси, перепробовал все формулы, везде пишет ошибка синтаксическая. Только в вашей формуле выводит значение. Но как только меняю на название тикера на ячейку, снова пишет ошибку(Нет данных для импорта). Где ошибка в формуле? Пробовал вписывать ячейку и с запятыми и с кавычками...
    =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")
    0
    - Доктор Ливси
      10 ноября 2023, 13:20
      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")
      0
      - gorushek
        10 ноября 2023, 13:32
        Доктор Ливси, к сожалению, теперь синтаксическая ошибка… А в настройках гугл таблиц ничего не надо настроить?
        0
        Доктор Ливси
        10 ноября 2023, 13:46
        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")
        0
        gorushek
        10 ноября 2023, 18:29
        Доктор Ливси, снова синтаксическая ошибка.
        Изменил кавычки два штриха, теперь это — Ошибка Ссылка 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 недействительна
        Как будто проблема настройках гугла...
        0
- Доктор Ливси
  08 февраля 2023, 15:51
  Доктор Ливси, Здесь для проверки указан тикер AFKS, работает. Поменяйте у себя на подстановку тикера из требуемой ячейки.
  0
  - Артём Кузнецов
    08 февраля 2023, 16:10
    Доктор Ливси, хмм… Работает только в новой таблице.
    0
  - Артём Кузнецов
    08 февраля 2023, 16:52
    Доктор Ливси, Спасибо большое, очень помогли.
    0
hdd
08 февраля 2023, 15:24
У меня работает: IMPORTxml(«iss.moex.com/iss/engines/stock/markets/shares/boards/TQBR/securities.xml?iss.meta=off&iss.only=marketdata&marketdata.columns=SECID,LAST»
+1
- Артём Кузнецов
  08 февраля 2023, 15:31
  hdd, а можете файл на гугл диск выложить с примером?
  +1
Akreg
08 февраля 2023, 15:26
тупо, конечно, но вставлю свои пять копеек. у меня excel после обновы перестал адрес показывать как «A2». Вместо этого теперь красуется «R2C1», может дело в названии?
0
- Артём Кузнецов
  08 февраля 2023, 15:30
  Akreg, я читал, что надо PREVADMITTEDQUOTE заменить на PREVLEGALCLOSEPRICE. Но у меня не срабатывает.
  0
- puz
  08 февраля 2023, 17:13
  Akreg, стиль ссылок в параметрах поменяйте. Галку снять надо
  0
hdd
08 февраля 2023, 15:46
Заметил, что у вас в ссылке https, а у меня http. Я менял в свое время, помогало, попробуйте
0
МихаилРостовПапа
08 февраля 2023, 16:16
=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"))

у меня все работает!
+1
Константин Ермаков
08 февраля 2023, 16:47
у меня тоже через раз грузятся данные.
0
Alexide
08 февраля 2023, 17:35
Если кому нужно, могу выложить вечером пример для LibreOffice Calc — у меня все работает — получение котировок с Мосбиржи по API бесплатному.
+2
- Xomyak147
  08 февраля 2023, 17:47
  Alexide, скажите, а можно как-то автоматом выгружать данные по открытым позициям юров/физов в ексель, те, которые ещё каждые 5 минут обновляются? Что надо вообще прописать?
  0
  - Alexide
    08 февраля 2023, 18:06
    Xomyak147, это вроде платный сервис Мосбиржи. Бесплатно они только вчерашние данные показывают. Я не умею извлекать такие данные.
    0
    - Xomyak147
      08 февраля 2023, 18:22
      Alexide, не, это бесплатные данные, просто проходите регистрацию и они доступны будут
      +1
- Максим
  08 февраля 2023, 18:09
  Alexide, Конечно выкладывайте! У меня Либре Офис на линуксе
  0
Дмитрий Sh
08 февраля 2023, 17:51
можно попросить код для получения котировки золота?
=IMPORTXML(«iss.moex.com/iss/engines/currency/markets/selt/securities/GLDRUB_TOM.xml», "/document/data[@id="«marketdata»"]/rows/row[@BOARDID=«CETS»]/@LAST")
Так пробовал не получается
+1
- Алексей Заказников
  24 февраля 2023, 16:53
  Дмитрий 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");".";",")
  0
- posetitel123
  12 марта 2023, 12:14
  Алексей Заказников, Подскажи, пожалуйста, код для получения курса евро и доллара.
  в приведенном выше коде пробовал менять GLDRUB_TOM на USDRUB_TOM, не получается
  0
Alexide
08 февраля 2023, 19:18
=ПОДСТАВИТЬ( ФИЛЬТР.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 внутри( замените на обычные двойные кавычки, иначе работать не будет
0
- Maxim Snitko
  25 ноября 2024, 16:11
  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");".";",") Не работает не подскажешь почему?
  0
  - Alexide
    25 ноября 2024, 21:39
    Maxim Snitko, уточню — этот скрипт только для LibreOffice (для Excel вероятно нужна поправка).
    2. Скопируйте еще раз аккуратно весь текст в моей цитате выше, каждый символ.
    3. Кавычки елочкой замените на обычные двойные кавычки. Без этой правки работать не будет. Это Смартлаб исказил текст.
    4. Еще вероятно появится всплывающая подсказка где-то вверху LibreOffice где нужно кликнуть и разрешить загрузку скрипта с внешнего сайта (Мосбиржи).
    0
Михаил Глазков
09 февраля 2023, 20:16
Да что за WTF? Ежедневно вношу какие-то изменения в ссылки в Google Sheets. Начинает работать, на след день открываешь рабочий файл, — опять ошибки. Че происходит-то

0
Гена Петров
28 февраля 2023, 09:38
Гугл таблицы: ранее работало =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 это Тикер.
Коллеги, перепробовал, что выше написали — не получается!
Выложите пожалуйста рабочую гугл таблицу, с рабочей формулой!
0
- Максим Малахов
  25 мая 2023, 17:48
  Гена Петров, подскажите от чего зависит как выводится в ячейке значение? По Вашему скрипту отображается или через точку, или через запятую. Рандомно для разных эмитентов. Но дальнейшее вычисление суммы в первом случае не высчитывается — ошибку показывает, во втором — нормальный расчет.
  0
  - gorushek
    29 октября 2023, 21:13
    Максим Малахов, нужно ли что еще настроить в гугл таблице для получения котировок?
    Вставил в ячейку, пишет Ошибка синтаксическая
    0
mir-vam
17 марта 2023, 17:33
Для таблиц 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")
0
Владимир Пудовкин
04 апреля 2023, 21:10
Дайте совет, как сделать, чтобы котировки в экселе обновлялись автоматически? Приходится нажимать 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");".";",")
0
Гена Петров
18 апреля 2023, 10:23
Гугл-таблица:
сегодня до 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")
0
O J
11 мая 2023, 21:06
А можно попросить (буду крайне благодарен), как сделать запрос из Эксель на получение котировки по натуральному газу например NGK3
0
Lendo Calendo
12 июля 2023, 14:18
Здравствуйте у меня в 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") Подскажите пожалуйста формулу для получения НКД?
0
Lendo Calendo
12 июля 2023, 21:38
ВСЕ САМ РАЗОБРАЛСЯ ПОМОЩЬ НЕ НУЖНА ВОТ ФОРМУЛА НКД =ФИЛЬТР.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")
0
gorushek
30 октября 2023, 18:33
Пришлите пожалуйста актуальную формулу. И нужно ли настроить сами гугл таблицы?

0
gorushek
09 ноября 2023, 23:05
Все формулы пробовал на форуме. Пишет синтаксическая ошибка… Что не так?

0
morsm
30 ноября 2023, 18:43
Подскажите, получаю по облигациям в excel доходность (EFFECTIVEYIELD), НКД (ACCRUEDINT) из 20 облигаций в некоторых (не системно) вместо НКД, например 30,58 выдает 42256. Тоже самое с доходностью. Открываю по прописанной в excel ссылке XML, там все правильно, а в excel пятизначные числа.
0
- Nik
  02 декабря 2023, 15:53
  morsm, здравствуйте, столкнулся с такой же проблемой по облигациям доходность нормально не выводиться как я понял если после точки меньше двух знаков то выводиться какая-то чушь, получилось разобраться?
  0
  - morsm
    04 декабря 2023, 10:26
    Nik, добрый день, нет, даже не знаю куда копать
    0
    - Nik
      04 декабря 2023, 10:38
      morsm, нашел решение у одного доброго человека который свой файл выложил в общи доступ. Решение:
      Файл -> Параметры -> Дополнительно -> убрать галочку с «Использовать системные разделители».
      Если не поможет, то поменяйте в этом параметре точку на запятую или наоборот.
      0
      - Nik
        04 декабря 2023, 10:40
        Nik, Это из за того что используется запятая в системе, а надо чтоб использовалась точка как разделитель.
        0
        morsm
        05 декабря 2023, 13:01
        Nik, Спасибо большое, помогло замена на точку как разделитель. Только excel пользуюсь и по работе. Можно забыть поменять ). Пробую в гугл таблицах.
        0