Загрузка исторических данных в EXCEL через API okama

Еще в 2021 году в EXCEL появилась функция =ИСТОРИЯАКЦИЙ (или =STOCKHISTORY в англоязычной версии EXCEL). Эта функция позволяет загрузить исторические данные различных биржевых активов и индексов. Мы подробно разбирали возможности загрузки в статье Загрузка истории торгов акций, ETF, индексов и валюты: новая функция EXCEL.

Но в EXCEL не было и до сих пор нет истории дивидендных выплат и нет истории полной доходности (adjusted close). Нет информации по товарным активам и спотовым ценам, например, на драгметаллы. Плохо работает ИСТОРИЯАКЦИЙ с биржевыми индексами. Что же делать, беремся за дело и ликвидируем этот недостаток.

Историю котировок можно получить из любого API при помощи PowerQuery. PowerQuery – это модуль получения и преобразования данных для EXCEL. Модуль доступен в качестве бесплатного дополнения add-in для EXCEL 2010 – 2013. Начиная с версии 2016, PowerQuery входит в базовую версию EXCEL и не требует дополнительных установок.

Я взял данные из базы нашего проекта okama. Эта база отличается тем, что имеются данные закрытия, дивидендов и adjusted close различных биржевых площадок (NASDAQ, NYSE, LSE, MOEX и др.), валюты и криптовалюты, индексы, товары, цены недвижимости и много еще чего. Немаловажно и то, что данные бесплатны.

Доступные типы данных

В представленном примере EXCEL можно получить следующие типы исторических данных:

• Акции, ETF на биржах мира
  (дневные или месячные цены закрытия, adjusted close, дивиденды)
• Цены пая российских ОПИФ
  (дневные или месячные цены закрытия)
• Цены недвижимости в России
  (ежеквартальные данные)
• Товарные активы
  (дневные или месячные цены закрытия)
• Валюты FOREX
  (дневные или месячные цены закрытия)
• Официальные курсы валют ЦБ России
• Криптовалюты
  (дневные или месячные цены закрытия)
• Биржевые индексы
  (дневные или месячные цены закрытия)

В прилагаемом EXCEL доступны акции и ETF следующих бирж мира:

• LSE Лондонская биржа (London Stock Exchange)
• MOEX Москвская биржа (Moscow Exchange)
• US Биржи США (NASDAQ, NYSE)
• XAMS Биржа Euronext Amsterdam (Нидерланды)
• XETR Биржа XETRA (Германия)
• XFRA Биржа Frankfurt Stock Exchange (Германия)
• XSTU Штутгартская фондовая биржа (Stuttgart Exchange)
• XTAE Тель-Авивская фондовая биржа (Tel Aviv Stock Exchange)

Для загрузки любого тикера после его символов необходимо указать namespace биржи. Namespace – это дополнительные символы, указывающие на принадлежность тикера к определенному разделу базы данных. Например, тикер Сбербанка на Московской бирже – это SBER.MOEX.

Полный список доступных данных вместе с namespace находится на первой вкладке прилагаемого EXCEL:

На второй вкладке находится поиск по базе данных.

На изображении – пример поиска валютной пары USDRUB. В базе данных USDRUB присутствует в двух разделах: валюты FOREX (FX) и курсы валют ЦБ (CBR).

Подготовка к загрузке данных. Разрешения

Файл EXCEL содержит запросы к внешним источникам данных. Если вы пользуетесь Windows, необходимо разрешить такие запросы. Подозреваю, что и на других операционных системах потребуются аналогичные действия.

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

Загрузка истории цен. Close

История цен доступна во вкладке "Close". Можно выбирать цены закрытия дня или цены закрытия месяца при помощи параметра "Период".

Загрузка истории цен, скорректированных на дивиденды. Adjusted close

Adjusted Close – это цены закрытия, которые скорректированы на размер полученных дивидендов и сплитов. Особенностью adjusted close является равенство сегодняшних цен. Цена закрытия сегодня всегда равна скорректированной цене закрытия. Зачем так нужно делать, мне не совсем понятно. В чём тут удобство? Но это международный стандарт в мире финансов. Надо сказать, что неудобства в таком подходе очевидны – приходится пересчитывать "вчерашние" цены. И делать это надо регулярно. Есть и другие минусы adjusted close. Расскажу о них как-нибудь в отдельной статье.

Главный плюс adjusted close – возможность расчета полной доходности ценной бумаги.

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

Загрузка истории дивидендов

История дивидендов доступна в отдельной вкладке EXCEL. Устройство её аналогично другим типам данных. Но в дивидендной истории данные всегда даются по дням (нет переключателя частоты данных).

Загрузка данных инфляции

В базе данных okama есть история инфляции различных стран (Россия, США, ЕС, Китай, Израиль и другие). Инфляция может быть полезна, например, для вычисления реальной доходности (за вычетом инфляции).

На вкладке "Инфляция" прилагаемого файла EXCEL доступны данные ежемесячной инфляции.

Обновление данных

Для обновления истории используется вкладка "Данные" меню EXCEL.

Есть два варианта обновления. По шагам это выглядит так:

1. Меню "Данные"
2. Кнопка "Обновить"
3. "Обновить всё" – обновляет все истории во всех вкладках (работает медленно)

Более быстрый вариант – "Обновить". Он обновляет данные только в той таблице, где стоит курсор.

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

Получение данных по API okama

Таблица EXCEL использует открытый API okama. Но вы можете использовать API и напрямую. Например, история adjusted close в формате csv получается следующим образом:

http://api.okama.io:5000/api/ts/adjusted_close/SPY.US?first_date=2019-01-01&last_date=2020-01-01&period=m

Все доступные форматы запросов описаны на русскоязычном форуме проекта.