Smoothing a series using the moving average method. Moving Average Method in Microsoft Excel
Extrapolation - this is the method scientific research, which is based on the dissemination of past and present trends, patterns, connections to the future development of the forecast object. Extrapolation methods include moving average method, exponential smoothing method, least squares method.
Moving average method is one of the well-known time series smoothing methods. Using this method, it is possible to eliminate random fluctuations and obtain values that correspond to the influence of the main factors.
Smoothing using moving averages is based on the fact that random deviations in average values cancel each other out. This occurs due to the replacement of the initial levels of the time series with an arithmetic average within the selected time interval. The resulting value refers to the middle of the selected time interval (period).
Then the period is shifted by one observation, and the calculation of the average is repeated. In this case, the periods for determining the average are taken to be the same all the time. Thus, in each case considered, the average is centered, i.e. is referred to the midpoint of the smoothing interval and represents the level for this point.
When smoothing a time series with moving averages, all levels of the series are involved in the calculations. The wider the smoothing interval, the smoother the trend. The smoothed series is shorter than the original by (n–1) observations, where n is the value of the smoothing interval.
At large values of n, the variability of the smoothed series is significantly reduced. At the same time, the number of observations is noticeably reduced, which creates difficulties.
The choice of smoothing interval depends on the objectives of the study. In this case, one should be guided by the period of time in which the action takes place, and, consequently, the elimination of the influence of random factors.
This method is used for short-term forecasting. His working formula:
An example of using the moving average method to develop a forecast
Task . There are data characterizing the unemployment rate in the region, %
- Construct a forecast of the unemployment rate in the region for November, December, January using the following methods: moving average, exponential smoothing, least squares.
- Calculate the errors in the resulting forecasts using each method.
- Compare the results and draw conclusions.
Solution using the moving average method
To calculate the forecast value using the moving average method, you must:
1. Determine the value of the smoothing interval, for example equal to 3 (n = 3).
2. Calculate the moving average for the first three periods
m Feb = (Jan + Ufev + U March)/ 3 = (2.99+2.66+2.63)/3 = 2.76
We enter the resulting value into the table in the middle of the period taken.
Next, we calculate m for the next three periods: February, March, April.
m March = (Ufev + Umart + Uapr)/ 3 = (2.66+2.63+2.56)/3 = 2.62
Next, by analogy, we calculate m for every three next to each other worthwhile periods and enter the results into a table.
3. Having calculated the moving average for all periods, we build a forecast for November using the formula:
where t + 1 – forecast period; t – period preceding the forecast period (year, month, etc.); Уt+1 – predicted indicator; mt-1 – moving average for two periods before the forecast; n – number of levels included in the smoothing interval; Уt – actual value of the phenomenon under study for the previous period; Уt-1 – the actual value of the phenomenon under study for two periods preceding the forecast one.
U November = 1.57 + 1/3 (1.42 – 1.56) = 1.57 – 0.05 = 1.52
We determine the moving average m for October.
m = (1.56+1.42+1.52) /3 = 1.5
We are making a forecast for December.
U December = 1.5 + 1/3 (1.52 – 1.42) = 1.53
We determine the moving average m for November.
m = (1.42+1.52+1.53) /3 = 1.49
We are making a forecast for January.
Y January = 1.49 + 1/3 (1.53 – 1.52) = 1.49
We enter the obtained result into the table.
We calculate the average relative error using the formula:
ε = 9.01/8 = 1.13% forecast accuracy high.
Next, we will solve this problem using methods exponential smoothing And least squares . Let's draw conclusions.
Moving average method a method of studying the main trend in the development of a phenomenon in dynamics.
The essence of the moving average method is that the average level is calculated from a certain number of the first in order levels of the series, then the average level from the same number of levels, starting from the second, then starting from the third, etc. Thus, when calculating middle level seem to “slide” along series of dynamics from its beginning to its end, each time discarding one level at the beginning and adding the next one.
The average of an odd number of levels refers to the middle of the interval. If the smoothing interval is even, then assigning the average to a specific time is impossible; it refers to the middle between dates. In order to correctly assign the average of an even number of levels, centering is used, i.e., finding the average of the average, which is already assigned to a specific date.
Let's demonstrate the use of the moving average using the following example. Example 3.1. Based on data on the yield of grain crops on the farm for 1989–2003. Let's smooth the series using the moving average method.
Dynamics of grain crop yields on the farm for 1989–2003. and calculation of moving averages
1 . Let's calculate three-year rolling amounts. We find the sum of the yield for 1989–1991: 19.5 23.4 25.0 67.9 and write this value in 1991. Then from this sum we subtract the value of the indicator for 1989 and add the indicator for 1992 .: 67.9 – 19.5 22.4 70.8 and we write this value in 1992, etc.
2 . Let's determine three-year moving averages using the simple arithmetic average formula:
We write the resulting value in 1990. Then we take the next three-year moving sum and find the three-year moving average: 70.8: 3 23.6, write the resulting value in 1991, etc.
Four-year rolling amounts are calculated in a similar manner. Their values are presented in column 4 of the table in this example.
Four-year moving averages are determined using the simple arithmetic average formula:
This value will be assigned between two years - 1990 and 1991, i.e. in the middle of the smoothing interval. In order to find four-year centered moving averages, you need to find the average of two adjacent moving averages:
This average will be referenced to 1991. The remaining centered averages are calculated in a similar manner; their values are recorded in column 6 of the table in this example.
4. Analytical alignment method
The equation of the straight line for analytical alignment of the dynamics series has the following form:
Where - leveled (average) level of dynamic series; a 0 , a 1 - parameters of the desired line;t- designation of time.
The least squares method gives a system of two normal equations for finding parameters a 0 and a 1:
Where at initial level series of dynamics ; n number of members of the series.
The system of equations is simplified if the values t choose so that their sum is equal to zero, i.e. move the beginning of time to the middle of the period under consideration.
If then 
Study of the dynamics of social-economic. phenomena and the establishment of the main development trend provide the basis for forecasting (extrapolation) determining the future size of the level of an economic phenomenon. The following extrapolation methods are used:
■ average absolute increase
s/indicator calculated for the expression average speed growth (decrease) social-ec. process. Determined by the formula: 
■ average growth rate;
■ extrapolation based on alignment according to any analytical formula. The method of analytical alignment is a method for studying the dynamics of social and economic. phenomena, allowing us to establish the main trends in their development.
Let's consider the use of the method of analytical straight line alignment to express the main trend onExampleE 4.1. Initial and calculated data for determining the parameters of the straight line equation:
One of the most simple ways solve this problem - use the sliding method average price(moving averages).
The moving average method allows the trader to smooth out and quickly determine the direction of the current trend.
Types of moving averages
There are three different types moving averages, which differ in calculation algorithms, but they are all interpreted in the same way. The difference in the calculations lies in the weight given to the prices. In one case, all prices may have equal weight; in another, more recent data has more weight.
The three most common types of moving averages are:
- simple
- linear weighted
- exponential
Simple Moving Average (SMA, Simple Moving Average)
This is the most common method for calculating moving average prices. You just need to take the sum of the closing prices for a certain period and divide by the number of prices used for the calculation. That is, this is the calculation of a simple arithmetic mean.
For example, for a ten-day simple moving average, we would take the closing prices of the last 10 days, add them together and divide by 10.
As you can see in the picture below, a trader can make moving averages smoother by simply increasing the number of days (hours, minutes) used for calculation. A long period for calculating a moving average is usually used to show a long-term trend.
Many people doubt the advisability of using simple moving average prices, since each point has same value. Critics this method calculations believe that more recent data should have more weight. It is arguments like this that led to the creation of other types of moving averages.
Weighted moving average (WMA, Linear Weighted Average)
This version of the moving average price is the least used indicator of the three. Initially, it was supposed to combat the shortcomings of calculating a simple moving average. To build a weighted moving average, you need to take the sum of closing prices for a certain period, multiplied by a serial number, and divide the resulting number by the number of factors.
For example, to calculate a five-day weighted moving average, you would take today's closing price and multiply it by five, then take yesterday's closing price and multiply it by four, and continue until the end of the period. Then these values must be added and divided by the sum of the factors.
Exponential Moving Average (EMA)
This type of moving average represents a "smoothed" version of the WMA, where more weight is given to recent data. This formula is considered more effective than the one used to calculate the weighted moving average.
You don't need to fully understand how all types of moving averages are calculated. Any modern trading terminal will build you this indicator with any settings.
The formula for calculating the exponential moving average is as follows:
EMA = (closing price – EMA (previous period) * multiplier + EMA (previous period)
The most important thing you should know about the exponential moving average is that it is more responsive to new data compared to the simple moving average. This is a key factor why exponential calculation is more popular and is used by most traders today.
As you can see in the image below, an EMA with a period of 15 reacts faster to price changes than an SMA with the same period. At first glance, the difference does not seem significant, but this impression is deceptive. This difference can play key role during real trading.
Determining the trend using moving averages
Moving averages are used to determine the current trend and when it will reverse, as well as to find resistance and support levels.
Moving averages allow you to very quickly understand which way to go. at the moment the trend is directed.
Look at the image below. Obviously, when the moving average moves below the price chart, we can confidently say that the trend is upward. Conversely, when the moving average is above the price chart, the trend is considered downward.
Another way to determine trend direction is to use two moving averages with different periods for calculation. When the short-term average is above the long-term average, the trend is considered upward. Conversely, when the short-term average is below the long-term average, the trend is considered downward.
Determining trend reversal using moving averages
Trend reversals using moving averages are determined in two ways.
The first is when the average crosses the price chart. For example, when a 50-period moving average crosses the price chart, as in the image below, it often means a change in trend from up to down.
Another option for receiving signals about possible trend reversals is to monitor the intersection of moving averages, short-term and long-term.
For example, in the image below you can see how the moving average with a calculation period of 15 crosses the moving average with a period of 50 from the bottom up, which signals the beginning of an uptrend.
If the periods used to calculate the averages are relatively short (for example, 15 and 35), then their intersections will signal short-term trend reversals. On the other hand, to track long-term trends, much longer periods are used, such as 50 and 200.
Moving averages as support and resistance levels
Another fairly common way to use moving averages is to determine support and resistance levels. For this, moving averages with long periods are usually used.
When the price approaches the support or resistance line, the probability of it “bouncing” from this level is quite high, as can be seen in the image below. If the price breaks the long-term moving average, then there is a high probability that the price will continue to move in the same direction.
Conclusion
Moving averages in technical analysis are one of the most powerful and at the same time simple tools for market analysis. They allow the trader to quickly determine the direction of long-term and short-term trends, as well as support and resistance levels.
Each trader uses his own settings to calculate moving averages. Much depends on the trading style and on what financial market they are used (market, currency exchange, etc.).
Moving averages help technical analysts remove the so-called “noise” of daily price fluctuations from the chart. Traditionally, moving averages are called trend indicators.
This is one of the oldest and most widely known methods for smoothing a time series. Smoothing is a method of local averaging of data in which non-systematic components cancel each other out. Thus, the moving average method is based on the transition from initial values series to their average values over a time interval, the length of which is selected in advance (this time interval is often called a “window”). In this case, the selected interval itself slides along the row.
The series of moving averages obtained in this way behaves more smoothly than the original series, due to the averaging of the deviations of the original series. Thus, this procedure gives an idea of the general trend in the behavior of the series. Its use is especially useful for series with seasonal fluctuations and unclear trend patterns.
Formal definition moving average method for a smoothing window, the length of which is expressed by an odd number p=2m+1. Let there be measurements in time: y 1, y 2 …y n.
Then the moving average method consists of transforming the original time series into a series of smoothed values (estimates) using the formula:
Where p is the window size, j is the serial number of the level in the smoothing window, m is the value determined by the formula: m = (p-1) / 2.
When applying the moving average method, the choice of the smoothing window size p should be based on considerations and reference to the seasonality period for seasonal waves. If the moving average procedure is used to smooth non-seasonal series, then the window is chosen to be three, five or seven. How larger size window, the smoother the moving average graph looks.
Task 2. Based on data on the production of washing machines by the company for 15 months of 2002-2003. you need to smooth the series using the three-term moving average method.
|
Washing cars, thousand pcs. |
Trinomial sliding amounts |
Trinomial moving averages |
|
Taking the data for the first three months, we calculate the three-term sums, and then the average:
etc.
To implement the moving average procedure, you can use the Microsoft Excel function. Bookmarked "Data Analysis" choose "moving average". This mode of operation is used to smooth the levels of a time series based on the simple moving average method. The interval is indicated – i.e. smoothing window size. By default p=3. We get the following output:
|
Washing cars, thousand pcs. |
Trinomial moving averages obtained using the Moving Average tool |
Trinomial moving averages obtained above manually |
|
The graph shows the original series and the smoothed one. Now, for a smoothed series, it is easier and more accurate to determine the main trend (for example, select a trend line).
A common technique for identifying development trends is smoothing the time series. The essence of various smoothing techniques comes down to replacing actual levels of a time series with calculated levels, which are subject to fluctuations to a lesser extent. This contributes to a clearer manifestation of the trend and development. Sometimes smoothing is used as a preliminary step before using other methods for identifying trends.
Moving averages make it possible to smooth out both random and periodic fluctuations, to identify an existing trend in the development of a process, and therefore are an important tool for filtering components of a time series.
If the phenomenon under consideration is linear, then a simple moving average is used. Smoothing algorithm using a simple moving average can be represented as the following sequence of steps:
1. Determine the length of the smoothing interval g, which includes g successive levels of the series (g 2. The entire observation period is divided into sections, with the smoothing interval sliding along the series with a step equal to 1. 3. Arithmetic averages are calculated from the levels of the series that form each section. 4. Replace the actual values of the series located in the center of each section with the corresponding average values. In this case, it is convenient to take the length of the smoothing interval g in the form of an odd number: g=2p+1, because in this case, the obtained moving average values fall on the middle term of the interval. The observations that are taken to calculate the average are called active smoothing section.
With an odd value of g, all levels of the active section can be represented as: yt-p, yt-p+1, ... , yt-1, yt, yt+1, ... , yt+p-1, yt+ p, and the moving average is determined by the formula: The smoothing procedure leads to the complete elimination of periodic oscillations in a time series if the length of the smoothing interval is taken equal to or a multiple of the cycle, the period of oscillations. To eliminate seasonal fluctuations, it would be desirable to use four- and twelve-member moving averages, but in this case the condition of oddity of the length of the smoothing interval will not be met. Therefore, with an even number of levels, it is customary to take the first and last observation in the active section with half the weights: Then, to smooth out seasonal fluctuations when working with time series of quarterly or monthly dynamics, you can use the following moving averages: When using a moving average with the length of the active section g=2p+1, the first and last p levels of the series cannot be smoothed, their values are lost. Obviously, the loss of the values of the last points is a significant drawback, because For the researcher, the latest “fresh” data has the greatest information value. Let's consider one of the techniques that allows you to recover lost values of a time series
. To do this you need: 1. Calculate the average increase in the last active section yt-p, yt-p+1, ... , yt, ... , yt+p-1, yt+p 2. Obtain P smoothed values at the end of the time series by sequentially adding the average absolute increase to the last smoothed value. A similar procedure can be implemented to estimate the first levels of a time series. The simple moving average method is applicable if the graphical representation of a time series resembles a straight line. When the trend of the aligned series has bends, and it is desirable for the researcher to preserve small waves, the use of a simple moving average is inappropriate. If the process is characterized by nonlinear development, then a simple moving average can lead to significant distortions. In these cases, using a weighted moving average is more reliable. When building weighted moving average
at each smoothing section, the value of the central level is replaced by the calculated one, determined by the weighted arithmetic mean formula, i.e. The row levels are weighed. A weighted moving average assigns weight to each level depending on the removal of this level to the level in the middle of the smoothing section. When smoothing using a weighted moving average, polynomials of the second (parabola) or third order are used. Smoothing using a weighted moving average is carried out as follows: for each smoothing section, a polynomial of the form is selected: Y i = a j + a 1 t Y i = a o + a 1 t + a 2 t 2 +… a p t p The parameters of the polynomial are found using the least squares method. In this case, the starting point is transferred to the middle of the smoothing section, for example, if the length of the smoothing intervals = 5, then the level indices of the smoothing section will be equal to: -2, -1, 0, 1, 2. Then the smoothing value for the level located in the middle of the smoothing section will be the value of the parameter a 0. There is no need to re-calculate the weighting coefficients each time for the series levels included in the smoothing section, since they will be the same for each smoothing section, for example, if the smoothing interval includes 5 subsequent series levels and the alignment is performed using a parabola, then the parabola coefficients are found using the method of least squares, given that t = 0. The least squares method in this situation gives the following system of equations: To find the parameter a0 use equations 1 and 3 - 34-=5*34a0-10*10a0 34-=a0(170-100) a0= If the length of the smoothing interval is 7, the weighting factors are as follows: Let us note the important properties of the given scales: 1) They are symmetrical relative to the central level. 2) The sum of the weights, taking into account the common factor taken out of brackets, is equal to one. 3) The presence of both positive and negative weights allows the smoothed curve to preserve various bends of the trend curve. There are techniques that allow, with the help of additional calculations, to obtain smoothed values for P of the initial and final levels of the series with the length of the smoothing interval g=2p+1. Weighting coefficients for smoothing using second- and third-order polynomials Topic 5: Methods for measuring and studying the stability of time series. o stability of series levels;
o trend stability.
According to statistical theory, a statistical indicator contains elements of the necessary and random. Necessity manifests itself in the form of a time series trend, and randomness in the form of fluctuations in levels relative to the trend. Trend characterizes the process of evolution. The division of time series into component elements is a conventional descriptive technique. However, the decisive factor determining the trend is purposeful human activity, and the main reason for fluctuation is changes in living conditions. It follows that sustainability does not necessarily mean repeating the same level from year to year. The concept of series stability as the complete absence of any level fluctuations was too narrow. Reducing fluctuations in series levels is one of the main tasks in increasing stability. Stability of time series- this is the presence of the necessary trend of the studied indicator with minimal influence of unfavorable conditions on it. For measuring the stability of time series levels
use the following indicators:
1) the range of fluctuations - is defined as the difference in average levels for favorable and unfavorable periods of time in relation to the phenomenon being studied: R=y favorable – unfavorable Favorable time periods include all periods with levels above the trend, and unfavorable periods include those below the trend. 3) average linear deviation: 1) standard deviation: S(t)= A decrease in fluctuations over time will be equivalent to stability of levels. For stability characteristics
The following indicators are also recommended: 1) percentage range (PR): Wmax/min – max/min relative increase. W= 2) Moving average (MA) estimates the value of the average deviation from the level of moving averages (хt): 3) Average percentage change (APC) evaluates the average value of absolute values, relative increases and squared relative increases: ARS= To assess the stability of time series levels, relative indicators of variability are used: K=100 – V(t) – stability coefficient (in percentage or fractions of units). For measuring the stability of the dynamic trend (trend)
use the following indicators:
1) rank correlation coefficient (Spearman coefficient): d is the difference between the ranks of the levels of the series under study and the ranks of the numbers of periods or points in time. To determine this coefficient, the values of the levels are numbered in ascending order, and if there are identical levels, they are assigned a certain rank equal to the quotient of the division of the ranks per number of these equal values. The Spearman coefficient can take values ranging from 0 to ±1. If each level of the period under study is higher than the previous one, then the ranks of the levels of the series and the number of years coincide - Kp = +1. This means complete stability of the very fact of growth in the levels of the series, that is, continuity of growth. The closer Kp is to +1, the closer the growth of levels is to continuous, that is, the higher the stability of growth. If Kp=0, growth is completely unstable. For negative values, the closer Kp is to -1, the more stable the decrease in the studied indicator. I= The correlation index shows the degree of correlation between fluctuations of the studied indicators and a set of factors that change them over time. Approximation of the correlation index to 1 means greater stability of changes in the levels of time series. The number of row levels for two indicators must be the same. Also applicable comprehensive sustainability indicators
, the essence of which is to determine them not through the levels of time series, but through indicators of their dynamics. 1. The Kayakina indicator is defined as the ratio of the average increase in the linear trend, i.e. parameter a1 to the standard deviation of levels from the trend: The greater the value of this indicator, the less likely it is that the level of the series in the next period will be less than the previous one. 2. Lead indicator, which is obtained by comparing the growth rate of series levels with the rate of fluctuation value: If the lead indicator is > 1, then this indicates that the levels of the series on average are growing faster than fluctuations or decreasing more slowly than fluctuations. In this case, the coefficient of level fluctuations will decrease, and the coefficient of level stability will increase. If the lead indicator is less than 1, then the fluctuations grow faster than the trend levels and the volatility coefficient increases, and the level stability coefficient decreases, that is, the lead indicator determines the direction of the dynamics of the level stability coefficient.
at t t t
y1 -2
y2 -1
y3
y4
y5
t=0
