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

Как же определить, принесло ли владение данной ценной бумагой доход или одни лишь убытки и разочарование?

(Источник)

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

На примере клиентской отчетности Открытие Брокер мы рассмотрим парсинг и консолидацию брокерских отчетов для фондового рынка, построение архитектуры облачной отчетной системы с последующим простым и удобным анализом в AWS Quicksight.

Описание задачи

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

Признаюсь, поначалу и я пробовал старательно следовать совету ведения журнала, скрупулёзно записывал каждую сделку с ее параметрами в таблицу Excel, строил какие-то отчеты, сводные диаграммы, планировал будущие сделки, но… мне все это быстро надоело.


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

К примеру, Открытие Брокер позволяет получать в личном кабинете брокерские отчеты в формате XML, но если у вас есть ИИС и обычный брокерский счет на Московской фондовой бирже (MOEX) – это будут два разных отчета, а если у вас есть еще счет на Санкт-Петербургской фондовой бирже (SPB), то к первым двум добавится еще один.

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

Вышеупомянутые отчеты на MOEX и SPB немного отличаются своими форматами, что необходимо будет учесть в процессе имплементации маппинга данных.

Архитектура разрабатываемой системы

На диаграмме ниже представлена модель архитектуры разрабатываемой системы:

Реализация парсера

Получим в Личном кабинете отчеты по всем трем счетам за максимально возможный период (можно разбить на несколько отчетов за каждый год), сохраняем их в формате XML и складываем в одну папку. В качестве тестовых данных для исследования будем использовать вымышленный клиентский портфель, но с максимально приближенными параметрами к рыночным реалиям.


Предположим, что у рассматриваемого нами инвестора Мистера Х небольшой портфель из пяти бумаг:

1. В отчете по бирже SPB будет две бумаги: Apple и Microsoft;
2. В отчете по бирже MOEX (брокерский) одна бумага: ФСК ЕЭС;
3. В отчете по бирже MOEX (ИИС) две бумаги: ММК и ОФЗ 24019;

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

И тут в дело вступает Python, считываем в один массив информацию из всех отчетов:

my_files_list = [join('Data/', f) for f in listdir('Data/') if isfile(join('Data/', f))]
my_xml_data = []

# Считывание отчетов из каталога
for f in my_files_list:
    tree = ET.parse(f)
    root = tree.getroot()
    my_xml_data.append(root)

Для аналитики, из отчетов нам потребуется несколько сущностей, а именно:

• Позиции бумаг в портфеле;
• Заключенные сделки;
• Неторговые операции и прочие движения по счету;
• Средние цены открытых позиций

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

dict_stocks = {'stock_name': [], 'account': [], 'currency': [], 'current_cost': [], 'current_cost_rub': [], 'saldo' : []}
dict_deals  = {'stock_name': [], 'account': [], 'date_oper': [], 'type_oper': [], 'quantity': [], 'price': [], 'currency': [], 'brokerage': [], 'result': []}
dict_flows  = {'stock_name': [], 'account': [], 'date_oper': [], 'type_oper': [], 'result': [], 'currency': []}
dict_avg_price = {'stock_name': [], 'account': [], 'avg_open_price' : []}

Несколько слов о том, что из себя представляют эти словари.





Обработаем массив XML-документов и заполним эти словари соответствующими данными:

# Сбор данных из соответствующих частей отчетов
for XMLdata in my_xml_data:
    # Информация о Бирже и счете
    exchange_name = 'SPB' if XMLdata.get('board_list') == 'ФБ СПБ' else 'MOEX' 
    client_code =  XMLdata.get('client_code')
    account_name = get_account_name(exchange_name, client_code)
    
    # Маппинг тегов
    current_position, deals, flows, stock_name,     saldo, ticketdate, price, brokerage,     operationdate, currency,     current_cost, current_cost_rub,     stock_name_deal, payment_currency, currency_flows = get_allias(exchange_name)
    
    # Информация о состоянии клиентского портфеля
    get_briefcase(XMLdata)
    df_stocks = pd.DataFrame(dict_stocks)
    df_stocks.set_index("stock_name", drop = False, inplace = True)
       
    # Информация о сделках
    get_deals(XMLdata)
    df_deals = pd.DataFrame(dict_deals)
    df_avg = pd.DataFrame(dict_avg_price)
    
    # Информация о неторговых операциях по счету
    get_nontrade_operation(XMLdata)
    df_flows = pd.DataFrame(dict_flows)

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

Но дальше приходится применять специальную конструкцию, которая обеспечит получение необходимого псевдонима для тега исходя из отчета (SPB или MOEX), т.к. одинаковые по своей сути данные в этих отчетах называются по-разному.



Функция get_allias возвращает наименование необходимого тега для обработки, принимая на вход наименование торговой площадки:


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


Далее, с помощью функции get_deals извлекается информация о сделках:


Кроме обработки массива с информацией о параметрах сделки, здесь также выполняется расчет средней цены открытой позиции и реализованного PNL методом FIFO. За этот расчет отвечает класс PnlSnapshot, для создания которого с небольшими модификациями был принят за основу код представленный здесь: P&L calculation

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


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

Функция get_company_from_str извлекает наименование эмитента из комментария:


Функция get_company_from_briefcase приводит наименование компании к словарю, если находит соответствие среди компаний, которые принимали участие в сделках:


И, наконец, итоговая функция сбора данных по неторговым операциям — get_nontrade_operation:


Результатом сбора данных из отчетов будут три DataFrame, которые представляют собой примерно следующее:

1. DataFrame с информацией по средним ценам открытых позиций:
   
   
2. DataFrame с информацией о сделках:
   
   
3. DataFrame с информацией о неторговых операциях:
   
   
   
   

Итак, все, что нам остается сделать – это выполнить внешнее объединение таблицы сделок с таблицей информации о портфеле:

df_result = pd.merge(df_deals, df_stocks_avg, how='outer', on=['stock_name', 'account', 'currency']).fillna(0)
df_result.sample(10)

И, наконец, финальная часть обработки массива данных – это слияние полученного на предыдущем шаге массива данных с DataFrame для неторговых сделок.
Итог проделанной работы – одна большая плоская таблица со всей необходимой информацией для анализа:

df_result_full = df_result.append(df_flows, ignore_index=True).fillna(0)
df_result_full.sample(10).head()

Результирующий набор данных (Финальный отчет) из DataFrame легко выгружается в CSV и далее может использоваться для детального анализа в любой BI-системе.

if not exists('OUTPUT'): makedirs('OUTPUT')
report_name = 'OUTPUT\my_trader_diary.csv'

df_result_full.to_csv(report_name, index = False, encoding='utf-8-sig')

Загрузка и обработка данных в AWS

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

Одним из самых крупных и известных на рынке поставщиков облачных технологий является компания Amazon. Рассмотрим на примере среды Amazon Web Services (AWS) построение аналитической системы для обработки данных по нашему инвестиционному портфелю.

В AWS обширный выбор инструментов, но мы будем пользоваться следующими:

• Amazon S3 – объектное хранилище, которое позволяет хранить практически неограниченные объемы информации;
• AWS Glue – мощнейший облачный ETL-сервис, который может сам определять по заданным исходным данным структуру и генерить ETL-код;
• Amazon Athena – serverless сервис интерактивных запросов SQL, позволяет быстро анализировать данные из S3 без особой подготовки. Еще он имеет доступ к метаданным, которые подготавливает AWS Glue, что позволяет сразу после прохождения ETL обращаться к данным;
• Amazon QuickSight – serverless BI-сервис, можно строить любую визуализацию, аналитические отчеты «на лету» и т.д..

С документацией у Amazon все в порядке, в частности, существует неплохая статья Best Practices When Using Athena with AWS Glue, где описано как создавать и пользоваться таблицами и данными, посредством AWS Glue. Давайте и мы воспользуемся основными идеями этой статьи и применим их для создания своей архитектуры аналитической отчетной системы.

Подготовленные нашим парсером отчетов CSV-файлы будем складывать в S3 bucket. Планируется, что соответствующая папка на S3 будет пополняться каждую субботу – по завершении торговой недели, поэтому не обойтись без секционирования данных по дате формирования и обработки отчета.
Помимо оптимизации работы SQL-запросов к таким данным, этот подход позволит нам проводить дополнительный анализ, например, получать динамику изменения финансового результата по каждой бумаге и т.д.



Рассмотрим сформированную таблицу более подробно.
Если щёлкнуть по названию созданной таблицы, то мы перейдем на страницу с описанием метаданных. Внизу расположена схема таблицы и самым последним идет столбец, которого не было в исходном CSV-файле — date_report. Этот столбец AWS Glue создает автоматически на основе определения секций исходных данных (в бакете S3 мы специальным образом именовали папки — date_report=YYYY-MM-DD, что позволило использовать их как секции, разделенными по дате).

Анализ данных

Имея в своем распоряжении загруженные обработанные данные, можем с легкостью приступить к их анализу. Для начала, рассмотрим возможности Amazon Athena как самого простого и быстрого способа выполнения аналитических запросов. Для этого переходим в сервис Amazon Athena, выбираем нужную нам базу данных (financial) и пишем такой SQL-код:

select 
	d.date_report, d.account,
	d.stock_name,  d.currency,
	sum(d.quantity) as quantity,
	round(sum(d.result), 2) as result 
from my_trader_diary d
group by 
	d.date_report, d.account,
	d.stock_name,  d.currency
order by 
	d.account, d.stock_name,
	d.date_report;

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


А что, если мы хотим визуализировать полученные данные в виде гибких таблиц или диаграмм? Тут на помощь приходит сервис Amazon QuickSight, с помощью которого можно настроить гибкую аналитику практически так же быстро, как и написать SQL-запрос. Перейдем в сервис Amazon QuickSight (если Вы там еще не зарегистрировались, то необходима регистрация).

Нажимаем на кнопку New analyses -> New dataset и в появившемся окне выбора источников для датасета, щелкаем на Athena:



Придумаем название нашему источнику данных, например «PNL_analysis» и нажимаем на кнопку «Create data source».

Следом откроется окно Choose your table, где необходимо выбрать базу данных и таблицу-источник данных. Выберем базу данных – financial, и таблицу в ней: my_traider_diary. По умолчанию используется таблица целиком, но при выборе «Use custom SQL» можно кастомизировать и тонко настроить нужную Вам выборку данных. Для примера воспользуемся таблицей целиком и нажмем на кнопку Edit/Preview Data.

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

Теперь необходимо в наш датасет добавить дополнительные вычисляемые поля: квартал и год выполнения операции. Внимательный читатель может заметить, что подобные манипуляции легче было проделать на стороне парсера перед сохранением Финального отчета в CSV. Бесспорно, но моя цель сейчас продемонстрировать возможности и гибкость настроек BI-системы «на лету». Продолжим создание вычисляемых полей, нажимая на кнопку «New field».


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



Когда вычисляемые поля успешно созданы и добавлены в выборку, даем название нашему датасету, например, «my_pnl_analyze» и нажимаем на кнопку «Save and visualize».

После этого переносимся на основную доску Amazon QuickSight и первое, что мы должны сделать – это настроить фильтр для даты отчета (с учетом того, что одни и те же данные были собраны из трех секций). Выбираем отчетную дату 2018-10-01 и нажимаем на кнопку Apply и переходим на закладку Visualize.


Теперь мы можем визуализировать результат по портфелю в любой плоскости, например, по каждой ценной бумаге внутри торгового счета, и разделенную в свою очередь по валютам (т.к. результат в разных валютах не сопоставим) и типам операций. Начнем с мощнейшего инструмента любого BI – сводных таблиц. Для экономии места и гибкости отображения, я вынес валюты в отдельный контрол (аналог среза в MS Excel)

В приведенной выше таблице видно, что если инвестор решит продать сейчас все акции ФСК ЕЭС, то он тем самым зафиксирует убыток, т.к. выплаченные дивиденды в размере 1 509.91 р. не покрывают его издержки (1 763.36 р. – отрицательная курсовая разница и 174 р. – НДФЛ на дивиденды). Есть смысл повременить и дождаться лучших времен на Бирже.

Следующий график – столбчатая диаграмма:


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



Итоговая таблица представлена на скриншоте ниже:

Выводы и итоги

Мы рассмотрели с Вами реализацию парсера отчетов и способы анализа подготовленных им данных «на лету» при помощи сервисов Amazon. Также затронули некоторые бизнесовые и фундаментальные аспекты анализа инвестиционного портфеля, т.к. эта тема практически необъятная и уместить ее в одной статье довольно сложно, думаю, что есть смысл поместить ее в отдельную публикацию или даже, цикл публикаций.

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

Уверен, что данная система найдет свое применение и будет иметь дальнейшее развитие. Например, планируется добавить в расчет полного PNL по портфелю учет депозитарной и иной комиссии (например, за вывод денежных средств), а так же погашение облигаций и т.д… Вычисляемые поля на стороне Quicksight были использованы с демонстрационной целью, в следующей версии парсера, все эти дополнительные столбцы будут перенесены в Python и будут рассчитываться на стороне парсера.

Как архитектор и главный бизнес-заказчик данного решения, я вижу дальнейшую модернизацию следующим образом: ну не хочу я каждый раз вручную запрашивать эти XML-отчеты! Конечно, пока иной возможности нет, но API Брокера с передачей токена и диапазона выборки идеально подошло бы для еженедельного получения сырой отчетности. Последующая полная автоматическая обработка на стороне Amazon: от срабатывания ETL-job на AWS Glue до получения готовых результатов в виде графиков и таблиц в Amazon QuickSight позволят полностью автоматизировать процесс.

Полный исходный код можно найти в моем репозитории на GitHub