Как рассчитать ema в excel

Exponential moving average option is a data smoothing approach which gives exponentially decreasing weights to values over time. In this guide, we’re going to show you how to calculate exponential moving average in Excel.

Download Workbook

What is exponential moving average (EMA)?

Exponential moving average (EMA), also known as exponentially weighted moving average (EWMA) is form of weighted moving average which assumes that more current data points are more important since they are more relevant than older data points. The difference is that the weights are distributed exponentially.

The formula of EMA is the following where,

• the coefficient α a constant smoothing factor between 0 and 1 and
• n is the period.

You can use simple moving average when calculating the first EMA (n), since there will not be a previous time (n-1).

You can calculate the α easily by the following formula. The N represents the number of data values in a period.

Let’s use these formulas on an example to calculate exponential moving average in Excel.

Calculating exponential moving average

The first step is to find the α value. Simply apply the formula into your worksheet. In our example, we are calculating a three-point EMA, which is defined in cell C4. Thus, the α value for three-points is 0.5, 2 / (3 + 1).

Since our period includes three points, we need the average of the first 3 values. Use the AVERAGE formula to find it.

Finally, we can use the EMA formula starting from the 4^th data point.

Previous EMA (n-1): C10

Price (n): B11

Next step is to copy the formula down along with data. The important point is to avoid the $ signs in the range reference to keep the range relative while keeping weight range absolute. Excel adjusts relative references based on the formula cell’s location. Thus, you do not need to update your formula each time.

Learn how to calculate the exponential moving average in Excel and VBA, and get a free web-connected spreadsheet.

The spreadsheet retrieves stock data from Yahoo Finance, calculates EMA (over your chosen time window) and plots the results.

The download link is at the bottom. The VBA can be viewed and edited; it’s completely free.

But first disover why EMA is important to technical traders and market analysts.

Historical stock price charts are often polluted with a lot of high-frequency noise. This often obscures major trends. Moving averages help smooth out these minor fluctuations, giving you greater insight into the overall market direction.

The exponential moving average places greater importance on more recent data. The larger  the time period, the lower the importance of the most recent data.

EMA is defined by this equation.

where P is the price and T is the time period. Essentially, the EMA today is the sum of

• today’s price (multiplied by a weight)
• and yesterday’s EMA (multiplied by 1-weight)

You need to kickstart the EMA calculation with an initial EMA (EMA₀). This is usually a simple moving average of length T.

The chart above, for example, gives the EMA of Microsoft between 1st January 2013 and 14th January 2014.

Technical traders often use the cross-over of two moving averages – one with a short timescale and another with a long timescale – to generate buy/sell signals. Often 12- and 26-day moving averages are used.

When the shorter moving average rises above the longer moving average, the market is trending updwards; this is a buy signal. However, when the shorter moving averages falls below the long moving average, the market is falling; this is a sell signal.

Let’s first learn how to calculate EMA using worksheet functions. After that we’ll discover how to use VBA to compute EMA (and automatically plot charts!)

Calculate EMA in Excel with Worksheet Functions

Step 1. Let’s say that we want to calculate the 12-day EMA of Exxon Mobil’s stock price. We first need to get historic stock prices – you can do that with this bulk stock quote downloader.

Step 2. Calculate the simple average of the first 12 prices with Excel’s Average() function. In the screengrab below, in cell C16 we have the formula =AVERAGE(B5:B16) where B5:B16 contains the first 12 close prices

Step 3. Just below the cell used in Step 2, enter the EMA formula above

Step 4. Copy the formula entered in Step 3 down to calculate the EMA of the entire set of stock prices.

There you have it! You’ve succesfully calculated an important technical indicator, EMA, in a spreadsheet.

Calculate EMA with VBA

Now let’s mechanize the calculations with VBA, including the automatic creation of plots. I won’t show you the full VBA here (it’s available in the spreadsheet below), but we’ll discuss the most critical code.

Step 1. Download historical stock quotes for your ticker from Yahoo Finance (using CSV files), and load them into Excel or use the VBA in this spreadsheet to get historical quotes straight into Excel. Your data may look something like this:

Step 2. This is where we need to excercise a few braincells – we need to implement the EMA equation in VBA. We can use R1C1 style to programatically enter formulae into individual cells. Examine the code snippet below.

1
2

Sheets("Data").Range("h" & EMAWindow + 1) = "=average(R[-" & EMAWindow - 1 & "]C[-3]:RC[-3])"
Sheets("Data").Range("h" & EMAWindow + 2 & ":h" & numRows).FormulaR1C1 = "=R[0]C[-3]*(2/(EMAWindow + 1)) + R[-1]C[0] * (1-(2/(EMAWindow+1)))"

• EMAWindow is a variable that equals the desired time window
• numRows is the total number of data points + 1 (the “+ 1” is because we’re assuming that the actual stock data starts on row 2)
• the EMA is calculated in column h

Assuming that EMAWindow = 5 and numrows = 100 (that is, there are 99 data points)

• the first line places a formula in cell h6 that calculates the arithmetic average of the first 5 historical data points

• The second line places formulas in cells h7:h100 that calculates the EMA of the remaining 95 data points

Step 3
This VBA function creates a plot of the close price and EMA.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31

Set EMAChart = ActiveSheet.ChartObjects.Add(Left:=Range("a12").Left, Width:=500, Top:=Range("a12").Top, Height:=300)
With EMAChart.Chart
 
    .Parent.Name = "EMA Chart"
 
    With .SeriesCollection.NewSeries
        .ChartType = xlLine
        .Values = Sheets("data").Range("e2:e" & numRows)
        .XValues = Sheets("data").Range("a2:a" & numRows)
        .Format.Line.Weight = 1
        .Name = "Price"
    End With
 
    With .SeriesCollection.NewSeries
        .ChartType = xlLine
        .AxisGroup = xlPrimary
        .Values = Sheets("data").Range("h2:h" & numRows)
        .Name = "EMA"
        .Border.ColorIndex = 1
        .Format.Line.Weight = 1
    End With
 
    .Axes(xlValue, xlPrimary).HasTitle = True
    .Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = "Price"
    .Axes(xlValue, xlPrimary).MaximumScale = WorksheetFunction.Max(Sheets("Data").Range("e2:e" & numRows))
    .Axes(xlValue, xlPrimary).MinimumScale = Int(WorksheetFunction.Min(Sheets("Data").Range("e2:e" & numRows)))
    .Legend.Position = xlLegendPositionRight
    .SetElement (msoElementChartTitleAboveChart)
    .ChartTitle.Text = "Close Price & " & EMAWindow & "-Day EMA"
 
End With

Get this spreadsheet for the full working implementation of the EMA calculator with automatic download of historical data.

Download Excel Spreadsheet to Calculate EMA with VBA

Как и многие из моих руководств по Excel, это также вдохновлено одним из вопросов, которые я получил от друга. Она хотела рассчитать скользящее среднее в Excel, и я попросил ее поискать его в Интернете (или посмотреть видео на YouTube об этом).

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

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

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

Примечание. Я не являюсь экспертом в области статистики, и в этом руководстве я не собираюсь описывать все, что касается скользящих средних. Я лишь хочу показать вам, как рассчитывать скользящие средние в Excel (с кратким описанием значений скользящих средних).

Что такое скользящая средняя?

Я уверен, что вы знаете, каково среднее значение.

Если у меня есть данные о температуре за три дня, вы можете легко сказать мне среднее значение за последние три дня (подсказка: для этого вы можете использовать функцию СРЕДНЕЕ в Excel).

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

Например, в день 3, если я спрошу вас о 3-дневной скользящей средней температуре, вы дадите мне среднее значение температуры для дней 1, 2 и 3. А если в день 4 я спрошу вас о 3-дневной скользящей средней температуре. , вы дадите мне среднее значение для дней 2, 3 и 4.

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

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

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

Типы скользящих средних

Есть три типа скользящих средних:

• Простая скользящая средняя (SMA)
• Взвешенная скользящая средняя (WMA)
• Экспоненциальная скользящая средняя (EMA)

Простая скользящая средняя (SMA)

Это простое среднее значение точек данных за заданный период времени.

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

Это может быть достигнуто путем усреднения точек данных за заданную продолжительность. В Excel это легко сделать с помощью функции СРЕДНЕЕ (она рассматривается далее в этом руководстве).

Взвешенное скользящее среднее (WMA)

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

Температура дня 10, скорее всего, будет лучшим индикатором тенденции по сравнению с днем ​​1 (поскольку температура падает с каждым днем).

Итак, нам будет лучше, если мы будем больше полагаться на ценность 10-го дня.

Чтобы это отразилось в нашем скользящем среднем, вы можете придать больший вес последним данным и меньший — прошлым. Таким образом, вы по-прежнему получаете тренд, но с большим влиянием последних данных.

Это называется взвешенной скользящей средней.

Экспоненциальная скользящая средняя (EMA)

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

Его также называют экспоненциально-взвешенной скользящей средней (EWMA).

Разница между WMA и EMA заключается в том, что с WMA вы можете назначать веса на основе любых критериев. Например, в 3-точечном скользящем среднем вы можете присвоить возраст 60% веса последней точке данных, 30% средней точке данных и 10% самой старой точке данных.

В EMA больший вес присваивается последнему значению, а вес продолжает экспоненциально снижаться для более ранних значений.

Хватит лекции по статистике.

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

Расчет простой скользящей средней (SMA) с использованием пакета инструментов анализа данных в Excel

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

Это называется Пакет инструментов для анализа данных.

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

Если у вас уже есть опция «Анализ данных» на вкладке «Данные», пропустите приведенные ниже шаги и просмотрите шаги по вычислению скользящих средних.

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

1. Перейдите на вкладку Файл.
2. Нажмите на Параметры
3. В диалоговом окне «Параметры Excel» нажмите «Надстройки».
4. В нижней части диалогового окна выберите в раскрывающемся списке Надстройки Excel и нажмите «Перейти».
5. В открывшемся диалоговом окне надстройки отметьте опцию Analysis Toolpak.
6. Щелкните ОК.

Вышеупомянутые шаги позволят включить Data Analysis Toolpack, и теперь вы увидите эту опцию на вкладке «Данные».

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

Ниже приведены шаги по использованию анализа данных для расчета простого скользящего среднего:

1. Перейдите на вкладку «Данные».
2. Нажмите на опцию анализа данных
3. В диалоговом окне «Анализ данных» выберите параметр «Скользящее среднее» (возможно, вам придется немного прокрутить экран, чтобы его достичь).
4. Щелкните ОК. Откроется диалоговое окно «Скользящее среднее».
5. Во Входном диапазоне выберите данные, для которых вы хотите вычислить скользящее среднее (B2: B11 в этом примере).
6. В поле «Интервал» введите 3 (поскольку мы рассчитываем трехточечную скользящую среднюю).
7. В области вывода введите ячейку, в которой вы хотите получить результаты. В этом примере я использую C2 в качестве диапазона вывода.
8. Нажмите ОК.

Вышеупомянутые шаги дадут вам результат скользящего среднего, как показано ниже.

Обратите внимание, что первые две ячейки в столбце C имеют результат как # N / A error. Это потому, что это трехточечная скользящая средняя, ​​и для получения первого результата требуется как минимум три точки данных. Таким образом, фактические значения скользящего среднего начинаются после третьей точки данных.

Вы также заметите, что весь этот пакет инструментов анализа данных применяет к ячейкам формулу СРЕДНЕГО. Так что, если вы хотите сделать это вручную без инструментария анализа данных, вы, безусловно, можете это сделать.

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

Расчет скользящих средних (SMA, WMA, EMA) с использованием формул в Excel

Вы также можете рассчитать скользящие средние по формуле СРЕДНИЙ.

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

Кроме того, Data analysis Toolpak дает только простую скользящую среднюю (SMA), но если вы хотите рассчитать WMA или EMA, вам нужно полагаться только на формулы.

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

Предположим, у вас есть набор данных, показанный ниже, и вы хотите рассчитать 3-точечный SMA:

В ячейке C4 введите следующую формулу:

= СРЕДНИЙ (B2: B4)

Скопируйте эту формулу для всех ячеек, и она даст вам SMA на каждый день.

Помните: при вычислении SMA с использованием формул необходимо убедиться, что ссылки в формуле являются относительными. Это означает, что формула может быть = СРЕДНЕЕ (B2: B4) или = СРЕДНЕЕ ($ B2: $ B4), но не может быть = СРЕДНЕЕ ($ B $ 2: $ B $ 4) или = СРЕДНЕЕ (B $ 2: B $ 4. ). Часть ссылки с номером строки должна быть без знака доллара. Вы можете узнать больше об абсолютных и относительных ссылках здесь.

Поскольку мы вычисляем 3-точечное простое скользящее среднее (SMA), первые две ячейки (для первых двух дней) пусты, и мы начинаем использовать формулу с третьего дня и далее. При желании вы можете использовать первые два значения как есть и использовать значение SMA, начиная с третьего.

Расчет взвешенного скользящего среднего с использованием формул

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

Например, предположим, что вам нужно рассчитать 3-точечный WMA для приведенного ниже набора данных, где 60% веса дается последнему значению, 30% — предыдущему и 10% — предыдущему.

Для этого введите следующую формулу в ячейку C4 и скопируйте для всех ячеек.

= 0,6 * B4 + 0,3 * B3 + 0,1 * B2

Поскольку мы вычисляем 3-точечное взвешенное скользящее среднее (WMA), первые две ячейки (для первых двух дней) пусты, и мы начинаем использовать формулу с третьего дня и далее. При желании можно использовать первые два значения как есть и использовать значение WMA, начиная с третьего.

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

Экспоненциальная скользящая средняя (EMA) придает больший вес последнему значению, а веса продолжают экспоненциально снижаться для более ранних значений.

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

EMA = [Последнее значение - Предыдущее значение EMA] * (2 / N + 1) + Предыдущая EMA

… Где N будет 3 в этом примере (поскольку мы вычисляем трехточечную EMA)

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

Предположим, у вас есть следующий набор данных и вы хотите рассчитать трехпериодную EMA:

В ячейке C2 введите то же значение, что и в B2. Это потому, что нет предыдущего значения для расчета EMA.

В ячейке C3 введите приведенную ниже формулу и скопируйте для всех ячеек:

= (B3-C2) * (2/4) + C2

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

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

Добавление линии тренда скользящего среднего на столбчатый график

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

Предположим, у вас есть набор данных, как показано ниже:

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

1. Выберите набор данных (включая заголовки)
2. Щелкните вкладку Вставка
3. В группе «Диаграмма» щелкните значок «Вставить столбец или гистограмму».
4. Нажмите на опцию Clustered Column chart. Это вставит диаграмму в рабочий лист.
5. Выделив диаграмму, щелкните вкладку «Дизайн» (эта вкладка появляется только при выборе диаграммы).
6. В группе «Макеты диаграмм» нажмите «Добавить элемент диаграммы».
7. Наведите курсор на параметр «Линия тренда», а затем нажмите «Дополнительные параметры линии тренда».
8. На панели «Форматирование линии тренда» выберите параметр «Скользящее среднее» и установите количество периодов.

Вот и все! Вышеупомянутые шаги добавят движущуюся линию тренда к вашей столбчатой ​​диаграмме.

Если вы хотите вставить более одной линии тренда скользящего среднего (например, одну для 2 периодов и одну для 3 периодов), повторите шаги с 5 по 8).

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

Форматирование линии тренда скользящего среднего

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

Вот несколько вещей, которые вы можете отформатировать на линии тренда:

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

Чтобы отформатировать линию тренда скользящего среднего, щелкните ее правой кнопкой мыши и выберите параметр «Форматировать линию тренда».

Откроется панель «Форматировать линию тренда» справа. Эта панель, как и все параметры форматирования (в разных разделах — «Заливка и линия», «Эффекты» и «Параметры линии тренда»).