Валентин Арьков - Анализ рядов динамики в электронных таблицах

В последнем уравнении тренда в качестве «иксов» учитывается дата.

Напомним, что дата в Excel хранится как целое число — номер дня. Начало отсчёта — 1 января 1900 года. Отображение на экране зависит от выбранного формата ячеек, но внутри ячейки всё равно хранится порядковый номер дня.

Задание. Введите в любую ячейку таблицы число 1. Установите формат ячейки для отображения даты. Обратите внимание на то, какая дата теперь выводится на экран

Далее мы построим модель тренда за последний год и сделаем прогнозы по доходности.

Создадим копию нашего листа с котировками биржевого фонда. Выделим столбец дат. Установим общий формат отображения содержимого ячеек:

Format Cells — Number — Category — General

Даты превратились в целые положительные числа (рис. 10.30).

Рис. 10.30. Общий формат ячеек

Задание. Установите общий формат вывода для колонки дат.

Подготовим исходные данные для регрессионного анализа. Найдём среднее взвешенное значения по четырём ценам (рис. 10.31). В этой формуле вы даём увеличенный вес ценам открытия и закрытия. В знаменателе — сумма весовых коэффициентов.

Рис. 10.31. Средневзвешенная цена

Добавляем новую колонку в таблицу. Подсчитываем средневзвешенные цены (рис. 10.32).

Рис. 10.32. Средневзвешенные цены

Задание. Добавьте новый столбец и подсчитайте средневзвешенные цены.

Построим график и нанесём на него линию тренда (рис. 10.33). Для начала используем встроенные средства «точечной» диаграммы.

Рис. 10.33. Линия тренда

Задание. Постройте график средневзвешенных цен и включите отображение линейного тренда. Выведите уравнение тренда на свободное место графика.

Для дальнейшей работы нам понадобятся коэффициенты уравнения тренда. Вызываем надстройку «Анализ данных» и строим линейное уравнение регрессии (рис. 10.34). Подробности работы с надстройкой мы уже рассмотрели чуть раньше в данной работе.

Рис. 10.34. Построение линейной регрессии

Получаем оценки коэффициентов. Нам нужны всего два коэффициента. Так что всё лишнее безжалостно удаляем.

Оформляем уравнение регрессии (рис. 10.35). Используем ссылки на ячейки с коэффициентами. Вся процедура была рассмотрена и описана в предыдущих разделах.

Рис. 10.35. Линейная регрессия

Задание. Постройте уравнение тренда с помощью надстройки «Анализ данных». Оформите уравнение регрессии с использованием ссылок на оценки коэффициентов.

У нас всё готово для анализа и прогнозирования доходности.

Нам понадобятся два стандартных показателя динамики (рис. 10.36):

— коэффициент роста К р;

— темп прироста Т пр.

Коэффициент роста говорит, «во сколько раз» увеличилось значение уровня ряда, а темп прироста сообщает, «на сколько процентов».

В этих формулах y 0 — это начальное значение, а y t — конечное значение цены актива за какой-нибудь период времени. Например, за год.

Для расчёта средней доходности нам нужно оценить средний коэффициент роста, а затем по нему найти средний темп прироста. Здесь попутно нужно будет найти корень n-й степени.

Рис. 10.36. Показатели динамики

Следующие три задания выполняются на новом листе отчёта.

Задание. Запишите формулы для текущего и среднего значения коэффициента роста и темпа прироста. Вставьте формулы в отчёт.

Задание. Представим, что К р= 1,2. Вычислите значение Т пр. Что нам сообщают эти значения коэффициента роста и темпа прироста? Запишите фразы, выражающие смысл этих показателей.

Задание. Представим, что цена выросла в 1,2 раза за два года. Определите, во сколько раз и на сколько процентов в среднем цена росла каждый год. Запишите соответствующие фразы.

Переходим к анализу доходности. Обычно в рекламе фондов нам предлагают взять текущие значения в начале и в конце какого-нибудь благополучного периода. Дальше по этим значениям находят те самые К ри Т пр.

Добавим колонку дат и настроим формат ячеек для вывода даты. Теперь у нас есть и номер дня, и дата. Временно скроем лишние ячейки

Возьмём данные по ценам закрытия за 27 декабря 2018 и 2019 годов, чтобы найти доходность за 2019 год (рис. 10.37). Лучше бы, конечно, взять данные за 1 января, но биржа в праздники была закрыта.

Делаем расчёты по нашим стандартным формулам. Получаем доходность 6,6% годовых.

Рис. 10.37. Доходность за 2019 год

На следующем рисунке 10.38 мы провели анализ доходности за последние пять лет и в среднем за год. Получаем среднюю доходность около 8,4% годовых.