Учет инвестиций в гугл-таблицах №2 — получаем котировки акций и ETF с Мосбиржи
В прошлой статье мы разобрали способ получения котировок акций ( и ETF) с помощью функции GoogleFinance. Правда по некоторым российским бумагам получении инфы таким образом невозможно. Речь идет о привилегированных акциях (типа Сбера, Татнефти, Сургута) и практически всех российских биржевых фондах (FXIT, VTBX, SBSP и так далее).
Ок, будет разбираться как это можно сделать другим способом. Расскажу какие формулы можно использовать для этих целей. Отдаю готовый шаблон гугл-таблицы с уже заполненными данными.
Котировки российских акций
Для вызова котировок привилегированных акций (впрочем как и обыкновенных) используем формулу следующего вида (все пишется слитно, без пробелов):
Вот как это выглядит в таблице:
Получение котировок для российских привилегированных акций
Дополнительно, чтобы не прописывать руками, можно сделать автоматический вывод названия компании. Мы сразу будем видеть, что формула ничего не путает и отдает нам котировки нужных нам акций.
Для вывода названия акций (компании) используем формулу:
Получаем цену ETF и БПИФ в рублях / долларах и евро
Если мы захотим получить котировки российских биржевых фондов по вышеописанной формуле, просто заменив тикер акции на тикер ETF (или БПИФ), то нас постигнет фиаско. Формула будет выдавать ошибку.
Для биржевых фондов нужна формула следующего вида:
Как всегда, при необходимости меняем адрес ячейки с тикером.
Получаем название и котировки российских биржевых фондах в Гугл-таблицах
Что это за зверь такой?
Соответственно прописываем в формулу нужный идентификатор.
Дабы сэкономить читателю время, в формуле вызова котировок используем идентификатор:
Для автоматического получения названия фонда по тикеру, в формуле соответственно также нужно поменять идентификатор.
В таблице накидал несколько примеров вызова цен по валютным фондам.
Выводим котировки биржевых фондов РФ в долларах и евро
Цена иностранных акций на Мосбирже
На Московской бирже обращаются иностранные акции, номинированные в рублях.
Формула вызова котировок имеет следующий вид:
Красным пометил отличия от предыдущих формул:
Настройка таблицы или возможные проблемы
Для исправлении ошибки выбираем в меню «Файл->Настройки таблицы».
На вкладке «Общие» в поле Региональные настройки» нужно выбрать Соединенные Штаты. 
Нужно знать
Не забываем про задержку во времени. Так же как и ГуглФинанс, котировки по API Мосбиржи подгружаются примерно с 20 минутным опозданием.
В приоритете (там где это возможно) лучше использовать функцию GoogleFinfnce. Если в таблице много инструментов, то загрузка по API может быть очень долгой. В этом плане Гуглфинанс работает пошустрее. Поэтому используем API Мосбиржи только там, где Гугл бессилен: префы российских компаний и биржевые фонды.
Для акций у которых котировки рассчитываются до более 2-х знаков после запятой, тоже лучше использовать API. В этом случае цена будет передаваться более точная.
Из подобного есть еще Русал, ИнтерРао, Русгидро, АФК Система, Россети, ФСК ЕЭС и много чего еще.
Готовый шаблон
Комментарии, замечания, пожелания приветствуются!
В третьей части будем автоматически выдергивать информацию по дивидендам российских акций.
Всем огромное спасибо за замечания!
Акции представляют собой долевую собственность компании. Акции торгуются на таких биржах как: Нью-Йоркская фондовая биржа (NYSE), NASDAQ, Лондонская фондовая биржа (LSE) и многие другие. Вы можете покупать и продавать акции через брокеров.
Google Финансы — это средство для отслеживания акций.
Google также предлагает Google Финансы как способ для просмотра биржевых данных, включая диаграммы для просмотра производительности компании на протяжении определенного времени. Самое приятное то, что Google предлагает способ легко использовать данные из их службы Google Финансы в Таблицах.
В этом уроке вы узнаете, как использовать функцию GOOGLEFINANCE в Google Таблицах для того, чтобы получить данные от Google Финансов и вставить их в электронную таблицу.
Чтобы начать работу с уроком, убедитесь, что у вас есть аккаунт Google и перейдите в Диск Google. Создайте новую таблицу и продолжайте читать, чтобы узнать больше.
Зачем нужно отслеживать данные об акциях?
Для многих отслеживание данных акций является хобби. Цена акций для многих считается показателем успешности компании. Если вы заинтересованы в успехе компании, можете следить за ценой акций для его оценки.
Для некоторых инвестирование в акции является способом спланировать свой выход на пенсию. Многие люди покупают акции, облигации и взаимные фонды, ожидая увеличение их стоимости с течением времени для финансирования себя в те годы, когда они не будут работать.
Скриншот сверху показывает цену акций в Google Финансах, в то время как нижний показывает формулу в Google Таблицах, которая автоматически импортирует цену в электронную таблицу.
Самое главное: биржевые цены постоянно меняются. Если вы хотите контролировать цены на акции, вам нужен простой способ поддерживать ваши данные в актуальном состоянии.
Независимо от того, почему вы отслеживаете биржевые данные, Google Таблицы с подключением к Google Финансам являются самым простым способом, который я нашел, чтобы свести данные об акциях в электронную таблицу. Давайте научимся, как это сделать.
Как отслеживать акции с помощью функции GOOGLEFINANCE (короткий видео-урок)
На скриншоте ниже продемонстрировано, как построить электронную таблицу с динамическим отслеживанием акций в Google Таблицах.
Чтобы узнать больше об использовании функции GOOGLEFINANCE, ознакомьтесь с моей пошаговой инструкцией ниже.
Получение биржевых данных
Компании, чьи акции торгуются на фондовых биржах имеют биржевой код, который является сокращенной формой их названия, например AMZN для Amazon.
Обычно вы можете найти биржевые коды компаний через быстрый поиск, или можно использовать такой инструмент как поиск Yahoo Финансы для поиска кода акций компании по вашему выбору.
Когда я сравниваю компании, я создаю список биржевых кодов в Google Таблице в столбик:
Начните с создания списка биржевых кодов в столбце таблицы.
В Таблицах Google, мы можем использовать функцию =GOOGLEFINANCE для импорта биржевых данных. Чтобы начать, щелкните ячейку и начните вводить функцию.
Нет необходимости вводить полное имя компании для каждого биржевого кода. Напишем нашу первую функцию GOOGLEFINANCE и посмотрим на биржевой код и название компании. Вот как выглядит формула, которую мы будем использовать:
В приведенном ниже примере я использую формулу в ячейке A2, которая выводит название «Volkswagen AG» по биржевому коду VLKAY. Затем просто тянем формулу вниз для вывода названия компании для каждого биржевого кода.
Теперь, когда мы составили список биржевых кодов и названий компаний, за которыми мы следим, давайте получим некоторые финансовые данные о них.
Текущий курс акций
Чтобы получить цену акции, используйте формулу GOOGLEFINANCE следующим образом:
Можно ввести символ акции в скобках, или передать формуле номер ячейки с биржевым кодом для получения цены. На скриншоте ниже вы видите использование формулы, можно потянуть ее вниз, чтобы получить цены акций для каждой компании.
Цена акции за определенную дату
Что делать, если вы хотите зафиксировать цену акции за определенный день или узнать сколько акция стоила в прошлом? Мы можем сделать это в нашей таблице.
Давайте используем вот такую формулу, чтобы получить цену акции Amazon на момент закрытия торгов 1 февраля:
Эта формула выводит не только цену акции на момент закрытия торгов, но также выводит и дату.
Обратите внимание, что эта формула создает сетку с некоторыми дополнительными данными, включая заголовки «Дата» и «Закрытие», а также дату и цену на момент закрытия.
Если вы хотите вывести только цену при закрытии торгов, то нужно обернуть наши формулы в INDEX и потянуть вниз только нижнее правое поле (цена). Вот формула, которую вы можете использовать:
Я просто обернул формулу в INDEX, а затем добавил «,2,2)» в конце формулы, чтобы вывести только цену акции без заголовков.
Оберните ваши формулы в INDEX чтобы выводить исключительно цену акции.
История цены на акции
Вот функция, которую можно использовать для получения истории цены акции:
=GOOGLEFINANCE(«F», «price», DATE(2017,1,1), DATE(2017,2,11), «DAILY»)
Давайте разберем эту формулу:
На скриншоте ниже вы увидите, что результаты выводятся в строках таблицы:
Формула, введенная выше, выводит список ежедневных биржевых цен за определенный период.
Теперь у вас есть список цен на акции за определенный период времени. Вы можете ввести любой период, и Таблицы автоматически будут получать данные из Google Финансов. Далее легко превратить данные в диаграммы непосредственно внутри таблицы.
Теперь у нас есть отличное представление о ценах акций. Давайте узнаем, какие еще полезные финансовые данные мы можем вставить в Таблицах.
Соотношение цены к доходу (P/E)
Коэффициент цена-доход, или P/E является общим методом оценки компаний. Он делит стоимость акций компании на ее доходы. Широко признанный принцип оценки акций состоит в том, что высокий P/E коэффициент указывает на будущий рост акций.
Чтобы получить P/E коэффициент акций от Google Финансов, используйте следующую формулу:
В примере ниже я буду получать P/E коэффициент акций с помощью ссылки на ячейку, вот пример формулы:
Затем просто перетаскиваем формулу вниз, чтобы вывести коэффициент для всех моих выбранных акций.
Как читать коэффициент P/E? Вам необходимо исследовать аналогичные компании, чтобы понять смысл этих значений. Для акций в областях с более высокими темпами роста, таких как технологии (например Google или Facebook) коэффициент P/E будет выше, чем, например, у коммунальных компаний.
Если вы хотите узнать, как рассчитывается P/E коэффициент, посмотрите это определение от Investopedia.
Рост и Падение цен за последние 52 недели
Некоторые акции сильно меняются в цене в течение года. Это поможет следить за их колебаниями.
Чтобы получить самую высокую цену, которую имели акции в течение последних 52 недель, используйте следующую формулу:
Вы также можете получить самую низкую цену закрытия акции за последние 52 недели:
На скриншоте ниже вы можете увидеть, как я применил эти формулы к списку акций:
Я часто отслеживаю, где торгуются акции в привязке к их высшей и низшей цене. Если я собираюсь получить прибыль и продать акции, я стараюсь убедиться, что акции не находятся в низкой точке.
Эти формулы содержат лишь некоторые из доступных атрибутов, которые Google Таблицы могут получать от Google Финансов. Использование сочетаний этих факторов может помочь вам отслеживать ключевые финансовые показатели и исследовать компании — будь то для удовольствия или прибыли.
Для закрепления и дальнейшего изучения
В этом уроке вы использовали Google Таблицы, чтобы создать электронную таблицу для отслеживания ситуации на бирже. Является ли это для вас просто хобби или обучающим упражнением, или может быть потенциальным источником дохода, Таблицы имеют простую интеграцию с Google Финансами для того, чтобы вы могли отслеживать котировки интересующих вас акций.
Интересуетесь ли вы котировками акций? Используете ли вы Google Таблицы или другой инструмент для отслеживания ваших данных? Расскажите мне об этом в комментариях.
Автоматическое получение биржевых котировок в Google Spreadsheet
Приветствую вас, начинающие (и не только) портфелеводы. В прошлый раз (https://smart-lab.ru/blog/492069.php) мы значительно облегчили себе жизнь, частично автоматизировав ввод сделок. Сегодня сделаем еще один небольшой шажок в светлое будущее, научим наш Гугл документ по расписанию забирать актуальные котировки.
Шаг этот будет немного скучный (так как придется немного попрограммировать), но, надеюсь, полезный.
Итак, приступим. Без лишних слов хочу показать Гугл документ, в котором уже реализовано обновление котировок: https://docs.google.com/spreadsheets/d/1vGj_NszrlVt-1sA225RAgkOLEkdiGBmnSa3lTpsWfzI/edit?usp=sharing
Вполне возможно, что вам этого будет достаточно. Если же остались вопросы, то коротенько опишу, что он делает и как работает.
Во-первых, в этом документ есть лист «Портфель», который содержит информацию по вашим ценным бумагам. Для примера я оставил несколько бумаг (акций и облигаций), убрав все лишнее (в том числе и форматирование).
Для обновления котировок нужно нажать большую красную кнопку, после чего скрипт запросит у вас (вы должны быть залогинены в Гугл) определенные разрешения (их нужно дать, иначе не работает) и начнет получать данные. По результаты работы выдает такое сообщение:
Как же все работает?
Сначала в скрипте (сам скрипт можно скачать по ссылке: my.pcloud.com/publink/show?code=XZG0Q17ZuC1NsO3HKY8PaD2e8cAzXR5Byz47) идет «настроечная часть», где нужно прописать правильный ID листа, УРЛы до данных и т.п.:
var sheet_id = «1vGj_NszrlVt-1sA225RAgkOLEkdiGBmnSa3lTpsWfzI»;
var googleSpreadSheet = SpreadsheetApp.openById(sheet_id);
//my tickers settigns
var rangeDefPortfolio = «A8:AC120»;
var myBadTickersGlobal = [‘TODO:’,’EXTRA’]; //no real tickers which exist in my Portfolio. I want to ignore them
var myTickersDataGlobal = <>; //Some extra data about my tickers parsed from the Portolio sheet. I need to know number of lots, types, etc.
var myTickersGlobal = collectMyTickersNew();
var limitForWarningGlobal = 4;
var warningsArr = []; //if a price changes — we’ll push info about changed ticker into this global array
var allErrors = [];
С помощью функции collectMyTickersNew() собираются данные о ваших ценных бумагах с листа «Портфель». Это нужно, чтобы сохранять информацию только по нужным активам.
Основная функция — stocksAndBondsRealTime(), именно ее нужно запускать, чтобы получить обновленные котировки. Сама она достаточно простая.
Сначала забирает XML данные по указанным в начале адресам, формирует массив в нужном формате, а потом записывает его на лист «XMLStocks».
Все легко и элегантно, в результате на листе XMLStocks появляются нужные нам данные:
Для облигаций дополнительно сохраняется информация по купону (дата и размер следующего и т.д.), экспирации, НКД.
Потом эти данные можно использовать для подсчета будущих денежных поступлений (описывал здесь: https://smart-lab.ru/blog/492305.php )
Осталось два небольших действия:
1. На листе «Портфель» в колонку «Рыночная цена» прописываем формулу, которая будет показывать актуальные данные с листа XMLStocks: VLOOKUP($A8,XMLStocks!$A$2:$P$98, 3, FALSE)
2. Добавляем в триггеры проекта регулярное выполнение функции stocksAndBondsRealTime()
Я запускаю эту функцию раз в час, чаще необходимости не вижу. Да и зачем создавать лишнюю нагрузку на сервера Гугла и Биржи?
В дополнение для каждой бумаги в портфеле я добавил две колонки «Цена алерта покупать» и «Цена алерта продавать». Они нужны для оповещения по почте, когда рыночная цена оказывается меньше или больше желаемой. Скрипт, получив все котировки, проверяет целевые цены и формирует массив на отправку. Также у меня есть оповещения (код функций я здесь не привожу, там все несложно, просто сравниваю одну цену с другой), если цена какой-то бумаги изменилась больше, чем на заданную величину (limitForWarningGlobal = 4 в моем случае). Выглядит письмо примерно так:
Сначала я думал, что это мне будет полезно, но в реальной жизни оказалось, что я все равно никаких действий не предпринимаю, даже если цена поменялась на эти 4-5 процента. Разве что может быть полезно узнать, что облигации ваши сильно просели. Если у эмитента начались какие-то проблемы, то облигации падают не так быстро, как акции. Можно успеть слить их процентов по 90 от номинала, а не дожидаться падения до 30% и ниже (как у меня было с Татфондбанком)
Как я слежу за акциями в гугл-таблице: три простых способа
Я использую гугл-таблицы для портфеля инвестиций, потому что с ними проще вести учет.
Упрощение связано с тем, что можно в автоматическом режиме получать данные о текущих ценах и других параметрах активов, которые публикуются открыто.
Для этого я комбинирую три бесплатных сервиса, которые подтягивают данные с разных бирж с помощью 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 параметров каждой бумаги.
Смотрите, как это работает:
«Яху-финанс»
Для чего. Отслеживать любые финансовые данные со всего мира.
Я остановился на втором варианте — получение данных через формулу IMPORTHTML. Важно: эта формула подтягивает данные только из таблиц или списков, другую информацию через нее не получить.
Формула работает так: сервис Гугла обращается по адресу страницы, указанному в формуле, и ищет все таблицы, которые обозначены в разметке этой страницы. Таблиц может быть несколько, но у каждой свой порядковый номер. Поэтому в формуле нужно указать конкретный номер таблицы — данные из нее и появятся в гугл-таблице.
Моя формула обращается к тикеру, который нужно прописать самостоятельно, и ищет данные по этой акции на «Яху». К написанию некоторых тикеров есть требования:
