Не секрет, что для финансистов Excel является отраслевым стандартом. Альтернатив этому инструменту нет. И хотя Excel традиционно понимается как офисное ПО, в российских условиях это не совсем так. Заглянем на HeadHunter.
Вот что предлагается в объявлениях
А вот что получается на практике
Хотя в объявлениях о приеме на работу мы видим много красивых слов типа «градиентный бустинг» или «AutoML-платформа», по большей части работа аналитика данных выглядит так, как на втором фото. И неважно, куда вы попадете — в банк (даже крупный), страховую компанию или любую другую финансовую организацию. Вам все равно придется сперва «запилить» свою заготовку, пройдя несколько сотен итераций выгрузки и загрузки данных. Скрипт справа внизу на втором фото — это обычный код для выгрузки «чего-нибудь откуда-нибудь» в Excel.
Почему все так сложно? Потому что данные берутся «кусками»: один фрагмент — из одного источника, другой — из другого. А после соединяются кустарным способом — можно сказать, с помощью гаечного ключа. В розничном кредитовании, например, даже продвинутые алгоритмы принятия решений AutoML все равно работают с табличными данными. А табличные данные надо подготовить. (Что, кстати, привело к выделению в организациях «специально обученных» сотрудников, которые готовят данные.)
Итак, что бы ни писали в объявлениях, подготовка табличных данных всегда будет вестись в Excel. А дополнительная сложность в том, что после подготовки данных руководство часто требует те же самые расчеты, но в другой группировке — и к завтрашнему утру.
Так что же должен знать аналитик данных, устраиваясь на работу в финансовую организацию?
Разбираем 15 ключевых функций на примере данных компании Enron
Понятно, что обанкротившаяся компания со множеством нарушений не может рассматриваться как положительный пример. Мы используем эти данные только потому, что они наглядны и находятся в открытом доступе (см.: Felienne Hermans, Emerson Murphy-Hill. Enron’s Spreadsheets and Related Emails: A Dataset and Analysis. May 2015. DOI:10.1109/ICSE.2015.129).
База данных Enron (Enron Corpus) представляет собой базу из более чем 600 000 электронных писем, созданных 158 сотрудниками корпорации Enron за годы, предшествовавшие краху компании в декабре 2001 г. Сайт EDRM.net опубликовал исправленную и расширенную версию 2-го корпуса, содержащую более 1,7 миллиона сообщений, которая была размещена на Amazon S3 для облегчения доступа исследователей. Это 15 770 корпоративных Excel-файлов.
По этим файлам можно посмотреть, как делаются расчеты в финансовых моделях, как готовятся отчеты о прибылях и убытках (PNL), как формируются свопы, активные и пассивные позиции и т.д.
Что самое интересное — есть агрегирующая статистика, показывающая, какие формулы используются в данных книгах Excel. Проанализировано около 20 миллионов ячеек.
Как видим, 99% расчетов охватываются 15 функциями
Причем речь идет о крупной компании, оперирующей в своих расчетах сотнями миллиардов долларов.
Итак, ключевые функции, которые вам нужно знать, — это:
1) СУММ;
2–5) арифметические действия «+», «–», «/», «*»;
6) ЕСЛИ;
7) ТДАТА();
8) СРЗНАЧ;
9) ВПР;
10) ОКРУГЛ;
11) СЕГОДНЯ — подвид функции ТДАТА();
12) ПРОМЕЖУТОЧНЫЕ.ИТОГИ — функция, которая в качестве первого параметра принимает некий агрегат;
13) МЕСЯЦ;
14) ЯЧЕЙКА;
15) ГОД.
Вся корпоративная «жизнь» оказалась сосредоточена в этих 15 функциях.
Вот еще несколько инсайтов, полученных из открытых данных:
— макросы используются крайне редко (47 файлов);
— именованные диапазоны практически не используются (720 файлов);
— ошибки в книгах — серьезнейшая проблема менеджмента (14 084 упоминания в переписке о проблемах в книгах, 2205 рабочих книг с проблемами);
— пароли практически не используются (419 книг);
— 80% книг состоят из одного листа.
Наш собственный опыт работы с российскими финансовыми организациями показывает, что в России дела обстоят так же и корпоративная реальность похожа на ситуацию в США в 2000-х годах (хотя в России некоторые виды расчетов до сих пор не применяются массово).
Так в чем же сложность?
Любой человек может освоить, например, перемножение даже не именованных диапазонов, а простых адресных ссылок. Почему же мы не зарабатываем ежегодные бонусы, равные 13 зарплатам?
Рассмотрим несколько кейсов, чтобы понять, что делают в финансовых организациях с помощью названных 15 функций. Похоже, что, несмотря на дурную славу компании Enron, в ней работали грамотные специалисты.
Все файлы находятся в открытом доступе и могут быть высланы нами по запросу: vladimir@raisk.ru.
Кейс 1. Формулы простые, но их много: расчет револьверного кредита
Файл Vkaminski 40690 Amod2.xlsx
На скриншоте — типичная формула 5-уровневого ЕСЛИ, которая встречается в каждой книге. Да, нам говорили, что так делать нельзя, но на практике все так делают. Обратите внимание на «И» внутри формулы, подсуммирование, бинарные условия.
То есть нужно не просто изучение формул, а скорее быстрое понимание булевой логики (да/нет). Именно с помощью этой логики проверяется каждая строка представленной таблицы.
Кейс 2. Более 40 имен в книге: расчет подразумеваемой волатильности опционов
Файл John griffith15579VolSkew050101oct.xlsx
Скажем по секрету, что Банк России работает так же. Когда моделисты готовят свои модели на выборке из сотен тысяч розничных кредитов, Банк России просит выгрузить статистику, расчеты рыночных рисков и т.п. в сводную таблицу (spreadsheet).
На скриншоте применена известная в финансовой математике функция расчета: если рыночная стоимость опциона не соответствует формуле, то в цену закладывается некая надбавка, которая означает подразумеваемую волатильность. Справа видно, сколько имен используется в расчете. Сам расчет несложный, сложно понять, что к чему.
Пример расчета (формула именного диапазона)
В примере расчета используется функция СМЕЩ, которую тоже очень любят применять в финансовых моделях, — смещение по столбцам и строкам. Мы знаем, что эта функция сильно запутывает: непонятно, из какой ячейки будет взято итоговое значение. Но мы также знаем, что разработчики подобных решений получают десятки тысяч долларов в месяц.
Кейс 3. Цена ошибки — 100 миллионов долларов США: расчет стоимости под риском
Файл Sara shackleton 36501ENE MTM 0630 revised 2.xlsx
Здесь интересна прежде всего цена возможной ошибки — и то, что такие суммы фигурируют в Excel-расчетах.
Кейс 4. Цветное суммирование: диаграмма Гантта для строительного проекта
Файл Phillip allen 28910 m version 2 20.xlsx
Диаграмма Гантта — мастхэв для всех, кто изучает Excel. Такие таблицы вам придется делать очень часто, где бы вы ни работали. На самом деле они интуитивно понятны, сложной логики здесь нет, просто нужно «набить руку».
Кейс 5. Ручные исключения: расчет баланса открытых позиций
Файл Sally beck 33936 SEC VaR.xlsx
Ручные исключения — это, наверное, основная причина популярности и незаменимости Excel. Он не может быть заменен Pandas или другими похожими инструментами, потому что когда «Matthew told Jennifer that there were no positions» — мы, игнорируя формулу, проставляем в ячейке 0. Это очень распространенная ситуация. В конечном итоге данные верифицируются специалистами, которые проставляют нули в соответствующих ячейках, после чего данные попадают в отчеты для регулятора, баланс и т.д.
Кейс 6. Подключение к локальной базе данных: сводная по операциям
Файл Sally beck 35801 Summary Report for EOL 2-29.xlsx
Как известно, Excel легко подключить к чему угодно — к базе данных, JSON, Binance, Google Map или любому другому источнику. Но политика банков такова, что даже с «родным» клиентом базы данных могут возникнуть проблемы доступа. На скриншоте используется функция БДСУММ, которая позволяет «забирать» из базы в ячейку определенные значения.
Кейс 7. Финансовая статистика: расчет дисконтированного денежного потока
Файл Kevin hyatt 19604 Base Model 111601.xlsx
Расчет дисконтированного денежного потока — это та задача, для решения которой Excel используют в финансах чаще всего. На скриншоте особенно интересны цифры (в тысячах долларов США). Повторим: реальный бизнес считает большие деньги в Excel-таблицах.
Кейс 8. Разложение Холецкого: портфельная оптимизация
Файл Vkaminski 40915 LJM Simulation3.xlsx
На скриншоте показан расчет свопов, где с помощью функции Excel сделано разложение Холецкого — очень распространенное упражнение. Примечательно, что здесь не используется VBA, обычно применяемый для таких целей.
Стоит иметь в виду, что такие вещи, как Cholesky Factorization, лучше использовать со зрелыми библиотеками. Если это делает любитель на VBA, даже имеющий финансовую степень, — это его личное видение. Например, в Python-библиотеках результаты разных статистических пакетов просто не сходятся. Для зрелых статистических пакетов проводят бенчмаркинг по результатам с MATLAB, R (потому что в научной среде в R очень большая экосистема) и т.п. Но, конечно, Python дружелюбнее к новичкам.
На скриншоте мы видим типичный пример того, как делать не нужно. Матричных операций, как и оконных функций (функций над всем столбцом, которые позволяют рассчитать результат предыдущего столбца с подсортировкой), в Excel лучше избегать. Тем не менее, в банках продолжают это делать в Excel, к чему вы должны быть готовы. Хотя сейчас многие финансовые организации переходят на AutoML-платформы, на Python и т.д., этот инструмент будет жить еще лет 10–15.
***
Как утверждает компания Microsoft, «каждый крупный прогресс в аппаратном обеспечении был узаконен с помощью электронной таблицы». Но стоит вспомнить еще одно высказывание. Дэн Филстра, стоявший у истоков индустрии программных продуктов и разработавший в конце 1970-х годах программу VisiCalc (позже перешедшую к Microsoft), сказал: «Инновации трудно планировать». (Интересно, что Дэн Филстра до сих пор делает солверы для Excel.)
Действительно, инновации — продукт вдохновения. А вдохновение рождается из опытности. Так что хотелось бы пожелать читателям опытности в табличных данных — это самое наглядное представление из всего того, что мы имеем сегодня.
Статья написана на основе выступления управляющего директора компании Raisk Владимира Козлова на методическом семинаре Цифровой кафедры МГИМО «Как изучать и преподавать современный Excel» (3 апреля 2023 г.).