Как получить котировки акций в excel

Обновлено: 06.01.2025

Ранее уже писал про получение данных с Московской биржи через формулы Google Таблиц. Однако остался вопрос — можно ли получать эти же данные при локальном использовании Microsoft Excel или его свободного аналога LibreOffice Calc? Без использования скриптов или ручного копирования.

Microsoft Excel с формулами получения данных с Мосбиржи

И на этот вопрос можно дать положительный ответ. Это даже более удобно, поскольку не приходится ожидать загрузки результатов работы функции IMPORTXML в Гугл Таблицах.

Аналогом этой функции в Excel и Calc выступает связка формул: WEBSERVICE (ВЕБСЛУЖБА) + FILTERXML (ФИЛЬТР.XML).

При работе с Microsoft Excel есть некоторые нюансы:

Эти функции доступны только в Excel 2013 и более поздних версиях для Windows.
Эти функции не будет возвращать результаты на компьютере Mac.

Требуется LibreOffice 4.2 и выше.
Нет ограничений на используемую ОС. Работает под:
Windows
Linux
Mac OS
Файл Excel .xlsx открывается и работоспособен, но визуально форматирование может быть нарушено.

Протестировал этот файл в Microsoft Excel 2019 под Windows 10 и в LibreOffice Calc 6.4 под Linux Mint 19.3. Под Mac OS у меня возможности протестировать не было.

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

В API Московской биржи очень многое зависит от параметра «Идентификатор режима торгов» (primary_boardid), который можно посмотреть прямо у них на сайте через форму поиска.

Идентификатор режима торгов для акций Тинькофф

Автоматическое получение имени акций, облигаций и ETF

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

Файл « API Мосбиржи в Microsoft Excel.xlsx » с примерами автоматического получения имени для разных классов активов. Корректно работает и в LibreOffice Calc

Автоматическое получение текущих цен

На этой вкладке представлены актуальные примеры для получения цен акций, облигаций и ETF с Московской биржи.

Цена предыдущего дня берётся через PREVADMITTEDQUOTE, а не LAST с 15 минутной задержкой, поскольку по некоторым низко ликвидным инструментам через LAST цены может просто не быть.

Автоматическое получение дивидендных выплат для акций

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

Автоматическое получение облигационных выплат

По облигациям (не только корпоративным, но также ОФЗ и еврооблигациям) можно автоматически получать дату выплаты следующего купона и его значение.

Автоматическое получение дат оферт

Удобно планировать собственные финансы, получая даты оферт (дата, в которую инвестор или эмитент имеют право досрочно погасить облигацию по цене номинала) автоматически.

UPD. Пользователь mixei подсказывает, что автоматическое обновление настраивается через Параметры — Центр управления безопасностью — вкладка Внешнее содержимое — там надо поставить все флажки где не рекомендуется :) Но это на страх и риск пользователей.

API Московской биржи предоставляет широкие возможности, которые гораздо шире чем описанные в данной статье. Это статья своеобразная шпаргалка для долгосрочного частного инвестора, который ведёт учёт в локальном файле на собственном компьютере.

Также хочу отметить, что я никак не связан с Московской биржей и использую ИСС Мосбиржи только в личных интересах.

Функция ИСТОРИЯАКЦИЙ восстанавливает исторические данные о финансовом инструменте и загружает их в виде массива, который перенесется, если будет получен конечный результат формулы. Это означает, что Excel будет динамически создавать соответствующий по размеру диапазон массива при нажатии клавиши ВВОД.

Технические подробности

= ИСТОРИЯАКЦИЙ(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 (т. е. показывать заголовки). При включении заголовки представляют собой текстовые строки, являющиеся частью массива, возвращаемого функцией.

property0 - property5

Необязательный. Столбцы, извлекаемые для каждой из акций указаны следующим образом: 0 = Дата, 1 = Закрыть, 2 = Открыть, 3 = Высокий, 4 = Низкий и 5 = Объем. Если хотя бы один из них присутствует, в указанном порядке возвращаются только указанные столбцы. По умолчанию используется значение 0,1 (, т. е., Дата и Закрыть).

Функция ИСТОРИЯАКЦИЙ не отмечает формат ячеек, в которые она переводится. При удалении формулы, ячейки, которые были заполнены, будет иметь общий формат.

При вводе аргументов свойства введите число для каждого свойства от 0 до 5 в том порядке, в котором они должны отображаться. Значение, введенное для каждого свойства, соответствует номеру свойства. Например, чтобы добавить дату, открыть и закрыть, введите 0,2,1. Эти свойства определены следующим образом:

Первый допустимый торговый день в периоде

Цена закрытия за последний торговый день в периоде

Цена открытия за последний торговый день в периоде

Максимальная цена за самый большой день в периоде

Наименьшая цена самого низкого дня в периоде

Объем продаж за период

Функция ИСТОРИЯАКЦИЙ относится к семейству Ссылки и массивы.

Аргументы даты могут быть датами, заключенными в двойные кавычки (например, "01-01-2020") или формула (например, TODAY()) или ссылка на ячейку с датой.

Возвращаемая дата может предшествовать указанной дате. Например, если в качестве даты начала и интервала задано значение 31 декабря 2019, то будет возвращен 1 декабря 2019, так как это дата начала для запрошенного периода.

В Excel можно вставлять сведения об акциях и географические данные. Для этого достаточно просто ввести текст в ячейку и преобразовать его в тип данных Stocks (Акции) или Geography (География). Эти два типа данных считаются связанными, так как они связаны с источником данных в Интернете. Такое подключение позволяет получать разнообразные полезные сведения, которые можно использовать и обновлять.

Примечание: Если Excel распознает то, что вы введете, как финансовое или географическое расположение, оно предлагает настроить связанный тип данных ("Акции" или "География").

Сведения о других доступных типах данных можно найти в этой Excel.

Примечание: Типы данных Stocks и Geography доступны толькоMicrosoft 365 или с бесплатной учетной записью Майкрософт. В языковых параметрах Office также должен быть добавлен английский, французский, немецкий, итальянский, испанский или португальский язык редактирования.

Stocks (Акции)

На рисунке выше ячейки с названиями компаний в столбце A содержат тип данных Stocks (Акции). Это известно, потому что у них есть такой значок: . Тип данных Stocks (Акции) подключен к веб-источнику, который содержит дополнительные сведения. Столбцы B и C извлекают эти сведения. В частности, значения цены и изменения цен извлекаются из типа данных Stocks (Акции) в столбце A.

Ищете простой способ получения исторических финансовых данных? Попробуйте функцию STOCKHISTORY.

Geography (География)

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

Затем выберем ячейки.

Хотя это необязательно, рекомендуем создать таблицу Excel. Это упростит получение сведений из Интернета. Чтобы создать таблицу, выберите Вставка > Таблица.

Выбирая ячейки, перейдите на вкладку Данные и выберите stocks (Акции) или Geography (География).

Если Excel находит совпадение между текстом в ячейках и веб-источниками, текст преобразуется в тип данных Stocks (Акции) или Geography (География). Вы узнаете, что преобразование было преобразовано, если у них есть значок акций: и значок географии:

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

Примечание: Если вместо вы видите значок, Excel не можете найти соответствие текста данным в веб-источниках. Исправьте орфографические ошибки и нажмите клавишу ВВОД, чтобы повторить попытку. Можно также нажать и появится области выбора. Выполните поиск по ключевым словам, выделите нужные данные, а затем нажмите кнопку Выбрать.

Если вы хотите получить актуальные данные для типов данных, щелкните правой кнопкой мыши ячейку со связанным типом данных и выберите Тип данных > Обновить. При этом будет обновлена выделенная ячейка и все остальные, в которых используется этот же тип данных.

Дополнительные действия

Связанные типы данных подключаются к источнику данных в сети. После преобразования текста в связанный тип данных в книге создается подключение к внешним данным. Если данные изменяются в Интернете, можно получить обновленные данные, обновив их в Excel. Чтобы обновить данные, щелкните правой кнопкой мыши ячейку со связанным типом данных и выберите тип данных >обновить. При этом будет обновлена выделенная ячейка и все остальные, в которых используется этот же тип данных.

Если вы хотите обновить все связанные типы данных и все подключения к данным, которые могут быть в книге (включая запросы, другие подключения к данным и стебли), выберите данные> Обновить все или нажмите CTRL+ALT+F5.

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

Например, на этом рисунке показана карточка для записи France (Франция). Одним из полей, доступных для нее, является Capital (Столица). Его значение — Paris (Париж). Еще одно поле — Leader(s) (Руководители), значениями которого являются имена руководителей.

Если вы хотите увидеть другие пары полей и значений, прокрутите вниз страницу внутри карточки.

Если вы хотите узнать, откуда приходят поля и значения, обратите внимание на заметку Powered by в нижней части карточки.

Чтобы вставить цену акций в Excel, сначала преобразуем текст в тип данных Stocks (Акции). Затем вы сможете извлечь в другой столбец нужные сведения из этого типа данных, такие как цены акций или их изменения.

Примечание: Тип данных Stocks (Акции) доступен толькоMicrosoft 365 или с бесплатной учетной записью Майкрософт. В языковых параметрах Office также должен быть добавлен английский, французский, немецкий, итальянский, испанский или португальский язык редактирования.

Введите текст в ячейки. Например, в каждой ячейке введите тикер, название компании или название фонда.

Затем выберем ячейки.

Выбирая ячейки, перейдите на вкладку Данные и нажмите кнопку Акции.

Если Excel обнаружит совпадение между текстом в ячейках и веб-источниками, текст будет преобразован в тип данных Stocks. Вы будете знать, что они преобразованы, если у них есть значок акций: .

Выберите одну или несколько ячеек с типом данных, и появится добавить столбец. Нажмите эту кнопку, а затем щелкните имя поля, чтобы извлечь дополнительные сведения. Например, для акций можно выбрать Price.

Чтобы увидеть все поля, доступные для компании или фонда, щелкните значок акций ( ) или выберите ячейку и нажмите CTRL+SHIFT+F5.

Если вы видите вместо значка, Excel не удается сопоставить текст с данными в веб-источниках. Исправьте орфографические ошибки и нажмите клавишу ВВОД, чтобы повторить попытку. Кроме того, вы можете щелкнуть , чтобы открыть область выделения. Выполните поиск по ключевым словам, выделите нужные данные, а затем нажмите кнопку Выбрать.

Вы также можете писать формулы, ссылаясь на типы данных, или использовать функцию STOCKHISTORY.

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

Количество частных инвесторов на Московской бирже удвоилось за последний год и составило 3,86 млн: за 2019 счета на Мосбирже открыли 1,9 млн человек. Санкт-Петербургская биржа, специализирующаяся на торгах акциями иностранных компаний, в прошлом году зафиксировала трехкратный прирост счетов – с 910 000 до 3,06 млн шт.

Это означает, что на рынок пришло почти 2 млн новичков, которые никогда не занимались трейдингом и не использовали специализированный софт для торгов и учета позиций.

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

Ранее я уже описывал на Хабре возможности при использовании разных приложений (Intuit Quicken, KMyMoney, Microsoft Excel, Google Таблицы) для совокупного учета капитала “в одном окне”.

API Московской биржи в формулах Google Таблиц

Вообще вариантов использования API Мосбиржи два:

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

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

В API Московской биржи очень многое зависит от параметра “Идентификатор режима торгов” (primary_boardid), который можно посмотреть прямо у них на сайте через форму поиска.

Идентификатор режима торгов для акций Сбербанка

Идентификатор режима торгов primary_boardid для акций Яндекс

Автоматическое получение имени инструмента с Московской биржи по его идентификатору

Очень удобно, что можно получить полное наименование инструмента. Также можно автоматически получать и краткое наименование инструмента, но полное название более понятно особенно для облигаций.

Гугл таблица с примерами автоматического получения имени для разных классов активов

Автоматическое получение цены инструмента с Московской биржи по его идентификатору

Цены на российские активы можно получать с множества сайтов, но если мы говорим про API Московской биржи, то на этой вкладке представлены актуальные примеры.

Гугл таблица с примерами автоматического получения цен для разных классов активов

Автоматическое получение даты следующего купона и его значения с Московской биржи по идентификатору облигации

Гугл таблица с примерами автоматического получения дат и значений купонов

Автоматическое получение дивидендной доходности на конкретную дату с Московской биржи по идентификатору облигации

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

Гугл таблица с примерами автоматического получение дивидендной доходности на конкретную дату

Автоматическое получение дат оферт с Московской биржи по идентификатору облигации

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

Гугл таблица с примерами автоматического получения дат оферт

API Московской биржи предоставляет широкие возможности. В статье описал только те инструменты, которыми пользуюсь сам как долгосрочный частный инвестор.

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

Читайте также: