Как рассчитать ema в excel
Обновлено: 08.07.2024
Некоторые самые первые (но важные) статьи о Excel не вошли в группу, т.к. на момент их написания группы еще не было. Находятся эти статьи в персональном блоге. Ссылки на них:
Если Вы используете Excel в своей работе, то советую вступить в группу. Для этого надо всего лишь нажать одну кнопку ниже
Зато после вступления Вы будете получать уведомления на почту о появлении новых тем и точно ничего интересного не пропустите.
Лучшее от Kaur
Системная торговля. Готовы ли вы к просадкам? 51
2013-01-24 12:54:00 0
Как подняться трейдеру, если денег нет, а прибыльная торговая система есть? 24
2015-01-11 16:35:29 0
Первая сделка на валютном рынке за 7 шагов - инструкция для начинающих 61
2014-03-27 16:41:56 0
Информационная безопасность для трейдера 21
2014-09-30 16:19:15 0
Хотел бы напомнить участникам группы, что группа — это не столько курсы по обучению, сколько совместная работа по теме. Вступили в группу по Excel, нашли интересный материал, публикуйте. А то меня начинают обвинять уже, что я не довожу до конца обучение
Вот, скажем, увидели на Калита-финанс видео по Экселю. Можно опубликовать. Почему нет? Если при публикации нет нужной группы для пкбликации, значит уровня пока не хватает. Ничего страшного, публикуете в свой блог, делаете приписку, что хотите поместить в такую-то группу. Если материал действительно походит, то в тот же день модератор переместит в нужную группу.
В качестве примера добавляю видео от Калиты.
Из описания к видео:
Видеоролик показывает различие между простыми и экспоненциальными скользящими, приводится построение экспоненциальных скользящих в Excel с целью дальнейшего тестирования торговой системы, построенной на пересечении двух скользящих EMA.Понравилcя материал? Не забудьте поставить плюс и поделиться в социальной сети! Вступите в группу "Excel на службе трейдера", чтобы следить за обновлениями
ПРИСОЕДИНИТЬСЯ К ГРУППЕ
Графический смысл гистограммы MACD заключается в подтверждении продолжения тенденции (направления к развитию) движения цены. Грубо говоря, акции продолжают дешеветь или дорожать. Направление движения цены определяется как разница между двумя соседними столбиками.
Для построения гистограммы MACD мы используем excel.
1) Сначала нам потребуются исторические данные для анализа. В предыдущей статье я приводил пример, где такие данные можно раздобыть. Последуем этому примеру и перейдем на брокерскую страничку экспорта данных:
Выставив требования к формату скачиваемых данных получаем файл с данными формата csv, который понимает excel.
Также исторические данные по интересующему нас инструменту можно скачать на сайте брокера ЗАО «ФИНАМ по этой ссылке.
2) В конечном итоге должен получиться вот такой набор:
3) Теперь создадим новый лист в книге excel для расчетов и построения графика технического анализа. Так и назовем этот лист: «Расчет MACD». Затем скопируем на этот лист столбец с датами <DATE> и столбец с данными цены закрытия <CLOSE>. Вот так:
4) Теперь рассчитаем экспоненциальную скользящую среднюю с окном в 12 дней (EMA 12). ЕМА 12 рассчитывается по формуле:
Заложим эту формулу в столбец справа от цены закрытия <CLOSE>. Для этого запись в ячейку начинаем с символа «=», что сообщает процессору excel о том, что будет вводится формула. Для первой ячейки формула немного другая чем для остальных ячеек, из-за того, что вместо вчерашней EMA12 следует подставить сегодняшнюю цену закрытия. Вот так:
Скопируем получившуюся формулу в ячейку ниже и немного подредактируем: вместо значения из ячейки 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 :
наводим курсор на ячейку с адресом G2 и жмем левую кнопку мыши, ячейка выделяется тонкой пунктирной рамочкой:
Колесом мыши или с помощью боковых ползунков окна excel переходим к ячейке с адресом G54, нажимаем кнопу <Shift> и удерживая ее тыкаем левой кнопкой мыши в ячейку G54 при этом весь столбец охватывается тонкой пунктирной линией:
После того, как столбец с данными охвачен тонкой пунктирной линией в окошке «Изменение ряда» следует нажать кнопочку справа. После этого откроется окно «Изменение ряда» с двумя строками. Вот в этом окошке можно нажать кнопку <<OK>> и перейти к окну публикации графика:
Вернувшись на лист с наименованием «ГРАФИКИ» в окне выбора данных для построения гистограммы тоже нажимаем кнопку <<OK>>:
Можно немного поиграть с размером окон для графиков и получить тот результат, который кажется нагляднее:
А вот те же самые графики, построенные торговой системой QUIK. Похоже получилось у нас с вами ?
Экспоненциальное скользящее среднее (англ. Exponential Moving Average, EMA) является частным случаем взвешенного скользящего среднего и применяется в техническом анализе как самостоятельная методика, так и в качестве составляющей части других индикаторов. Целью такого сглаживания является передача большего веса последним значениям цен, и меньшего веса более ранним.
Формула
В общем виде формула для расчета значения экспоненциального скользящего среднего в период времени t (EMAt) может быть записана следующим образом:
Pt – значение случайной величины в период времени t;
EMAt-1 – значение экспоненциального скользящего среднего в период времени (t-1).
где N – интервал сглаживания.
Следует понимать, что экспоненциальное сглаживание в этом случае не будет удовлетворять критерию минимизации среднеквадратической ошибки.
Также для расчета значения экспоненциального скользящего среднего в период времени t необходимо знать его значение в предыдущем периоде времени (t-1). При этом, в качестве первого значения берется простое скользящее среднее (англ. Simple Moving Average) с тем же самым интервалом сглаживания.
Пример расчета
Рассмотрим методику расчет экспоненциального скользящего среднего на примере данных о цене акций Компании XYZ за последние пятнадцать периодов, которые представлены в таблице.
В качестве первоначального значения экспоненциального скользящего среднего используем простое скользящее среднее с интервалом сглаживание 4, которое составляет 6,8.
Следующее значение EMA, рассчитанное по приведенные выше формуле, составит 6,1.
Последующие значения рассчитываются аналогичным образом и графически представлены на рисунке.
Как можно видеть на графике, экспоненциальное сглаживание позволяет сгладить наиболее резкие отклонения цен и установить направление сложившегося на рынке тренда.
Скользящее среднее используется для сглаживания краткосрочных колебаний с целью определения долгосрочного тренда. Вычислим скользящее среднее с помощью надстройки MS EXCEL Пакет анализа, формулами и с помощью линии тренда на диаграмме.
Метод скользящего среднего состоит в вычислении средних значений на основе предшествующих значений исследуемого числового ряда.
В случае усреднения за 3 периода скользящее среднее равно:
Y скол.i =(Y i + Y i-1 + Y i-2 )/3
На картинке ниже показано как вычислить в MS EXCEL скользящее среднее путем усреднения значений за три периода (за два предыдущих и один текущий).
Примечание : В англоязычной литературе для скользящего среднего используется термин Moving Average (MA) или Simple Moving Average (SMA) , а также rolling average, running average, moving mean.
Получить ряд, сглаженный методом скользящего среднего, можно с помощью надстройки MS EXCEL Пакет анализа (Analysis ToolPak) . Надстройка доступна из вкладки Данные, группа Анализ (EXCEL 2010).
В поле Интервал установим значение 3 – будем усреднять значения ряда за 3 периода. В поле Выходной интервал достаточно ввести ссылку на левую верхнюю ячейку диапазона с результатами (укажем ячейку D7).
Также поставим галочки в поле Вывод графика и Стандартные погрешности (будет выведен столбец с расчетами погрешностей, англ. Standard Errors).
Нажмем ОК.
Диаграмма
Вычисление погрешности
В столбце E, начиная с ячейки Е11, MS EXCEL разместил формулы для вычисления погрешностей (англ. Standard Errors):
=КОРЕНЬ(СУММКВРАЗН(B9:B11;D9:D11)/3)
Т.е. данная погрешность вычисляется по формуле:
Значения y i – это значения исходного ряда в период i. Значения «yi с крышечкой» - значения ряда, полученного методом скользящего среднего, в тот же в период i. Значение n равно 3, т.к. в нашем случае усреднение производится 3 периода.
Формула погрешности совпадает с выражением среднеквадратичной ошибки (англ. RMSE – Root Mean Squared Errors, квадратный корень из среднего значения квадратов ошибок), но вычисленной не для всей выборки (ряда), а только на интервале сглаживания (в нашем случае за 3 периода).
Обычно рассчитывается 2 типа ошибок: ошибка сглаживания (ошибка подгонки модели; англ. fitting errors или residuals) и ошибка прогнозирования (forecast errors).
Погрешности, вычисленные надстройкой Пакет анализа, являются ошибками прогнозирования. Эту погрешность можно использовать, чтобы рассчитать интервал предсказания (prediction interval). Про вычисление прогнозного значения и его интервала предсказания см. статью Прогнозирование методом скользящего среднего .
Отметим, что MS EXCEL вычисляет целый массив погрешностей (столбец Е), но для построения интервала предсказания необходимо только последнее значение.
Недостатком формул, получаемых с помощью Пакета анализа, является то, что при изменении количества периодов усреднения приходится перезапускать расчет, вызывая Надстройку заново.
В файле примера на листе Формулы создана форма для автоматического пересчета скользящего среднего в зависимости от количества периодов.
Значения сглаженного ряда вычисляются с помощью формулы:
= ЕСЛИ(A11
в ячейке D8 содержится количество периодов усреднения. Про функцию СМЕЩ() можно прочитать в этой статье .
Погрешности вычисляются по формуле:
= КОРЕНЬ(СУММКВРАЗН(СМЕЩ(B11;-$D$8+1;;$D$8);СМЕЩ(C11;-$D$8+1;;$D$8))/$D$8)
Выбор количества периодов усреднения для удобства осуществляется с помощью элемента управления Счетчик .
На диаграмме с помощью линии тренда можно построить график Скользящего среднего с заданным количеством периодов усреднения.
Используем данные файла примера на листе Формулы . Сначала построим ряд скользящего среднего с 5-ю периодами усреднения с помощью формул.
Теперь построим линию тренда, которая совпадет с красным графиком «Сглаженный ряд». Для этого:
• Щелкните диаграмму, чтобы выделить ее.
• Выберите ряд данных, к которому нужно добавить график скользящего среднего (синий график).
• На вкладке Макет в группе Анализ нажмите кнопку Линия тренда и выберите пункт Дополнительные параметры линии тренда.
• В открывшемся окне выберите Линейная фильтрация и установите в поле Точки значение 5.
После закрытия окна будет выведен график скользящего среднего, полностью совпадающий с красным графиком, ранее построенным с помощью формул.
Примечание : У метода Скользящее среднее есть несколько модификаций, которые рассмотрены в одноименной статье.
Метод скользящей средней – это статистический инструмент, с помощью которого можно решать различного рода задачи. В частности, он довольно часто используется при прогнозировании. В программе Excel для решения целого ряда задач также можно применять данный инструмент. Давайте разберемся, как используется скользящая средняя в Экселе.
Применение скользящей средней
Смысл данного метода состоит в том, что с его помощью происходит смена абсолютных динамических значений выбранного ряда на средние арифметические за определенный период путем сглаживания данных. Этот инструмент применяется для экономических расчетов, прогнозирования, в процессе торговли на бирже и т.д. Применять метод скользящей средней в Экселе лучше всего с помощью мощнейшего инструмента статистической обработки данных, который называется Пакетом анализа. Кроме того, в этих же целях можно использовать встроенную функцию Excel СРЗНАЧ.
Способ 1: Пакет анализа
Пакет анализа представляет собой надстройку Excel, которая по умолчанию отключена. Поэтому, прежде всего, требуется её включить.
- Перемещаемся во вкладку «Файл». Делаем щелчок по пункту «Параметры».
После этого действия пакет «Анализ данных» активирован, и соответствующая кнопка появилась на ленте во вкладке «Данные».
А теперь давайте рассмотрим, как непосредственно можно использовать возможности пакета Анализ данных для работы по методу скользящей средней. Давайте на основе информации о доходе фирмы за 11 предыдущих периодов составим прогноз на двенадцатый месяц. Для этого воспользуемся заполненной данными таблицей, а также инструментами Пакета анализа.
-
Переходим во вкладку «Данные» и жмем на кнопку «Анализ данных», которая размещена на ленте инструментов в блоке «Анализ».
В поле «Интервал» следует указать интервал обработки значений методом сглаживания. Для начала давайте установим значение сглаживания в три месяца, а поэтому вписываем цифру «3».
В поле «Выходной интервал» нужно указать произвольный пустой диапазон на листе, где будут выводиться данные после их обработки, который должен быть на одну ячейку больше входного интервала.
Также следует установить галочку около параметра «Стандартные погрешности».
При необходимости, можно также установить галочку около пункта «Вывод графика» для визуальной демонстрации, хотя в нашем случае это и не обязательно.
В поле «Интервал» ставим цифру «2».
В поле «Выходной интервал» указываем адрес нового пустого диапазона, который, опять же, должен быть на одну ячейку больше входного интервала.
Способ 2: использование функции СРЗНАЧ
В Экселе существует ещё один способ применения метода скользящей средней. Для его использования требуется применить целый ряд стандартных функций программы, базовой из которых для нашей цели является СРЗНАЧ. Для примера мы будем использовать все ту же таблицу доходов предприятия, что и в первом случае.
Как и в прошлый раз, нам нужно будет создать сглаженные временные ряды. Но на этот раз действия будут не настолько автоматизированы. Следует рассчитать среднее значение за каждые два, а потом три месяца, чтобы иметь возможность сравнить результаты.
Прежде всего, рассчитаем средние значения за два предыдущих периода с помощью функции СРЗНАЧ. Сделать это мы можем, только начиная с марта, так как для более поздних дат идет обрыв значений.
-
Выделяем ячейку в пустой колонке в строке за март. Далее жмем на значок «Вставить функцию», который размещен вблизи строки формул.
Обязательным является только один аргумент.
Мы произвели расчет прогноза при помощи метода скользящей средней двумя способами. Как видим, данную процедуру намного проще выполнить с помощью инструментов Пакета анализа. Тем не менее некоторые пользователи не всегда доверяют автоматическому расчету и предпочитают для вычислений использовать функцию СРЗНАЧ и сопутствующие операторы для проверки наиболее достоверного варианта. Хотя, если все сделано правильно, на выходе результат расчетов должен получиться полностью одинаковым.
Отблагодарите автора, поделитесь статьей в социальных сетях.
Читайте также: