1. Сбор и подготовка финансовых данных: использование типа данных «Акции» в Excel
Сбор и подготовка финансовых данных: использование типа данных «Акции» в Excel
Предиктивная аналитика фондовых рынков опирается на один фундаментальный принцип: качество прогноза напрямую зависит от качества исходных данных. Любая математическая модель, будь то простая линейная регрессия или сложный алгоритм машинного обучения, требует точной, актуальной и непрерывной исторической информации. Раньше аналитикам и частным инвесторам приходилось тратить часы на ручной перенос котировок с финансовых порталов в электронные таблицы, что неизбежно приводило к опечаткам и потере времени. Сегодня современные табличные процессоры предлагают встроенные инструменты для автоматизации этого процесса.
Интеграция облачных технологий и искусственного интеллекта в Microsoft Excel позволила создать динамические типы данных, которые самостоятельно связываются с мировыми биржами и обновляют информацию в реальном времени ru.extendoffice.com. Это кардинально меняет подход к финансовому моделированию, позволяя сосредоточиться на поиске закономерностей, а не на техническом сборе цифр.
> Если количество ценных бумаг не так велико, то подобное использование таблиц оправдано... Но у такого метода учета есть и свои минусы, главным образом связанные с необходимостью ручного обновления котировок. Печально и долго. > > habr.com
Интеллектуальный тип данных «Акции»
Тип данных «Акции» (Stocks) — это специальный формат ячеек в Microsoft 365, который распознает текстовые названия компаний или их биржевые тикеры и преобразует их в структурированный набор финансовых показателей finas.su. Вместо обычного текста ячейка начинает содержать скрытый массив данных, из которого можно извлечь любую нужную метрику.
Процесс преобразования обычного текста в финансовые данные состоит из нескольких простых шагов:
После этого рядом с текстом появится небольшая иконка здания биржи. Это означает, что Excel успешно связался с сервером и загрузил пакет информации по данной бумаге. Важно отметить, что система поддерживает не только американские площадки вроде NYSE или NASDAQ, но и Московскую биржу, что делает инструмент универсальным для локальных инвесторов rostsber.ru.
| Характеристика | Ручной сбор данных | Тип данных «Акции» в Excel | | --- | --- | --- | | Скорость обновления | Низкая (требует ручного поиска) | Высокая (обновление по нажатию кнопки) | | Вероятность ошибки | Высокая (человеческий фактор при вводе) | Минимальная (прямая трансляция с бирж) | | Масштабируемость | Сложно анализировать более 10-20 бумаг | Легко обрабатывать сотни тикеров одновременно | | Доступные метрики | Только те, что скопированы вручную | Более 20 встроенных показателей (P/E, Beta, Объем и др.) |
При нажатии на специальный значок добавления столбца (или при вводе формулы вида =A2.[Price]) вы можете извлечь текущую цену, процентное изменение за день, капитализацию, объем торгов и даже количество сотрудников компании.
Предположим, в вашем портфеле находится 150 акций компании Microsoft. Вчера цена закрытия составляла 320 USD. Сегодня на открытии торгов цена выросла до 325 USD. Если ваша таблица настроена с использованием типа данных «Акции», общая стоимость позиции автоматически пересчитается с 48 000 USD до 48 750 USD при обновлении книги, без необходимости искать текущую котировку в интернете.
Получение исторических данных для предиктивных моделей
Текущая цена полезна для оценки портфеля, но для предиктивной аналитики нам нужна история. Прогнозирование будущих движений цен строится на анализе прошлых тенденций. Для этого в Excel предусмотрена функция динамических массивов.
Функция ИСТОРИЯАКЦИЙ (в английской версии STOCKHISTORY) позволяет загружать массивы исторических котировок за любой заданный период rostsber.ru.
Синтаксис функции выглядит следующим образом: =ИСТОРИЯАКЦИЙ(Акции; Нач_дата; [Кон_дата]; [Интервал]; [Заголовки]; [Свойство1]; ...)
Ключевые параметры функции: * Акции — ссылка на ячейку с тикером или текст в кавычках. * Нач_дата — дата, с которой начинается загрузка истории. * Кон_дата — дата окончания периода (по умолчанию — текущий день). * Интервал — частота данных: 0 (ежедневно), 1 (еженедельно), 2 (ежемесячно).
Допустим, нам нужно получить ежемесячные цены закрытия для акций Tesla (TSLA) за весь 2023 год. Мы вводим формулу: =ИСТОРИЯАКЦИЙ("TSLA"; "01.01.2023"; "31.12.2023"; 2). Excel автоматически создаст таблицу из 12 строк, где каждому месяцу будет соответствовать точная цена закрытия. Если в январе цена была 150 долл., а в декабре 250 долл., мы получим готовый временной ряд для дальнейшего анализа.
Подготовка данных: расчет базовых метрик
Сырые цены (абсолютные значения) редко используются в предиктивных моделях напрямую. Цены разных акций несопоставимы: изменение цены акции стоимостью 10 долл. на 1 долл. — это рост на 10%, а для акции стоимостью 1000 долл. — всего на 0,1%. Поэтому первым этапом подготовки данных является переход от абсолютных цен к относительным доходностям.
Простая доходность за период рассчитывается по следующей формуле:
где — доходность в момент времени , — цена закрытия в текущий период, — цена закрытия в предыдущий период.
Рассмотрим пример. Акции Сбербанка закрылись в понедельник () на отметке 250 руб. Во вторник () цена закрытия составила 255 руб. Подставляем значения в формулу: (255 - 250) / 250 = 0,02. Это означает, что дневная доходность составила 2%.
Для сглаживания краткосрочных колебаний и выявления глобального тренда аналитики часто используют скользящие средние. Это один из самых популярных инструментов подготовки признаков (features) для машинного обучения в финансах.
Простая скользящая средняя (SMA) вычисляется так:
где — значение скользящей средней, — цена закрытия в день , — выбранный период сглаживания (количество дней).
Представим, что мы рассчитываем 5-дневную скользящую среднюю. Цены закрытия за последние 5 дней составили: 100, 102, 101, 105 и 107 руб. Сумма этих значений равна 515 руб. Делим на период () и получаем руб. Если на следующий день цена составит 110 руб., самое старое значение (100 руб.) выпадет из расчета, и новая средняя будет равна (102 + 101 + 105 + 107 + 110) / 5 = 105 руб.
Очистка данных и обработка пропусков
При работе с функцией ИСТОРИЯАКЦИЙ вы неизбежно столкнетесь с проблемой пропущенных значений. Фондовые биржи не работают в выходные и праздничные дни. Если вы попытаетесь сопоставить ежедневные данные по акциям с макроэкономическими показателями, которые публикуются каждый день, возникнет рассинхронизация временных рядов.
В Excel такие пропуски часто отображаются как ошибки #Н/Д (нет данных). Для предиктивной модели наличие пустых ячеек или текстовых ошибок критично — алгоритм просто не сможет произвести математические вычисления.
Существует два основных подхода к решению этой проблемы:
Например, цена в пятницу составила 50 долл. В понедельник торгов не было (праздник), а во вторник цена открылась на уровне 52 долл. При использовании метода Forward Fill мы искусственно проставляем для понедельника цену 50 долл. Это позволяет сохранить непрерывность временного ряда, что критически важно для расчета скользящих средних и авторегрессионных моделей.
Сбор и первичная обработка данных — это фундамент, на котором строится вся дальнейшая аналитика. Использование встроенных типов данных и функций извлечения истории в Excel позволяет автоматизировать рутину и подготовить качественный, математически корректный набор данных для построения предиктивных моделей.