Выберите в меню Сервис пункт Анализ данных , появится окно с одноименным названием, главным элементом которого является область Инструменты анализа . В данной области представлен список реализованных в Microsoft Excel методов статистической обработки данных. Каждый из перечисленных методов реализован в виде отдельного режима работы, для активизации которого необходимо выделить соответствующий метод указателем мыши и щелкнуть по кнопке ОК. После появления диалогового окна вызванного режима можно приступать к работе.
Режим работы «Скользящее среднее » служит для сглаживания уровней эмпирического динамического ряда на основе метода простой скользящей средней.
Режим работы «Экспоненциальное сглаживание » служит для сглаживания уровней эмпирического динамического ряда на основе метода простого экспоненциального сглаживания.
В диалоговых окнах данных режимов (рисунок 2 и 3) задаются следующие параметры:
2. Флажок Метки – устанавливается активное состояние, если первая строка (столбец) во входном диапазоне содержит заголовки. Если заголовки отсутствуют, флажок следует деактивизировать. В этом случае будут автоматически созданы стандартные названия для данных выходного диапазона.
3. Интервал (только в диалоговом окне Скользящее среднее) – вводится размер окна сглаживания р . По умолчанию р=3 .
Рисунок 2 – Диалоговое окно скользящего среднего
4. Фактор затухания (только в диалоговом окне Экспоненциальное сглаживание) – вводится значение коэффициента экспоненциального сглаживания p . По умолчанию, p=0,3 .
5. Выходной интервал / Новый рабочий лист / Новая рабочая книга – в положении Выходной интервал активизируется поле, в которое необходимо ввести ссылку на левую верхнюю ячейку выходного диапазона. Размер выходного диапазона будет определен автоматически, и на экране появится сообщение в случае возможного наложения выходного диапазона на исходные данные. В положении Новый рабочий лист открывается новый лист, в который начиная с ячейки А1 вставляются результаты анализа. Если необходимо задать имя в поле, расположенное напротив соответствующего положения переключателя. В положении Новая рабочая книга открывается новая книга, на первом листе которой начиная с ячейки А1 вставляются результаты анализа.
6. Вывод графика – устанавливается в активное состояние для автоматической генерации на рабочем листе графиков фактических и теоретических уровней динамического ряда.
7. Стандартные погрешности – устанавливаются в активное состояние, если требуется включить в выходной диапазон столбец, содержащий стандартные погрешности.
Рисунок 3 – Диалоговое окно экспоненциального сглаживания
Пример 1.
Данные о реализации (млн. руб.) продуктов сельскохозяйственного производства магазинами потребительской кооперации города приведены в таблице, сформированной на рабочем листе Microsoft Excel (рисунок 4). В указанном периоде (2009 – 2012 гг.) требуется выявить основную тенденцию развития данного экономического процесса.
Рисунок 4 – Исходные данные
Для решения задачи используем режим работы «Скользящее среднее ». Значения параметров, установленных в одноименном диалоговом окне, представлены на рисунке 5, рассчитанные в данном режиме показатели – на рисунке 6, а построенные графики – на рисунке 7.
Рисунок 5 – Заполнение диалогового окна
Рисунок 6 – Результаты анализа
Рисунок 7– Скользящее среднее
В столбце D (рисунок 5) вычисляются значения сглаженных уровней. Например, значение первого сглаженного уровня рассчитывается в ячейке D5 по формуле =СРЗНАЧ(С2:С5), значение второго сглаженного уровня – в ячейке D6 по формуле =СРЗНАЧ(С5:С8) и т.д.
В столбце E вычисляются значения стандартных погрешностей с помощью формулы =КОРЕНЬ (СУММАКВРАЗН (блок фактических значений; блок прогнозных значений) / размер окна сглаживания).
Например, значение в ячейке Е10 рассчитывается по формуле =КОРЕНЬ(СУММКВРАЗН(С7:С10;О7:В10)/4).
Вместе с тем, как отмечалось выше, если размер окна сглаживания является четным числом (р=2m ), то рассчитанное усредненное значение нельзя сопоставить какому-либо определенному моменту времени t, поэтому необходимо применять процедуру центрирования.
Для рассматриваемого примера р=4 , поэтому процедура центрирования необходима. Так, первый сглаженный уровень (265,25) записывается между II и III кв. 2009 г. и т.д. Применяя процедуру центрирования (для этого используем функцию СРЗНАЧ), получаем сглаженные уровни с центрированием. Для III кВ. 2009 г. определяется серединное значение между первым и вторым сглаженными уровнями: (265,25 + 283,25)/2 = 274,25; для IV кв. 2009 г. центрируются второй и третий сглаженные уровни: (283,25 + 292,00)/2 = 287,6 и т.д. Рассчитанные значения представлены в таблице 1. Скорректированный график скользящей средней представлен на рисунке 8.
Таблица 1 – Динамика сглаженных уровней реализации продукции
| Год | Квартал | Размер реализации, млн. руб. | Сглаженные уровни с центрированием |
| 274,25 | |||
| 287,63 | |||
| 297,00 | |||
| 307,50 | |||
| 334,63 | |||
| 374,13 | |||
| 402,88 | |||
| 421,00 | |||
| 429,00 | |||
| 430,75 | |||
| 435,38 | |||
| 446,63 | |||
Рисунок 8 – Скорректированный график скользящего среднего
Пример 2.
Рассмотренная задача может быть решена и с помощью метода простого экспоненциального сглаживания. Для этого необходимо использовать режим работы «Экспоненциальное сглаживание». Значения параметров, установленных в одноименном диалоговом окне, представлены на рисунке 9, рассчитанные в данном режиме показатели – рисунок 10, а построенные графики – на рисунке 11.
Рисунок 9 – Заполнение диалогового окна «Экспоненциальное сглаживание»
Рисунок 10 – Результаты анализа
Рисунок 11 – Экспоненциальное сглаживание
В столбце D (рисунок 10) вычисляются значения сглаженных уровней на основе рекуррентных соотношений.
В столбце E рассчитываются значения стандартных погрешностей с помощью формулы =КОРЕНЬ(СУММКВРАЗН (блок фактических значений; блок прогнозных значений) / 3). Как легко заметить (сравните рисунок 8 и 11), при использовании метода простого экспоненциального сглаживания, в отличие от метода простой скользящей средней, сохраняются мелкие волны.
Скользящая средняя представляет собой статическую функцию, которая дает возможность с легкостью получать результаты по различным задачам. К примеру, задачи по получению прогноза.
Скользящая средняя позволяет изменять абсолютные динамические значения целого ряда ячеек на средние арифметические, используя сглаживание данных. Ее часто применяют в подсчетах на экономических биржах, в торговли и других сферах.
Как его применять в Excel - давайте разберем все по этапам.
Данный метод в Excel применяется через использование функции пакета анализа и непосредственно через саму встроенную функцию, которая получила название «СРЗНАЧ».
Рассмотрим первый способ использования метода скользящей средней через пакет анализа:
1. Пакета анализа в стандартном наборе функций нет, поэтому его необходимо включить. Делается это через параметры документа – «Файл» - «Параметры» - «Надстройки». Внизу диалогового окна есть вкладка «Надстройки». Именно она нам и нужна.
Включаем «Пакет анализа» и сохраняемся. Весь функциональный добавился в «Данные» и полностью готов к использованию.
2. Чтобы понять, каким образом работает метод скользящей средней, попробуем получить данные за 12 месяц на основе тех, которые мы уже получили за 11 прошлых – сделаем прогноз. Заполняем исходные значения таблицы.
3. В ранее добавленном функционале «Анализ данных» на рабочей панели с параметров надстроек документа, выбираем искомую «Скользящую среднюю» функцию и нажимаем «Ок».
4. В появившемся диалоговом окне заполним все значения. «Входной интервал» - все наши показатели за 11 месяцев без искомой ячейки. «Интервал» - показатель сглаживания, касаемо наших исходных данных, установим «3». «Выходной интервал» - ячейки, куда будут выводиться полученные данные методом скользящей средней. Включаем «Стандартные погрешности» и получаем все искомые значения.
5. Для получения более верного результата выполним повторное сглаживание с интервалом в «2» единицы. Укажем новый «Выходной интервал» и получаем новые данные.
6. На основе новых полученных данных можно сделать прогноз показатель на искомый месяц путем расчета метода скользящей средней за последний период. Основываемся на том, что чем меньше показатель стандартной погрешности, тем точнее данные.
Рассмотрим второй способ - функцию СРЗНАЧ:
1. Если пакет анализа делает практически все операции автоматизированными, то использование функции СРЗНАЧ требует применения нескольких стандартных функций Excel. Используем те же исходные данные по 11 месяцам. Вставим функцию.
2. В диалоговом окне Мастера функций перейдем во вкладку «Статистические» и выберем нашу искомую функцию «СРЗНАЧ».
3. Функция «СРЗНАЧ» имеет очень простой синтаксис – «=СРЗНАЧ(число1;число2;число3;...). Укажем в аргументе «число 1» диапазон за «Январь» и «Февраль».
4. Рассчитаем показатель для оставшихся периодов времени путем протягивания маркера заполнения формулы по столбцу вниз.
5. Проведем эту же операцию, но с разницей в период за 3 месяца.
6. Но какие данные в нашем случае верны, на основе двух месяцев или трех? Для получения правильного ответа применим расчет абсолютного отклонения, среднего квадратического и еще пары других показателей. За абсолютное отклонение отвечает функция «ABS».
В диалоговом окне функции указываем разность между доходом и скользящей средней за два месяца.
7. Маркером заполнения заполним столбец и рассчитаем «СРЗАНЧ» за все время.
8. Проведем аналогичную операцию для поиска абсолютного отклонения и среднего значения за период в три месяца.
9. Осталось еще пару шагов. Для начала рассчитаем относительное отклонение по двум и трем месяцам путем функции поиска абсолютного значения разделения найденного отклонения на имеющиеся исходные данные, а также найдем среднее значение полученных значений.
Все данные представим в процентах.
10. Для получения конечного результата метода скользящей средней осталось подсчитать среднее квадратическое отклонение также за два и за три месяца.
Наше искомое среднее квадратическое отклонение будет равняться квадратному корню из суммы квадратов разностей исходных данных о выручке и полученных данных методом скользящей средней, разделенной на период времени.
Пропишем нашу функцию «КОРЕНЬ(СУММКВРАЗН(B6:B12;C6:C12)/СЧЁТ(B6:B12))», заполним столбцы маркерами заполнения и найдем среднее значение по полученным данным.
11. Проведем анализ полученных данных и можем с уверенностью сделать вывод – сглаживание по двум месяцам дало наиболее правдивые конечные показатели.
Я повторюсь. Поведение толпы инерционно. А значит вероятность того, что толпа завтра будет вести себя также как вчера и позавчера гораздо выше, чем вероятность перемены настроения.
Для того, чтобы отслеживать поведение толпы на рынке существует древний индикатор MACD. Его аббревиатура расшифровывается как moving average convergence-divergence или если по русски схождение-расхождение скользящих средних (имеются ввиду исторические значения цен на акции или другие инструменты).
Графический смысл гистограммы MACD заключается в подтверждении продолжения тенденции (направления к развитию) движения цены. Грубо говоря, акции продолжают дешеветь или дорожать. Направление движения цены определяется как разница между двумя соседними столбиками.
Для построения гистограммы MACD мы используем excel.
1) Сначала нам потребуются исторические данные для анализа. В предыдущей статье я приводил пример, где такие данные можно раздобыть. Последуем этому примеру и перейдем на брокерскую страничку экспорта данных:
Выставив требования к формату скачиваемых данных получаем файл с данными формата csv, который понимает excel. Также исторические данные по интересующему нас инструменту можно скачать на сайте брокера ЗАО «ФИНАМ по этой ссылке .
2) даные следует отформатировать как описано в .
В конечном итоге должен получиться вот такой набор:
3) Теперь создадим новый лист в книге excel для расчетов и построения графика технического анализа. Так и назовем этот лист: «Расчет MACD». Затем скопируем на этот лист столбец с датами
4) Теперь рассчитаем экспоненциальную скользящую среднюю с окном в 12 дней (EMA 12). ЕМА 12 рассчитывается по формуле:
Заложим эту формулу в столбец справа от цены закрытия
Скопируем получившуюся формулу в ячейку ниже и немного подредактируем: вместо значения из ячейки B3, во второй части формулы, подставим значение из ячейки C2. C2- это и будет EMA12 предыдущего дня.
Должно получиться вот так:
Теперь размножим формулу полученную во второй ячейке для всего столбца EMA12. Для этого кликнем один раз мышкой в ячейку C3 так, чтобы вокруг ячейки появилась черная жирная рамочка, затем перемещаем курсор в правый нижний угол черной жирной рамочки так, чтобы курсор принял форму жирного черного крестика и двойным кликом левой кнопки мышки размножаем формулу на весь столбец. Вот так:
Теперь аналогичным образом рассчитаем экспоненциальную скользящую среднюю с окном в 26 дней (EMA 26). ЕМА 26 рассчитывается по формуле:
Заложим эту формулу в столбец справа от рассчитанной EMA12. Для этого запись в ячейку начинаем с символа «=», что сообщает процессору excel о том, что будет вводится формула. Для первой ячейки формула немного другая чем для остальных ячеек, из-за того, что вместо вчерашней EMA26 следует подставить сегодняшнюю цену закрытия. Вот так:
Скопируем получившуюся формулу в ячейку ниже и немного подредактируем: вместо значения из ячейки B3, во второй части формулы, подставим значение из ячейки D2. D2- это и будет EMA26 предыдущего дня. Должно получиться вот так:
Теперь размножим формулу полученную во второй ячейке для всего столбца EMA26. Для этого кликнем один раз мышкой в ячейку D3 так, чтобы вокруг ячейки появилась черная жирная рамочка, затем перемещаем курсор в правый нижний угол черной жирной рамочки так, чтобы курсор принял форму жирного черного крестика и двойным кликом левой кнопки мышки размножаем формулу на весь столбец. Вот так:
Поздравляю! Мы с вами справились с расчетом экспоненциальных средних. Теперь следует получить «быструю» линию MACD. Для этого нужно из EMA12 вычесть EMA26. Забьем эту формулу в следующий столбец справа:
Теперь нужно вычислить девятидневную экспоненциальную скользящую среднюю для «быстрой» линии MACD. Полученная линия будет называться «сигнальной» линией MACD. Расчет произведем по следующей формуле:
Аналогичным образом забиваем формулу расчета в excel в ячейку правее «быстрой» линии MACD:
В ячейке нижнего ряда корректируем формулу также, как делали это при расчете двадцатишестидневной и двенадцатидневной экспоненциальных скользящих средних. Вот такая должна быть формула в ячейке F3:
И наконец-то мы можем рассчитать последний столбец данных для построения гистограммы MACD. Значениями этого столбца для построения гистограммы является разность между «быстрой» и «сигнальной» линиями MACD. Вбиваем последнюю формулу расчета данных для построения гистограммы:
Рассматривать гистограмму MACD гораздо удобнее рядом с графиком колебания цен на анализируемый инструмент. В предыдущей статье я подробно описал как построить такой график. Для построения графика цен на инструмент скопируем выборку необходимых данных на отдельный лист. Как-то так:
Построение биржевого графика проще всего произвести здесь же, на этом листе. Затем следует его скопировать на отдельный лист, тот на котором мы разместим и гистограмму MACD.
Создаем отдельный лист для наших графиков. Вставляем из буфера обмена скопированную диаграмму и немного настраиваем ее внешний вид. Окно с графиком растягивается и сокращается по длине и ширине подобно окнам в самой Windows.
А ткнув левой кнопкой мыши в шкалу со значениями цен можно изменить формат данных оси построения графика. После такого тычка шкала значений вертикальной (в нашем случае) оси выделяется прямоугольной рамкой. Как только появилась такая рамка следует нажать правую кнопку мыши для вызова контекстного меню. В контекстном меню левой кнопкой мыши выбираем строку <Формат оси…>, вот так:
В открывшемся диалоговом окне настройки параметров оси графика настраиваем минимальное значение (80) и максимальное (160). Это верхние две строчки в открывшемся диалоговом окне. На рисунке ниже показано нужное положение радиокнопок и вписаны значения 80 и 160 в соответствующие строки:
Под окном графика цен вставляем окно для будущей гистограммы MACD. В главном меню выбираем вкладку <<Вставка>> затем подменю <<Гистограмма>> и в выпадающем меню выбираем левый верхний значок гистограммы, этот значок подсвечен желтым на скрин-шоте ниже:
Главное, перед вставкой второго графика не забыть снять выделение с первого. Иначе может произойти замещение одного графика другим, а нам нужны оба графика.
Перед вызовом меню <<Гистограмма>> недурно будет навести курсор на ячейку А16 и нажать левую кнопку мыши. После вставки гистограммы нам необходимо указать наш столбец с расчетными данными гистограммы MACD. Для этого следует навести курсор мыши на гистограмму и нажать правую кнопку мыши для вызова контекстного меню управления диаграммой. В открывшемся контекстном меню выбираем пункт <Выбрать данные>:
После нажатия кнопки <<Добавить>> в предыдущем окне нам следует набрать наименование нашего графика — «MACD», а в нижнем ряду нажать кнопочку справа от ряда:
После нажатия на кнопку справа от нижнего ряда открывается узенькое окошко «Изменение ряда». Не закрывая этого окна переходим с помощью мыши на лист с названием MACD:
После того, как столбец с данными охвачен тонкой пунктирной линией в окошке «Изменение ряда» следует нажать кнопочку справа. После этого откроется окно «Изменение ряда» с двумя строками. Вот в этом окошке можно нажать кнопку <
Вернувшись на лист с наименованием «ГРАФИКИ» в окне выбора данных для построения гистограммы тоже нажимаем кнопку <
Можно немного поиграть с размером окон для графиков и получить тот результат, который кажется нагляднее:
А вот те же самые графики, построенные торговой системой QUIK. Похоже получилось у нас с вами?
Дорогой читатель! Если ты решил построить эти графики и у тебя что-то не получается — оставь свой вопрос в комментариях и вместе мы обязательно разберемся и научимся строить графики в excel.
Исходные файлы excel с которых сделаны скриншоты и в которых есть построенные графики можно скачать по .
Практическое моделирование экономических ситуаций подразумевает разработку прогнозов. С помощью средств Excel можно реализовать такие эффективные способы прогнозирования, как: экспоненциальное сглаживание, построение регрессий, скользящее среднее. Рассмотрим подробнее использование метода скользящего среднего.
Использование скользящих средних в Excel
Метод скользящей средней – один из эмпирических методов для сглаживания и прогнозирования временных рядов. Суть: абсолютные значения ряда динамики меняются на средние арифметические значения в определенные интервалы. Выбор интервалов осуществляется способом скольжения: первые уровни постепенно убираются, последующие – включаются. В результате получается сглаженный динамический ряд значений, позволяющий четко проследить тенденцию изменений исследуемого параметра.
Временной ряд – это множество значений X и Y, связанных между собой. Х – интервалы времени, постоянная переменная. Y – характеристика исследуемого явления (цена, например, действующая в определенный период времени), зависимая переменная. С помощью скользящего среднего можно выявить характер изменений значения Y во времени и спрогнозировать данный параметр в будущем. Метод действует тогда, когда для значений четко прослеживается тенденция в динамике.
Например, нужно спрогнозировать продажи на ноябрь. Исследователь выбирает количество предыдущих месяцев для анализа (оптимальное число m членов скользящего среднего). Прогнозом на ноябрь будет среднее значение параметров за m предыдущих месяца.
Задача. Проанализировать выручку предприятия за 11 месяцев и составить прогноз на 12 месяц.
Сформируем сглаженные временные ряды методом скользящего среднего посредством функции СРЗНАЧ. Найдем средние отклонения сглаженных временных рядов от заданного временного ряда.
Относительные отклонения:
Средние квадратичные отклонения:
При расчете отклонений брали одинаковое число наблюдений. Это необходимо для того, чтобы провести сравнительный анализ погрешностей.
После сопоставления таблиц с отклонениями стало видно, что для составления прогноза по методу скользящей средней в Excel о тенденции изменения выручки предприятия предпочтительнее модель двухмесячного скользящего среднего. У нее минимальные ошибки прогнозирования (в сравнении с трех- и четырехмесячной).
Прогнозное значение выручки на 12 месяц – 9 430 у.е.
Применение надстройки «Пакет анализа»
Для примера возьмем ту же задачу.
На вкладке «Данные» находим команду «Анализ данных». В открывшемся диалоговом окне выбираем «Скользящее среднее»:
Заполняем. Входной интервал – исходные значения временного ряда. Интервал – число месяцев, включаемое в подсчет скользящего среднего. Так как сначала будем строить сглаженный временной ряд по данным двух предыдущих месяцев, в поле вводим цифру 2. Выходной интервал – диапазон ячеек для выведения полученных результатов.
Установив флажок в поле «Стандартные погрешности», мы автоматически добавляем в таблицу столбец со статистической оценкой погрешности.
Точно так же находим скользящее среднее по трем месяцам. Меняется только интервал (3) и выходной диапазон.
Сравнив стандартные погрешности, убеждаемся в том, что модель двухмесячного скользящего среднего больше подходит для сглаживания и прогнозирования. Она имеет меньшие стандартные погрешности. Прогнозное значение выручки на 12 месяц – 9 430 у.е.
Составлять прогнозы по методу скользящего среднего просто и эффективно. Инструмент точно отражает изменения основных параметров предыдущего периода. Но выйти за пределы известных данных нельзя. Поэтому для долгосрочного прогнозирования применяются другие способы.
Скользящее среднее или просто МА (Moving Average) , является среднеарифметическим ценового ряда. Общая формула скользящего среднего следующая:
Где:
МА – скользящее среднее;
n- период усреднения;
Х – значения цены акции.
Для прогнозирования цены акции на несколько периодов вперед воспользуемся формулой. Прогноз цены в следующем период будет равнять значения скользящего среднему в предыдущем периоде.
Спрогнозируем
с помощью модели скользящего среднего стоимость акций
компании Аэрофлот (AFLT)
. Для этого экспортируем котировки акции с сайта finam.ru за половину 2009 года. Всего будет 20 значений.
График стоимости акций Аэрофлота за выбранный промежуток времени представлен ниже.
Выбор периода усреднения
n
в модели скользящего среднего
Использование большего в модели МА(n) приводит к сильному искажению данных, в результате чего существенные значения ценового ряда усредняются, и в результате теряется четкость прогноза, можно сказать что он становится “размытым”. Использование слишком мелкого периода усреднения добавляет в прогноз больше шумовой компоненты. Как правило, период усреднения подбирается эмпирическим путем на исторических данных.
Построим скользящее среднее с периодом усреднения в три месяца MA(3). Для расчета значения скользящего среднего для акции, воспользуемся формулой Excel.
СРЗНАЧ(C2:C4)
В колонке “D” рассчитаны значения скользящего среднего с периодом усреднения 3.
После расчета скользящего среднего построим прогноз на 3 периода вперед (на три месяца вперед). Воспользуемся формулой для определения значения цены акции, первое прогнозное значение будет равно последнему значению скользящего среднего. Оранжевая область это область прогнозов. С22 будет равна значению скользящего среднего, то есть:
С22 =D21 С23=D22 и т.д.
От новых прогнозных данных стоимости акции рассчитывается скользящее следующее среднее.
Построим прогнозные значения по скользящему среднему для акций Аэрофлота на три месяца вперед. Ниже представлен график и прогнозные значения акции.