Как выгрузить котировки акций в excel с финам

Обновлено: 07.07.2024

Ранее уже писал про получение данных с Московской биржи через формулы 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 Московской биржи предоставляет широкие возможности, которые гораздо шире чем описанные в данной статье. Это статья своеобразная шпаргалка для долгосрочного частного инвестора, который ведёт учёт в локальном файле на собственном компьютере.

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

QUIK позволяет экспортировать данные из таблиц в программу MS Excel. Для вывода в Excel доступны практически все таблицы, в частности текущая таблица инструментов, таблицы заявок и таблицы сделок.

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

Начать вывод данных из таблицы можно несколькими способами:

Сделать активной нужную таблицу (например, кликнув на ней левой кнопкой мыши) и на панели инструментов нажать кнопку
В разделе «Экспорт данных» главного меню выбрать пункт «вывести в Excel»
Кликнуть на таблице правой кнопкой мыши и в контекстном меню выбрать пункт «вывести в Excel»

В результате откроется следующее диалоговое окно:

Рис.65. Окно настройки экспорта данных в Excel.

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

В этом окне вы должны заполнить следующие поля:

Рабочая книга – название того файла типа *.xls, в который будут добавляться данные
Лист – название рабочего листа в файле *.xls,
Ряд и колонка – номер строки и номер столбца, координаты начальной ячейки для передачи данных
выводить таблицу, начиная со строки – здесь можно установить, начиная с какого номера строки, выводить в Excel строки таблицы. Может применяться для сокращения времени повторного вывода таблиц.
вывод после создания – если стоит этот флаг, то функция экспорта данных активизируется сразу после открытия программы QUIK
вывод при нажатии Ctrl+Shift+L - при установленном флаге экспорт из таблицы может быть активизирован нажатием приведенной комбинации клавиш
С заголовками строк – если стоит этот флаг, то в качестве первого столбца в Excel выводятся заголовки строк таблицы
С заголовками столбцов – если стоит этот флаг, то в качестве первой строки в Excel выводятся заголовки столбцов таблицы
Формальные заголовки – при установленном флаге в качестве заголовков будут выводиться служебные наименования
выводить пустые ячейки вместо нулей – при установленном флаге ячейки, содержащие нулевые значения, будут оставаться пустыми. Этот флаг рекомендуется ставить в том случае, если вы хотите строить графики средствами MS Excel (в ином случае линии графика будет «проваливаться» в местах, где данные отсутствуют)

Теперь нажмите кнопку «Начать вывод» и данные начнут выводится в указанный рабочий лист файла MS Excel. Теперь можно закрыть окно настройки экспорта данных, в случае, если экспорт уже начат, он будет осуществляться в фоновом режиме. Чтобы остановить вывод данных, нужно нажать кнопку «Остановить вывод». Кнопка «вывести сейчас» предназначена для разового вывода данных.

Заголовок обязывает перейти непосредственно к программному коду… Но, думаю, все же необходима вводная часть. А зачем, собственно, это нужно?

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

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

Дилинговые залы брокерских контор… там существует своя, особая атмосфера. Атмосфера общения, обмена опытом, эмоциями. Мне нравятся дилинговые залы. По тому как человек входит в сделку, трейдеров можно разделить на две группы. Я буду говорить о тех, чей результат, как правило, печален. И таких трейдеров — большинство. Итак — описываю процесс входа в рынок трейдера соответствующей группы. В дилинговый зал вбегает мужчина лет 20-60 выкрикивает: «Куда идем?! Вверх?! Вниз?!» Со стороны встречающих слышаться неоднозначные выкрики «Вверх! Вниз!» Новоприбывший присоединяется к наиболее громко крикнувшей группе и… делает ТЫЦ. ТЫЦ по кнопке покупки или продажи. Все. Теперь человек в рынке. С этого момента он рискует своими деньгами. С этого момента трейдер не похож на трейдера. Он похож на болельщика. Вувузела в руках такого трейдера, думаю, была бы уместным инструментом торговли.

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

Однако, таким образом предоставляется возможность скачать лишь один файл за одну загрузку. А что если мы хотим получить больше данных для анализа? Гораздо больше? Практически по всем инструментам! По всем периодам! Это даст богатейшие возможности для анализа данных. Оу… возможно ли такое? Ответ: да возможно.

Пока же определимся с перечнем бумаг (инструментов), а также с основными принципиальными моментами, которые позволят нам получить данные о котировках. Перечень бумаг (инструментов) которые предоставляться компанией «ФИНАМ» будем брать отсюда:

Эта страница интересна для нас тем, что на ней есть, во-первых, большая часть инструментов которые дает «ФИНАМ»; во-вторых, веб-ссылки, по которым можно перейти непосредственно на страницу каждой ценной бумаги (инструмента).

Ссылки имеют следующий вид:

Пропарсив соответствующую станицу получим файл ссылок. Теперь мы знаем где «живут» инструменты. Файл можете скачать по этой ссылке. Зачем нам место жительства каждого инструмента? Этот параметр нам еще пригодится. Запаситесь терпением. Пока имеем ссылки по 6131 бумаге (инструменту).

Что требует сервер «ФИНАМ»? Какие параметры для получения данных? Давайте попробуем получить один файл, и посмотрим параметры запроса. Скачивая котировки компании Polymetal, имею вот такой GET запрос:

Среди всего перечня хотелось бы акцентировать внимание на параметрах em, market, code. Параметр em следует понимать как индекс, своеобразную метку бумаги (инструмента). Если мы хотим скачивать не один инструмент, а массив данных по нескольким бумагам (инструментам) мы должны знать em каждого из них. Переменная market говорит о том, где вращается данная бумага (инструмент) – на каком рынке? Маркетов много: МосБиржа топ***, МосБиржа пифы***, МосБиржа облигации***, Расписки и т.д. Параметр code – это символьная переменная по инструменту.

Итак, для получения файла котировок нам нужно добыть эти три параметра: em и market и code. По всем бумагам (инструментам). Вопрос — где их взять? Ответ: вспоминаем о файле со ссылками. В файле есть, например, такая ссылка:

Зайдем на нее и в исходном коде страницы увидим то, что нам нужно — в элементах javascript сидят наши искомые параметры, которые относятся к данной бумаге (инструменту):

Заметим, что в данном кусочке кода id — это и есть em; имеется параметр code, а также параметры маркета – id и его русскоязычное название. Данный кусок кода с вариациями присутствует у каждого бумаги (инструмента). Сходим, например, на:

и увидим все то же самое. Теперь, думаю, общая цепочка получения данных понятна: в цикле перебираем ссылки, где живут отдельные бумаги (инструменты). Парсим кусочки javascript, собирая параметры em, market и code для каждой позиции. Имея на руках эти данные, можем программно заходить на сервер «ФИНАМ» и получать файлы котировок. Осталось дело за техникой исполнения.

Чем будем парсить? Парсить будем, используя Java. И… из всех велосипедов я выбираю тот, который стоит у меня в гараже. А именно Jsoup. Хотя можно было бы использовать и htmlunit.

Небольшое уточнение. При парсинге страницы мною были получены также данные – русскоязычное название бумаги (1) и раздел, в который «ФИНАМ» определили данную бумагу (инструмент) (2). Таким образом, на входе парсера имеется три файла. Напомню, имеем 6131 позиций — бумаг (инструментов). Всю эту информацию, а также результаты парсинга объединим в один файл. Код парсера можно скачать по этой ссылке.

В результате выполнения имеем файл function_parameters.csv. Каждая строка файла при построчном считывании может использоваться как перечень параметров для функции обращения к серверу «ФИНАМ» за котировками. Файл function_parameters.csv можно скачать по этой ссылке.

Для того чтобы написать функцию обращения к серверу «ФИНАМ» (а писать мы будем ее на Python), еще раз рассмотрим параметры GET запроса:

POLY_170620_170623 – очевидно, что данная строка представляет параметр code, а также временные характеристики.

.txt – расширение файла; расширение упоминается в параметре e; при написании функции следует помнить об этом нюансе.

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

Код функции можно скачать также по этой ссылке.

Что дальше? Теперь возможно использовать данную функцию в цикле по имеющимся у нас позициям. Всего имеем, напомню, 6131 позицию. Из файла function_parameters.csv подгружаем параметры, указываем дату, выбираем нужный формат. И, используя данный код, не забудьте о правилах хорошего тона – поставьте задержку в пару секунд в итерацию цикла, дабы не перегружать сервер-источник.

Данных для анализа рынка, думаю, у вас будет предостаточно. Искренне надеюсь, что клиентов у компании «ФИНАМ» после написания данной статьи только прибавится!

Последние обновления 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, сначала преобразуем текст в тип данных Stocks (Акции). Затем вы сможете извлечь в другой столбец нужные сведения из этого типа данных, такие как цены акций или их изменения.

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

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

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

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

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

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

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

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

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

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

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

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