Алгоритм построения тренда в MS Excel

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

Предлагаемый алгоритм описывает процесс построения линии тренда на базе стандартного офисного пакета Microsoft Office Excel 2007, но он идентичен и для более ранних версий.

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

Процесс построения линии тренда можно разделить на 6 этапов:

Этап 1. Формирование временного ряда по показателю.

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

Исторические данные котировок цен можно получить с нескольких сайтов:

Брокера Алор

Брокера Финам

Этап 2. Построение диаграммы временного ряда показателя

Экспорт котировок

Экспорт котировок

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

Биржевой график

Использование диаграммы - Биржевой график

 

Для построения данного графика использовалась Биржевая диаграмма, при этом использовались всего четыре столбца:OPEN, HIGH, LOW, CLOSE.

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

Этап 3. Добавление линии тренда на диаграмму временного ряда показателя

После того как была построена диаграмма временного ряда показателя необходимо добавить на нее линию тренда. Для этого активизируем поле диаграммы (щелчок левой кнопкой мыши) и выделяем линию основного ряда (щелчок левой кнопкой мыши). Затем открываем контекстное меню линии ряда (щелчок правой кнопкой мыши) и выбираем пункт «добавить линию тренда».

Добавить линию тренда

Остается только добавить линию тренда

В результате выполненных действий открывается диалоговое окно «Формат линии тренда», в котором можно задать тип линии тренда, выбрать имя для линии тренда и объем выводимой информации. С целью формирования математически обоснованных представлений о модели прогноза активизируем (отмечаем) пункты: «показывать уравнение на диаграмме» и «поместить на диаграмму величину достоверности аппроксимации (R^2)» (нижний блок диалогового окна).

В центральном блоке диалогового окна можно скорректировать название аппроксимирующей (сглаженной) кривой, отметив позицию «другое» и введя желаемое название.

Далее производим выбор типа линии тренда. Существует шесть различных линий тренда (аппроксимация и сглаживание), которые могут быть добавлены на диаграмму в MS Excel. Выбор типа зависит от особенностей представленных данных.

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

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

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

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

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

Скользящее среднее (линейная фильтрация). Использование в качестве приближения скользящего среднего позволяет сгладить колебания данных и таким образом более наглядно показать характер зависимости. Такая линия тренда строится по определенному числу точек (оно задается параметром «Точки»). Элементы данных усредняются, и полученный результат используется в качестве среднего значения для приближения. Так, если выбрано значение «2», первая точка сглаживающей кривой определяется как среднее значение первых двух элементов данных, вторая точка – как среднее следующих двух элементов и так далее.

Как правило, для прогнозирования экономических категорий используется полиномиальная линия тренда, а степень полинома определяется на основе реализации двух принципов: максимизации коэффициента детерминации (величина R2 должна стремиться к 1) и экономической логичность динамики показателя в прогнозируемый период. Для приемлемых моделей предполагается, что коэффициент детерминации должен быть не меньше 0,5, то есть моделью объясняется более 50 % изменения прогнозируемой величины. Модели с коэффициентом детерминации выше 0,8 можно признать достаточно хорошими. Значение коэффициента детерминации 1,0 означает функциональную зависимость между переменными, но в тоже время повышает вероятность сильной погрешности прогнозных значений при изменении условий функционирования системы.

Таким образом, выбор типа линии тренда осуществляется на основе опытной проверки и соответствия прогноза модели ожидаемой динамики по экспертной оценке. Возможности MS Excel 2007 позволяют провести опытную проверку всех типов линии тренда непосредственно в режиме корректировки параметров линии тренда, то есть в диалоговом меню «Формат линии тренда». Для этого достаточно переместить окно диалогового меню в левую часть экрана максимально открыв область диаграммы, а затем осуществлять переключение между типами линии тренда.

Также сменить тип линии тренда можно уже после первоначального выбора, полного закрытия диалогового меню «Формат линии тренда». Для этого необходимо активировать поле диаграммы (щелчок левой кнопкой мыши) и выделить линию тренда (щелчок левой кнопкой мыши). Затем открываем контекстное меню линии тренда (щелчок правой кнопкой мыши) и выбираем пункт «формат линии тренда». Либо активируем поле диаграммы, затем переходим на вкладку «Формат» в группе вкладок «Работа с диаграммами». В верхнем левом углу вкладки «Формат» выбираем в поле «Элементы диаграммы» элемент – «ряд *название прогнозируемого показателя*Линия тренда 1». После этого выбираем (щелчок левой кнопкой мыши) поле «Формат выделенного фрагмента». В результате выполненных действий открывается диалоговое окно «Формат линии тренда» о работе в котором говорилось выше.

Все. Линия тренда построена. Удачи.

Добавить комментарий