Функция история акций эксель
История акций и курсов валют в Excel
На прошлой неделе в бета-канале Excel для Office 365 дебютировала новая функция STOCKHISTORY. Функция может извлекать исторические цены на акции и обменные курсы с дневным, недельным или ежемесячным интервалом.
STOCKHISTORY будет доступен только в версиях Office 365 по подписке. Если у вас есть Excel 2013, Excel 2016, Excel 2019 (или в будущем Excel 2022), у вас не будет доступа к службе STOCKHISTORY.
Вот пример формулы STOCKHISTORY, возвращающей месячные цены закрытия для цены закрытия акций Coca-Cola: =STOCKHISTORY(A1,»1/1/1975″,TODAY(),2,1)
Месячные цены закрытия с STOCKHISTORY
Вы можете использовать аргументы в функции STOCKHISTORY для обеспечения гибкости. Стоит изучить различные варианты. Аргументы представлены в следующем порядке:
Вы можете указать значения в любом порядке. Например, одна из очень старых встроенных биржевых диаграмм в Excel требует даты, объема, открытия, максимума, минимума, закрытия. В этом случае аргументы с 6-го по 10-й будут 0,5,2,3,4,1.
Аргументы функции STOCKHISTORY
STOCKHISTORY поддерживает отдельные акции, валютные пары, ETF, индексные фонды, паевые инвестиционные фонды и облигации.
Чтобы узнать больше, посмотрите наше видео на YouTube.
Одна из особенностей STOCKHISTORY заключается в том, что она автоматически обеспечивает форматирование чисел на лету. Это новая функция в Excel. Узнайте больше о FNV здесь: https://techcommunity.microsoft.com/t5/excel-blog/announcing-stockhistory/ba-p/1404338.
Поскольку эта функция сейчас находится в стадии бета-тестирования, вы должны принять участие в программе предварительной оценки Office, как описано здесь.
Free API Мосбиржи в формулах Microsoft Excel
Ранее уже писал про получение данных с Московской биржи через формулы Google Таблиц. Однако остался вопрос — можно ли получать эти же данные при локальном использовании Microsoft Excel или его свободного аналога LibreOffice Calc? Без использования скриптов или ручного копирования.
Microsoft Excel с формулами получения данных с Мосбиржи
И на этот вопрос можно дать положительный ответ. Это даже более удобно, поскольку не приходится ожидать загрузки результатов работы функции IMPORTXML в Гугл Таблицах.
Аналогом этой функции в Excel и Calc выступает связка формул: WEBSERVICE (ВЕБСЛУЖБА) + FILTERXML (ФИЛЬТР.XML).
При работе с Microsoft Excel есть некоторые нюансы:
Протестировал этот файл в Microsoft Excel 2019 под Windows 10 и в LibreOffice Calc 6.4 под Linux Mint 19.3. Под Mac OS у меня возможности протестировать не было.
API Московской биржи в формулах MS Excel
В общем виде все запросы, которые можно отправить к API Мосбиржи есть в справочнике. Но лично для меня этот справочник до сих пор не особо понятен, хотя с API контактирую довольно часто.
Идентификатор режима торгов
В API Московской биржи очень многое зависит от параметра «Идентификатор режима торгов» (primary_boardid), который можно посмотреть прямо у них на сайте через форму поиска.
Идентификатор режима торгов для акций Тинькофф
Также этот идентификатор можно посмотреть через обычный HTTP-запрос к API:
Поиск через HTTP-запрос к API Мосбиржи по слову Пермь
Автоматическое получение имени акций, облигаций и ETF
Очень удобно, что можно получить полное или краткое наименование инструмента. Для облигаций полное название особенно понятно.
Файл « API Мосбиржи в Microsoft Excel.xlsx » с примерами автоматического получения имени для разных классов активов. Корректно работает и в LibreOffice Calc
Автоматическое получение текущих цен
На этой вкладке представлены актуальные примеры для получения цен акций, облигаций и ETF с Московской биржи.
Цена предыдущего дня берётся через PREVADMITTEDQUOTE, а не LAST с 15 минутной задержкой, поскольку по некоторым низко ликвидным инструментам через LAST цены может просто не быть.
Файл « API Мосбиржи в Microsoft Excel.xlsx » с примерами автоматического получения имени для разных классов активов. Корректно работает и в LibreOffice Calc
Автоматическое получение дивидендных выплат для акций
Очень удобная функция Мосбиржи, которая позволяет получать не только значение текущей выплаты, но и историю выплат дивидендов вместе с датами и значениями.
Файл « API Мосбиржи в Microsoft Excel.xlsx » с примерами автоматического получения имени для разных классов активов. Корректно работает и в LibreOffice Calc
Автоматическое получение облигационных выплат
По облигациям (не только корпоративным, но также ОФЗ и еврооблигациям) можно автоматически получать дату выплаты следующего купона и его значение.
Файл « API Мосбиржи в Microsoft Excel.xlsx » с примерами автоматического получения имени для разных классов активов. Корректно работает и в LibreOffice Calc
Автоматическое получение дат оферт
Удобно планировать собственные финансы, получая даты оферт (дата, в которую инвестор или эмитент имеют право досрочно погасить облигацию по цене номинала) автоматически.
Файл « API Мосбиржи в Microsoft Excel.xlsx » с примерами автоматического получения имени для разных классов активов. Корректно работает и в LibreOffice Calc
UPD. Пользователь mixei подсказывает, что автоматическое обновление настраивается через Параметры — Центр управления безопасностью — вкладка Внешнее содержимое — там надо поставить все флажки где не рекомендуется 🙂 Но это на страх и риск пользователей.
API Московской биржи предоставляет широкие возможности, которые гораздо шире чем описанные в данной статье. Это статья своеобразная шпаргалка для долгосрочного частного инвестора, который ведёт учёт в локальном файле на собственном компьютере.
Также хочу отметить, что я никак не связан с Московской биржей и использую ИСС Мосбиржи только в личных интересах.
Автоматическая загрузка котировок акций и валюты: новые функции EXCEL
Последние обновления EXCEL позволяют пользователям загружать данные по многим биржевым ценным бумагам в автоматическом режиме. Важно, что теперь это возможно сделать через встроенные типы данных и функции EXCEL без сторонних плагинов и VBA.
Как это работает?
Новые возможности опираются на встроенный тип данных «Акции». Теперь в любой ячейке можно ввести тикер ценной бумаги, например MSFT, выбрать на вкладке «Данные» тип «Акции».
После выбора ценной бумаг, тикер конвертируется в ее официальное название и появляется возможность в соседних колонках отображать информацию по выбранной ценной бумаге. Например, можно посмотреть ее текущую цену.
Список данных по бумагам довольно внушительный. В ячейках можно отображать среди прочего:
А также некоторые фундаментальные характеристики бумаг:
Важно, что кроме акций компаний доступна так же информация по ETF (в том числе по ETF и БПИФ Московской биржи).
Данные можно обновить в любой момент, нажав на «Обновить» на вкладке «Данные». Автоматическое обновление довольно просто настроить при помощи VBA.
Загрузка курса валют
Загрузка данных по валютным парам очень похожа на работу с акциями. В ячейке необходимо ввести обозначение валютной пары в произвольном формате. Например, для получения курса доллара США к рублю – USD/RUB. После этого на вкладке «Данные» выбираем тип данных «Акции» (немного странно, но именно так необходимо сделать). EXCEL автоматически распознает валютную пару и поменяет ее отображение в ячейке, подставив специальный значок финансовых данных.
Для получения данных по валютной паре в выпадающем списке выбираем необходимый параметр. Например, для получения курса валюты – Price (пока все финансовые параметры не переведены на русский язык).
Microsoft на этот раз не поскупились. Среди валют доступны даже некоторые криптовалюты. Например, для получения данных по Биткоину достаточно ввести символ валютной пары Биткоин/Доллар (BTC/USD). Кроме биткоина доступных котировки эфириума, XRP и других популярных сегодня криптовалют.
Новый синтаксис для финансовых функций EXCEL
Довольно удобен синтаксис новых финансовых функций. После того, как в ячейке выбран тип данных «Акции». В любой другой ячейке можно сделать ссылку на нее, поставить «.» и выбрать нужную функцию из выпадающего списка. Например, как в примере с Microsoft, можно в ячейке набрать B2.[P/E]
Недостатки
Они тоже, на мой взгляд, имеются. Например, нельзя посмотреть дивиденды по бумаге. Нет цены типа Adjusted Close, которая бы учитывала дивидендную доходность. Это ограничивает сколько-нибудь серьезное использование новых возможностей для отслеживания доходности ценной бумаги или набора ценных бумаг (портфеля).
Кроме того нет возможности посмотреть историю изменения цены или других параметров (TimeSeries).
В целом все изменения очень полезные и удобные, но новый функционал пока уступает аналогу из Google Spreadsheets. Будем надеяться, что это только первый шаг Microsoft в нужном направлении.
Пример использования новых функций EXCEL для отслеживания изменения стоимости портфеля ценных бумаг прилагается.
Загрузка истории торгов акций, ETF, индексов и валюты: новая функция EXCEL
В 2021 году EXCEL 365 получил полезное изменение. К существовавшей ранее возможности получать текущую информацию по валютам и биржевым тикерам добавили функцию загрузки исторических данных.
Новая функция ИСТОРИЯАКЦИЙ
Работает новая функция очень просто.
В любой ячейке пишем =ИСТОРИЯАКЦИЙ (или =STOCKHISTORY в англоязычной версии EXCEL) и заполняем несколько полей функции.
Обязательными полями являются «Акции» и «Кон-дата».
По умолчанию «Кон_дата» равна «Нач_дата». В случае пропуска «Кон_дата» показывается информация только для одной даты, что может быть удобно для отображения исторических данных сразу для нескольких тикеров.
ИСТОРИЯАКЦИЙ может показывать исторические данные на месячных, дельных и дневных интервалах. По умолчанию данные ежедневные. За это отвечает параметр:
Интервал: 0 – ежедневные, 1 – недельные, 2 – месячные
Последний параметр помогает выбрать заголовки в отображаемой таблице исторических данных:
Вариант с отображением тикеров удобен, когда показываются данные сразу нескольких ценных бумаг.
Другие данные
EXCEL умеет показывать кроме истории цен закрытия и другие данные: открытие, объем, high, low.
Для отображения нужного типа данных необходимо после параметра «Заголовки» указать соответствующие цифры:
История значений биржевых индексов
Кроме отдельных ценных бумаг теперь можно загрузить историю биржевых индексов.
Для этого необходимо вместо тикера ценной бумаги указать идентификатор индекса.
Правда, с индексами не всё так «гладко», как с отдельными ценными бумагами. У биржевых индексов нет единой общепринятой системы идентификаторов (тикеров). Поэтому, чтобы найти информацию по индексу желательно сначала выбрать его в отдельной ячейке, как это показано на рисунке. После этого формула =ИСТОРИЯАКЦИЙ может ссылаться на выбранную ячейку.
Ячейка с данными индекса должна быть в формате данных «Акции». Подробно, о том как работать с этим форматом данных рассказано в статье Автоматическая загрузка котировок акций и валюты: новые функции EXCEL.
Количество доступных биржевых индексов пока явно неудовлетворительное. Нет многих популярных индексов акций и облигаций.
История курсов валют и криптовалют
Третий тип данных, по которому можно загружать исторические данные – это валюта.
Тикер валюты указывается через «/» или «:».
Кроме обычных «фиатных» валют доступны все популярные криптовалюты. Например, для загрузки истории курса биткоина необходимо указать «BTC/USD».
Глубокие исторические данные
В базе данных существуют некоторые ценные бумаги, по которым глубина истории приближается к 100 лет.
Несмотря на то, что MITTX – активно управляемый фонд, у него высокая корреляция с индексами акций США. На его примере можно отслеживать поведение рынка в такие критические моменты как «Великая депрессия», Вторая мировая война, кризис «Дот-комов» и др.
В базе данных так же доступны данные по первым в истории индексным фондам управляющей компании Vanguard:
Недостатки функции ИСТОРИЯАКЦИЙ
Новая функция – это явный шаг вперед. Но пока в общем доступе нет истории дивидендных выплат и нет истории полной доходности (adjusted close). Без этого полноценное использование данных для изучения статистики вряд ли возможно. Нет информации по товарным активам и спотовым ценам, например, на драгметаллы. Кроме того, пока выглядит недоделанным раздел биржевых индексов.
Бесплатная финансово-экономическая база данных доступна в рамках проекта okama и одноименной библиотеки для языка Python :
Функция ИСТОРИЯАКЦИЙ
Функция ИСТОРИЯАКЦИЙ восстанавливает исторические данные о финансовом инструменте и загружает их в виде массива, который перенесется, если будет получен конечный результат формулы. Это означает, что Excel будет динамически создавать соответствующий по размеру диапазон массива при нажатии клавиши ВВОД.
Важно: Для функции STOCKHISTORY требуется подписка Microsoft 365 персональный, Microsoft 365 для семьи, Microsoft 365 бизнес стандартный или Microsoft 365 бизнес премиум подписку.
Технические подробности
= ИСТОРИЯАКЦИЙ(stock, start_date, [end_date], [interval], [headers], [property0], [property1], [property2], [property3], [property4], [property5])
Аргументы функции ИСТОРИЯАКЦИЙ описаны ниже.
Функция восстанавливает данные из исторических цен о финансовом инструменте, соответствующему данному значению. Введите символ деления в двойных кавычках (например, «MSFT») или ссылку на ячейку, содержащую тип данных Акции. При этом будут извлечены данные курса валют по умолчанию для инструмента. Вы также можете обратиться к определенному курсу, введя 4-значный код идентификатора (MIC) ISO, затем двоеточие, после которого следует символ деления (например, «XNAS:MSFT»). Подробнее об источниках данных.
Самая ранняя дата извлечения данных. Обратите внимание, что если интервал не равен 0 (ежедневно), первая точка данных может предшествовать start_date; она будет первой датой запрошенного периода.
Необязательный. Самая поздняя дата извлечения данных. По умолчанию используется значение start_date.
Необязательный. Интервал для каждого значения данных задается следующим образом: 0 = ежедневно, 1 = еженедельно, 2 = ежемесячно. Значение по умолчанию — 0.
Необязательный. Указывает, должны ли заголовки отображаться следующим образом: 0 — нет заголовков, 1 = показывать заголовки, 2 = показывать идентификаторы и заголовки инструмента. По умолчанию используется значение 1 (т. е. показывать заголовки). При включении заголовки представляют собой текстовые строки, являющиеся частью массива, возвращаемого функцией.
Необязательный. Столбцы, извлекаемые для каждой из акций указаны следующим образом: 0 = Дата, 1 = Закрыть, 2 = Открыть, 3 = Высокий, 4 = Низкий и 5 = Объем. Если хотя бы один из них присутствует, в указанном порядке возвращаются только указанные столбцы. По умолчанию используется значение 0,1 (, т. е., Дата и Закрыть).
Функция ИСТОРИЯАКЦИЙ не отмечает формат ячеек, в которые она переводится. При удалении формулы, ячейки, которые были заполнены, будет иметь общий формат.
При вводе аргументов свойства введите число для каждого свойства от 0 до 5 в том порядке, в котором они должны отображаться. Значение, введенное для каждого свойства, соответствует номеру свойства. Например, чтобы добавить дату, открыть и закрыть, введите 0,2,1. Эти свойства определены следующим образом:
Первый допустимый торговый день в периоде
Цена закрытия за последний торговый день в периоде
Цена открытия за последний торговый день в периоде
Максимальная цена за самый большой день в периоде
Наименьшая цена самого низкого дня в периоде
Объем продаж за период
Функция ИСТОРИЯАКЦИЙ относится к семейству Ссылки и массивы.
Аргументы даты могут быть датами, заключенными в двойные кавычки (например, «01-01-2020») или формула (например, TODAY()) или ссылка на ячейку с датой.
Возвращаемая дата может предшествовать указанной дате. Например, если в качестве даты начала и интервала задано значение 31 декабря 2019, то будет возвращен 1 декабря 2019, так как это дата начала для запрошенного периода.
Мы постоянно улучшаем функцию ИСТОРИЯАКЦИЙ. Если данные неверны или отсутствуют, или вы не можете выполнить свою задачу. сообщите нам! Дополнительные сведения о том, как отправить отзыв о функции ИСТОРИЯАКЦИЙ, см. в статье как отправить отзыв о Microsoft Office.