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

Учебное пособие

Валентин Юльевич Арьков

© Валентин Юльевич Арьков, 2020

ISBN 978-5-4498-0461-7

Создано в интеллектуальной издательской системе Ridero

Введение

В данной работе мы рассмотрим раздел «Динамика». Здесь изучают данные, привязанные ко времени. Мы будем опираться на две предыдущие работы: «Анализ распределения (Сводка и группировка)» и «Анализ взаимосвязи (Корреляция и регрессия)». Работа выполняется в последней версии Microsoft Excel, хотя при желании можно использовать любой другой пакет типа электронных таблиц.

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

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

1. Цель и задачи

Целью данной работы является освоение методов статистического АНАЛИЗА ДИНАМИКИ социально-экономических явлений. В статистике есть ряд стандартных инструментов и технологий для анализа динамики. Эти методы появились гораздо раньше, чем вычислительная техника. Фактически, большинство компьютерных программ реализуют те методы, которые были разработаны в далёком прошлом и прошли проверку временем.

Для достижения поставленной цели мы будем решать следующие задачи:

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

2. Мы рассмотрим методы сглаживания ряда динамики. Это разнообразные скользящие средние и их вариации. В числе прочего, нас ожидает скользящая медиана.

3. Мы проведём линию тренда на графике и построим уравнение тренда. Будем работать с самым простым вариантом — линейной моделью.

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

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

2. Отчёт

Отчёт по работе оформляется в одном файле (рабочей книге) Microsoft Excel. Все действия и комментарии фиксируем в документе. Отдельный текстовый файл оформлять не требуется. Оформление отчёта мы уже подробно рассмотрели в первой работе [1].

Задание. Создайте новый документ Excel и сохраните в файле с коротким информативным названием. При необходимости обращайтесь к описанию первой работы.

Первый лист книги Excel называем 01. На титульном листе указываем следующие сведения (в соответствии с требованиями к оформлению текстовых документов):

— название министерства;

— название вуза;

— название кафедры, которая проводит занятия;

— название дисциплины;

— тип документа;

— номер и тему работы;

— номер варианта;

— номер группы;

— фамилии и инициалы студентов;

— должность, фамилию и инициалы преподавателя;

— город;

— год.

Для каждого задания создаём новую страницу в книге Excel и называем её в соответствии с выполняемым действием. Чтобы не загромождать вкладки длинными названиями, будем указывать только порядковые номера страниц. А вот на самих страницах можно написать название поподробнее.

Задание. Создайте две новых страницы и озаглавьте их 02 и 03. В верхней части листов укажите соответственно «Оглавление» и «Задание».

Для удобства навигации по тексту сделаем оглавление со ссылками на каждую страницу отчёта. Кроме того, на каждой странице сделаем ссылку на страницу оглавления. Подробности работы с оглавлением мы тоже рассмотрели в описании первой работы.

Задание. Оформите оглавление.

3. Ключевые термины

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

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

3.1. Динамика

ДИНАМИКА — это раздел статистики, в котором изучается изменение каких-нибудь значений во времени. Это просто последовательность моментов времени и соответствующие им числовые значения. В других дисциплинах слово «динамика» может означать что-нибудь другое. К примеру, в физике слово «динамика» — это название раздела, в котором изучается движение тел под воздействием сил. Ну а в обычном, разговорном понимании «динамика» — это просто изменение чего-нибудь — безо всякого изучения, анализа и математических формул.

Происходит слово динамика от греческого dynamis — «сила, мощь». В английском языке есть дополнительное значение у похожего слова dynamics — «движущая физическая или моральная сила, источник изменений или движения в какой-либо ситуации». В русском языке у слова «динамика» такого значения нет, но зато многие родственные слова с корнем «динам…» имеют оттенок «силы или энергии». В статистике раздел «Динамика» имеет дело только с изменениями значений, и здесь не рассматриваются «движущие силы».

Задание. Дайте определение слову ДИНАМИКА.

3.2. Ряд динамики

Наши исходные данные называются РЯД ДИНАМИКИ — это последовательность числовых значений с привязкой ко времени. Иногда используют другие названия: динамический ряд или временнóй ряд. Английское название: Time Series.

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

Очень упрощённо можно сказать, что ряд динамики — это таблица последовательных значений чего-нибудь: цен на один и тот же товар, курса одной и той же валюты, количества произведённой продукции на одном и том же предприятии. Это колонка исходных данных, упорядоченных по времени. В таблице может быть указано время. А может и не быть указания времени. Но в любом случае, значения идут друг за другом, в хронологическом порядке.

Задание. Дайте определение выражению РЯД ДИНАМИКИ.

Вот, кстати, ещё одно слово: ХРОНОЛОГИЯ. В нашем случае слово хронология означает «последовательность каких-либо событий во времени; временнáя последовательность». Происходит от греческих слов chronos — «время» и logos — «учение, изучение, описание».

Задание. Дайте определение слову ХРОНОЛОГИЯ.

3.3. Уровень

Отдельное значение в ряду динамики называется УРОВЕНЬ РЯДА или просто уровень. Поэтому можно встретить такие фразы: «По сравнению в уровнем прошлого года». Условное обозначение: y (t) или просто y. С одной стороны, это латинская буква «игрек». С другой стороны, эта русская буква «У» — начало слова «уровень». Вот такая игра слов. Точнее, букв.

Задание. Дайте определение выражению УРОВЕНЬ РЯДА.

3.4. Анализ

Считается, что в рядах динамики могут присутствовать разные составные части. При анализе динамики эти части пытаются выделить и рассмотреть по отдельности. Слово АНАЛИЗ означает «разделение на части и рассмотрение по частям, а также изучение того, как эти части соединяются в единое целое». Происходит от греческого слова analysis — «разложение, растворение», где ana — «обратно» и lysis —«развязывание, разрешение, освобождение; растворение».

Задание. Дайте определение слову АНАЛИЗ.

3.5. Компонент

Итак, анализ — это деление на составные части, выделение компонентов. КОМПОНЕНТ — это составная часть чего-либо. Происходит от латинского слова componens — «составляющий», от componere — «складывать; строить; сочинять», где com- — «с, вместе» и ponere — «класть, ставить». Получается, что буквальное значение слова компонент — «составная часть, составляющая». Обратите внимание, что слово компонент мужского рода.

Задание. Дайте определение слову КОМПОНЕНТ.

3.6. Компоненты ряда динамики

Какие же компоненты ряда динамики пытаются выделить и изучить по отдельности при проведении анализа? Для начала перечислим эти три составные части:

T — тренд;

S — колебания;

E — случайность.

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

Каждый из этих трёх компонентов не похож на два других. Их перепутать невозможно. Слишком уж они разные.

Задание. Перечислите три компонента динамики и укажите их обозначения.

3.7. Тренд

ТРЕНД — основная тенденция ряда динамики, долгосрочное направление в развитии, изменении чего-либо. Английское название: Trend. Условное обозначение — латинская буква T. Происходит от английского слова trend — «изгиб реки; направление движения, изменения», от глагола trend — «отклоняться; изгибаться, загибаться».

Задание. Дайте определение слову ТРЕНД.

3.8. Колебания

КОЛЕБАНИЯ — это повторение изменений с течением времени. Колебания происходят вокруг среднего значения. А точнее, вокруг тренда. Происходит от слова колебать — «качать, укачивать». Получается, что это «качание» значений — то выше, то ниже среднего значения.

Задание. Дайте определение слову КОЛЕБАНИЯ.

3.9. Сезонные колебания

Мы будем рассматривать один из видов колебаний в экономике — это СЕЗОННЫЕ КОЛЕБАНИЯ. Слово СЕЗОН означает «время года», то есть «зима-весна-лето-осень». Эта смена времён года приводит к колебаниям цен в течение года — на картошку, апельсины, мороженое, плавки и тёплую одежду. Причины колебаний просты — на цены влияет время сбора урожая и, соответственно, температура за окном.

Происходит от французского слова saison — «время года», от латинского satio — «время сева», от serere — «сеять». Понятно, что время сева для каждого вида растений каждый год одинаково — например, весна.

Условное обозначение — латинская буква S.

Наличие сезонных колебаний и сами эти колебания для краткости иногда называют СЕЗОННОСТЬ.

Задание. Дайте определение слову СЕЗОННОСТЬ.

3.10. Случайная составляющая

СЛУЧАЙНАЯ СОСТАВЛЯЮЩАЯ — это нечто непредсказуемое; то, что не удаётся учесть. Это непредсказуемая часть динамики, которая не была учтена с помощью двух предыдущих компонентов — тренда и колебаний.

Условное обозначение — латинская буква E. Можно предположить, что буква E — это намёк на английское слово Error — «ошибка». Понятно, что ошибка — это чаще всего что-то случайное, непреднамеренное.

Иногда встречается сокращённое название СЛУЧАЙНОСТЬ.

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

Задание. Дайте определение выражению СЛУЧАЙНАЯ СОСТАВЛЯЮЩАЯ.

4. Модели рядов динамики

Ряд динамики составлен из трёх компонентов, которые мы только что рассмотрели. Теперь остаётся вопрос: как именно соединить эти части в одну модель?

Обычно рассматривают два вида моделей динамики.

Вариант первый: сложить.

Вариант второй: перемножить.

Мы обсудим оба типа моделей. А потом ещё и сгенерируем данные обоих типов.

4.1. Аддитивная модель

Сложение компонентов даёт нам АДДИТИВНУЮ МОДЕЛЬ:

y = T + S + E.

Естественно, это сумма для каждого момента времени. То есть каждый член уравнения зависит от времени и меняется со временем:

y (t) = T (t) + S (t) + E (t).

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

Слово АДДИТИВНАЯ означает «сложение», от английского add — «складывать».

Задание. Дайте определение слову АДДИТИВНАЯ.

Рассмотрим, как же происходит сложение компонентов (рис. 4.1).

На верхнем графике изображён тренд в виде прямой линии, направленной вверх. Это растущий тренд.

На среднем графике показаны сезонные колебания ВОКРУГ НУЛЯ. Например, это могут быть колебания от —20 до +20. То есть «плюс-минус двадцать рублей».

Теперь будем складывать эти два графика и построим нижний график: T + S. На графике колебаний есть точки, где линия S проходит через ноль: S = 0. Если к любому значению Т прибавить ноль, мы получаем то же самое значение Т. Это значит, что в этих точках мы окажемся на линии тренда.

Когда S положительно, линия S находится выше горизонтальной оси, и мы оказываемся выше линии тренда. Когда S отрицательно, мы будем ниже линии тренда. Так получаются колебания с постоянной амплитудой вокруг линии тренда. Размах этих колебаний соответствует амплитуде сезонных колебаний S. Например, «сто плюс-минус двадцать рублей» или «тысяча плюс-минус двадцать рублей».

Рис. 4.1. Тренд плюс сезонность

Нам предстоит делать много зарисовок по ходу выполнения работы. Технология вставки ручных зарисовок подробно описана в первой работе [1]. Можете ещё раз открыть это пособие и освежить в памяти материал.

Задание. Сделайте зарисовку рис. 4.1 и вставьте в отчёт.

4.2. Мультипликативная модель

Произведение компонентов даёт нам МУЛЬТИПЛИКАТИВНУЮ МОДЕЛЬ:

y = T * S * E.

Как и в предыдущем случае, каждый член уравнения является функцией времени:

y (t) = T (t) * S (t) * E (t).

Слово МУЛЬТИПЛИКАТИВНАЯ означает «умножение», от английского multiply — «умножать».

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

Задание. Дайте определение слову МУЛЬТИПЛИКАТИВНАЯ.

Рассмотрим, как же происходит умножение компонентов (рис. 4.2).

На верхнем графике изображён растущий тренд — как и в предыдущем примере.

На среднем графике показаны сезонные колебания. Но теперь это колебания ВОКРУГ ЕДИНИЦЫ. Например, это могут быть колебания от 0,8 до 1,2. То есть плюс-минус 20% от единицы. Здесь колебания будут В ПРОЦЕНТАХ, а не в рублях!

Перемножим эти два графика и построим нижний график:

T * S.

На графике колебаний есть точки, где S проходит через единицу: S = 1. Если любое значение Т умножить на единицу, получим то же самое значение Т. Это значит, что в этих точках мы окажемся на линии тренда.

Когда S больше единицы, после умножения мы окажемся выше линии тренда. Когда S меньше единицы, мы будем ниже линии тренда. Получаем колебания вокруг линии тренда. Но теперь амплитуда колебаний будет изменяться вместе с трендом. Например, «плюс-минус двадцать процентов». Это «плюс-минус 200 рублей» вокруг значения 1000. А вокруг значения 2000 уже получим колебания «плюс-минус 400 рублей».

Рис. 4.2. Тренд умножить на сезонность

Задание. Сделайте зарисовку рис. 4.2 и вставьте в отчёт.

5. Варианты заданий

Теперь пришло время выбрать свой вариант — последнюю цифру номера зачётки. Если попалась цифра 0, берите вариант номер 10. Нулевой вариант будет использоваться для демонстрации приёмов выполнения работы.

Задание. Выясните свой номер варианта и укажите его на титульном листе отчёта.

В таблице 5.1 приведены варианты заданий с 0 по 10. Нам предстоит вставить параметры своего варианта в отчёт. Пока что это просто семь чисел. В следующем разделе мы с ними познакомимся поподробнее.

Задание. Вставьте параметры своего варианта в отчёт.

6. Зарисовки графиков

На следующих страницах отчёта мы сделаем зарисовки общей формы графиков наших исходных данных.

Изобразим три компонента по отдельности и их соединение в виде моделей рядов динамики (аддитивной и мультипликативной). Зарисовки делаем от руки на бумаге. Фотографируем и вставляем в отчёт, как обычно.

Зарисовки для каждой из обеих моделей расположим на разных листах отчёта.

Задание. Создайте две новые страницы в отчёте и дайте им соответствующие заголовки.

6.1. Аддитивная модель

Аддитивную модель будем строить по следующим формулам (рис. 6.1).

Рис. 6.1. Уравнения аддитивной модели

Вставляем формулы в отчёт (рис. 6.2). Продолжаем тренироваться писать от руки.

Рис. 6.2. Аддитивная модель

Задание. Запишите формулы аддитивной модели и вставьте в отчёт.

6.1.1. Тренд

Вначале займёмся трендом. Запишем уравнение тренда для нулевого варианта (рис. 6.3).

Рис. 6.3. Уравнение тренда

Задание. Вставьте в отчёт уравнение тренда в соответствии со своим вариантом.

Теперь сделаем зарисовку формы тренда по нашему уравнению. Это уравнение прямой линии. Поэтому для построения графика достаточно взять две точки (рис. 6.4).

Рис. 6.4. Форма тренда

Линия тренда пересекает ось y в точке a. Это первая точка на графике. Координаты первой точки {0; a}.

Мы рассматриваем n отсчётов по времени. Значит, в правой части мы доходим до значения a + bn. Координаты второй точки {n; a + bn}.

Подставим параметры нулевого варианта. Получаем следующие точки (рис. 6.5).

Рис. 6.5. Расчёты тренда

Переходим к зарисовке тренда (рис. 6.6). Выбираем масштаб — «красивые» круглые числа. По горизонтальной оси от 0 до 50. По вертикальной оси от 0 до 30. Проводим линию тренда. Очень приблизительно. На глазок. Без линейки. Вставляем в отчёт.

Рис. 6.6. Зарисовка тренда

Задание. Сделайте зарисовку линии тренда и вставьте в отчёт.

6.1.2. Сезонность

В формулу для сезонности мы заложили, «спрятали» определённый период колебаний. Период — это промежуток времени, через который график повторяется. Период колебаний Tк и коэффициент при переменной t связаны по следующим формулам (рис. 6.7).

Рис. 6.7. Уравнения колебаний

Коэффициент А определяет амплитуду колебаний.

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

Сезонные колебания связаны с временами года. Попробуйте угадать с одного раза: с какой периодичностью к нам приходит Новый год? Через сколько месяцев? Вот это и есть период сезонных колебаний. С таким периодом происходит, например, ежегодный всплеск цен на турпоездки в тёплые страны.

Частота колебаний f — это величина, обратная периоду колебаний Тк. Это количество колебаний или оборотов в единицу времени. Частота по-английски называется FREQUENCY. Видимо, поэтому частоту обозначают латинской буквой f.

Круговая частота «омега» связана с частотой f через множитель «два пи». Это коэффициент при t в аргументе функции синуса (рис. 6.8).

Рис. 6.8. Период колебаний

Задание. Вычислите период сезонных колебаний.

Мы разобрались с периодом колебаний. Теперь можно записать уравнение для сезонных колебаний в нашей аддитивной модели (рис. 6.9). Здесь учитывается амплитуда и период колебаний для нулевого варианта.

Вставляем уравнение в отчёт.

Рис. 6.9. Уравнение сезонности

Задание. Запишите уравнение сезонных колебаний для своего варианта и вставьте в отчёт.

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

График синусоиды проходит через ноль в точках 0, 6, 12, 18 и так далее. Это шаг, равный половине периода колебаний. Минимумы и максимумы будут соответственно в точках 3, 9, 15 и так далее.

Сделаем схематичную, примерную зарисовку графика сезонности (рис. 6.10). Вставляем график в отчёт.

Рис. 6.10. Зарисовка сезонности

Задание. Сделайте зарисовку графика сезонности по своему варианту и вставьте в отчёт.

6.1.3. Случайность

Рассмотрим случайную составляющую модели. Мы будем использовать стандартное нормальное распределение со средним значением 0 и дисперсией 1. Обозначение такое: N (0; 1). Латинская буква N означает «нормальное распределение». Числа в скобках — это две характеристики распределения: среднее равно 0, сигма (с.к.о.) равна 1

По «правилу трёх сигм» большинство значений будет находиться в диапазоне «среднее плюс-минус три сигмы». Коэффициент d в нашей модели определяет значение сигмы в каждом варианте. Поэтому диапазон значений получается следующий (рис. 6.11).

Рис. 6.11. Разброс случайности

Задание. Оцените размах значений случайной составляющей и вставьте свои выкладки в отчет.

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

Делаем зарисовку (рис. 6.12) и вставляем в отчёт.

Рис. 6.12. Зарисовка случайной составляющей

Задание. Сделайте зарисовку графика случайной составляющей и вставьте в отчёт.

6.1.4. Сумма компонентов

Мы разобрались с отдельными компонентами и сделали зарисовки их графиков.

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

Как правильно сложить тренд и сезонность? Первое, что приходит в голову — это повернуть лист бумаги и нарисовать синусоиду вокруг линии тренда (левая часть рис. 6.13). Это будет не совсем правильно. Даже совсем неправильно. Подобные упущения встречаются даже в солидных учебниках, например, в книге [3].

Дело в том, что колебания происходят вверх-вниз от наклонной линии тренда (правая часть рис. 6.13). При растущем тренде растущая волна будет более пологая, а падающая часть — более крутая, обрывистая. Именно такую картину мы увидим, когда доберёмся до реальных данных.

Рис. 6.13. Зарисовка T + S

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

Рис. 6.14. Сумма компонентов

Задание. Сделайте зарисовку графика ряда динамики и вставьте в отчёт.

6.2. Мультипликативная модель

Второй вид моделей, который мы рассматриваем в данной работе, — это мультипликативные модели. На рис. 6.15 приведены формулы, по которым мы будем строить нашу мультипликативную модель.

Рис. 6.15. Мультипликативная модель

Задание. Запишите уравнения мультипликативной модели с параметрами своего варианта и вставьте в отчёт.

6.2.1. Тренд

Уравнение и график тренда будет точно таким же, как и для аддитивной модели. Конечно, это мы сделали, чтобы чуть-чуть облегчить работу студентам. Ведь нарисовать график — это сложная интеллектуальная работа. А если нужно вставить график в отчёт, потребуется сосредоточить всё внимание и силы.

В такой ситуации нас утешает только одно: с каждой зарисовкой дела у вас будут идти всё лучше. Потому что после многочисленных тренировок появляется НАВЫК — это способность, доведённая до автоматизма. Это привычные, отработанные действия. И этого можно достичь в любом деле, в любой работе. Тренируйтесь вырабатывать навык в своей работе — и будет вам счастье и довольные клиенты.

Задание. Сделайте зарисовку графика тренда и вставьте в отчёт.

6.2.2. Сезонность

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

Задание. Сделайте зарисовку графика сезонности и вставьте в отчёт.

6.2.3. Случайность

Третий компонент модели ряда динамики — случайная составляющая. Уравнение случайности тоже учитывает тот факт, что компоненты перемножаются. Как и в уравнении сезонных колебаний, случайные значения тоже будут разбросаны не вокруг нуля, а вокруг единицы. Разброс по-прежнему определяется коэффициентом перед нормально распределённой случайной величиной.

Задание. Сделайте зарисовку графика случайности и вставьте в отчёт.

6.2.4. Произведение компонентов

Мультипликативная модель — это произведение трёх компонентов, которые мы построили по отдельности.

Итак, тренд — это постепенное изменение от 7 до 22 (рис. 6.16). Это прямая линия на графике.

Рис. 6.16. Оценка тренда

Задание. Оцените значение уровня на линии тренда в начале и конце диапазона значений по времени и вставьте в отчёт.

Сезонная составляющая меняется в пределах амплитуды колебаний. Для нулевого варианта получаем границы 0,7 и 1,3 (рис. 6.17).

Рис. 6.17. Размах колебаний

Задание. Оцените амплитуду сезонных колебаний и вставьте в отчёт.

Как найти произведение тренда и сезонности? Нужно перемножить соответствующие значения. Рассмотрим крайние точки. Это моменты времени 0 и 50. Умножаем значения уровня ряда 7 и 22 для линии тренда на размах колебаний 0,7 и 1,3 (рис. 6.18). Получаем границы, интервалы значений.

Рис. 6.18. Оценка произведения T * S

Задание. Оцените размах произведения T * S и вставьте в отчёт.

Теперь можно сделать зарисовку графика произведения T * S. Вначале построим границы. Получаем линию тренда и две дополнительные линии — границы значений, размах колебаний вокруг линии тренда «плюс-минус 30%» (рис. 6.19).

Рис. 6.19. Границы колебаний T * S

Задание. Сделайте зарисовку размаха T * S и вставьте в отчёт.

Далее расставим опорные моменты времени для синусоиды. Положения нулей, минимумов и максимумов: 0, 3, 6, 9, 12 и т. д. Остаётся нарисовать колебания по этим опорным точкам. Это уже не так сложно (рис. 6.20).

Как и в предыдущих упражнениях, здесь не требуется особый художественный талант или точный глазомер. Это именно ЗАРИСОВКА. Приблизительный набросок. Неважно насколько кривыми будут линии. Важно понимание того, как должен выглядеть график. Единственное, что потребуется от студента — это начать ДУМАТЬ.

Возможны и другие «препятствия». Это если студент сдал профильную математику, но никогда не пытался её изучать. Обычно это объясняют так: «мы же гуманитарии». Тогда рисование графика синусоиды станет тяжёлым испытанием. Поскольку синус изучается в школе, мы предлагаем читателю освежить в памяти материал или полистать интернет насчёт графика синуса.

Рис. 6.20. Зарисовка произведения T * S

Задание. Сделайте зарисовку формы колебаний T * S и вставьте в отчёт.

Третий компонент — случайность. Произведение тренда и колебаний умножаем на случайный разброс. Этот разброс составит примерно три сигмы. Сигма определяется коэффициентом g. В нулевом варианте g = 0,06. Соответственно, три сигмы составят

3 * 0,06 = 0,18.

Это значит разброс плюс-минус 18% от текущего значения. Чтобы получить отклонение в меньшую сторону, умножим текущее значение на

(1 — 0,18) = 0,82.

Для отклонения в большую сторону — умножим на

(1 +0,18) = 1,18.

Оценим пределы разброса в трёх точках: в начале, в середине и в конце (рис. 6.21).

Рис. 6.21. Случайный разброс

Задание. Оцените случайный разброс для своего варианта и вставьте в отчёт.

На зарисовке мы укажем границы случайного разброса — вверх и вниз от графика T * S (рис. 6.22). Опять же всё делаем приблизительно, схематично.

Рис. 6.22. Размах произведения T * S * E

Задание. Сделайте зарисовку размаха T * S * E и вставьте в отчёт.

7. Имитационное моделирование

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

7.1. Аддитивная модель

Каждую модель мы разместим на отдельном листе.

Задание. Создайте новый лист для аддитивной модели. Разместите на нём параметры задания и формулы для расчётов.

Создадим столбец моментов времени от 0 до 50. Это для нулевого варианта. В других вариантах задания требуется другое количество моментов времени. Вводим заголовок столбца t. Заполняем первое значение 0. Вызываем в верхнем меню заполнение ряда ячеек (рис. 7.1):

Home — Editing — Fill — Series

Рис. 7.1. Заполнение колонки

В диалоговом окне Series настраиваем заполнение столбца нужными числами (рис. 7.2):

Series in — Columns

Type — Linear

Step value — 1

Stop value — 50.

Это арифметическая прогрессия. То есть это ряд чисел от 0 до 50 с постоянным шагом 1.

Рис. 7.2. Прогрессия

Задание. Сформируйте столбец моментов времени в соответствии с вариантом задания.

7.1.1. Тренд

Как и в предыдущих примерах, мы используем нулевой вариант для демонстрации приёмов работы.

Во втором столбце вводим уравнение тренда. Ссылаемся на значения коэффициентов a и b. С помощью клавиши F4 фиксируем адреса ячеек с коэффициентами (рис. 7.3).

Рис. 7.3. Расчёт тренда

Задание. Сформируйте столбец тренда.

Данные для линии тренда готовы. Построим график. Используем так называемую «точечную диаграмму» — как и в предыдущих работах [1, 2] — см. рис. 7.4:

Insert — Charts — Insert Scatter (X, Y) or Bubble Chart — Scatter — Scatter with Straight Lines.

Рис. 7.4. Точечная диаграмма

Появляется «пустое» окно будущего графика. Щёлкаем правой кнопкой по окну графика и выбираем через контекстное меню данные для графика (рис. 7.5):

Select Data — Select Data Source — Add — Edit Series

Указываем название ряда данных:

Series Name.

Озаглавим этот ряд для краткости Т. Это тренд.

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

Выбираем столбец моментов времени для отображения по оси «икс»: Series X Values.

Кстати говоря, диаграмма динамики, то есть график изменения во времени, обычно использует время по горизонтальной оси, а сами значения изучаемого статистического признака — по вертикальной. В этом есть некоторая привычная логика: ведь мы традиционно пишем от руки СЛЕВА НАПРАВО. То есть «прошлое» остаётся слева, а «будущее» находится справа. В других странах пишут по-другому, но графики от времени всё равно строят по тому же самому правилу:

ВРЕМЯ ДВИЖЕТСЯ СЛЕВА НАПРАВО

Выбираем столбец значений для построения линии тренда:

Series Y Values.

Рис. 7.4. Выбор данных для графика

Задание. Постройте график — линию тренда.

Настроим вид линии тренда:

Format Data Series — Series Options — Line — Solid Line

Color — Black

Width — 1.25 pt

Dash type — Long Dash

Получаем график, показанный на рис. 7.5.

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

Задание. Настройте параметры линии тренда.

Добавим заголовок и легенду (рис. 7.6):

Chart Elements — Chart Title

Chart Elements — Legend — Bottom

Чтобы вызвать меню настройки элементов диаграммы, достаточно щёлкнуть по графику и нажать кнопку [+] справа от графика.

Второй вариант — щёлкнуть левой кнопкой по графику и выбрать в верхнем меню:

Design — Add Chart Elements — Chart Title — Above Chart

Design — Add Chart Elements — Legend — Bottom

Рис. 7.6. Заголовок и легенда

Задание. Добавьте заголовок и легенду.

Полученный график можно сравнить с зарисовкой, которую мы уже сделали в предыдущем разделе. Мы видим прямую линию. Значения возрастают от 7 до 22. Всё в порядке.

Задание. Сравните график тренда со своей зарисовкой.

7.1.2. Сезонность

Сформируем столбец для сезонных колебаний (рис. 7.7). Формула и коэффициент нам известны.

Рис. 7.7. Колебания

Задание. Сформируйте столбец для построения графика сезонных колебаний.

Строим график сезонных колебаний в отдельном окне (рис. 7.8). Сравниваем полученный график с нашей зарисовкой. Общая картина должна быть похожа: период колебаний 12, амплитуда колебаний 3.

Рис. 7.8. График колебаний

Задание. Постройте график сезонной составляющей и сравните со своей зарисовкой.

Теперь построим границы размаха колебаний в аддитивной модели. У нас уже есть линия тренда к ней прибавится размах колебаний — амплитуда синусоиды. Она определяется значением коэффициента с в нашем задании (рис. 7.9). Получаем два столбца:

Т — с

Т + с

Рис. 7.9. Размах колебаний

Задание. Постройте столбцы для оценки границ колебаний.

Наносим полученные границы колебаний на график тренда. Настраиваем тип линий, как для линии тренда (рис. 7.10). Можно даже сделать линии чуть потоньше. Сравниваем график с зарисовкой.

Рис. 7.10. Границы размаха колебаний

Задание. Постройте границы колебаний. Сравните полученный график с зарисовкой.

Добавляем колебания к линии тренда — в новой колонке таблицы. Наносим линию на график. Настраиваем тип и цвет линии (рис. 7.11). Колебания вокруг линии тренда должны иметь постоянную амплитуду. Сравниваем с зарисовкой.

Рис. 7.11. Сумма тренда и колебаний

Задание. Постройте график колебаний вокруг тренда. Сравните с зарисовкой.

7.1.3. Случайность

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

Вызываем надстройку «Анализ данных»:

Data — Analysis — Data Analysis — Random Number Generation.

Настраиваем генератор случайных чисел (рис. 7.12).

Число случайных переменных (столбцов в таблице):

Number of Variables — 1

Количество случайных чисел (для моментов времени от 0 до 50):

Number of Random Numbers — 51

Выбираем стандартное нормальное распределение:

Distribution — Normal

Parameters:

Mean = 0

Standard deviation = 1

Начальное состояние генератора (любые четыре цифры):

Random Seed — 1234

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

Output options:

Output Range

Рис. 7.12. Настройки генератора

Задание. Сгенерируйте столбец случайных чисел со стандартным нормальным распределением.

Чтобы получить случайную составляющую с нужной сигмой, умножим случайные числа на наш коэффициент d. Результаты разместим в колонке E. Строим график случайной составляющей — на отдельном графике (рис. 7.13). Общая форма графика должна соответствовать нашей зарисовке — размах приблизительно плюс-минус три сигмы.

Рис. 7.13. Случайная составляющая

Задание. Сформируйте столбец значений случайной составляющей. Постройте график. Сравните с зарисовкой.

7.1.4. Сумма компонентов

Для начала оценим размах случайного разброса в модели.

Мы уже оценивали границы случайного разброса по правилу трёх сигм. Сигма определяется коэффициентом d в задании.

Построим два вспомогательных столбца:

T + S — 3d

T + S +3d

Наносим линии на график. Настраиваем цвет и тип линий (рис. 7.14). Сравниваем с зарисовкой.

Рис. 7.14. Границы случайного разброса

Задание. Сформируйте столбцы для оценки границ случайного разброса. Нанесите линии на график. Сравните с зарисовкой.

Наконец мы складываем все три компонента и размещаем их в новой колонке y. Наносим наш ряд динамики на график. Настраиваем цвет и тип линий (рис. 7.15). Внешний вид должен соответствовать зарисовке.

Рис. 7.15. Сумма трёх компонентов

Задание. Сложите три компонента и нанесите линию на график. Сравните результат с зарисовкой.

Поздравляем! Ряд динамики, сгенерированный по аддитивной модели, готов. Как видим, практически все значения после наложения случайного разброса находятся внутри наших границ «плюс-минус три сигмы».

7.2. Мультипликативная модель

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

Мультипликативную модель разместим на новом листе.

Размещаем на этом листе параметры модели и уравнения модели.

Задание. Создайте новый лист и укажите заголовок. На вкладке укажите только номер листа, а заголовок сделайте на самом листе, в его верхней части.

7.2.1. Тренд

Построим столбцы значений для моментов времени и для линии тренда. Строим график. Сравниваем с зарисовкой. Всё в точности, как в предыдущей модели.

Задание. Рассчитайте значения для линии тренда. Постройте график и сравните с зарисовкой.

7.2.2. Сезонность

Сезонная составляющая в данном случае — колебания вокруг единичного уровня. Период колебаний равен 12. Строим отдельный график.

Задание. Рассчитайте значения сезонных колебаний. Постройте график. Сравните с зарисовкой.

7.2.3. Случайность

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

Задание. Рассчитайте значения случайно составляющей. Постройте график. Сравните с зарисовкой.

7.2.4. Произведение компонентов

Повторяем действия из предыдущего раздела, где мы поработали с аддитивной моделью. Конечно, действуем с поправкой на произведение (рис. 7.16).

Нас интересуют следующие графики:

— размах сезонных колебаний вокруг линии тренда;

— произведение тренда и колебаний;

— размах случайного разброса вокруг произведения тренда и колебаний;

— произведение тренда, сезонности и случайности.

Все они в целом должны соответствовать нашим предварительным зарисовкам.

Рис. 7.16. Мультипликативная модель

Задание. Сделайте необходимые расчёты и постройте графики размаха сезонности вокруг тренда, произведения T * S, границ случайного разброса вокруг T * S и окончательного графика ряда динамики. Сравните с зарисовками.

8. Скользящие средние

Данные сгенерированы, можно переходить к анализу рядов динамики. Вначале мы рассмотрим скользящие средние. Другое название — механическое сглаживание. На входе имеем ряд динамики, на выходе — тоже ряд динамики, но более «гладкий».

Скользящая средняя — это средняя, которая «скользит» по времени. Для каждого момента времени вычисляется среднее значение за последние несколько дней. Данные, которые попадают в расчёт среднего значения — это период сглаживания. Неважно, какой длины исходный ряд. Важно, какой период сглаживания мы выберем. Формулы будут чуть позже.

Такой метод обработки данных также называют «механическим сглаживанием».

Эту обработку можно сравнить бельём, которое гладят утюгом. Размер утюга определяет результат. Маленький утюжок нужен для разглаживания мелких складок.

Ещё одна аналогия — это обработка поверхности доски с помощью рубанка. Короткий рубанок — для коротких досок. Длинный рубанок — для длинных досок.

Задание. Дайте определению выражению СКОЛЬЗЯЩАЯ СРЕДНЯЯ.

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

Рис. 8.1. Вставка значений

Задание. Создайте новый лист и скопируйте исходные данные.

Удалим вспомогательные колонки. Оставим только колонки времени, отдельных компонентов и их суммы (рис. 8.2).

Рис. 8.2. Основные столбцы

Задание. Удалите лишние столбцы.

8.1. Встроенное сглаживание

Самое простое сглаживание доступно на самом графике. Это встроенный инструмент.

Построим график нашего ряда динамики. Используем точечную диаграмму с прямыми отрезками и без маркеров отдельных точек (рис. 8.3). С этим видом графиков мы уже познакомились в предыдущих разделах.

Рис. 8.3. Ряд динамики

Задание. Постройте диаграмму динамики.

Проведём сглаживание с помощью встроенной скользящей средней. Щёлкаем левой кнопкой по графику и нажимаем кнопку [+] Chart Elements (рис. 8.4). Нажимаем на стрелку рядом с пунктом Trendline и выбираем More Options.

Рис. 8.4. Элементы диаграммы

В правой части окна Excel появляется окно Format Trendline (рис. 8.5). Выбираем пункт Moving Average. На графике появляется пунктирная сглаженная линия.

Рис. 8.5. Встроенная скользящая средняя

Задание. Добавьте встроенную скользящую среднюю на график.

Главный параметр скользящей средней — это период сглаживания (усреднения). Выбирается он в меню в пункте Period.

Короткий период сглаживания позволяет удалить случайную составляющую. Большой период позволяет удалить как случайность, так и сезонность. Остаётся только линия тренда (рис. 8.6).

Рис. 8.6. Удаление сезонности и случайности

Задание. Подберите период сглаживания для удаления случайности.

Задание. Сделайте копию графика и подберите период для удаления случайности и сезонности.

Задание. Включите легенду на обоих графиках.

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

Сами сглаженные данные присутствуют только на графике. Числа посмотреть или использовать не получится.

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

8.2. Надстройка

Построим скользящие средние с помощью надстройки «Анализ данных». Вызываем в верхнем меню

Data — Analysis — Data Analysis — Moving Average

Настраиваем параметры сглаживания (рис. 8.7).

Диапазон ячеек исходного ряда динамики:

Input — Input Range

Интервал сглаживания, который мы уже подобрали в предыдущем разделе:

Interval — 4

Адрес ячейки, начиная с которой будут выводиться сглаженные значения:

Output Options — Output Range

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

MA 4

Это означает «скользящее среднее с периодом 4 отсчёта по времени» (4 periods Moving Average).

Рис. 8.7. Параметры сглаживания

Задание. Вызовите надстройку «Анализ данных» и постройте две скользящие средние с выбранными ранее параметрами: 1) для удаления случайности и 2) для удаления случайности и сезонности. Укажите период сглаживания в заголовках столбцов.

Рассмотрим результаты сглаживания (рис. 8.8). Мы выбрали в этом примере скользящую среднюю по 4 точкам (периодам или моментам времени). После вызова надстройки в ячейках таблицы появились не только значения. Можно щёлкнуть по любой ячейке и обнаружить формулы.

Например, значение в ячейке G7 определяется по формуле

AVERAGE (E4:E7)

Рис. 8.8. Результаты сглаживания

Другими словами, первое сглаженное значение — это средняя арифметическая из первых четырёх уровней исходного ряда (рис. 8.9).

Рис. 8.9. Формулы сглаживания

Получается, что значение скользящей средней привязано к концу интервала сглаживания. Например, среднее на интервале из первых четырёх значений (t=0…3) приписано к моменту t=3. То же самое происходит и при сглаживании встроенными средствами графика. Кстати, чуть позже мы увидим, как этот подход работает в биржевой торговле.

Задание. Рассмотрите результаты сглаживания для обеих скользящих средних. Обратите внимание на формулы в ячейках. Подсчитайте, сколько значений пропадает после сглаживания. Как это связано с периодом сглаживания?

Наносим сглаженный ряд на наш график (рис. 8.10). Поскольку первые три значения пропадают, мы указываем ряд значений покороче — время меняется от 3 до 50. Можно видеть, что результаты встроенного сглаживания графика и сглаживания через надстройку совпадают. Теперь становится понятно, по каким формулам это было сделано.

Рис. 8.10. Сравнение средних

Задание. Нанесите обе скользящие средние на соответствующие графики. Сравните результаты.

8.3. Функции

Готовые функции Excel добавляют нам немного гибкости при сглаживании.

Как мы уже видели в предыдущем разделе, для сглаживания можно использовать функцию нахождения среднего значения:

AVERAGE (range_y)

СРЗНАЧ (интервал_y)

Это средняя арифметическая простая.

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

Рассмотрим пример MA 3 (рис. 8.11). В этом случае пропадают первые и последние значения сглаженного ряда.

Рис. 8.11. Сглаживание с привязкой по центру

Вводим функцию AVERAGE (E4:E6) в ячейку для момента времени t=1. Интервал адресов E4:E6 включает три ячейки (рис. 8.12). Копируем выражение по всему столбцу двойным щелчком по маркеру автозаполнения. Затем вручную удаляем последнюю ячейку, ведь у нас недостаточно данных для вычисления последнего сглаженного значения при t=50.

Рис. 8.12. Сглаживание с помощью функции

Задание. Проведите сглаживание ряда динамики с помощью надстройки. Используйте оба значения интервала сглаживания — короткий для удаления случайности и длинный для удаления сезонности.

Наносим сглаженный ряд на график. Естественно, мы выбираем моменты времени t = 1 … 49. Помним, что первое и последнее значения сглаженного ряда отсутствуют.

Для сравнения включаем встроенное сглаживание с таким же периодом в 3 точки по времени (рис. 8.13). Можно видеть, что привязка к середине интервала сглаживания располагает скользящую среднюю без смещения.

Рис. 8.13. Сравнение скользящих средних

Задание. Постройте графики. Сравните результаты сглаживания.

Теперь сравним результаты сглаживания с нашей исходной моделью. С помощью скользящей средней мы отфильтровали случайную составляющую. Должны были остаться только тренд и сезонные колебания. Наложим на наш график линию суммы T + S. Отключим встроенное сглаживание. Графики выглядят очень похоже (рис. 8.14). Значит, нам действительно удалось отфильтровать случайность. Конечно, результат фильтрации не совсем совпадает с исходным рядом T+S. Но в целом периодические изменения просматриваются очень хорошо.

Рис. 8.14. Фильтрация случайности

Задание. Наложите графики Y, T+S и MA. Убедитесь, что случайность отфильтрована.

Переходим ко второму графику. Проводим фильтрацию с периодом сглаживания 12, чтобы оставить только тренд. У нас пропадает 5 значений в начале ряда (рис. 8.15). Середина интервала от 0 до 11 должна соответствовать моменту времени t = 5,5. То есть ровно между 5 и 6. Такой строки у нас пока нет, но мы её легко можем сформировать:

начальное значение =5,5

шаг = 1

конечное значение = 44,5

Рис. 8.15. Первое сглаженное значение

В конце ряда пропадает 6 значений (рис. 8.16). Дальше просто недостаточно данных для нахождения среднего.

Рис. 8.16. Последнее сглаженное значение

Задание. Проведите сглаживание с периодом 12. Сформируйте вспомогательный столбец времени.

Сравним результаты сглаживания встроенными средствами графика и с помощью надстройки. Нанесём сглаженный ряд на график (рис. 8.17). Можно видеть, что сдвиг встроенной скользящей средней достаточно заметный. А вот наша линия, построенная «вручную», проходит «посередине».

Рис. 8.17. Сравнение результатов сглаживания

Задание. Постройте графики и сравните результаты сглаживания с периодом усреднения 12.

Фильтрация с периодом сглаживания 12 должна оставить только тренд. Сравним результаты сглаживания с исходной линией тренда. Нанесём на график линию тренда, исходный ряд динамики и сглаженный ряд (рис. 8.18). Результаты близки. Конечно, сезонные колебания и случайность даже после фильтрации немного исказили прямую линию. Но в целом всё очень хорошо.

Рис. 8.18. Отфильтрованный тренд

Задание. Постройте график исходного ряда, тренда и скользящей средней. Сравните результаты фильтрации с линией тренда.

При сглаживании можно использовать скользящую медиану — вместо скользящей средней. В Excel имеется готовая функция

MEDIAN (range)

МЕДИАНА (диапазон)

Результаты фильтрации с помощью скользящей медианы очень похожи на сглаживание с помощью скользящей средней. Небольшой интервал сглаживания устраняет случайную составляющую (рис. 8.19).

Рис. 8.19. Фильтрация случайности

Задание. Отфильтруйте случайную составляющую с помощью скользящей медианы. Постройте график и сравните со скользящей средней и исходными рядами T+S+E и T+S.

Чтобы выделить тренд и удалить случайность и колебания, используем интервал сглаживания, равный периоду колебаний. То есть 12 моментов времени. Наносим результаты на график. Снова используем вспомогательный столбец времени, чтоб отнести сглаженный ряд к точной середине интервала. Результаты достаточно близки к сглаживанию с помощью скользящей средней (рис. 8.20). Конечно, скользящая медиана не такая гладкая, но можно попробовать кратно увеличить интервал сглаживания.

Рис. 8.20. Отфильтрованный тренд

Задание. Выделите тренд с помощью скользящей медианы. Постройте график и сравните со скользящей средней и исходными рядами T+S+E и T.

Задание. Выполните предыдущее задание, используя период сглаживания 24 значения.

8.4. Формулы

Сглаживание можно провести с помощью формул, где все действия детально прописываются «вручную».

8.4.1. SMA

Для начала возьмём скользящую среднюю. Для вычисления каждого значения нужно сложить несколько чисел и поделить сумму на количество слагаемых. Это формула средней арифметической простой. Построим скользящую среднюю по трём точкам (рис. 8.21).

Рис. 8.21. Скользящая средняя по формуле

Озаглавим столбец SMA 3. Это означает 3 point Simple Moving Average. То есть простая скользящая средняя, построенная по трём отсчётам. Как и ранее, первое и последнее значения пропадают из-за нехватки данных для расчётов.

Задание. Постройте скользящие средние с помощью формул. Нанесите линии на график и сравните с предыдущими результатами.

8.4.2. EMA

Ещё один вид сглаживания — это экспоненциально взвешенная скользящая средняя. Обозначение: EMA. Расшифровывается как Exponential Moving Average. Расчёты проводятся по следующим формулам (рис. 8.22). В названии говорится что-то про экспоненту. Внутри расчётов действительно скрывается экспонента. Подробнее внутреннее устройство EMA обсуждается в курсе эконометрики. Пока просто примем к сведению — есть такое название у методики сглаживания. И эта самая EMA очень популярна в биржевой торговле. Нас будет интересовать, как она работает и что она даёт после сглаживания.

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

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

Рис. 8.22. Формулы экспоненциального сглаживания

Задание. Запишите формулы расчёта EMA и вставьте в отчёт.

Экспоненциальное скользящее среднее всегда отстаёт от исходного ряда. Это соответствует привязке к концу интервала сглаживания. Такой подход используют в биржевой торговле. Сглаженная линия при растущем тренде выступает в роли ЛИНИИ ПОДДЕРЖКИ (рис. 8.23). Английское название SUPPORT. Цена опускается до линии поддержки и отскакивает от неё вверх. Таким образом, линия поддержки как бы «поддерживает» цену и не даёт ей упасть. Всё это делается очень приблизительно. Цена может иногда слегка заходить за линию поддержки. Главное, чтобы она отскакивала.

Рис. 8.23. Линия поддержки

Задание. Дайте определение выражению ЛИНИЯ ПОДДЕРЖКИ.

Аналогично, при падающем тренде EMA отстаёт от цены. Но теперь она отстаёт так, цена оказывается ниже этой сглаженной линии. Теперь это линия сопротивления. Английское название RESISTANCE. Цена поднимается до линии сопротивления и отскакивает от неё вниз (рис. 8.24). Линия сопротивления как будто сопротивляется дальнейшему росту цены и не пускает её вверх.

Рис. 8.24. Линия сопротивления

Задание. Дайте определение выражению ЛИНИЯ СОПРОТИВЛЕНИЯ.

Проведём экспоненциальное сглаживание. Подберём такой период сглаживания, чтобы получить линию поддержки на графике (рис. 8.25). Для удобства введём формулы для пересчёта периода в коэффициент альфа. А сами формулы сглаживания пускай ссылаются на наши коэффициенты. Таким образом, можно менять значение n в одной ячейке таблицы, а столбец сглаженных значений и график будут пересчитываться автоматически.

Рис. 8.25. Экспоненциальное сглаживание

Задание. Проведите экспоненциальное сглаживание и нанесите EMA на график T+S+E. Подберите такой период сглаживания, чтобы сглаженная линия стала линией поддержки для графика.

8.5. Мультипликативная модель

Мы рассмотрели основные методы сглаживания на примере аддитивной модели. Теперь пришло время для мультипликативной модели.

Данные уже сгенерированы. Остаётся скопировать их на новый лист и проделать все рассмотренные выше манипуляции.

Задание. Скопируйте данные мультипликативной модели на новый лист. Проведите сглаживание этого ряда динамики всеми методами, описанными выше в разделе 8.

9. Тренд

Мы рассмотрели в разделе 8 различные методы МЕХАНИЧЕСКОГО ВЫРАВНИВАНИЯ рядов динамики, или механического сглаживания. Это различные вариации на тему скользящей средней. Слово «механическое» намекает, что с нашими данными производят механические действия, похожие на механическое движение из курса физики.

Задание. Дайте определение выражению МЕХАНИЧЕСКОЕ ВЫРАВНИВАНИЕ.

В данном разделе мы переходим к так называемому АНАЛИТИЧЕСКОМУ ВЫРАВНИВАНИЮ. Это получение уравнения тренда и соответствующей линии тренда на графике. Слово «аналитическое» намекает на методы математического анализа функций. Студенты для краткости называют этот предмет «матан». Когда мы используем уравнения, обычно говорят про аналитические методы. Так что здесь должно появиться уравнение. Уравнение тренда.

Задание. Дайте определение выражению АНАЛИТИЧЕСКОЕ ВЫРАВНИВАНИЕ.

На самом деле, мы уже рассмотрели все возможные способы построения уравнения тренда. Это произошло в предыдущей работе, где мы изучали взаимосвязь явлений [2]. И называлось это РЕГРЕССИОННЫЙ АНАЛИЗ. Напомним, что это такое. Регрессия (в статистике) — это построение уравнения связи В СРЕДНЕМ. Исходные данные — это точки, разбросанные вокруг линии (или многомерной поверхности). Линия может быть прямой или кривой. Коэффициенты уравнения подбирают так, чтобы линия проходила ближе к исходным точкам. То есть чтобы разброс точек вокруг этой линии был меньше, чем для любой другой линии.

Задание. Дайте определение выражению РЕГРЕССИОННЫЙ АНАЛИЗ.

В качестве инструмента для регрессионного анализа часто используют МЕТОД НАИМЕНЬШИХ КВАДРАТОВ (рис. 9.1). В этом случае находят такие значения коэффициентов в уравнении связи, чтобы получить наименьшую сумму квадратов расстояний между будущей линией и исходными данными (точками). Причём расстояние измеряется по вертикали. То есть берётся разность соответствующих «игреков». И никаких перпендикуляров — это слишком сложно для вычислений.

Рис. 9.1. Регрессионный анализ

Задание. Объясните, о каких КВАДРАТАХ идёт речь в методе наименьших квадратов?

При построении уравнения тренда тоже часто используется метод наименьших квадратов — сокращённо МНК. Единственное отличие: вместо «иксов» здесь участвуют моменты времени, причём расположенные по возрастанию (рис. 9.2). В остальном всё то же самое. Подойдут любые средства регрессионного анализа Excel:

— встроенные инструменты анализа графика

— надстройка «Анализ данных»

— функции

— формулы.

Мы рассмотрим их все, но уже не так подробно, как предыдущей работе. Если будет трудно, вернитесь к пособию [2] и перечитайте его.

Вначале мы пройдёмся по аддитивной модели. Потом повторим всю работу для мультипликативной модели.

Рис. 9.2. Уравнение тренда

Задание. Объясните, как применяется МНК при построении уравнения тренда.

9.1. Встроенный тренд

Первый и самый простой инструмент построения тренда встроен в графики.

Строим график исходного ряда динамики (аддитивная модель):

Y = T + S + E

Нажимаем кнопку [+] Chart Elements и выбираем Trendline (рис. 9.3). Это означает «линия тренда». Настраиваем цвет и тип линий. Включаем легенду под графиком: Legend — Bottom.

Рис. 9.3. «Встроенный» тренд

Задание. На новом листе постройте график исходного ряда динамики и включите линию тренда. Настройте оформление графика.

Мы выбираем масштаб по осям так, чтобы всё поле графика было занято нашими данными и линиями. Поэтому не обязательно всё начинается от нуля. Мы настроили масштаб, но теперь на оси «игреков» появились какие-то «некрасивые» числа.

Дважды щёлкнем по оси «игрек». В боковом меню Format Axis выбираем

Axis Options — Text Options — Units — Major — 5.0

Теперь наши метки идут с шагом 5 единиц. «Красивые» круглые числа (рис. 9.4).

Рис. 9.4. Настройка масштабных меток

Задание. Настройте шаг для масштабных меток — при необходимости.

У нас есть линия тренда. Теперь получим уравнение тренда. Щёлкаем по кнопке [+] Chart Elements и выбираем Trendline — More Options. Ставим галочку в пункте Display Equation on chart. На графике появилось уравнение (рис. 9.5).

Рис. 9.5. Включение уравнения тренда

Перетаскиваем уравнение мышкой на свободное место, и всё готово (рис. 9.6). Сравниваем с уравнением тренда по заданию:

T = 7 +0,3 t

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

Рис. 9.6. Уравнение тренда

Задание. Включите отображение уравнения тренда на графике. Расположите уравнение на свободном месте. Сравните полученное уравнение с параметрами задания.

9.2. Надстройка

Следующий способ получить уравнение тренда — это надстройка. Вызываем надстройку «Анализ данных»:

Data — Analysis — Data Analysis — Regression

Настраиваем параметры регрессионного анализа (рис. 9.7):

Исходные данные (вначале указываем «игреки»):

Input — Input Y Range

Input — Input X Range

Адрес для вывода результатов анализа — первая ячейка диапазона

Output options — Output Range

Это адрес первой ячейки в левом верхнем углу будущей таблички. Табличка будет довольно большой, так что лучше расположить её на свободном месте.

Рис. 9.7. Параметры регрессионного анализа

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

В полученной таблице нас будут интересовать только значения коэффициентов (рис. 9.8). По ним мы запишем уравнение тренда — средствами Excel.

Свободный член уравнения назван Intercept. То есть пересечение с осью «игреков».

Коэффициент регрессии (коэффициент при переменной t) — X Variable.

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

Рис. 9.8. Уравнение регрессии

Задание. Найдите значения коэффициентов уравнения в таблице результатов анализа и составьте уравнение тренда. Сравните с предыдущими результатами.

9.3. Функция LINEST

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

LINEST (Y, X)

ЛИНЕЙН (Y, X)

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

Обратим внимание, что здесь тоже вначале указывают «игреки», а затем «иксы» (в нашем случае столбец моментов времени t) — см. рис. 9.9.

Функция LINEST выдаёт результаты в виде массива ячеек. Так что вызов функции проводится в несколько этапов:

1) вводим вызов функции в одну ячейку таблицы, например

=LINEST (E4:E54,A4:A54)

2) выделяем диапазон, включающий две ячейки — начиная с той ячейки, куда мы уже ввели формулу, например I3:J3

3) нажимаем функциональную клавишу F2

4) нажимаем комбинацию клавиш Ctrl + Shift + Enter

Рис. 9.9. Параметры функции LINEST

На рис. 9.10 схематично показаны четыре шага по вызову функции массива. Если все шаги выполнены правильно, в результате мы получим два числа. Это оценки коэффициентов уравнения тренда. Формулы в обеих ячейках будут выглядеть одинаково. Вокруг формул появятся фигурные скобки. Это указывает, что здесь появилась формула массива.

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

Сравниваем полученные коэффициенты. Значения совпадают.

Рис. 9.10. Функция массива

Задание. Вызовите LINEST как функцию массива.

9.4. Линия тренда

Мы получили уравнение тренда несколькими способами.

Следующий шаг — построить график.

В нашем примере мы построили линейное уравнение. Это линейный тренд. Прямая линия.

Чтобы провести прямую линию, достаточно взять всего две точки. Мы выберем два крайних значения времени и найдём соответствующие значения уровней ряда по нашему уравнению (рис. 9.11). Запишем уравнение тренда, в котором округлим коэффициенты до трёх значащих разрядов. Оценим уровни ряда.

Рис. 9.11. Точки для линии тренда

Задание. Оцените «вручную» две точки для построения линии тренда.

Теперь найдём «точные» значения. Для этого проведём расчёты в Excel и используем ссылки на найденные значения коэффициентов (рис. 9.12). Сравниваем полученные значения с результатами ручных расчётов. Числа очень похожи.

Мы взяли слово «точные» в кавычки. На самом деле, достаточно смоделировать другой набор исходных данных, и получится другое уравнение тренда. Немного другой. В регрессионных оценках всегда скрыта случайная ошибка. Поэтому высокая точность расчётов (например, 20 знаков после запятой) не даёт очень точных результатов. Вот такой парадокс.

Рис. 9.12. Точки для линии тренда

Задание. Найдите «точные» значения для построения линии тренда. Сравните с предыдущими оценками.

Построим график исходного ряда и исходного тренда. Затем нанесём нашу линию тренда. Сравним результаты. Экспериментально найденная линия тренда практически совпадает с нашими исходными данными (рис. 9.13).

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

Задание. Нанесите линию тренда на график и сравните с исходными графиками.

9.5. Мультипликативная модель

Мы построили уравнение тренда несколькими способами. Затем посмотрели на эту линию на графике. Если правильно выполнить все действия, экспериментальная линия тренда пройдёт очень близко к «теоретической» линии — в соответствии с вариантом задания.

Мы поработали с адаптивной моделью. Пришло время для второй модели — мультипликативной.

Задание. Постройте уравнение и линию тренда для мультипликативной модели всеми описанными выше способами.

10. Биржевые данные

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

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

В данной работе мы остановимся только на статистических методах анализа динамики. Как их использовать в биржевой торговле — это тема для отдельного занятия.

Задание. Прочитайте в Википедии статью «биржа». Выясните, что такое биржа и каково происхождение этого названия.

Биржи и соответствующие методы анализа биржевых цен появились гораздо раньше современных компьютеров. В то время данные об итогах биржевых торгов за день публиковались в газетах — целые страницы цифр. Чтобы «увидеть» цены, пришлось рисовать графики. А на графиках обнаружились интересные закономерности, которые описывает «Теория Доу». Вот примеры этих открытий:

1) движение цен подвержено тенденциям;

2) текущая тенденция продолжается, пока не закончится.

Задание. Прочитайте в Википедии статью «Теория Доу». Выясните, какое определение тренда предложил Чарльз Доу. Сделайте зарисовку, поясняющую смысл этого определения.

10.1. Свечи и бары

Для наглядного изображения и анализа биржевых цен были придуманы особые биржевые графики. Это так называемые «японские свечи», «бары» (штриховые графики) и многие другие инструменты.

Задание. Прочитайте в Википедии статью «Японские свечи». Выясните, для какой биржи (для какого товара) были разработаны графики в виде японских свечей.

Японские свечи — это условное, упрощённое, сжатое изображение всех цен сделок за торговый период — например, за день или за час (рис. 10.1). Каждая свеча изображает четыре значения цены:

— OPEN — цена открытия (значение цены первой сделки, цена начала торгового периода);

— HIGH — максимальная цена за период;

— LOW — минимальная цена за период;

— CLOSE — цена закрытия (на конец интервала времени).

Цена может достигать одного и того же максимума несколько раз в течение торгового периода. Изображение свечи фиксирует это максимальное значение.

Если цены растут, то цена закрытия будет выше, чем цена открытия. В этом случае прямоугольник (тело свечи) белого цвета. Это растущая свеча. Если цены падают, тело свечи чёрное.

Второй вариант биржевых графиков — это так называемые «бары», или штриховые графики. Английское слово BAR в данном случае означает «чёрточка, штрих». Бары тоже изображают четыре значения цен (рис. 10.1). В некоторых случаях цену открытия не показывают. Тогда каждый бар передаёт только три значения цены.

Рис. 10.1. Свечи и бары

Задание. Дайте определения терминам СВЕЧА и БАР.

Задание. Прочитайте в Википедии статью «Промышленный индекс Доу — Джонса». Выясните, когда появился первый вариант индекса.

10.2. Исторические данные

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

Мы рассмотрим котировки акций крупных акционерных компаний. Их называют «голубыми фишками» — по аналогии с дорогими фишками в казино.

Задание. Прочитайте в Википедии статью «Голубые фишки». Выясните, какие компании входят в состав Индекса голубых фишек Московской биржи.

Список крупнейших компаний время от времени пересматривается. Разные варианты списка голубых фишек (базу расчёта) можно найти на сайте Московской биржи.

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

В предыдущих работах [1, 2] мы уже загружали исторические данные о биржевых котировках. В данной работе будем использовать для получения данных сайт компании Финам. Желающие могут использовать любые другие источники.

Открываем в браузере страницу компании

https://www.finam.ru/

Переходим по ссылкам:

Теханализ — Экспорт котировок.

Выбираем список МосБиржа акции.

Выбираем инструмент — название акции. В качестве примера загрузим дневные котировки банка ВТБ (рис. 10.2). Слово «дневные» означает, что каждая строка в таблице с данными (цены открытия, закрытия и т.д.) будет соответствовать одному торговому дню. Устанавливаем необходимые настройки и нажимаем кнопку «Получить файл».

Рис. 10.2. Загрузка данных

Задание. Загрузите дневные данные по выбранному инструменту за последние 5 лет. Сохраните файл в своём рабочем каталоге.

Рассмотрим полученный файл. Откроем его в приложении «Блокнот» (рис. 10.3). Первая строка содержит заголовки. Значения полей разделяются точкой с запятой. Первым указан «тикер» — это условное сокращённое название финансового инструмента. В нашем примере VTBR — это обыкновенные акции ВТБ (ВТБ ао). Далее указана периодичность: D означает «день» — дневные данные, данные за один торговый день. Дата указана в формате ГГГГММДД (год-месяц-день). Время заполнено нулями, мы его не будем использовать. Далее идут четыре значения цены. Последнее значение — объём торгов. Это количество акций, которые сменили своих владельцев за рассматриваемый торговый период.

Рис. 10.3. Исторические данные

Задание. Откройте загруженный файл в Блокноте и ознакомьтесь с форматом представления данных.

Загрузим файл в Excel (рис. 10.4):

File — Open — Browse — Text Files — Open

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

*.PRN

*.TXT

*.CSV

В данной работе нас устроит формат *.TXT.

Рис. 10.4. Открытие файла

Появляется окно мастера загрузки файлов в Excel (рис. 10.5).

На первом шаге указываем следующие настройки.

Файл с разделителями полей:

Original data type — Delimited

Наличие заголовков в первой строке файла:

My data has headers

Рис. 10.5. Шаг 1 импорта

На втором шаге импорта указываем символ-разделитель полей: точку с запятой (рис. 10.6):

Delimiters —Semicolon

В окне предварительного просмотра Data preview можно наблюдать разделение текста на столбцы.

Рис. 10.6. Шаг 2 импорта

На третьем шаге импорта выбираем колонку даты и указываем формат (рис. 10.7):

Column data format — Date — YMD

Рис. 10.7. Шаг 3 импорта

Дополнительно настроим разделитель целой и дробной части (рис. 10.8):

Advanced — Settings used to recognize numeric data — Decimal separator —.

В английской и русской версиях Excel используются разные разделители, так что за этой настройкой приходится следить.

Рис. 10.8. Дополнительные настройки

Нажимаем кнопку Finish и загружаем данные (рис. 10.9). Даты и числовые значения были правильно распознаны.

В выходные и праздничные дни торги на бирже не проводятся. Судя по загруженным данным, торги в январе 2016 года начались с 5 числа.

Рис. 10.9. Результаты импорта

Задание. Загрузите данные в Excel, сделав необходимые настройки.

Удаляем неинформативные столбцы:

— тикер

— период

— время

Название акций укажем в заголовке.

Формат даты выберем более привычный. Для этого выделяем столбец дат и нажимаем правую кнопку мыши. В контекстном меню выбираем Format Cells (рис. 10.10):

Format Cells — Number — Category — Date — Locale — Russian — Type — 14-мар-2012

Рис. 10.10. Формат даты

После настройки формата получаем следующее представление (рис. 10.11). При этом содержимое ячеек в строке формул отображается в первоначальном формате.

Рис. 10.11. Российский формат даты

Данные после импорта расположены в отдельной рабочей книге. Вставим текущий лист в наш отчёт.

Щёлкнем правой кнопкой мыши по вкладке листа и выберем в контекстном меню Move or Copy (рис 10.12). В диалоговом окне Move or Copy выбираем файл, в который нужно вставить текущий лист:

Move selected sheets — To book — (move to end)

Если при импорте в Excel мы не ещё сохранили загруженные данные, то отдельное окно просто исчезнет. Всё будет собрано в одной рабочей книге, где мы ведём свой отчёт.

Рис. 10.12. Копирование листа

Задание. Скопируйте лист с историческими данными в отчёт.

10.3. Биржевые графики

Данные загружены. Попробуем построить биржевой график баров.

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

Insert — Charts — Insert Waterfall, Funnel, Stock, Surface or Radar Chart — Stock — High-Low-Close

Получаем сообщение об ошибке. Нам намекают на правильный порядок следования столбцов цен (рис. 10.13).

Рис. 10.13. Порядок столбцов

Получается, что для графика баров требуется только три столбца. Другими словами, в данном пакете программ не учитывается цена открытия в графике баров.

Выделяем три указанных колонки и ещё раз вызываем построение графика. На этот раз успешно (рис. 10.14). Указываем заголовок. Отключаем легенду.

Рис. 10.14. График баров

По горизонтальной оси указаны порядковые номера точек. Добавим метки времени:

Select Data — Select Data Source — Horizontal (Category) Axis Labels — Edit

В диалоговом окне Axis Labels выбираем диапазон ячеек, в которых расположены даты: Axis label range.

Получаем метки с указанием дат (рис. 10.15).

Рис. 10.15. Бары и даты

Задание. Постройте график баров.

Рассмотрим график поподробнее. Настроим масштаб по времени так, чтобы оставить только один месяц (рис. 10.16).

Format Axis — Axis Options — Axis Type — Date axis — Bounds — Maximum — 2/28/2015

Обратим внимание, что дата снова указана в оригинальном (американском) формате: месяц / день / год.

Рис. 10.16. Диапазон дат

Выбираем масштаб по вертикальной оси (рис. 10.17). Предельные значения по оси должны позволить заполнить всё окно нашим графиком:

Format Axis — Axis Options — Bounds — Minimum / Maximum

После настройки масштаба стало видно отдельные бары.

При этом проявились последствия указания дат. Теперь появились разрывы на время выходных. Бары собраны группами по пять рабочих дней.

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

Рис. 10.17. Масштаб по вертикали

Задание. Настройте масштаб по осям. Рассмотрите график.

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

Переключим настройку горизонтальной оси на текстовые метки:

Format Axis — Axis Options — Axis Type — Text Axis

Теперь разрывы исчезли. Бары идут равномерно, один за другим. Метки по оси по-прежнему указывают даты. Но с точки зрения графика это просто строчки текста.

Рис. 10.18. Текстовые метки

10.4. Скользящие средние

На последнем варианте графика можно провести сглаживание.

Возвращаем весь диапазон данных на график:

Format Axis — Axis Options — Axis Type — Date Axis — Bounds — Reset

Устанавливаем текстовые метки по оси:

Format Axis — Axis Options — Axis Type — Text Axis

Добавим в качестве элемента диаграммы скользящую среднюю:

Chart Elements — Trendline — More Options

Поскольку на графике использованы три колонки цифр, нам предлагают указать, по какому ряду будем проводить сглаживание (рис. 10.19). В литературе рекомендуют использовать для анализа цену закрытия. Поэтому выбираем Series3.

Рис. 10.19. Выбор цен закрытия

По горизонтальной оси получились слишком частые метки. Сделаем их пореже:

Format Axis — Labels — Interval between labels — Specify interval unit

Подбираем такой период сглаживания, чтобы выделить долгосрочный тренд (рис. 10.20). На нашем графике можно видеть отсутствие направленного движения (боковой тренд) с 2015 по 2017 годы. Здесь цены многократно пресекают скользящую среднюю. В период падающего тренда с 2017 по 2019 годы цены находятся ниже скользящей средней. С 2019 года просматривается начало растущего тренда: цены выше скользящей средней.

Рис. 10.20. Линия сопротивления на графике баров

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

Построим второй вид биржевых графиков — японские свечи:

Insert — Charts — Insert Waterfall, Funnel, Stock, Surface or Radar Chart — Stock — Open-High-Low-Close

Как видим, здесь используются четыре значения цены (рис. 10.21).

Рис. 10.21. Построение свечного графика

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

Рис. 10.22. Линия сопротивления на свечном графике

Задание. Постройте график японских свечей. Проведите сглаживание с помощью скользящей средней.

Построим свечной график за последние полгода (рис. 10.23). Теперь стали видны отдельные свечи.

Рис. 10.23. Японские свечи

Задание. Постройте графики свечей и баров за последние полгода.

При анализе биржевых данных за несколько лет можно использовать более «крупные» свечки, например за месяц.

Скачаем данные с периодом месяц за те же годы.

Построим свечной график и нанесём скользящую среднюю (рис. 10.24). На этот раз выделяем столбцы времени и цен в качестве исходных данных. Нам остаётся немного настроить оформление графика.

Рис. 10.24. Свечной график (период — месяц)

Задание. Скачайте данные с периодом месяц. Постройте свечной график. Нанесите скользящую среднюю.

Для полноты картины построим график баров — тоже с периодом месяц (рис. 10.25). Включим такую же скользящую среднюю.

Рис. 10.25. График баров (период — месяц)

Задание. Постройте график баров с периодом месяц. Нанесите скользящую среднюю.

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

Задание. Прочитайте в Википедии статью «Скользящая средняя (индикатор)». Изучите раздел «Торговая стратегия». Сделайте зарисовку, поясняющую идею использования скользящей средней в биржевой торговле.

10.5. Анализ тренда

Модель тренда — это ещё один инструмент анализа биржевых данных.

В этом разделе мы проведём анализ динамики биржевого фонда. Exchange Traded Fund (ETF) — это инвестиционный фонд, который торгуется на бирже. С точки зрения участника торгов, это ценная бумага, которую можно в любой момент купить или продать на бирже, — точно так же, как акции или облигации.

Задание. Прочитайте в Википедии первый абзац статьи «Торгуемый на бирже фонд». Выясните преимущества ETF по сравнению с ПИФами.

Мы будем анализировать динамику котировок одного фонда под названием FXMM ETF. Буквы MM в названии означают Money Market — «фонд денежного рынка». Общее описание фонда находим на сайте Московской Биржи по адресу:

https://place.moex.com/products/etfs/FXMM

Здесь же можно почитать описание ETF как финансового инструмента, если нажать на кнопку [?] справа от слова ETF (рис. 10.26).

Рис. 10.26. Описание фонда

Задание. Ознакомьтесь с описанием фонда на сайте Мосбиржи. Выясните, как определяется стоимость акций фонда.

Управляющая компания (которая управляет инвестиционным фондом) берёт оплату (комиссию) за свою работу. Как и в каком размере? С условиями компании можно подробнее ознакомиться на её сайте

https://finex-etf.ru

Задание. Найдите на сайте компании FinEx условия по фонду FXMM. Выясните размер комиссии.

Загрузим исторические данные для анализа. Переходим на сайт компании «Финам» — Теханализ — Экспорт котировок (рис. 10.27). Выбираем раздел Мосбиржа ETF. Инструмент: FXMM ETF. Импортируем данные в Excel.

Рис. 10.27. Загрузка котировок ETF

Задание. Загрузите котировки FXMM ETF и импортируйте файл в Excel.

Построим график в виде японских свечей. Настроим оформление графика. Включим линейный тренд. Выведем уравнение тренда на экран.

Попробуйте повторить все описанные действия и получить такой же результат, как на нашем рисунке 10.28.

Рис. 10.28. Свечной график котировок фонда

Задание. Постройте свечной график и нанесите на него линию тренда.

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

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

Переключаем формат оси на даты:

Format Axis — Axis options — Axis Type — Date Axis.

Уравнение тренда изменилось (рис. 10.29).

Рис. 10.29. Настройка оси времени

Задание. Измените настройки горизонтальной оси на отображение даты. Обратите внимание на уравнение тренда.

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

Напомним, что дата в 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.6. Показатели динамики

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Рис. 10.38. Доходность фонда

Задание. Повторите рассмотренные выше расчёты доходности. Определите доходность за 2015, 2016, 2017 и 2018 годы по отдельности. Запишите вывод об изменении доходности фонда со временем.

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

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

1. Представленные сведения не являются инвестиционной рекомендацией.

2. Доходность в прошлом не гарантирует доходности в будущем.

3. Инвестор принимает решения о покупке и продаже активов на свой собственный страх и риск.

4. Наша компания вам ничего не обещает и вообще ни за что не отвечает.

Для нашего прогноза используем полученное линейное уравнение долгосрочного пятилетнего тренда. Получаем прогноз на 2020 год около 6,3% и среднюю доходность 5,6% годовых на следующие 5 лет (рис. 10.39).

Рис. 10.39. Прогноз доходности

Задание. Повторите описанные выше расчёты прогноза доходности. Сделайте прогнозы на каждый год (2021, 2022, 2023 и 2024) по отдельности.

Доходность биржевых фондов можно сравнить с процентными ставками по депозитам в крупных российских банках. На сайте Центрального Банка России можно найти «Перечень системно значимых кредитных организаций».

Задание. Прочитайте в Википедии статью «Системно значимые банки». Выясните, могут ли такие организации обанкротиться.

Банковскую процентную ставку по вкладам можно считать примером «безрисковой доходности».

Учтите, что коммерческие банки часто предлагают вклады с возможностью частичного СНЯТИЯ СРЕДСТВ. При этом обычно оговаривается сумма минимального остатка на счёте. Процентная ставка по таким вкладам будет ниже, чем по срочному депозиту без возможности снятия.

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

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

Построим график динамики котировок акций биржевого фонда в соответствии с нашим уравнением тренда. Для этого добавим к нашим расчётам прогнозное значение на начало 2015 года (рис. 10.40).

Рис. 10.40. Данные для линейного прогноза

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

Рис. 10.41. Линейный прогноз курса фонда

Задание. Постройте график исторических котировок и нанесите на него линейный прогноз. Используйте прогнозы для каждого из пяти последующих лет.

Мы рассмотрели динамику котировок и сделали прогнозы доходности на основе долгосрочного пятилетнего линейного тренда. Однако наклон линии немного меняется с течением времени. Если рассматривать только тренд за последний год, прогноз может несколько измениться.

Задание. Постройте уравнение линейного тренда только по данным за последний год. Проведите прогнозирование динамики курса на пять лет вперёд. Оцените ежегодную и среднюю доходность. Сравните с предыдущими результатами.

Словарь основных терминов

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

динамика: раздел статистики, в котором изучается изменение каких-нибудь значений во времени. Происходит от греческого слова dynamis — «сила, мощь».

ряд динамики: последовательность числовых значений с привязкой ко времени. Также называется временнóй ряд. Английское название: Time Series. Каждое отдельное значение называется «уровень ряда».

хронология: последовательность каких-либо событий во времени; временнáя последовательность. Происходит от греческих слов chronos — «время» и logos — «учение, изучение, описание».

анализ: разделение чего-нибудь на части и рассмотрение по частям, а также изучение того, как эти части соединяются в единое целое. Происходит от греческого слова analysis — «разложение, растворение», где ana — «обратно» и lysis —«развязывание, разрешение, освобождение; растворение».

тренд: основная тенденция ряда динамики, долгосрочное направление в развитии, изменении чего-либо. Происходит от английского слова trend — «изгиб реки; направление движения, изменения», от глагола trend — «отклоняться; изгибаться, загибаться».

аддитивная модель: модель ряда динамики в виде суммы компонентов — «тренд плюс сезонность плюс случайность». Слово аддитивная означает «основанная на сложении, содержащая сумму», от английского add — «складывать, прибавлять».

мультипликативная модель: модель ряда динамики в виде произведения компонентов — «тренд умножить на сезонность умножить на случайность». Слово мультипликативная означает «основанная на умножении, содержащая произведение». Происходит от английского слова multiply — «умножать», от латинского multus — «много».

Благодарности

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

Следующие студенты активно помогали в проработке материала и составлении чернового варианта:

— Корнеева Мария

— Кострюкова Анна

— Токарева Татьяна

— Муслимов Роберт

В тестировании учебных материалов, выявлении упущенных моментов и оценке трудоёмкости участвовали следующие студенты:

— Яковлева Виктория

— Исхаков Радмир

— Халиков Ильшат

— Мурадян Гарик

— Мухаметьянова Ольга

— Хайретдинова Ирина

— Макаров Владислав

— Макрушин Константин

— Портнов Владислав

Литература

1. Анализ распределения в Excel: Учебное пособие / В.Ю.Арьков. — [б. м.]: Издательские решения, 2019. — 158 с.

2. Статистический анализ взаимосвязи в Excel: Учебное пособие / В.Ю.Арьков. — [б. м.]: Издательские решения, 2019. — 146 с.

3. Теория статистики: учебник / Р.А.Шмойлова, В.Г.Минашкин, Н.А.Садовникова, Е.Б.Шувалова; под ред. Р. А. Шмойловой. — М.: Финансы и статистика, 2014. — 656 с.

Выпуски

Данное учебное пособие входит в серию книг по статистике и бизнес-аналитике. На сегодняшний день автором подготовлено пять выпусков. Каждое пособие предназначено для самостоятельного освоения одного из ключевых разделов дисциплины «Бизнес-аналитика и статистика».

Напомним, что действующие учебные планы подготовки бакалавров и специалистов предусматривают время для самостоятельной работы студентов (СРС), а также для контроля самостоятельной работы (КСР).

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

Выпуск 2. Корреляция и регрессия. Статистическое изучение взаимосвязи. Соответствующий раздел предмета: «Взаимосвязь явлений», «Фондовые индексы». Мы узнаем, что прямую линию можно провести через любое количество точек, если делать это «в среднем». А ещё нам предстоит выяснить, как связаны между собой различные отрасли экономики — если посмотреть на них глазами Московской биржи.

Выпуск 3. Динамика. Компоненты рядов динамики. Скользящие средние. Уравнение и линия тренда. Соответствующий раздел предмета: «Динамика». Здесь мы рассмотрим составные части динамики (изменения во времени) и биржевые графики, на которых наносят общую тенденцию (тренд).

Выпуск 4. Сводные таблицы. Соответствующий раздел предмета: «Бизнес-аналитика». Это одновременно и самый простой, и самый сложный раздел, потому что настройка таблиц делается визуально, через меню, а внутри спрятаны уже изученные статистические методы: сводка и группировка, показатели, взаимосвязь, динамика и т. д. Снаружи мы в конечном счёте увидим «приборную панель» руководителя предприятия — такую же, как приборная панель автомобиля.

Выпуск 5. Гистограммы в управлении качеством. Мы обсудим проблему управления качеством производства. Подробно рассмотрим грамотное построение гистограмм и их применение в управлении качеством производства. Гистограмму относят к самым простым и самым эффективным инструментам системы качества — с начала развития массового, серийного производства (то есть около ста лет).

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

Все выпуски доступны для бесплатного скачивания на сайте издательства «Ридеро».

Content

Анализ рядов динамики в электронных таблицах
Учебное пособие
Валентин Юльевич Арьков

Введение

1. Цель и задачи

2. Отчёт

3. Ключевые термины

3.1. Динамика

3.2. Ряд динамики

3.3. Уровень

3.4. Анализ

3.5. Компонент

3.6. Компоненты ряда динамики

3.7. Тренд

3.8. Колебания

3.9. Сезонные колебания

3.10. Случайная составляющая

4. Модели рядов динамики

4.1. Аддитивная модель

4.2. Мультипликативная модель

5. Варианты заданий

6. Зарисовки графиков

6.1. Аддитивная модель

6.1.1. Тренд

6.1.2. Сезонность

6.1.3. Случайность

6.1.4. Сумма компонентов

6.2. Мультипликативная модель

6.2.1. Тренд

6.2.2. Сезонность

6.2.3. Случайность

6.2.4. Произведение компонентов

7. Имитационное моделирование

7.1. Аддитивная модель

7.1.1. Тренд

7.1.2. Сезонность

7.1.3. Случайность

7.1.4. Сумма компонентов

7.2. Мультипликативная модель

7.2.1. Тренд

7.2.2. Сезонность

7.2.3. Случайность

7.2.4. Произведение компонентов

8. Скользящие средние

8.1. Встроенное сглаживание

8.2. Надстройка

8.3. Функции

8.4. Формулы

8.4.1. SMA

8.4.2. EMA

8.5. Мультипликативная модель

9. Тренд

9.1. Встроенный тренд

9.2. Надстройка

9.3. Функция LINEST

9.4. Линия тренда

9.5. Мультипликативная модель

10. Биржевые данные

10.1. Свечи и бары

10.2. Исторические данные

10.3. Биржевые графики

10.4. Скользящие средние

10.5. Анализ тренда

10.6. Показатели динамики

Словарь основных терминов

Благодарности

Литература

Выпуски