Меню

Новый синтаксис для финансовых функций EXCEL



Автоматическая загрузка котировок акций и валюты: новые функции EXCEL

Последние обновления EXCEL позволяют пользователям загружать данные по многим биржевым ценным бумагам в автоматическом режиме. Важно, что теперь это возможно сделать через встроенные типы данных и функции EXCEL без сторонних плагинов и VBA.

Как это работает?

Новые возможности опираются на встроенный тип данных «Акции». Теперь в любой ячейке можно ввести тикер ценной бумаги, например MSFT, выбрать на вкладке «Данные» тип «Акции».

Тип данных EXCEL - Акции

После этого EXCEL предлагает уточнить во вкладке «Выбор данных», о какой конкретно ценной бумаге идет речь. Это необходимо, так как данные могут быть загружены с разных бирж (NYSE, NASDAQ, Лондонская биржа — LSE, Шанхайская биржа – SSE и т.п.). Важно, что в перечне рынков присутствует и Московская биржа ( полный список биржевых площадок ). Это, значит, что есть возможность анализировать наборы бумаг с разных бирж, что довольно удобно.

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

EXCEL загрузка котировок акций

Список данных по бумагам довольно внушительный. В ячейках можно отображать среди прочего:

  • Текущая Цена
  • Цена закрытия
  • Изменение цены (в %)
  • Название биржи
  • Тикер
  • Валюта бумаги
  • Время последних торгов (полезно для зарубежных бирж)

А также некоторые фундаментальные характеристики бумаг:

  • Капитализацию
  • Количество обыкновенных акций
  • Количество сотрудников компании
  • Расположение главного офиса
  • Сектор экономики
  • Год создания компании
  • P/E
  • Коэффициент бета

Важно, что кроме акций компаний доступна так же информация по ETF (в том числе по ETF и БПИФ Московской биржи).

Данные можно обновить в любой момент, нажав на «Обновить» на вкладке «Данные». Автоматическое обновление довольно просто настроить при помощи VBA.

EXCEL обновление финансовых данных

Загрузка курса валют

Загрузка данных по валютным парам очень похожа на работу с акциями. В ячейке необходимо ввести обозначение валютной пары в произвольном формате. Например, для получения курса доллара США к рублю – USD/RUB. После этого на вкладке «Данные» выбираем тип данных «Акции» (немного странно, но именно так необходимо сделать). EXCEL автоматически распознает валютную пару и поменяет ее отображение в ячейке, подставив специальный значок финансовых данных.

Автоматическая загрузка курса валют в EXCEL. Встроенные функции

Для получения данных по валютной паре в выпадающем списке выбираем необходимый параметр. Например, для получения курса валюты – Price (пока все финансовые параметры не переведены на русский язык).

Microsoft на этот раз не поскупились. Среди валют доступны даже некоторые криптовалюты. Например, для получения данных по Биткоину достаточно ввести символ валютной пары Биткоин/Доллар (BTC/USD). Кроме биткоина доступных котировки эфириума, XRP и других популярных сегодня криптовалют.

Новый синтаксис для финансовых функций EXCEL

Довольно удобен синтаксис новых финансовых функций. После того, как в ячейке выбран тип данных «Акции». В любой другой ячейке можно сделать ссылку на нее, поставить «.» и выбрать нужную функцию из выпадающего списка. Например, как в примере с Microsoft, можно в ячейке набрать B2.[P/E]

Новые финансовые функции EXCEL. Информация по ценным бумагам

Недостатки

Они тоже, на мой взгляд, имеются. Например, нельзя посмотреть дивиденды по бумаге. Нет цены типа Adjusted Close, которая бы учитывала дивидендную доходность. Это ограничивает сколько-нибудь серьезное использование новых возможностей для отслеживания доходности ценной бумаги или набора ценных бумаг (портфеля).

Кроме того нет возможности посмотреть историю изменения цены или других параметров (TimeSeries).

В целом все изменения очень полезные и удобные, но новый функционал пока уступает аналогу из Google Spreadsheets. Будем надеяться, что это только первый шаг Microsoft в нужном направлении.

Пример использования новых функций EXCEL для отслеживания изменения стоимости портфеля ценных бумаг прилагается.

Отслеживание портфеля акций в EXCEL
Файл: stocks.xlsx
Размер: 98812 байт

Для скачивания файлов необходимо зарегистрироваться или авторизоваться

Источник

Таблицы для отслеживания акций

22 мая 2020, 15:27

В данной статье мы рассмотрим полезные сервисы для инвесторов. Проанализируем, где сравнивать и выбирать акции компаний РФ и США, где подбирать облигации, ETF и фонды недвижимости REIT. Современные технологии упрощают процесс анализа и выбора активов. Хотя в начале нулевых, заниматься трейдингом и инвестированием могли только избранные, поскольку информации относительно данной отрасли было минимум. Полезные сервисы для инвестора позволяют определить прибыльность дивидендов, отслеживать важные дивидендные даты. Кроме этого для учета инвестиций, сравнения, анализа инвестиционных портфелей есть неплохие решения.

Далее будет представлена подборка самых полезных инструментов, детально останавливаться на каждом не будем, это наводка для инвесторов кто в поиске дополнительных инструментов.

E-disclosure.ru

Сайт центра раскрытия корпоративной информации, это основа основ. Эта главная страница в интернете для человека, который инвестирует в российский рынок — это первоисточник. Именно сюда компании на прямую выкладывают корпоративную информацию, события, отчеты. Далее СМИ и прочие сайты-агрегаторы растаскивают данные. Сайт бесплатный.

Читайте также:  Что означает каскадные таблицы стилей

Сайты базы данных

Это сайты— агрегаторы, в чем то они отличаются, но можно выбрать определенный вариант для себя. Но, 100% полагаться на информацию на этих сайтах нельзя, потому что сюда данные забиваются в ручную или же используются парсеры. Бывают ошибки, не обновленные данные, поэтому лучше сравнивать информацию с нескольких источников.

  • Blackterminal — здесь есть карточка с информацией о каждой компании, данные за 5 лет приводятся, есть финансовые показатели, данные по рентабельности, собственникам фирмы, новости по эмитентам. Здесь можно вести портфель. Платный сервис, максимальная плата 590 рублей ежемесячно, но есть и бесплатные варианты;
  • FinanceMarker — присутствует скринер по подбору акций, вы можете выбрать актив основываясь на показателях, которые вас интересуют, здесь есть детальная информация по дивидендам. Представлены подборки инвестиционных идей, портфели управляющих. Сайт удобный, есть платные и бесплатные варианты;
  • Тезис (Tezis) — сервис собирает карточки популярных зарубежных компаний со всей детальной информацией, отличие от аналогов, что сервис разработан с Ньютон брокером, что позволяет покупать бумаги на прямую. Доступно мобильное приложение;
  • Conomy — параметры по акциям доступны для работы. Из минусов — отсутствует США, нет облигаций, это чисто российский фондовый рынок, доступ от 400 рублей;
  • Смартлаб — сайт по российском рынку, доступна качественная информация по акциям, дивидендам, рынку облигациям, доступна хорошая карта доходности.

Если вас интересует американский рынок, используйте скринеры США:

  • Finviz;
  • Yahoo Finance;
  • Stockwatcher;
  • Marketwatch и т.д.

ИнвестИдеи

Сервис весьма удобный, подходит для ленивых инвесторов, которые не хотят ничего анализировать. Здесь собраны инвестиционные рекомендации от топовых инвестиционных компаний, причем рекомендации оцениваются по истечению заданного срока. В результате чего получается, что каждый прогнозист получает рейтинг. Это весьма удобно и эффективно. По праву это лучший сервис для инвесторов.

Есть бесплатная версия, но доступны платные тарифы.

TradingView

Для технического анализа — этот сервис оптимальное решение. Здесь доступны удобные инструменты для онлайн-графика, представлены разные активы, есть блог с инвестиционными идеями. Но, именно виджет этого сервиса используют разные брокеры и компании.

Дивиденды

УК Доходъ — сайт считается самым удобным, можно ознакомиться с данными по дивидендной политике, но представлены только российские компании, доступны более продвинутые варианты, как вариант: InvestMint.

Касательно иностранного рынка — Dividend.com, удобные фильтры, много полезной информации, можно подбирать под себя компании с определенной дивидендной доходностью и по другим параметрам.

Другие категории активов

Здесь доступны такие варианты в США, как: Nareit, Reitscreener. Если интересуют облигации, нужно отметить Rusbonds, InvestFunds (детальная информация по каждой бумаге), УК Доходъ, Смартлаб.

Скринеры ETF для российских активов — RusEtfs, сервис отличный в работе. По американским — ETF.com и ETF Database.

Где можно вести учет портфелей? Intelinvest считается одним из самых практичных, доступны различные активы от акций до дивидендов, представлены опции расчета расходов, доходов для каждого портфеля по отдельности. Работает отличное приложение, которое позволяет следить за состоянием портфеля на протяжении дня. Здесь присутствует валютная переоценка, что весьма удобно. Доступны бесплатные версии и платные, цена может дойти до 200 рублей ежемесячно.

Capitalgains.ru — еще один сервис, не самый современный с точки зрения визуального оформления, но удобный, чтобы делать тестирование портфелей сколько в прошлом вы заработали, если бы вложили средства. Отличный сервис, но нет аналогов американским вариантам.

Portfolio Charts и Portfolio Vizualiser — это мощные сервисы, профессиональные, требуют детального изучения и анализа, но результат того стоит.

Перед вами представлены одни из лучших сервисов для инвесторов, которые достойны внимания. Возможно какой-то из инструментов заинтересует вас и упростит инвестирование.

Источник

Таблицы для отслеживания акций

22 мая 2020, 15:27

В данной статье мы рассмотрим полезные сервисы для инвесторов. Проанализируем, где сравнивать и выбирать акции компаний РФ и США, где подбирать облигации, ETF и фонды недвижимости REIT. Современные технологии упрощают процесс анализа и выбора активов. Хотя в начале нулевых, заниматься трейдингом и инвестированием могли только избранные, поскольку информации относительно данной отрасли было минимум. Полезные сервисы для инвестора позволяют определить прибыльность дивидендов, отслеживать важные дивидендные даты. Кроме этого для учета инвестиций, сравнения, анализа инвестиционных портфелей есть неплохие решения.

Далее будет представлена подборка самых полезных инструментов, детально останавливаться на каждом не будем, это наводка для инвесторов кто в поиске дополнительных инструментов.

E-disclosure.ru

Сайт центра раскрытия корпоративной информации, это основа основ. Эта главная страница в интернете для человека, который инвестирует в российский рынок — это первоисточник. Именно сюда компании на прямую выкладывают корпоративную информацию, события, отчеты. Далее СМИ и прочие сайты-агрегаторы растаскивают данные. Сайт бесплатный.

Сайты базы данных

Это сайты— агрегаторы, в чем то они отличаются, но можно выбрать определенный вариант для себя. Но, 100% полагаться на информацию на этих сайтах нельзя, потому что сюда данные забиваются в ручную или же используются парсеры. Бывают ошибки, не обновленные данные, поэтому лучше сравнивать информацию с нескольких источников.

  • Blackterminal — здесь есть карточка с информацией о каждой компании, данные за 5 лет приводятся, есть финансовые показатели, данные по рентабельности, собственникам фирмы, новости по эмитентам. Здесь можно вести портфель. Платный сервис, максимальная плата 590 рублей ежемесячно, но есть и бесплатные варианты;
  • FinanceMarker — присутствует скринер по подбору акций, вы можете выбрать актив основываясь на показателях, которые вас интересуют, здесь есть детальная информация по дивидендам. Представлены подборки инвестиционных идей, портфели управляющих. Сайт удобный, есть платные и бесплатные варианты;
  • Тезис (Tezis) — сервис собирает карточки популярных зарубежных компаний со всей детальной информацией, отличие от аналогов, что сервис разработан с Ньютон брокером, что позволяет покупать бумаги на прямую. Доступно мобильное приложение;
  • Conomy — параметры по акциям доступны для работы. Из минусов — отсутствует США, нет облигаций, это чисто российский фондовый рынок, доступ от 400 рублей;
  • Смартлаб — сайт по российском рынку, доступна качественная информация по акциям, дивидендам, рынку облигациям, доступна хорошая карта доходности.
Читайте также:  Двустворчатые моллюски Описание особенности строение и виды двустворчатых моллюсков

Если вас интересует американский рынок, используйте скринеры США:

  • Finviz;
  • Yahoo Finance;
  • Stockwatcher;
  • Marketwatch и т.д.

ИнвестИдеи

Сервис весьма удобный, подходит для ленивых инвесторов, которые не хотят ничего анализировать. Здесь собраны инвестиционные рекомендации от топовых инвестиционных компаний, причем рекомендации оцениваются по истечению заданного срока. В результате чего получается, что каждый прогнозист получает рейтинг. Это весьма удобно и эффективно. По праву это лучший сервис для инвесторов.

Есть бесплатная версия, но доступны платные тарифы.

TradingView

Для технического анализа — этот сервис оптимальное решение. Здесь доступны удобные инструменты для онлайн-графика, представлены разные активы, есть блог с инвестиционными идеями. Но, именно виджет этого сервиса используют разные брокеры и компании.

Дивиденды

УК Доходъ — сайт считается самым удобным, можно ознакомиться с данными по дивидендной политике, но представлены только российские компании, доступны более продвинутые варианты, как вариант: InvestMint.

Касательно иностранного рынка — Dividend.com, удобные фильтры, много полезной информации, можно подбирать под себя компании с определенной дивидендной доходностью и по другим параметрам.

Другие категории активов

Здесь доступны такие варианты в США, как: Nareit, Reitscreener. Если интересуют облигации, нужно отметить Rusbonds, InvestFunds (детальная информация по каждой бумаге), УК Доходъ, Смартлаб.

Скринеры ETF для российских активов — RusEtfs, сервис отличный в работе. По американским — ETF.com и ETF Database.

Где можно вести учет портфелей? Intelinvest считается одним из самых практичных, доступны различные активы от акций до дивидендов, представлены опции расчета расходов, доходов для каждого портфеля по отдельности. Работает отличное приложение, которое позволяет следить за состоянием портфеля на протяжении дня. Здесь присутствует валютная переоценка, что весьма удобно. Доступны бесплатные версии и платные, цена может дойти до 200 рублей ежемесячно.

Capitalgains.ru — еще один сервис, не самый современный с точки зрения визуального оформления, но удобный, чтобы делать тестирование портфелей сколько в прошлом вы заработали, если бы вложили средства. Отличный сервис, но нет аналогов американским вариантам.

Portfolio Charts и Portfolio Vizualiser — это мощные сервисы, профессиональные, требуют детального изучения и анализа, но результат того стоит.

Перед вами представлены одни из лучших сервисов для инвесторов, которые достойны внимания. Возможно какой-то из инструментов заинтересует вас и упростит инвестирование.

Источник

Учет инвестиций в гугл-таблицах №2 — получаем котировки акций и ETF с Мосбиржи

В прошлой статье мы разобрали способ получения котировок акций ( и ETF) с помощью функции GoogleFinance. Правда по некоторым российским бумагам получении инфы таким образом невозможно. Речь идет о привилегированных акциях (типа Сбера, Татнефти, Сургута) и практически всех российских биржевых фондах (FXIT, VTBX, SBSP и так далее).

Ок, будет разбираться как это можно сделать другим способом. Расскажу какие формулы можно использовать для этих целей. Отдаю готовый шаблон гугл-таблицы с уже заполненными данными.

  1. Котировки российских акций
  2. Получаем цену ETF и БПИФ в рублях / долларах и евро
  3. Цена иностранных акций на Мосбирже
  4. Настройка таблицы или возможные проблемы
  5. Нужно знать
  6. Готовый шаблон

Котировки российских акций

Для вызова котировок привилегированных акций (впрочем как и обыкновенных) используем формулу следующего вида (все пишется слитно, без пробелов):

= IMPORTxml ( «https://iss.moex.com/iss/engines/stock/markets/shares/boards/TQBR/securities.xml?iss.meta=off&iss.only=marketdata&marketdata.columns=SECID,LAST» , concatenate ( «//row[@SECID='» , B5 , «‘]/@LAST» ) )

В формуле B5 (выделено красным) — это адрес ячейки, где прописан тикер акции (у вас адрес может быть другим). В остальном формула остается неизменной для всех российских акций.

Читайте также:  Html таблица черными линиями

Вот как это выглядит в таблице:

Префы РФ - котировки

Получение котировок для российских привилегированных акций

Дополнительно, чтобы не прописывать руками, можно сделать автоматический вывод названия компании. Мы сразу будем видеть, что формула ничего не путает и отдает нам котировки нужных нам акций.

Для вывода названия акций (компании) используем формулу:

= IMPORTxml ( «https://iss.moex.com/iss/engines/stock/markets/shares/boards/TQBR/securities.xml?iss.meta=off&iss.only=securities&securities.columns=SECID,SECNAME» , concatenate ( «//row[@SECID='» , B5 , «‘]/@SECNAME» ) )

Также при необходимости меняем только адрес ячейки с тикером (в моей формуле — это B5).

Получаем цену ETF и БПИФ в рублях / долларах и евро

Если мы захотим получить котировки российских биржевых фондов по вышеописанной формуле, просто заменив тикер акции на тикер ETF (или БПИФ), то нас постигнет фиаско. Формула будет выдавать ошибку.

Для биржевых фондов нужна формула следующего вида:

= IMPORTxml ( «https://iss.moex.com/iss/engines/stock/markets/shares/boards/TQTF/securities.xml?iss.meta=off&iss.only=marketdata&marketdata.columns=SECID,LAST» , concatenate ( «//row[@SECID='» , B14 , «‘]/@LAST» ) )

Как всегда, при необходимости меняем адрес ячейки с тикером.

Котировки ETF

Получаем название и котировки российских биржевых фондах в Гугл-таблицах

На первый взгляд формулы для акций и ETF абсолютно идентичны. Правда есть небольшое отличие всего в четыре буквы. Для вызова акций мы использовали в формуле TQBR, а для ETF — заменили на TQTF.

Что это за зверь такой?

TQBR или TQTF — это идентификатор режима торгов. Узнать его можно на сайте Мосбирже. Идентификатор прописывается в карточку каждого инструмента. Для примера возьмем ETF FXRU. Находим его через поиск на сайте бирже. И видим TQTF.

Соответственно прописываем в формулу нужный идентификатор.

Идентификатор торгов

Дабы сэкономить читателю время, в формуле вызова котировок используем идентификатор:

  • для акций — TQBR;
  • для рублевых ETF — TQTF;
  • для ETF с расчетам в евро — TQTE;
  • для ETF с расчетам в долларах — TQTD.

Для автоматического получения названия фонда по тикеру, в формуле соответственно также нужно поменять идентификатор.

В таблице накидал несколько примеров вызова цен по валютным фондам.

Цены ETF в долларах и евро

Выводим котировки биржевых фондов РФ в долларах и евро

Цена иностранных акций на Мосбирже

На Московской бирже обращаются иностранные акции, номинированные в рублях.

В отличии от оригинала, они имеют тикер дополненный «-RM». То есть для Apple, рублевый тикер будет AAPL-RM, для Facebook вместо FB — пишем FB-RM и так далее.

Формула вызова котировок имеет следующий вид:

= IMPORTxml ( «https://iss.moex.com/iss/engines/stock/markets/ foreignshares /boards/ FQBR /securities.xml?iss.meta=off&iss.only=marketdata&marketdata.columns=SECID,LAST» , concatenate ( «//row[@SECID='» , B47 , «‘]/@LAST» ) )

Красным пометил отличия от предыдущих формул:

  • Как обычно — меняем адрес ячейки с тикером под себя.
  • Рублевые иностранные акции имеют идентификатор торгов — FQBR
  • Вместо shares ставим foreignshares (иностранные ценные бумаги).

Настройка таблицы или возможные проблемы

Иногда про получении данных о котировках по API (по вышеописанным формулам) может выходить ошибка. Часто причина ошибки — неправильные настройки таблицы. Данные передаются в формате дробных чисел с точкой (например — 0.55). В настройках вашей таблицы может по умолчанию стоит отображение чисел с запятой (0,55).

Для исправлении ошибки выбираем в меню «Файл->Настройки таблицы».

Настройки таблицы

На вкладке «Общие» в поле Региональные настройки» нужно выбрать Соединенные Штаты.

Нужно знать

Не забываем про задержку во времени. Так же как и ГуглФинанс, котировки по API Мосбиржи подгружаются примерно с 20 минутным опозданием.

На открытии торгов Мосбиржи (в 10 утра по МСК) — некоторое время все (или многие) котировки могут быть недоступны и формула будет выдавать ошибку.

В приоритете (там где это возможно) лучше использовать функцию GoogleFinfnce. Если в таблице много инструментов, то загрузка по API может быть очень долгой. В этом плане Гуглфинанс работает пошустрее. Поэтому используем API Мосбиржи только там, где Гугл бессилен: префы российских компаний и биржевые фонды.

Для акций у которых котировки рассчитываются до более 2-х знаков после запятой, тоже лучше использовать API. В этом случае цена будет передаваться более точная.

Как пример, акции ВТБ. Текущие котировки — 0.04244. GoogleFinance округлит цену до 0.04.

Из подобного есть еще Русал, ИнтерРао, Русгидро, АФК Система, Россети, ФСК ЕЭС и много чего еще.

Готовый шаблон

Как всегда прикладывают файл-шаблон со всеми прописанными формулами. Таблица только для просмотра. Для использования сохраните себе копию (в верхнем меню: «Файл — > Создать копию»).

Комментарии, замечания, пожелания приветствуются!

В третьей части будем автоматически выдергивать информацию по дивидендам российских акций.

Как оказалось после публикации статьи — при копировании формул вызова котировок выходила ошибка. Проблема заключалась в неправильном отображении блогом кавычек. Вместо двойных кавычек (сверху и снизу по две запятых » . текст. » система отображала французские (или кавычки-елочки). А для гугл-таблиц — это ошибка. Сейчас настроил правильное отображение и все работает.

Всем огромное спасибо за замечания!

За обновлениями в этой и других статьях теперь можно следить на Telegram-канале: @vsedengy.

Источник