Расчет экспоненциальной скользящей средней по формуле Excel скачать

Экспоненциальное скользящее среднее EMA (Exponential Moving Average) – это индикатор для технического анализа статистических данных о изменения динамики цен на финансовых рынках. Главное отличие EMA от стандартной скользящей средней (SMA или просто MA) заключается в том, что EMA придает больший вес более свежим данным.

Рассмотрим Excel формулы для построения кривых индикаторов EMA и MA. В качестве примера создадим шаблон для практического использования экспоненциальной скользящей средней на графике цен в Excel. Также рассмотрим возможности Excel для разработки собственных индикаторов с настройками, основанных на формулах со статистическими функциями. Некоторые стратегии будут даже достойными для безопасной торговли на фьючерсах. Готовый шаблон с примерами формул можно скачать одним файлом Excel в конце статьи.

Пример расчета формулы экспоненциальной, скользящей средней в Excel

Для вычисления EMA используется рекурсивная формула:

${EMA}_{t} = (P_{t} \times α) + ({EMA}_{t-1} \times (1 - α))$

Где:

EMA_t — текущее значение EMA;
P_t — текущее значение цены (или другого показателя);
α (альфа) — коэффициент сглаживания:

$α = \frac{2}{n + 1}$

Где:

n — период EMA (например, 10, 20 или 50 дней);
EMA {t-1} — предыдущее значение EMA.

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

Из сайта популярного интернет-ресурса о финансовых рынках мы загрузили в Excel диапазон A5:H7 таблицу с ценами актива на фьючерсных рынках за последние пару месяцев:

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

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

Первое что нам необходимо сделать для простого освоения данного материала – это сначала составить формулу для построения стандартной кривой MA на основе текущих цен. EMA – это усовершенствованная модель индикатора скользящей средней. Для их сравнения посмотрите на таблицу технических характеристик ниже.

В чем Отличия индикаторов EMA и SMA

Характеристика	EMA	SMA
Вес данных	Больше вес на последние значения	Равный вес для всех значений
Реакция на изменения	Быстрее реагирует на новые данные	Медленнее реагирует на изменения
Гладкость линии	Менее сглаженная	Более сглаженная

Истинна познается в сравнении! От теории сразу переходим к практике.

Как рассчитать скользящую среднюю SMA в Excel

В ячейке I5 добавим заголовок для нового столбца к исходной таблице и назовем его «Moving Average». Над этим же столбцом в ячейке I4 введем числовое значение, например – 7. Мы будем использовать это число в качестве указания количество периодов, на основе которых будет формироваться кривая индикатора MA. Это число будет передаваться в аргумент формулы, но оно выведено отдельно в значение ячейки, для того чтобы можно было изменять количество периодов и настраивать анализ.

Далее заполняем диапазон ячеек последнего столбца I6:I74 следующей формулой расчета стандартной скользящей средней в Excel:

=AVERAGE(OFFSET(B6,0,0,$I$4,1))

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

Как рассчитать экспоненциальную скользящую среднюю по формуле Excel

Добавляем еще один столбец и называем его заголовок – EMA. Первая ячейка должна содержать такое же значение, как и первая ячейка соседнего столбца MA. Поэтому просто указываем ссылку на ячейку I6.

Формула для вычислений значений EMA будет содержать еще одну настройку, кроме периода – это сглаживание. Один из аргументов формулы будет ссылаться на отдельную ячейку, чтобы предоставить пользователю возможность настраивать этот параметр. Поэтому в ячейку J2 введем значение 1. При увеличении этого значения кривая экспоненциальной скользящей средней будет сглаживаться для настроек разных торговых стратегий.

Обрабатывать этот параметр будет специальная формула вычисления параметра альфа в отдельной ячейке J4. На этот альфа параметр и будут ссылаться аргументы формулы EMA в Excel.

Для заполнения всех остальных ячеек последнего столбца в диапазоне J7:J74 теперь используем формулу расчета экспоненциальной скользящей средней в Excel:

=(I7*$J$4+J6*(1-$J$4))

Формула расчета экспоненциальной скользящей средней

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

Как сделать график японские свечи в Excel

Каждый Чарт типа Stock не позволяет в Excel создавать комбинированные графики. Поэтому будем использовать два отдельных чарта, наложенных на разных слоях. Только важно соблюдать точность совпадения в областях Plot Area при наложении и совпадении осей XY. Далее рассмотрим, как это сделать.

Сначала создадим Stock Chart на основе четырех столбцов цен:

Цена открытия.
Максимум цены бара.
Минимум цены бара.
Цена закрытия.

Как построить график японских свечей в Excel? – это очень просто! Выполните следующие простые действия. Выделите диапазон ячеек C5:F74 и выберите инструмент: Insert – Charts – Stock – Open-High-Low-Close:

График для свечного анализа в Excel – готов! Осталось его только настроить.

Настройка диаграммы японские свечи в Excel

Сначала настроим оптимальные значения для оси Y. Нам нужно определить максимальное и минимальное значение цены на графике. Максимальное значение находится в столбце High, а минимальное – Low соответственно.

По этим значением ориентировочно указываем минимальное и максимально означение для оси Y на Сток Чарте. Делаем клик правой кнопкой мышки по оси Y и из появившегося контекстного меню выбираем опцию: Format Axis.

Для красоты изменим числовые значения дней по оси X на даты. Для этого кликните левой кнопкой мышки по сток-чарту, чтобы сделать его активным и выберите инструмент: Chart Design – Data – Select Data.

В появившемся окне нажмите на кнопку Edit и в поле ввода дочернего окна настроек инструмента Axis label range укажите ссылку на диапазон ячеек с датами цен:

=Sheet1!$A$6:$A$74

Также не забудьте изменить настройки подписи данных для оси X. Вызовите окно настроек Format Axis и в разделе Labels отмечаем опцию Specify interval unit. После чего поле ввода станет активным. Там же указываем параметр 1. Теперь каждая свеча будет подписана на чарте своей собственной датой на оси X.

Обратите внимание! Порядок дат едет справа налево, как и все значения на чарте. Это общепринятый формат стиля оформления чартов для фьючерсных рынков. Самые новые актуальные значения должны находиться справой стороны. Данный факт следует учитывать при наложении чарта кривой EMA! Там нужно будет сделать реверс в настройках оси X, чтобы получить обратный порядок построения кривой линии.

График индикатора экспоненциальной скользящей средней в Excel

Как сделать график экспоненциальной скользящей средней в Excel? – очень легко и просто! Выделите диапазон ячеек, где формула EMA возвращает нам свои значения в последнем столбце таблицы J6:J7 и выберите инструмент: Insert – Charts – 2-D Line.

EMA Chart – готов! Теперь нам нужно его настроить, чтобы правильно и без ошибок наложить на график свечей, который мы создали ранее.

Сначала мы настроим минимальные и максимальные значения на оси Y, так же как предыдущий раз: MIN 75 000 и MAX 110 000.

Внимание! Особенно важно сделать обратный порядок значений по оси X. Для этого следует включить опцию Categories in reverse order в настройках формата оси X:

График свечного анализа формируется справа налево, как и все сток-чарты для технического анализа цен на фьючерсных рынках. Поэтому наш индикатор EMA в Excel также должен быть построен справа налево.

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

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

При наложении необходимо сделать так чтобы размеры области построения визуализации (Plot Area) полностью и точно совпадали на всех чартах.

Индикатор экспоненциальной скользящей средней в Excel – ГОТОВ!

Как построить канала EMA на графике Excel

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

Ширину канала мы также должны иметь возможность настраивать. Поэтому выведем в отдельную ячейку значение для установки ширины канала в K3.

В ячейках ниже находятся формулы для расчета отклонения уровней поддержки и сопротивления от скользящей средней.

Например, если ширина канала в ячейке K3 указана 15% то:

в ячейке K4 формула =100%-K3 возвращает значение 85%;
в ячейке L4 формула =100%+K3 возвращает значение 115%.

Параметры формул для формирования канала

Теперь заполняем оба столбца простой формулой, где перемножаются значения EMA на процент отклонения полученный в K4 для уровня поддержки (формула =J6*$K$4) и в ячейке L4 для уровня сопротивления – соответственно (формула =J6*$L$4). Заполняем полностью все диапазоны K6:K74 и L6:L74:

Формулы для построения канала на графике

На основе полученных данных из двух последних столбцов строим новые EMA с отклонениями на 15% вниз и вверх. А также добавляем на чарт 2 новых кривых и получаем канал EMA:

Добавления уровней поддержки и сопротивления

На первый взгляд, казалось бы, что может быть проще установил одинаковые отступы от среднестатистического значения для формирования диапазона и все что за его границами неучитываем и воспринимаем за статистические выбросы. Но для практического анализа бизнес-данных так делать нельзя!!! Такой подход приведет к накоплению новых ошибок на этапе планирования и на пути реализации бизнес-стратегии. Для качественного решения данной задачи нужно использовать КВАРТИЛЬ – более сложную функцию. Как рассчитать квартиль в Excel и что он показывает – описано на этом сайте в разделе функции.

Формулы алгоритмов индикаторов в Excel для безопасной торговли на фьючерсах

По такому же принципу можно создавать в Excel не только EMA а и свои собственные индикаторы, основанные на своих собственных формулах.

Статистическая функция МЕДИАНА

Очень упрощенный пример можно использовать функцию МЕДИАНА:

Функция МЕДИАНА в отличии от функции СРЕДЗНАЧ вычисляет среднее значение особым образом. Она сортирует все значения по возрастанию и возвращает то которое находится ровно по середине. Такой нехитрый метод дает возможность отфильтровать статистические выбросы. А значит на техническом анализе – это эффективный инструмент для исключения ложный пробоев уровней поддержки и сопротивления. СРЕДЗНАЧ рассчитывает арифметическим методом и один существенный статистический выброс может сильно исказить информацию о рациональном среднем значении. Иными словами, если один человек ест говяжий пэтти, а другой человек ест только хлебную булку, то с точки зрения функции СРЕДЗНАЧ оба едят бургер – что весьма далеко от истины.

Формула со статистической функцией МОДА для анализа профиля рынка

Очень интересную функцию можно использовать в Excel для анализа рыночного профиля. Функция МОДА – определяет самую популярную цену на графике за определенный период. Кроме того, в этой функции можно настраивать чувствительность к рыночному шуму на фьючерсах.

Изменя значение в ячейке I2, где указан коэффициент чувствительности, формула фильтрует шум рынка на графике фьючерсной цены актива.

Анализ рыночного профиля основан на определении уровня ценовой моды – место, где наиболее популярной была цена для конкретного временного диапазона. Известный факт, что мода текущего периода никогда не находится на одном и том же уровне, где была мода на предыдущем временном периоде. Мода всегда меняется.

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

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

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

Дашборд экспоненциальной скользящей средней для анализа рынка

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

Скачать индикаторы экспоненциальной скользящей средней в Excel download file

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