Автоматическая загрузка котировок акций и валюты: новые функции EXCEL
Последние обновления EXCEL позволяют пользователям загружать данные по многим биржевым ценным бумагам в автоматическом режиме. Важно, что теперь это возможно сделать через встроенные типы данных и функции EXCEL без сторонних плагинов и VBA.
Как это работает?
Новые возможности опираются на встроенный тип данных «Акции». Теперь в любой ячейке можно ввести тикер ценной бумаги, например MSFT, выбрать на вкладке «Данные» тип «Акции».
После этого EXCEL предлагает уточнить во вкладке «Выбор данных», о какой конкретно ценной бумаге идет речь. Это необходимо, так как данные могут быть загружены с разных бирж (NYSE, NASDAQ, Лондонская биржа — LSE, Шанхайская биржа – SSE и т.п.). Важно, что в перечне рынков присутствует и Московская биржа (полный список биржевых площадок). Это, значит, что есть возможность анализировать наборы бумаг с разных бирж, что довольно удобно.
После выбора ценной бумаг, тикер конвертируется в ее официальное название и появляется возможность в соседних колонках отображать информацию по выбранной ценной бумаге. Например, можно посмотреть ее текущую цену.
Список данных по бумагам довольно внушительный. В ячейках можно отображать среди прочего:
- Текущая Цена
- Цена закрытия
- Изменение цены (в %)
- Название биржи
- Тикер
- Валюта бумаги
- Время последних торгов (полезно для зарубежных бирж)
А также некоторые фундаментальные характеристики бумаг:
- Капитализацию
- Количество обыкновенных акций
- Количество сотрудников компании
- Расположение главного офиса
- Сектор экономики
- Год создания компании
- P/E
- Коэффициент бета
Важно, что кроме акций компаний доступна так же информация по ETF (в том числе по ETF и БПИФ Московской биржи).
Данные можно обновить в любой момент, нажав на «Обновить» на вкладке «Данные». Автоматическое обновление довольно просто настроить при помощи VBA.
Загрузка курса валют
Загрузка данных по валютным парам очень похожа на работу с акциями. В ячейке необходимо ввести обозначение валютной пары в произвольном формате. Например, для получения курса доллара США к рублю – USD/RUB. После этого на вкладке «Данные» выбираем тип данных «Акции» (немного странно, но именно так необходимо сделать). EXCEL автоматически распознает валютную пару и поменяет ее отображение в ячейке, подставив специальный значок финансовых данных.
Для получения данных по валютной паре в выпадающем списке выбираем необходимый параметр. Например, для получения курса валюты – Price (пока все финансовые параметры не переведены на русский язык).
Microsoft на этот раз не поскупились. Среди валют доступны даже некоторые криптовалюты. Например, для получения данных по Биткоину достаточно ввести символ валютной пары Биткоин/Доллар (BTC/USD). Кроме биткоина доступных котировки эфириума, XRP и других популярных сегодня криптовалют.
Новый синтаксис для финансовых функций EXCEL
Довольно удобен синтаксис новых финансовых функций. После того, как в ячейке выбран тип данных «Акции». В любой другой ячейке можно сделать ссылку на нее, поставить «.» и выбрать нужную функцию из выпадающего списка. Например, как в примере с Microsoft, можно в ячейке набрать B2.[P/E]
Недостатки
Они тоже, на мой взгляд, имеются. Например, нельзя посмотреть дивиденды по бумаге. Нет цены типа Adjusted Close, которая бы учитывала дивидендную доходность. Это ограничивает сколько-нибудь серьезное использование новых возможностей для отслеживания доходности ценной бумаги или набора ценных бумаг (портфеля).
Кроме того нет возможности посмотреть историю изменения цены или других параметров (TimeSeries).
В целом все изменения очень полезные и удобные, но новый функционал пока уступает аналогу из Google Spreadsheets. Будем надеяться, что это только первый шаг Microsoft в нужном направлении.
Пример использования новых функций EXCEL для отслеживания изменения стоимости портфеля ценных бумаг прилагается.
Файлы для скачивания
Отслеживание портфеля акций в EXCEL
Файл: stocks.xlsx
Размер: 98812 байт
Для скачивания файлов необходимо зарегистрироваться или авторизоваться
Как получить котировки акций в Excel (за 5 шагов)
Большинство людей используют Microsoft Excel для вычислений и хранения данных. Есть и другие функции, которые очень полезны, но они недостаточно понятны. Одна из них — возможность импортировать подробные котировки акций непосредственно в электронную таблицу Microsoft Excel.
Шаг 1
Откройте Microsoft Excel. Сначала выберите «Пуск» в главном меню операционной системы. Затем выберите «Программы». Затем нажмите «Microsoft Office» в меню программ. Наконец, выберите «Microsoft Excel» в меню Microsoft Office.
Шаг 2
Щелкните «Данные» в экранном меню Microsoft Excel в его главном меню. Затем выберите «Получить внешние данные» в меню данных. Появится диалоговое окно со списком установленных источников данных. Наконец, выберите источник данных для тега «Котировка акций инвестора».
Шаг 3
Выберите ячейку в электронной таблице для ввода информации об акциях или выберите параметр «Создать новую электронную таблицу», чтобы поместить цену акций в новую электронную таблицу. Выбрав тот или иной вариант, нажмите «ОК» в диалоговом окне.
Шаг 4
Введите символ действия в следующем диалоговом окне Microsoft Excel. Если пользователь хочет обновить цену акций в будущем, он выбирает вариант «Использовать это значение / ссылку для будущих обновлений». Вы также можете установить второй флажок, если хотите, чтобы информация обновлялась сама.
Шаг 5
Сохраните файл Microsoft Excel для будущего использования. Выберите «Сохранить» в главном меню файла, назовите файл и выберите соответствующее место на жестком диске вашего компьютера, чтобы сохранить его.
Как связать цены на акции с файлом Excel
Связывание цен на акции с таблицами Excel может избавить вас от необходимости экспортировать новую информацию или, возможно, от постоянного просмотра в интернет-браузере или телефонном приложении для просмотра текущих цен на акции.
Создано Джошуа Краудером
Наличие книги Excel со связанной котировкой акций может иметь неоценимое значение. Особенно, если вы создаете собственную информацию о портфеле в Excel на основе курса акций. В этом руководстве мы будем искать в магазине Microsoft надстройку, которая будет отображать цены на акции в Excel. Кроме того, мы свяжем акции с надстройкой, и цены будут отображаться в электронной таблице.
Нужна надстройка
Более старые версии Excel позволяют обновлять ячейки прямо из MSN Money. Что ж, люди, те дни прошли. Еще один способ получать обновленные котировки акций на листах Excel — это надстройка. Если на ленте нет вкладки разработчика для управления надстройками, узнайте, как добавить ее сюда.
Чтобы найти надстройку, начните с нажатия на вкладку разработчика. Это открывает множество вариантов. Нажмите кнопку надстроек, расположенную в разделе надстроек.
Найти надстройки
Создано Джошуа Краудером
Искать в офисном магазине
Чтобы найти надстройки Excel в магазине Microsoft Store, нажмите кнопку с надписью Магазин Office.
Купить надстройку
Создано Джошуа Краудером
Выберите надстройку
Введите stock в поле поиска, и вы найдете несколько бесплатных надстроек Excel в списке. Выберите надстройку «Stock Connector», нажав кнопку «Добавить».
Добавляем Stock Connector
Вы не ограничены надстройкой Stock Contributor. Вы можете проверить, какие надстройки подходят вам лучше всего.
Создано Джошуа Краудером
Перейти к Stock Connection
После добавления надстройки вы сможете найти надстройку на главной вкладке всех ваших листов Excel.
Найдите надстройку
По умолчанию надстройка будет расположена на главной вкладке и появится в дальнем правом углу ленты.
Создано Джошуа Краудером
Запустить Stock Connector
Щелкните значок надстройки, чтобы запустить Stock Connector, и панель появится в правой части листа Excel.
Стандартная панель разъемов
На приведенной выше иллюстрации загружена надстройка Stock Connector, но нет ссылок на котировки акций.
Создано Джошуа Краудером
Найдите свой сток
Чтобы найти котировку акций, щелкните поле с надписью «Введите акцию» и введите либо название публичной компании, от которой вы хотите получить котировку акций, либо введите их тикер.
Далее появится окно. Щелкните ячейку, в которой цитата должна отображаться в вашей электронной таблице, затем нажмите кнопку ОК.
Подробная информация об этой акции, включая цену, изменение цены и обновленное время, появится на панели надстройки справа. Цитата также появится в выбранной вами ячейке.
Добавление цитаты в электронную таблицу
Создано Джошуа Краудером
Процентное изменение
Процентное изменение котируемой цены также может отображаться в вашей электронной таблице. Чтобы добавить процентное изменение, щелкните процент на панели, и появится окно с названием «Выбрать данные». Щелкните ячейку, в которой вы хотите отобразить процентное изменение, и нажмите ОК.
Добавление процентного изменения котировки акций на рабочий лист
Создано Джошуа Краудером
Форматирование
Надстройка даст вам только котировку акций и процентное увеличение или уменьшение для компании. Это означает, что вам нужно будет выполнить собственное форматирование, чтобы идентифицировать эту информацию.
Добавить заголовки столбцов и строк
Создано Джошуа Краудером
Чтобы узнать больше об использовании связывания книг Excel с внешними источниками, я рекомендую Библию Excel . Я использую Библию Excel в течение многих лет, чтобы лучше понять все аспекты этого продукта Microsoft.
Портфель взвешенный по капитализации или в равных долях (часть 5) | Готовый шаблон для учета инвестиций
Сделал шаблон для учета инвестиций по стратегии равно взвешенного портфеля. Расскажу вкратце что умеет делать таблица.
Таблица состоит из нескольких блоков. Для удобства и наглядности блоки выделены разными цветами. Вот как это выглядит у меня на начальном этапе.
Начало — веса, котировки и названия
Перед началом пользования таблицей нужно указать сколько акций в портфеле вы хотите иметь. Это нужно для вычисления доли на одну акцию (5, 10 или 20%).
В первом блоке накидываем для себя список акций, который вы хотите иметь в портфеле. Для примера я добавил в файл 20 компаний из индекса Мосбиржи.
Котировки подтягиваются с биржи автоматически (прописана формула). Если будете менять бумагу на другую (или добавлять новые имена), в формуле нужно прописать новый тикер.
На примере формулы для Сбера. Тикер выделил красным. Его и нужно менять на другой.
= IMPORTXML ( «http://iss.moex.com/iss/engines/stock/markets/shares/securities/ SBER .xml» , «/document/data[@id=»»marketdata»»]/rows/row[@BOARDID=»»TQBR»»]/@MARKETPRICE» )
Твой портфель
Второй сектор показывает текущее состояние вашего портфеля. Сколько и каких акций куплено и на какую сумму. А также пропорции этих акций в портфеле.
Заполнять количество акций можно в колонке «Акций куплено«. Но бывает ситуации, что бумаги могут быть раскиданы по разным брокерам. И даже акции одного эмитента могут находиться по разным счетам. К примеру у меня так. Часть у одного брокер, часть у другого. Есть даже бумаги, лежащие у одного брокера, но по разным счетам (ИИС и обычный брокерский счет).
Это доставляет определенные неудобства при заполнении таблицы. Нужно постоянно складывать данные в уме. «у брокера А у меня лежит 100 акций Сбера, у брокера Б — еще 250. По брокеру В — сегодня купил 60 и было до этого на счете 40. Сколько итого нужно записать?» Или бывает случайно удалил данные по количеству акций, к примеру того же Сбера. Типа рука дрогнула и ты не заметил сразу (и не можешь сделать отмену действий). И что нужно сделать, чтобы восстановить данные? Пройтись по всем своим брокерам, посмотреть нет ли у них акций Сбера. А если удалил не одну, а несколько ячеек? У меня так было несколько раз. Приходилось не только восстанавливать, но делать сверку по всем брокерам — вдруг я что-то еще удалил случайно.
Второй минус — ты не видишь полной картины, какие бумаги и у какого брокера у тебя находятся.
Для подобных случаев я добавил разделение по брокерам. Заполняем количество по каждому брокеру отдельно, а потом все автоматом суммируется в отдельную колонку (акций куплено) При необходимости можно нажимать на «плюсик» (или «минус») и колонки с брокерами будут скрыты.
Помощь в ребалансировке
Для наглядности я сделал колонку «Расхождение весов«. Она показывает на сколько отклоняются текущие пропорции акций от первоначально заданных. В зависимости от цвета колонки инвестор понимает, что ему нужно сделать с акциями:
- красный цвет — вес акции в портфеле превышен. Нужно продать часть.
- зеленый цвет — доля акций меньше заданного. Нужно докупать.
Если портфель постоянно пополняется, то продавать необязательно. Можно выравнивать пропорции покупкой отстающих, доля которых на данный момент минимальна, а расхождение максимально (зеленый цвет).
Новые пополнения
В таблице можно заполнить поле «Сумма для инвестиций (кэш)» и система сама посчитает каких акций и в каком количестве нужно купить. Причем учитывается уже купленные акции.
По сути — это подсказка куда направить новые поступления денег. Даже думать не надо. ��
Дивиденды
Таблица выдергивает данные по дивидендам за 12 месяцев с сайта Доход (можете сравнить правильность). В итоге мы наглядно видим не только данные по каждой бумаге, но и сколько может денег приносить наш портфель в целом и какая у него дивидендная доходность. Можно поиграть с наименованием бумаг, чтобы увеличить дивидендную доходность портфеля.
Сектора
Необязательный столбец. Показывает к какому сектору относятся ваши акции. Я использую его для наглядности.
В шаблоне выводится две диаграммы — сколько веса занимает в вашем портфеле каждый сектор. Одна диаграмма показывает запланированный веса портфеля (бенчмарк). Вторая — реальные.
Во-первых, когда вы выбираете эмитентов в свой портфель, сразу видно распределение по секторам. Это помогает избежать сильного доминирования одного сектора в портфеле. К примеру, большинство крупных компаний на Мосбирже относятся к нефтегазовому сектору. И если вы захотите собрать портфель из 10 акций голубых фишек, то, скорее всего, больше половины веса будет приходиться на нефтегаз. А это с точки зрения диверсификации — не есть гуд. И желательно такой портфель разбавить акциями из других отраслей.
Этот же принцип работает и в обратную сторону. Мы можем посмотреть на шаблонный вариант портфеля и заметить, что нам не хватает какого-нибудь сектора (доля которого в портфеле презрительно мала или вообще отсутствует). Это будет нам сигнал к размышлению.
Во-вторых, на диаграмме по реальному распределению по секторам мы сразу можем увидеть сильно ли наш портфель «разъехался», по сравнению с шаблонным вариантом.
К примеру, глядя на диаграммы ниже, я сразу вижу, что доля сектора «Металл и добыча» у меня намного больше запланированного. А вот сектор «Нефтегаз» сильно отстает. Следовательно, мне нужно направлять в него все новые деньги в первую очередь. И пока не вкладываться в Металлы.
Файл-шаблон
Файл для учета равно взвешенного портфеля доступен по ссылке. Напоминаю, чтобы утащить к себе, нужно в меню «Файл» выбрать «Создать копию». В этом случае вам будет доступно редактирование документа.