Как я слежу за акциями в гугл-таблице: три простых способа
Я использую гугл-таблицы для портфеля инвестиций, потому что с ними проще вести учет.
Упрощение связано с тем, что можно в автоматическом режиме получать данные о текущих ценах и других параметрах активов, которые публикуются открыто.
Для этого я комбинирую три бесплатных сервиса, которые подтягивают данные с разных бирж с помощью API. В статье расскажу, как настроить их под свои нужды.
Что за АПИ
В большинстве случаев данные с бирж передаются через программный интерфейс, называемый API — application programming interface. Грубо говоря, это инструмент, благодаря которому одна программа начинает говорить на одном языке с другой. В нашем случае мы используем API финансовых бирж для «дружбы» с гугл-таблицами.
API Московской биржи
Для чего. Получить информацию о российских акциях и облигациях.
Как это работает. У Московской биржи есть API, который позволяет видеть любую информацию с российской биржи внутри гугл-таблиц. Например, можно получать имена инструментов, цены закрытий, даты оферт и другие параметры.
Работа этой функции полностью зависит от сервера Московской биржи. Если биржа поменяет формат выдачи, организует какие-то другие режимы торгов или изменит что-то в существующей системе, то для корректной работы формул потребуется их доработка.
Такая выборочная информация с биржи пригодится для разных целей. Можно создать гугл-таблицу, чтобы покупать облигации с хорошей ликвидностью, — об этом я уже рассказывал. А можно следить за параметрами бумаг в собственном портфеле — например, за датами оферт. Это поможет не пропустить важную дату, если хотите принять участие в досрочном погашении.
Еще таблица пригодится, если вас не устраивают существующие сервисы учета — Intelinvest и Investing.com.
В таблице-примере к этой статье я привел несколько полезных параметров ценных бумаг. Вот что она умеет:
- подтягивать название бумаги по идентификатору — столбец «Названия акций и облигаций»;
- показывать текущие курсы — столбец «Цены акций и облигаций»;
- получать даты выплат облигаций — столбец «Даты купона и значения для облигаций»;
- получать дивиденды — столбец «Даты и значения дивиденда для акций»;
- видеть возможность досрочного погашения облигаций — столбец «Даты оферт».
Список далеко не полный, ведь на Мосбирже огромное количество параметров, включая срочный рынок и валюты.
Я везде старался использовать формулы в понятной записи с разбивкой на несколько строк — чтобы каждый смог воспроизвести эту конструкцию в своей таблице.
Googlefinance внутри гугл-таблиц
Для чего. Получить информацию об американских акциях, которые торгуются на Санкт-Петербургской бирже.
Как это работает. Googlefinance — это встроенная функция гугл-таблиц. Она помогает получить текущие или архивные данные о ценных бумагах из сервиса «Гугл-финансы».
Российские акции в сервисе тоже есть. Чтобы их увидеть, надо указывать тикер — то есть код из нескольких букв, который принадлежит определенному финансовому инструменту, — с приставкой «MCX:» так, чтобы получилось «MCX:SBER».
Эта функция позволяет получать название, текущую цену акций и еще 18 параметров — например, максимальную и минимальную цену за 52 недели, количество акций в обращении. Все это есть в моем шаблоне.
Главное преимущество в том, что формулы для получения этих параметров предельно просты: например, имя бумаги — name, самая высокая цена на текущий день — high. Описание всех формул и атрибутов есть в инструкции от Гугла.
В первом столбце я оставил идентификаторы двух акций — Сбербанка (MCX;SBER) и «Нетфликс» (NFLX) — и фонда Van Eck (RSX). При изменении тикеров во втором столбце будет подтягиваться название акций и актуальные цены.
А чтобы получить подробную информацию об акции или фонде, используйте ячейки В13 и В35: в них нужно занести тикер и в таблицах ниже сразу же обновится информация.
Например, вы хотите отслеживать акции американского производителя сетевого оборудования Ubiquiti Inc (UI) и высокодивидендного фонда Highland Small-Cap Equity Y (HSZYX). В ячейку В13 заносите тикер акции UI, а в ячейку В35 — тикер фонда HSZYX. Получите 19 параметров каждой бумаги.
Смотрите, как это работает:
«Яху-финанс»
Для чего. Отслеживать любые финансовые данные со всего мира.
Как это работает. «Яху-финанс» передает данные с рынков 79 стран, включая Россию.
Чтобы подружить «Яху» и таблицы, можно пойти двумя путями: написать скрипт, который будет импортировать выдачу API в специальном текстовом формате JSON в гугл-таблицу, или воспользоваться формулой IMPORTHTML — еще есть вариант с IMPORTXML, — которая импортирует таблицы и списки с сайтов.
Я остановился на втором варианте — получение данных через формулу IMPORTHTML. Важно: эта формула подтягивает данные только из таблиц или списков, другую информацию через нее не получить.
Формула работает так: сервис Гугла обращается по адресу страницы, указанному в формуле, и ищет все таблицы, которые обозначены в разметке этой страницы. Таблиц может быть несколько, но у каждой свой порядковый номер. Поэтому в формуле нужно указать конкретный номер таблицы — данные из нее и появятся в гугл-таблице.
Моя формула обращается к тикеру, который нужно прописать самостоятельно, и ищет данные по этой акции на «Яху». К написанию некоторых тикеров есть требования:
- для российских акций после тикера нужно добавить приставку «.ME», чтобы получилось LKOH.ME;
- для бумаг, торгующихся на немецкой бирже, нужно добавить «.DE». Например, чтобы посмотреть подробности по Bayerische Motoren Werke Aktiengesellschaft (BMW), надо написать BMW.DE;
- для итальянской фондовой биржи Borsa Italiana S.p. A., ISE, расположенной в Милане, после тикера надо дописывать приставку «.MI», чтобы получилось UBI.MI.
Запомнить
- API Московской биржи в гугл-таблицах — практически самый быстрый способ получить данные с Мосбиржи.
- Встроенная функция Googlefinance внутри гугл-таблиц пригодится, если нет времени разбираться с формулами, но хочется видеть курсы акций.
- Любые финансовые данные со всего мира получайте в API «Яху-финанс» с помощью простой функции IMPORTHTML.
- Если вы только начинаете инвестировать, пройдите бесплатный курс от Т—Ж.
25.08.20, 09:19
Пользователи Тинькофф Инвестиций могут следить за состоянием своего портфеля и через API заносить информацию в Google-таблицы. Тут написал об этом подробнее: https://habr.com/ru/post/516210
14.04.22, 11:48
Dmitriy, посмотрела публикацию на Хабре, вставила в таблицу токен. Курс доллара не подгрузился, портфель тоже. Бедапечаль(
10.04.23, 20:48
Лиза, скорее всего в статье используется API старой версии, а Тинькофф Инвестиции не так давно перешли на новую.
Попробуйте воспользоваться скриптами по этой ссылке https://github.com/ErhoSen/gas-tinkoff-trades и использовать функции только из API v2, должно получиться
09.11.20, 13:42
Dmitriy, А api тинькофф инвестиций уже научились в readonly токены? А то стрёмно
08.09.21, 06:53
Георгий, вот в тему вопрос. Занялся сборкой своей таблицы чтобы видеть сколько дивов и когда получу и нет нормальных инструментов для этого. Какие то кривые приложения где нужно руками вбивать свои покупки. А токен страшно как то в гугл табицы вставлять свой)
03.10.21, 11:47
Иван, я сделал связку гугл таблицы и сервис Доход.
03.10.21, 11:49
Святослав, Каким образом связали?
11.01.22, 12:24
Михаил, формулами. В основном меня интересовали дивиденды на определенные компании.
Через IMPORTHTML И "TABLE".
Интересовали акции, которые больше 1 раза в год выплачивают дивиденды.
Спасибо за статьи, очень помогают.
25.02.22, 22:51
Dmitriy, что делать если после вставки токена в ячейку B1 в листе «Настройки», в ячейке B3 не появляется текущий курс доллара? Опробованы все возможные для получения в личном кабинете токены
14.04.22, 11:47
Dmitriy, посмотрела статью на хабре, вставила токен в таблицу, курс доллара не появился, портфель не подгружается. Бедапечаль
02.09.20, 11:27
Dmitriy, а как там посмотреть "Избранное"?
25.08.20, 10:00
Dmitriy, вы автор?
26.08.20, 03:17
Михаил, да
26.08.20, 03:48
Dmitriy, хорошая статья, спасибо 👍
25.08.20, 05:33
Функции импорта, если их хотя бы пара десятков на листе работают из Google Sheets крайне медленно.
Сам использую API мосбиржи, но лист с портфелем открывается с неизменной ошибкой с предложением снизить количество IMPORTXML и подобных.
25.08.20, 07:36
Сергей, если импортить при помощи gscript то такой проблемы не будет
25.08.20, 09:57
Пончик, ну с ними с наскока не разобраться. Хотя это JavaScript.
25.08.20, 09:38
Пончик, спасибо, попробую!
25.08.20, 09:56
Сергей, да, такое есть. Но если ждёшь, то в итоге загрузка происходит. Вам правильно посоветовали гугл скрипты. Но с ними тоже нюансы есть. Хотя бы то, что это JavaScript.
03.09.20, 10:24
Изначально делал колхоз на c++/php/html (локальный веб-сервер), портфель держу в csv, учитывая не только сами позиции, но и дивиденды/купоны, а также доходности по разным инструментам, группам и т.п.
Сейчас пишу приложение под андроид с тем же функционалом. Готовые не понравились :)
03.09.20, 10:38
Денис, https://journal.tinkoff.ru/investment-report/ ?
03.09.20, 13:22
Михаил, я ещё веду учёт поступлений по каждой покупке внутри позиции, т.е. в любой момент могу глянуть доходность, цену и дату покупки, поступления по сути каждого лота (точнее, их группы). Так же по FIFO уменьшаю начисленные дивиденды, если продаю часть прежней покупки.
Всё в виде веб-сайта, от таблиц я отказался :)
03.09.20, 18:21
Денис, вот мой опыт: https://habr.com/ru/post/477920/
03.09.20, 18:20
Денис, у вас сложная структура получается наверное.
03.09.20, 18:52
Михаил, немного замороченно, да :)
08.09.21, 06:55
Денис, О вот это интересно! Очень интересно, расскажите подробней плиз!
08.09.21, 16:22
Иван, и вторая картинка.
08.09.21, 16:21
Иван, сложно как-то подробно расписать :) Есть сайт, он кушает csv - в них и портфель, и котировки. Котировки грузит отдельная программа с разных бирж через api, если не удалять файлы, можно смотреть состояние портфеля за прошлые даты. Также есть графики эквити + бенчмарки (таблицы с данными тоже в csv, собираются отдельно). В самом портфеле каждая покупка отдельно, в т.ч. и приходящиеся на неё дивиденды, это удобнее средней цены в терминале. Если нажать на тикер в общем списке (главная страница), то видны как раз покупки внутри одного тикера.
Для наглядности пара скринов не моего портфеля низкого риска (там нет ПД, так что ничего вроде не раскрываю :)).
25.10.22, 09:04
Денис, можете поделиться этой таблицей или сайтом?
09.09.21, 06:19
Денис, своя система учета у вас :)
24.08.20, 20:32
В заголовке статьи Эксель, а в тексте про него ни слова. Поправьте, пожалуйста.
25.08.20, 09:55
Alexander, да, спасибо. Исправили. Но про эксель я тоже писал: Free API Мосбиржи в формулах Microsoft Excel [ https://habr.com/ru/post/498268/ ].
26.08.20, 09:32
Михаил, 365 — это не веб-версия, это просто версия, которую сейчас выдают с подпиской. Data Types в Excel 2019 не вошли, насколько я знаю (но «релизные» версии это сейчас скорее корпоративная история, а у реально работающих с рыночными данными людей должны быть расширения от Bloomberg или Refinitiv).
В веб-версии есть data types, но механизмы расширения Экселя (VBA, VSTO, JS add-ins, Office Scripts, что там еще) на каждой платформе свои. Это беда, конечно.
02.09.20, 15:39
Marat, 102 это %.
03.09.20, 21:09
rinat, используй ; как разделитель. Это зависит от локальных настроек таблицы. В данном случае попробуй =IMPORTxml("https://iss.moex.com/iss/securities/"&A31&".xml?iss.meta=off&iss.only=boards&boards.columns=secid,is_primary,boardid"; "//document//data//rows//row[@is_primary=1]/@boardid")
07.10.20, 08:16
Pavel, можно конечно, но в таблице можно какие-то дополнительные вычисления производить, балансировку портфеля например, или строить собственные графики, а вносить в нее данные вручную утомительно и нудно
31.03.21, 13:03
Binotto, рекомендуем остановиться на одном таком комментарии, а то придется вас забанить за флуд
10.08.21, 11:08
Михаил, а как вытащить с мосбиржи gldrub_tom ?
05.02.22, 21:03
Михаил, добрый день! (точнее, я пишу это ночью, потому что второй час пытаюсь воспроизвести функцию в гугл таблицах).
Прежде всего огромное спасибо за статьи и таблицы - каждая из них - просто кладезь полезной информации.
У меня есть таблица для ведения и расчета параметров структуры портфеля. Сейчас все тяну встроенными функциями googlefinance. Но данных по бпифам и облигациям сильно не хватает. Решила воспользоваться функциями из п.1. Не получается, выдается синтаксическая ошибка. Уже даже специально вставила в гугл таблице в те же ячейки - все равно ошибка. В вашей табличке при этом все работает.
Подскажите, пожалуйста, в чем может быть загвоздка?
12.03.22, 13:47
Михаил, добрый день!
Нужен совет профессионала!
В секции http://iss.moex.com/iss/securities/"&A3&"/dividends.xml есть данные о дивидендах по ряду акций (SBER, LOKH, NLMK и др.).
Однако нет данных о дивидендах таких акций как AVAN, SGHZ, RZSB, LSNGP, HIMCP. Могли бы подсказать в какой секции их искать? Все перерыл... Не могу найти...
Заранее спасибо за ответ! )))
17.04.22, 08:40
Подскажите, как подтягивать в таблицу цены облигаций по ISIN? USG*******89
18.04.22, 08:12
Михаил, пытаюсь подставить ISIN иностранных облигаций, и ничего не подтягивается...
09.07.22, 14:17
Написал для получения последней цены, странно что автор в примере приводит цену предидущего дня... боится что мы перегрузим сервак московской биржи))
12.09.22, 07:07
API Московской биржи в гугл-таблицах перестали работать в последние дни.
Кто-то знает что случилось, это временно или надолго?
12.09.22, 08:56
Павел, всё работает. Проверяйте на своей стороне.
Вот пример: https://docs.google.com/spreadsheets/d/1MI-FfjjOhPCYb0H2E9--bEWdsbu4HfOKly3ySySN-0k/
02.11.22, 18:10
Михаил, похоже отвалился механизм передачи от московской в Таблицы :( Можете обновить код?
07.01.23, 05:22
Эти таблицы жуткая вещь! Кучу раз бывает что пишешь формулу, а она не работает вообще по непонятным причинам! Пытаюсь получить данные с Мосбиржи, например по фонду по TRUR. У меня подтягивается цена 45 тыс. за 1 пай, когда реальная 5,9 руб.
Вписываю тикер в вашу таблицу - показывает правильную цену. Копируют оттуда формулу в свою таблицу (естественно корректируя ссылку на строку с тикером) - пишет синтаксическая ошибка, хотя все четко. Ладно, думаю, сейчас целиком лист с таблицей к себе скопирую и буду ковыряться.
Но как только таблица копируется (целиком в отдельный лист) к моей таблице, то она тоже начинает показывать цену TRUR не 5,9 руб., а 45 тыс. Я не понимаю что за магия такая происходит. Открыто параллельно две вкладки и на одной правильная цена, на второй - нет. Разница только что одна таблица скопировалась в новый документ, а вторая в отдельный лист моей таблицы...
07.01.23, 05:23
Эти таблицы жуткая вещь! Кучу раз бывает что пишешь формулу, а она не работает вообще по непонятным причинам! Пытаюсь получить данные с Мосбиржи, например по фонду по TRUR. У меня подтягивается цена 45 тыс. за 1 пай, когда реальная 5,9 руб.
Вписываю тикер в вашу таблицу - показывает правильную цену. Копируют оттуда формулу в свою таблицу (естественно корректируя ссылку на строку с тикером) - пишет синтаксическая ошибка, хотя все четко. Ладно, думаю, сейчас целиком лист с таблицей к себе скопирую и буду ковыряться.
Но как только таблица копируется (целиком в отдельный лист) к моей таблице, то она тоже начинает показывать цену TRUR не 5,9 руб., а 45 тыс. Я не понимаю что за магия такая происходит. Открыто параллельно две вкладки и на одной правильная цена, на второй - нет. Разница только что одна таблица скопировалась в новый документ, а вторая в отдельный лист моей таблицы...
03.03.23, 06:14
Почему-то последние недели цена закрытия перестала подтягиваться. Не подскажете как это исправить?