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 the initial values ​​of a 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.

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:

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).

The moving average method is a statistical tool that can be used to solve various types of problems. In particular, it is quite often used in forecasting. In Excel, you can also use this tool to solve a number of problems. Let's look at how a moving average is used in Excel.

Meaning this method consists in the fact that with its help, the absolute dynamic values ​​of the selected series are changed to arithmetic averages for a certain period by smoothing the data. This tool is used for economic calculations, forecasting, in the process of trading on the stock exchange, etc. It is best to use the moving average method in Excel using a powerful statistical data processing tool called Analysis package. In addition, you can use the built-in Excel function for the same purposes AVERAGE.

Method 1: Analysis package

Analysis package is an Excel add-in that is disabled by default. Therefore, first of all, you need to enable it.

After this action the package "Data Analysis" activated, and the corresponding button appeared on the ribbon in the tab "Data".

Now let's look at how you can directly use the package's capabilities Data Analysis for working using the moving average method. Let's make a forecast for the twelfth month based on information about the company's income for 11 previous periods. To do this, we will use a table filled with data, as well as tools Analysis package.

1. Go to the tab "Data" and press the button "Data Analysis", which is located on the tool ribbon in the block "Analysis".



2. A list of tools that are available in Analysis package. Select a name from them "Moving average" and press the button "OK".



3. The data entry window for forecasting using the moving average method opens.

   In the field "Input interval" We indicate the address of the range where the monthly revenue amount is located without the cell in which the data should be calculated.

   In the field "Interval" you should specify the interval for processing values ​​using the smoothing method. First, let's set the smoothing value to three months, and therefore enter the number "3".

   In the field "Output Interval" you need to specify an arbitrary empty range on the sheet where the data will be displayed after processing, which should be one cell larger than the input interval.

   You should also check the box next to the parameter "Standard errors".

   If necessary, you can also check the box next to the item "Graph output" for visual demonstration, although in our case this is not necessary.

   After all the settings have been made, click on the button "OK".



4. The program displays the result of processing.



5. Now we will perform smoothing over a period of two months to determine which result is more correct. For these purposes, we launch the tool again "Moving average" Analysis package.

   In the field "Input interval" we leave the same values ​​as in the previous case.

   In the field "Interval" put a number "2".

   In the field "Output Interval" We indicate the address of the new empty range, which, again, must be one cell larger than the input interval.

   We leave the rest of the settings the same. After that, click on the button "OK".



6. Following this, the program performs a calculation and displays the result on the screen. In order to determine which of the two models is more accurate, we need to compare the standard errors. The less this indicator, the higher the probability of accuracy of the result obtained. As you can see, for all values ​​the standard error when calculating a two-month moving average is less than the same indicator for 3 months. Thus, the predicted value for December can be considered the value calculated by the sliding method for the last period. In our case, this value is 990.4 thousand rubles.

Method 2: Using the AVERAGE function

There is another way to use the moving average method in Excel. To use it, you need to use a number of standard program functions, the basic of which for our purpose is AVERAGE. For example, we will use the same table of enterprise income as in the first case.

Just like last time, we will need to create smoothed time series. But this time the actions will not be so automated. You should calculate the average for every two and then three months in order to be able to compare the results.

First of all, let's calculate the average values ​​for the two previous periods using the function AVERAGE. We can do this only starting from March, since for later dates there is a break in values.

1. Select a cell in an empty column in the row for March. Next, click on the icon "Insert Function", which is located near the formula bar.



2. The window is activated Function Wizards. In category "Statistical" looking for the meaning "AVERAGE", select it and click on the button "OK".



3. The operator arguments window opens AVERAGE. Its syntax is as follows:

   AVERAGE(number1,number2,…)

   Only one argument is required.

   In our case, in the field "Number1" we must provide a link to the range where the income for the two previous periods (January and February) is indicated. Place the cursor in the field and select the corresponding cells on the sheet in the column "Income". After that, click on the button "OK".



4. As you can see, the result of calculating the average value for the two previous periods was displayed in the cell. In order to perform similar calculations for all other months of the period, we need to copy this formula to other cells. To do this, place the cursor in the lower right corner of the cell containing the function. The cursor changes to a fill handle that looks like a cross. Hold down the left mouse button and drag it down to the very end of the column.



5. We get the calculation of the results of the average value for the two previous months before the end of the year.



6. Now select the cell in the next empty column in the row for April. Calling the function arguments window AVERAGE in the same way as described earlier. In the field "Number1" enter the coordinates of the cells in the column "Income" from January to March. Then click on the button "OK".



7. Using the fill marker, copy the formula into the table cells below.



8. So, we have calculated the values. Now, as before, we will need to figure out which type of analysis is better: with a smoothing of 2 or 3 months. To do this, you should calculate the standard deviation and some other indicators. First, let's calculate the absolute deviation using the standard Excel function ABS, which returns their modulus instead of positive or negative numbers. This value will be equal to the difference between real indicator revenue for the selected month and forecast. Place the cursor in the next empty column in the row for May. Calling Function Wizard.



9. In category "Mathematical" highlight the name of the function "ABS". Click on the button "OK".



10. The function arguments window opens ABS. In a single field "Number" indicate the difference between the contents of cells in columns "Income" And "2 months" for May. Then click on the button "OK".



11. Using the fill marker, copy this formula to all rows of the table up to November inclusive.



12. We calculate the average value of the absolute deviation for the entire period using the function already familiar to us AVERAGE.



13. We perform a similar procedure in order to calculate the absolute deviation for a 3-month moving average. First we apply the function ABS. Only this time we calculate the difference between the contents of cells with actual income and planned income, calculated using the moving average method for 3 months.



14. Next, we calculate the average value of all absolute deviation data using the function AVERAGE.



15. The next step is to calculate the relative deviation. It is equal to the ratio of the absolute deviation to the actual indicator. In order to avoid negative values, we will again take advantage of the opportunities offered by the operator ABS. This time, using this function, we divide the absolute deviation value when using the 2-month moving average method by the actual income for the selected month.



16. But the relative deviation is usually displayed as a percentage. Therefore, select the corresponding range on the sheet and go to the tab "Home", where in the tool block "Number" in a special formatting field we set the percentage format. After this, the result of calculating the relative deviation is displayed as a percentage.



17. We perform a similar operation to calculate the relative deviation with data using smoothing for 3 months. Only in this case, to calculate as the dividend we use another column of the table, which we have the name “Abs. off (3m)". Then we translate numeric values in percentage form.



18. After this, we calculate the average values ​​for both columns with relative deviation, as before using the function AVERAGE. Since for the calculation we take percentage values ​​as function arguments, there is no need to perform additional conversion. The output operator produces the result in percentage format.



19. Now we come to calculating the standard deviation. This indicator will allow us to directly compare the quality of the calculation when using smoothing for two and three months. In our case, the standard deviation will be equal to the square root of the sum of the squares of the differences between actual revenue and the moving average, divided by the number of months. In order to make calculations in the program, we have to use a number of functions, in particular ROOT, SUM DIFFERENT And CHECK. For example, to calculate the standard deviation when using a smoothing line for two months in May, in our case, the following formula will be used:

    SQRT(SUMVARE(B6:B12,C6:C12)/COUNT(B6:B12))

    We copy it to other cells of the column and calculate the standard deviation using the fill marker.



20. We perform a similar operation to calculate the standard deviation for the 3-month moving average.



21. After this, we calculate the average value for the entire period for both of these indicators using the function AVERAGE.



22. Having compared calculations using the moving average method with smoothing for 2 and 3 months using such indicators as absolute deviation, relative deviation and standard deviation, we can say with confidence that smoothing for two months gives more reliable results than applying smoothing over three months. This is evidenced by the fact that the above indicators for a two-month moving average are less than for a three-month moving average.



23. Thus, the projected income of the enterprise for December will be 990.4 thousand rubles. As you can see, this value completely coincides with the one we received when calculating using tools Analysis package.

We calculated the forecast using the moving average method in two ways. As we see, this procedure much easier to do with tools Analysis package. However, some users do not always trust automatic calculation and prefer to use the function for calculations AVERAGE and accompanying operators to check the most reliable option. Although, if everything is done correctly, the output result of the calculations should be completely the same.

Hello, dear friends!

In this article, as its title suggests, we will look at the principle of operation of one of the most common indicators technical analysis — moving average (movingaverage or MA), in the jargon of traders it is also called simply “moving average” or “mashka”.

A moving average is a way to smooth out price fluctuations over time. In other words, a moving average calculates the average price of a price over a certain period of time. The moving average is a trend indicator in pure form. With its help, you can track the beginning of a new trend and the end of the current one; by the angle of inclination you can judge the strength of the trend.

Although the moving average is a primitive indicator, I consider it a basic indicator of technical analysis; it is the basis for many trading strategies and various indicators, so every trader must know the “device” and operating principle of this indicator.

There are several methods for constructing a moving average:

1. Simple.
2. Linear-Weighted.
3. Exponential.
4. Smoothed.

All methods are based on the same principles; only the formulas by which they are calculated differ. Naturally, each method has its pros and cons. Let's look at each method in more detail.

SIMPLE moving average (SMA)

Most often when we're talking about about the moving average, this method of construction is implied. This is one of the simplest and most primitive indicators of technical analysis.

It is calculated using a very simple formula:

Where, Pi — price (most often calculated based on the closing prices of the candle, but can also be applied to the maximum minimum, opening price, average price etc.).

N — period of the moving average. This is the main parameter when constructing, it is also called the smoothing length.

Let's look at an example.

Let's say we want to build a moving average with a period of 8 based on closing prices. To get the midpoint for the current formed bar, you need to take the closing prices of the previous 8 bars (in the figure below they are indicated by numbers 1−8), add their closing prices and divide by total quantity periods (8). As a result, we will get the average value for the currently formed bar:

Accordingly, if we need to construct a moving average with a period of 60, then we will calculate the average based on the closing prices of 60 previous bars.

As you can see, nothing complicated. The construction of a simple moving average is usual example calculating the arithmetic mean from school curriculum mathematics.

Below in the figure you can see how a simple moving average with different periods“smoothes out” the price:

The main disadvantage This method is that the calculation is based on data for a fixed period of time, and not all prices, and each price value in history is assigned equal importance. But would you agree that the price that took place 30 days ago is not as important as the price that was 5 days ago?

Also, speaking about the disadvantages of a simple average, it is worth mentioning the significant lag of this indicator, so when trading, the trader will not be able to take most of the trend movement.

To the advantages It can be attributed to the fact that SMA has low sensitivity compared to other types and will give fewer false signals, but you will have to “pay” for this with a later signal to enter the position.

LINEAR WEIGHTED MOVING AVERAGE (Linear-Weighted)

As I wrote above, the simple MA has a significant drawback in that when calculated, it gives the same “weight” to the price, no matter how close or far it is from the present moment. This drawback has been eliminated in this method of constructing a moving average.

The formula for calculating the weighted moving average is as follows:

Where, Pi — price value for i-periods ago; Wi — weight for the price i-periods ago.

The essence of this method is that when constructing a weighted moving average, a certain weight is assigned to the price, so that the near prices of nearby bars have a greater share than the prices of past bars.

Let's try to calculate a linear weighted moving average with a period of 5.

It will look like this:

That is, we took five closing prices of the last 5 bars. Our closest bar is the most significant and we assigned the maximum weight to it (in our case it will be 5) and with each closing price of the subsequent bar. The result obtained was divided by the sum of all specific gravity. As a result, we received a weighted point for a specific bar. Of course, we will not need to make these calculations, since the technical program. the analysis will do everything itself.

Below in the figure you can see a comparison of simple and weighted moving averages, both have a period of 60:

The disadvantages of a linear-weighted moving average include:

• It gives fairly late signals to enter and exit a trend, but due to the weight it adds, it reacts much faster to price changes than a simple moving average.
• When trading in a flat it gives many false signals.

EXPONENTIAL (Exponential) AND SMOOTHED (Smoothed) MOVING AVERAGES

The principle of calculating the exponential MA is that it takes into account all the prices that are on the chart and assigns them a certain weight (the importance of the latter is higher than the previous ones).

Calculation formula exponential moving average It’s quite complicated and I won’t focus on it. It is important for us as traders to know that the exponential moving average is very sensitive to price changes and provides more “interesting” entry points into a trade, but it can also fail during strong price fluctuations.

Look at the figure below, it shows a comparison of two MAs with the same period (60):

Smoothed moving average is perhaps the most difficult to calculate and has the lowest sensitivity. This type of moving average is very rarely used by traders and only on charts with a very large amplitude of price fluctuations.

Let's see how simple and smoothed moving averages with the same period behave:

Notice how much this MA smooths the price compared to the simple moving average!

Previously, I compared each method of constructing a moving average with a simple MA. Now let’s plot all 4 moving averages on the price chart at once:

Now we have come to the end of the article. Let's summarize.

Moving average is a trend indicator that works great when there is a trend in the market and is absolutely useless when the market is moving sideways. Although this is a trend-following indicator, due to the fact that it is calculated based on past data, it gives quite late entry points. To correct this drawback, other methods of calculating MA using “scales” were used.

In this article, we did not touch on exactly how to trade using moving averages, how to look for entry and exit points, or how to filter signals. We will discuss all these and many other questions in the next article.

That's all I have for today. Good luck in trading!

PS Be sure to read the continuation of this article by following this link. From it you will learn about practical application moving averages.

In-depth analysis of time series requires the use of more complex methods of mathematical statistics. If there is a significant random error (noise) in the time series, one of two simple techniques is used - smoothing or leveling by enlarging the intervals and calculating group averages. This method allows you to increase the visibility of the series if most of the “noise” components are located within the intervals. However, if the “noise” is not consistent with the periodicity, the distribution of indicator levels becomes coarse, which limits the possibility of a detailed analysis of changes in the phenomenon over time.

More accurate characteristics are obtained if moving averages are used - a widely used method for smoothing the indicators of the average series. It is based on the transition from the initial values ​​of the series to the average in a certain time interval. In this case, the time interval when calculating each subsequent indicator seems to slide along the time series.

The use of a moving average is useful when there are uncertain trends in the time series or when there is a strong impact on the performance of cyclically recurring outliers (outliers or intervention).

The larger the smoothing interval, the smoother the moving average chart looks. When choosing the value of the smoothing interval, it is necessary to proceed from the value of the time series and the meaningful meaning of the reflected dynamics. Large value of time series with a large number of source points allows the use of larger smoothing time intervals (5, 7, 10, etc.). If the moving average procedure is used to smooth a non-seasonal series, then most often the smoothing interval is taken equal to 3 or 5. https://tvoipolet.ru/iz-moskvi-v-nyu-jork/ - an excellent opportunity to choose an airline for a flight from Moscow to New York

Let's give an example of calculating the moving average number of farms with high yields (more than 30 c/ha) (Table 10.3).

Table 10.3 Smoothing a time series by enlarging intervals with a moving average

Examples of moving average calculations:

1982(84 + 94 + 92) / 3 = 90.0;

1983 (94 + 92 + 83) / 3 = 89.7;

1984(92 + 83 + 91) / 3 = 88.7;

1985(83 + 91 + 88) / 3 = 87.3.

A schedule is drawn up. The years are indicated on the abscissa axis, and the number of farms with high yields is indicated on the ordinate axis. The coordinates of the number of farms on the graph are indicated and the resulting points are connected by a broken line. Then the coordinates of the moving average by year are indicated on the graph and the points are connected by a smooth bold line.

A more complex and effective method is smoothing (leveling) the dynamics series using various approximation functions. They allow you to form a smooth level of the general trend and the main axis of dynamics.

Most effective method smoothing using mathematical functions is simple exponential smoothing. This method takes into account all previous observations of the series according to the formula:

S t = α∙X t + (1 - α ) ∙S t - 1 ,

where S t - each new smoothing at time t; S t - 1 - smoothed value at the previous time t -1; X t - actual value of the series at time t; α is the smoothing parameter.

If α = 1, then previous observations are completely ignored; when α = 0, current observations are ignored; α values ​​between 0 and 1 give intermediate results. By changing the values ​​of this parameter, you can select the most appropriate alignment option. The choice of the optimal value of α is carried out by analyzing the obtained graphic images the original and aligned curves, or based on the sum of squared errors (errors) of the calculated points. Practical use of this method should be carried out using a computer in MS Excel. Mathematical expression Patterns of data dynamics can be obtained using the exponential smoothing function.

First, we will consider several simple forecasting methods that do not take into account the presence of seasonality in the time series. Let's assume that the RBC magazine provides a summary of the prices for oranges at the close of the exchange for the last 12 days (including today). Using this data, you need to predict tomorrow's cocoa price (also at the time the stock exchange closes). Let's look at several ways to do this.

If the last (today's) value is the most significant compared to the others, then it is the best forecast for tomorrow.

Perhaps, due to the rapid change in prices on the exchange, the first six values ​​are already outdated and not relevant, while the last six are significant and have equal value for the forecast. Then, as a forecast for tomorrow, you can take the average of the last six values.

If all values ​​are significant, but today's 12th value is the most significant, and the previous ones are 11th, 10th, 9th, etc. become less and less significant, you should find the weighted average of all 12 values. Moreover, the weighting coefficients for latest values must be greater than for the previous ones, and the sum of all weighting coefficients must be equal to 1.

The first method is called a “naive” forecast and is quite obvious. Let's take a closer look at the other methods.

Moving average method

One of the assumptions underlying this method is that a more accurate forecast for the future can be obtained if recent observations were used, and the “newer” the data, the greater its weight for the forecast. Surprisingly, this “naive” approach turns out to be extremely useful for practice. For example, many airlines use a proprietary type of moving average to create forecasts of air travel demand, which in turn are used in complex revenue management and optimization mechanisms. Moreover, almost all inventory management software packages contain modules that perform forecasts based on some type of moving average.

Consider the following example. A marketer needs to predict the demand for the machines his company produces. Sales data for last year The company’s work is located in the file “LR6.Example 1.Machines.xls”.

Simple moving average. In this method, the average of a fixed number of N recent observations is used to estimate the next value of the time series. For example, using machine tool sales data for the first three months of the year, a manager obtains a value for April using the formula below:

The manager calculated the sales volume based on a simple moving average for 3 and 4 months. However, it is necessary to determine what number of nodes gives a more accurate forecast. To assess the accuracy of forecasts, we use mean of absolute deviations(SAO) and average of relative errors, in percent (SOOP), calculated using formulas (3) and (4).

Where x i –i-th real value of the variable in i th moment of time, and x’ i –i th predicted value of the variable in i th point in time, N is the number of forecasts.

According to the results obtained on the sheet “Simple sc. average" of the workbook "LR6.Example 1.Machines.xls" (see Figure 56), the moving average for three months has a CAO value equal to 12.67 ( cell D16), while for the 4-month moving average the CAO value is 15.59 ( cell F16). One might then hypothesize that using more statistics worsens rather than improves the accuracy of the moving average forecast.

Figure 56. Example 1 – forecasting results using the simple moving average method

On the graph (see Figure 57), constructed from the results of observations and forecasts with an interval of 3 months, you can notice a number of features common to all applications of the moving average method.

Figure 57. Example 1 – graph of the forecast curve using the simple moving average method and graph of the actual sales volume

The forecast value obtained by the simple moving average method is always less than the actual value if the original data is monotonically increasing, and greater than the actual value if the original data is monotonically decreasing. Therefore, if the data is monotonically increasing or decreasing, then using a simple moving average cannot provide accurate forecasts. This method is best suited for data with small random deviations from some constant or slowly changing value.

The main disadvantage of the simple moving average method arises from the fact that when calculating the predicted value, the most recent observation has the same weight (i.e. significance) as the previous ones. This is because the weight of all the last N observations involved in calculating the moving average is 1/N. Giving equal weight contradicts the intuition that, in many cases, recent data can tell more about what will happen in the near future than previous data.

Weighted moving average. The contribution of different points in time can be taken into account by introducing a weight for each indicator value in a sliding interval. The result is a weighted moving average method, which can be written mathematically as follows:

where is the weight with which the indicator is used in the calculation.

Weight is always positive number. In the case when all the weights are the same, the simple moving average method degenerates.

Now the marketer can use the 3-month weighted moving average method. But first you need to understand how to choose weights. Using the Find Solution tool, you can determine the optimal node weight. To determine the weight of nodes using the Find Solution at which the value of the mean of absolute deviations would be minimal, follow these steps:

Select the command Tools -> Search for a solution.

In the Find a Solution dialog box, set cell G16 as the target cell (see the “Weights” sheet), minimizing it.

Use the editable cells to specify the range B1:B3.

Set limits B4 = 1.0; В1:ВЗ ≥ 0; B1:B3 ≤ 1; B1 ≤ B2 and B2 ≤ B3.

Start searching for a solution (the result is displayed).

Figure 58. Example 1 – the result of searching for weights of indicator values ​​using the weighted moving average method

The results show that the optimal distribution of weights is such that all the weight is concentrated on the most recent observation, with a mean absolute deviation value of 7.56 (see also Figure 59). This result supports the assumption that more recent observations should carry more weight.

Figure 59. Example 1 – graph of the forecast curve using the weighted moving average method and graph of the actual sales volume