Как я слежу за акциями в гугл-таблице: три простых способа
Эксельки
203K

Как я слежу за акциями в гугл-таблице: три простых способа

227

Я использую гугл-таблицы для портфеля инвестиций, потому что с ними проще вести учет.

Упрощение связано с тем, что можно в автоматическом режиме получать данные о текущих ценах и других параметрах активов, которые публикуются открыто.

Для этого я комбинирую три бесплатных сервиса, которые подтягивают данные с разных бирж с помощью 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 параметров каждой бумаги.

Смотрите, как это работает:


«Яху-финанс»

Для чего. Отслеживать любые финансовые данные со всего мира.

Чтобы подружить «Яху» и таблицы, можно пойти двумя путями: написать скрипт, который будет импортировать выдачу 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.

Запомнить

  1. API Московской биржи в гугл-таблицах — практически самый быстрый способ получить данные с Мосбиржи.
  2. Встроенная функция Googlefinance внутри гугл-таблиц пригодится, если нет времени разбираться с формулами, но хочется видеть курсы акций.
  3. Любые финансовые данные со всего мира получайте в API «Яху-финанс» с помощью простой функции IMPORTHTML.
  4. Если вы только начинаете инвестировать, пройдите бесплатный курс от Т⁠—⁠Ж.
А что облегчает вам учет инвестиций?
Комментарии проходят модерацию по правилам журнала
Загрузка
0

Пользователи Тинькофф Инвестиций могут следить за состоянием своего портфеля и через API заносить информацию в Google-таблицы. Тут написал об этом подробнее: https://habr.com/ru/post/516210

26

Dmitriy, посмотрела публикацию на Хабре, вставила в таблицу токен. Курс доллара не подгрузился, портфель тоже. Бедапечаль(

2

Лиза, скорее всего в статье используется API старой версии, а Тинькофф Инвестиции не так давно перешли на новую.
Попробуйте воспользоваться скриптами по этой ссылке https://github.com/ErhoSen/gas-tinkoff-trades и использовать функции только из API v2, должно получиться

1

Dmitriy, А api тинькофф инвестиций уже научились в readonly токены? А то стрёмно

2

Георгий, вот в тему вопрос. Занялся сборкой своей таблицы чтобы видеть сколько дивов и когда получу и нет нормальных инструментов для этого. Какие то кривые приложения где нужно руками вбивать свои покупки. А токен страшно как то в гугл табицы вставлять свой)

1

Иван, я сделал связку гугл таблицы и сервис Доход.

1

Святослав, Каким образом связали?

0

Михаил, формулами. В основном меня интересовали дивиденды на определенные компании.

Через IMPORTHTML И "TABLE".

Интересовали акции, которые больше 1 раза в год выплачивают дивиденды.

Спасибо за статьи, очень помогают.

0

Dmitriy, что делать если после вставки токена в ячейку B1 в листе «Настройки», в ячейке B3 не появляется текущий курс доллара? Опробованы все возможные для получения в личном кабинете токены

1

Dmitriy, посмотрела статью на хабре, вставила токен в таблицу, курс доллара не появился, портфель не подгружается. Бедапечаль

0

Dmitriy, а как там посмотреть "Избранное"?

0

Dmitriy, вы автор?

0

Михаил, да

3

Dmitriy, хорошая статья, спасибо 👍

1
0

Функции импорта, если их хотя бы пара десятков на листе работают из Google Sheets крайне медленно.
Сам использую API мосбиржи, но лист с портфелем открывается с неизменной ошибкой с предложением снизить количество IMPORTXML и подобных.

4

Сергей, если импортить при помощи gscript то такой проблемы не будет

2

Пончик, ну с ними с наскока не разобраться. Хотя это JavaScript.

0

Пончик, спасибо, попробую!

0

Сергей, да, такое есть. Но если ждёшь, то в итоге загрузка происходит. Вам правильно посоветовали гугл скрипты. Но с ними тоже нюансы есть. Хотя бы то, что это JavaScript.

1
0

Изначально делал колхоз на c++/php/html (локальный веб-сервер), портфель держу в csv, учитывая не только сами позиции, но и дивиденды/купоны, а также доходности по разным инструментам, группам и т.п.

Сейчас пишу приложение под андроид с тем же функционалом. Готовые не понравились :)

3

Михаил, я ещё веду учёт поступлений по каждой покупке внутри позиции, т.е. в любой момент могу глянуть доходность, цену и дату покупки, поступления по сути каждого лота (точнее, их группы). Так же по FIFO уменьшаю начисленные дивиденды, если продаю часть прежней покупки.

Всё в виде веб-сайта, от таблиц я отказался :)

1

Денис, вот мой опыт: https://habr.com/ru/post/477920/

1

Денис, у вас сложная структура получается наверное.

0

Михаил, немного замороченно, да :)

2

Денис, О вот это интересно! Очень интересно, расскажите подробней плиз!

0

Иван, и вторая картинка.

1

Иван, сложно как-то подробно расписать :) Есть сайт, он кушает csv - в них и портфель, и котировки. Котировки грузит отдельная программа с разных бирж через api, если не удалять файлы, можно смотреть состояние портфеля за прошлые даты. Также есть графики эквити + бенчмарки (таблицы с данными тоже в csv, собираются отдельно). В самом портфеле каждая покупка отдельно, в т.ч. и приходящиеся на неё дивиденды, это удобнее средней цены в терминале. Если нажать на тикер в общем списке (главная страница), то видны как раз покупки внутри одного тикера.

Для наглядности пара скринов не моего портфеля низкого риска (там нет ПД, так что ничего вроде не раскрываю :)).

1

Денис, можете поделиться этой таблицей или сайтом?

0

Денис, своя система учета у вас :)

0
0

В заголовке статьи Эксель, а в тексте про него ни слова. Поправьте, пожалуйста.

2

Alexander, да, спасибо. Исправили. Но про эксель я тоже писал: Free API Мосбиржи в формулах Microsoft Excel [ https://habr.com/ru/post/498268/ ].

2

26.08.20, 09:32

Отредактировано

Михаил, 365 — это не веб-версия, это просто версия, которую сейчас выдают с подпиской. Data Types в Excel 2019 не вошли, насколько я знаю (но «релизные» версии это сейчас скорее корпоративная история, а у реально работающих с рыночными данными людей должны быть расширения от Bloomberg или Refinitiv).

В веб-версии есть data types, но механизмы расширения Экселя (VBA, VSTO, JS add-ins, Office Scripts, что там еще) на каждой платформе свои. Это беда, конечно.

2

Marat, 102 это %.

2

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")

2

Pavel, можно конечно, но в таблице можно какие-то дополнительные вычисления производить, балансировку портфеля например, или строить собственные графики, а вносить в нее данные вручную утомительно и нудно

2

Binotto, рекомендуем остановиться на одном таком комментарии, а то придется вас забанить за флуд

2
0

Михаил, а как вытащить с мосбиржи gldrub_tom ?

2
0

Михаил, добрый день! (точнее, я пишу это ночью, потому что второй час пытаюсь воспроизвести функцию в гугл таблицах).
Прежде всего огромное спасибо за статьи и таблицы - каждая из них - просто кладезь полезной информации.
У меня есть таблица для ведения и расчета параметров структуры портфеля. Сейчас все тяну встроенными функциями googlefinance. Но данных по бпифам и облигациям сильно не хватает. Решила воспользоваться функциями из п.1. Не получается, выдается синтаксическая ошибка. Уже даже специально вставила в гугл таблице в те же ячейки - все равно ошибка. В вашей табличке при этом все работает.
Подскажите, пожалуйста, в чем может быть загвоздка?

1
0

Михаил, добрый день!
Нужен совет профессионала!
В секции http://iss.moex.com/iss/securities/"&A3&"/dividends.xml есть данные о дивидендах по ряду акций (SBER, LOKH, NLMK и др.).

Однако нет данных о дивидендах таких акций как AVAN, SGHZ, RZSB, LSNGP, HIMCP. Могли бы подсказать в какой секции их искать? Все перерыл... Не могу найти...

Заранее спасибо за ответ! )))

1
0

Подскажите, как подтягивать в таблицу цены облигаций по ISIN? USG*******89

1

Михаил, пытаюсь подставить ISIN иностранных облигаций, и ничего не подтягивается...

1

09.07.22, 14:17

Отредактировано

Написал для получения последней цены, странно что автор в примере приводит цену предидущего дня... боится что мы перегрузим сервак московской биржи))

1
0

API Московской биржи в гугл-таблицах перестали работать в последние дни.
Кто-то знает что случилось, это временно или надолго?

1

Павел, всё работает. Проверяйте на своей стороне.

Вот пример: https://docs.google.com/spreadsheets/d/1MI-FfjjOhPCYb0H2E9--bEWdsbu4HfOKly3ySySN-0k/

1
0

Михаил, похоже отвалился механизм передачи от московской в Таблицы :( Можете обновить код?

1
0

Эти таблицы жуткая вещь! Кучу раз бывает что пишешь формулу, а она не работает вообще по непонятным причинам! Пытаюсь получить данные с Мосбиржи, например по фонду по TRUR. У меня подтягивается цена 45 тыс. за 1 пай, когда реальная 5,9 руб.
Вписываю тикер в вашу таблицу - показывает правильную цену. Копируют оттуда формулу в свою таблицу (естественно корректируя ссылку на строку с тикером) - пишет синтаксическая ошибка, хотя все четко. Ладно, думаю, сейчас целиком лист с таблицей к себе скопирую и буду ковыряться.
Но как только таблица копируется (целиком в отдельный лист) к моей таблице, то она тоже начинает показывать цену TRUR не 5,9 руб., а 45 тыс. Я не понимаю что за магия такая происходит. Открыто параллельно две вкладки и на одной правильная цена, на второй - нет. Разница только что одна таблица скопировалась в новый документ, а вторая в отдельный лист моей таблицы...

1
0

Эти таблицы жуткая вещь! Кучу раз бывает что пишешь формулу, а она не работает вообще по непонятным причинам! Пытаюсь получить данные с Мосбиржи, например по фонду по TRUR. У меня подтягивается цена 45 тыс. за 1 пай, когда реальная 5,9 руб.
Вписываю тикер в вашу таблицу - показывает правильную цену. Копируют оттуда формулу в свою таблицу (естественно корректируя ссылку на строку с тикером) - пишет синтаксическая ошибка, хотя все четко. Ладно, думаю, сейчас целиком лист с таблицей к себе скопирую и буду ковыряться.
Но как только таблица копируется (целиком в отдельный лист) к моей таблице, то она тоже начинает показывать цену TRUR не 5,9 руб., а 45 тыс. Я не понимаю что за магия такая происходит. Открыто параллельно две вкладки и на одной правильная цена, на второй - нет. Разница только что одна таблица скопировалась в новый документ, а вторая в отдельный лист моей таблицы...

1
0

Почему-то последние недели цена закрытия перестала подтягиваться. Не подскажете как это исправить?

1

Сообщество