В предыдущих статьях я писала, как получить цену акций на конкретную дату или интервал дат через функцию GOOGLEFINANCE. Но этот способ подходит не для всех инструментов, например для ETF или префов с Московской биржи исторические данные получить не получится.
Для всех инструментов, торгующихся на Мосбирже, цену на конкретный день можно узнать через API-запрос.
Как получить текущую цену на акции и ETF по API Московской биржи
Как получить историческую цену для ETF
Рассмотрим на примере.
Требуется получить цену закрытия на FXUS ETF на дату 12 января 2021.
Формула будет выглядеть так:
=IMPORTxml("https://iss.moex.com/iss/history/engines/stock/markets/shares/boards/TQTF/securities.xml?iss.meta=off&iss.only=history&history.columns=SECID,CLOSE&start=0&date=2021-01-12, concatenate("//row[@SECID='","FXUS","']/@CLOSE"))
Жирным шрифтом выделены параметры, которые будем менять в зависимости от того, какие данные хотим получить.
🔥 Рабочая гугл-таблица с формулами
Теперь разберем подробнее что означает каждый параметр и какие значения он может принимать.
Идентификатор
Каждый инструмент на Мосбирже имеет свой идентификатор торгов.
Его можно посмотреть в карточке бумаги на сайте Мосбиржи.
Для ETF есть 3 варианта идентификаторов:
для ETF с расчетами в рублях — TQTF, например #FXUS #VTBE #SBSP
для ETF с расчетами в $ — TQTD, например #TECH #tgld #aksp #fxim
для ETF с расчетами в € — TQTE, например #teur #akeu
Подставляем нужный идентификатор в формулу:
=IMPORTxml(concatenate("https://iss.moex.com/iss/history/engines/stock/markets/shares/boards/ИДЕНТИФИКАТОР/securities.xml?iss.meta=off&iss.only=history&history.columns=SECID,ЦЕНА&start=0&date=2020-01-12, concatenate("//row[@SECID='","FXUS","']/@ЦЕНА"))
Меняем идентификатор в зависимости от требуемого инструмента, в примере для FXUS указываем TQTF.
Цена
Для каждой даты доступны 4 варианта цены:
Цена открытия — OPEN
Цена закрытия — CLOSE
Цена MAX — HIGH
Цена MIN — LOW
Прописываем нужное значение цены в формуле в двух местах:
=IMPORTxml("https://iss.moex.com/iss/history/engines/stock/markets/shares/boards/ИДЕНТИФИКАТОР/securities.xml?iss.meta=off&iss.only=history&history.columns=SECID,ЦЕНА&start=0&date=2020-01-12, concatenate("//row[@SECID='","FXUS","']/@ЦЕНА")
По условиям задачи нам нужна была цена закрытия, поэтому в формуле указываем параметр CLOSE.
Дата
Дата для запроса должна быть указана в формате ГГГГ-ММ-ДД, иначе выдается ошибка. В нашем примере задаем 12 января 2021 так: date=2021-01-12
Тикер/ISIN
Тикер (ISIN) можно указать явно (должен быть заключен в кавычки) или дать адрес ячейки с тикером/ISIN.
Для примера в формуле тикер указан явно — "FXUS", а в рабочей таблице уже указан адрес ячейки, чтобы не прописывать этот параметр вручную для каждого инструмента.
🔥 Рабочая гугл-таблица с формулами
Как получить цену на дату для акций РФ
Например, нам нужно получить цену закрытия для #сургутнефтегаз преф SNGSP на 12 января 2021.
=IMPORTxml("https://iss.moex.com/iss/history/engines/stock/markets/shares/boards/TQBR/securities.xml?iss.meta=off&iss.only=history&history.columns=SECID,CLOSE&start=200&date=2021-01-12, concatenate("//row[@SECID='",A10,"']/@CLOSE"))
Формула похожа, но есть 2 отличия, которые выделены жирным:
1. Идентификатор торгов для акций РФ — TQBR:
2. Указываем строку, с которой начинаем получаем данные — start=ЦЕЛОЕ_ЧИСЛО (по умолчанию – 0)
Этот параметр нужен по той причине, что API выдает данные по 100 строк, и по умолчанию получим только первые 100 строк, это будут строки для тикеров от ABRD до KZMS. Поэтому для акций, которые лежат за пределами этой сотни, указываем точку начала start=100, например для PMSBP, или start=200 для тех акций которые по алфавиту идут в конце списка из 267 акций. В рабочей таблице есть все формулы, но этот параметр необходимо указывать вручную для каждого инструмента, чтобы он попал в нужный диапазон выгрузки.
Таблица диапазонов:
Например, для получения цены на акции MTSS укажем в формуле start=140
Чтобы не забивать вручную параметр start, можно использовать способ получения цены за период, указав одну и ту же дату на начало и конец периода. Об этом способе расскажу ниже.
Как получить цену на дату для акций иностранных эмитентов
Например, нужно получить цену открытия для бумаг CSCO-RM на 12 января 2021:
=IMPORTxml("https://iss.moex.com/iss/history/engines/stock/markets/foreignshares/boards/FQBR/securities.xml?iss.meta=off&iss.only=history&history.columns=SECID,OPEN&start=0&date=2021-01-12, concatenate("//row[@SECID='","CSCO-RM","']/@OPEN"))
Для акций иностранных эмитентов Мосбиржи указываем идентификатор FQBR и вместо shares в строке запроса заменяем на foreignshares. В остальном все работает как с акциями РФ.
🔥 Рабочая гугл-таблица с формулами
Пока иностранных акций всего 55 наименований, поэтому пишем в формуле start=0. Но со временем их число будет расти и тогда пригодится эта таблица:
Как получить цену за период
Формула будет несколько отличаться:
=IMPORTxml("https://iss.moex.com/iss/history/engines/stock/markets/shares/boards/ИДЕНТИФИКАТОР/securities/ТИКЕР?iss.meta=off&iss.only=history&history.columns=SECID,ЦЕНА&from=ДАТА_НАЧ&till=ДАТА_КОН, "//@ЦЕНА")
ИДЕНТИФИКАТОР = меняем в зависимости от инструмента, как описано выше.
ТИКЕР= тикер или ISIN бумаги
ЦЕНА (меняем в двух местах) = CLOSE/OPEN/HIGH/LOW
ДАТА_НАЧ = дата начала периода в формате ГГГГ-ММ-ДД
ДАТА_КОН = дата конца периода в формате ГГГГ-ММ-ДД
Разберем на примере
Например, нужно получить цены закрытия для AAPL-RM за 3 предыдущих дня (не считая сегодня). Для той задачи меняем параметры на такие:
в адресе заменим shares -> foreignshares
ИДЕНТИФИКАТОР = FQTF
ТИКЕР = AAPL-RM
ЦЕНА (меняем в двух местах) = CLOSE
ДАТА_НАЧ = TODAY()-4
ДАТА_КОН = TODAY()-1
По формуле получаем массив данных за 19, 20, 21 и 22 января.
Используя эту формулу, можно получить среднюю цену за указанный период. Для этого используем выражение = AVERAGE(НАША_ФОРМУЛА):
🔥 Рабочая гугл-таблица с формулами
👍Если статья была вам полезна, поставьте лайк и подпишитесь на канал, чтобы не пропустить ничего важного. Все анонсы статей и небольшие заметки я публикую в телеграм-канале @justcountit.
👩💻Если у вас остались вопросы, с радостью отвечу на них в комментариях.
Ценные бумаги и другие финансовые инструменты, упомянутые в данном обзоре, приведены исключительно в информационных целях; обзор не является инвестиционной идеей, советом, рекомендацией, предложением купить или продать ценные бумаги и другие финансовые инструменты.