Moving average calculation in Excel and forecasting. Forecasting in Excel using the moving average method

transcript

1 Forecasting in excel method moving average Ph.D. mat. Sciences, Professor Gavrilenko V.V. assistant Parokhnenko L.M. (National Transport University) Theoretical reference. When modeling various economic processes in practice, the increasing capabilities of modern computer technologies are widely used, as well as effective ways forecasting. So, to develop forecasts in the Excel package, you can use tools such as: building regressions; exponential smoothing; moving average. In this paper, the process of developing a forecast using Excel is carried out using the moving average method. Note that the forecasting technique using regressions is described in sufficient detail by the authors in . The moving average method is used for smoothing and forecasting time series. Recall that a time series is a set of data pairs (X, Y), in which X are moments or periods of time (independent variable), and Y is a parameter characterizing the magnitude of the process under study (dependent variable). The moving average method allows you to identify trends in the actual values of the Y parameter over time and predict future Y values. The resulting model can be effectively used in cases where there is a stable trend in dynamics for the values of the predicted parameter. This method is not so effective in cases where such a trend is violated, for example, during natural disasters, military operations, civil unrest, with a sharp change in the parameters of the internal or external situation (inflation, commodity prices); with a radical change in the plan of activity of the company that suffers losses. The main idea of the moving average method is to replace the actual levels of the time series under study with their average values, which dampen random fluctuations. Thus, the result is a smoothed series of values of the parameter under study, which makes it possible to more clearly identify the main trend in its change. The moving average method is a relatively simple time series smoothing and * forecasting method based on representing the forecast y t as the average of m previous observed y values (i= 1, m), then m * 1 is: y t = yt i. If, for example, when studying a time series of data m i = 1 on the profit of an enterprise by months, a moving average for three months (m = 3) is chosen as a forecast, then the forecast for June will be the average value of t i

2 indicators for the previous three months (March, April, May). If you choose a 4-month moving average (m = 4), then the forecast for June will be the average of the indicators for the previous four months (February, March, April, May). Often, for example, when developing a sales forecast for an enterprise, a moving average method based on observations for the previous 3 (or 4) months is more effective (allows you to track actual sales with greater accuracy) than methods based on long-term observations (for 12 months and more). This is explained by the fact that as a result of applying the 3-month moving average, each of the 3 values of the indicator (for these three months) is responsible for one third of the forecast value. With a 12-month moving average, the values of each of the indicators of the same last three months are responsible for only one twelfth of the forecast. Unfortunately, there is no rule for choosing the optimal number m of moving average terms. However, it can be noted that the smaller m, the stronger the forecast responds to fluctuations in the time series, and vice versa, the larger m, the more inertial the forecasting process becomes. In practice, the value of m is usually taken in the range from 2 to 10. If there are a sufficient number of elements of the time series, the value of m acceptable for the forecast can be determined, for example, as follows: set several preliminary values of m; smooth the time series using each given value of m; calculate average error forecasting by one of the formulas: 1 * o ε = y t y t (mean absolute deviation); n 1 yt o ε = y n y t t t * t (average relative deviation); 1 * 2 o ε = (yt yt) (standard deviation), n t where n is the number of times t used in the calculation; choose the value m corresponding to the smaller error. Implementation of the process of smoothing and forecasting using the moving average method in the Excel environment can be carried out by: introducing the appropriate formula into the cells, for example, using the built-in AVERAGE() function; using the Moving Average tool of the Analysis ToolPak add-in; adding a trend line to the chart built on the original time series based on the linear filtering method.

3 Task. Given the data presented in the table of the monthly profit of the company for 11 months current year, make a forecast of the company's profit for the 12th month. Fig.1. Table of company profit values by months Problem solution In the future, when solving the formulated problem, for the convenience of presenting the results of calculations, worksheets Z1, Z2, Z3, Z4 will be used: worksheet Z1 for the formation of smoothed time series based on the moving average method using the AVERAGE() function and calculation of their mean deviations from the original time series; sheet Z2 to implement the process of smoothing the original time series using the Moving Average tool of the Analysis Package add-on; sheet Z3 for visual representation of the smoothed time series built using a trend line of type Linear filtering based on a chart for the original time series; worksheet Z4 for a comparative analysis of the results obtained using the tools selected above: based on the initial time series, smoothed time series of 2-month moving average values are built using the AVERAGE() function, the Moving Average tool of the "Analysis Package" add-on and a trend line of the Linear type filtration. Application of the built-in function AVERAGE() The process of obtaining a smoothed time series, as well as a forecast of the company's profit for the 12th month of the current year according to the initial time series, will be carried out according to the following scenario: 1. Based on the data given in the table in Fig. 1, on working Excel sheet a table is created that is filled with the data of the original time series. 2. Data of smoothed time series for 2, 3 and 4-month moving average are formed and entered into the table.

4 3. Graphs of the original time series and smoothed time series are built. 4. According to one of the above formulas, the average deviations of the obtained smoothed time series from the original time series are calculated. 5. As a model, a smoothed time series with a smaller average deviation is selected, and based on its indicators, a forecast is made about the company's profit for the 12th month of the current year. We pass to the implementation of the solution of the problem. 1. Fill in the range of cells A5:B15 of worksheet Z1 with time series data from the table in Fig.1. As a result, we obtain the table shown in Figure 2. Fig.2. The initial table on the Excel worksheet 2. Based on the data of the time series from the range of cells A5:B15, we build three models of the dependence under study based on the moving average method based on data for 2, 3 and 4 previous months, respectively. The values of the obtained smoothed time series are located respectively in the ranges of cells C7:C16; D8:D16; E9:E16. First, we build a series of moving average values for two months: in cell C7 we enter the formula =AVERAGE(B5:B6) and, using the fill marker, copy it to the range of cells C8:C16, as a result of which the range of cells C7:C16 is filled with the calculated indicators 2- x monthly moving average. Similarly, series of 3- and 4-month moving average values are built: in cell D8, enter the formula =AVERAGE(B5:B7) and, using the fill marker, copy it to the range of cells D9:D16, resulting in the range of cells D8:D16 filled with indicators of a 3-month moving average; enter the formula =AVERAGE(B5:B8) into cell E9 and use the fill marker to copy it to the range of cells E10:E16, as a result of which the range of cells E9:E16 is filled with the indicators of the 4-month moving average. Figure 3 4 shows tables with results for 2, 3 and 4-month moving averages, as well as the formulas used.

5 Fig.3. Table of values for 2, 3, 4-month moving average Fig.4. The contents of the cells of the table in Fig. 3 Fig. 5 shows the graph of the initial time series and the predictive moving average trend lines constructed relative to it. Note that these graphs were built according to the standard method of constructing diagrams in Excel. Since the obtained values of the smoothed time series based on the moving average are based on the data of previous observations, they lag behind the corresponding values of the original time series: the moving average trend lines are shifted relative to the original time series graph (Fig. 5). The tables in Fig.6-10 show the absolute, relative and root mean square deviations of the values of the 2nd, 3rd and 4th monthly moving average

6 from the corresponding values of the original time series, as well as the contents of the cells in these tables. Fig.5. Graphs of the original time series and smoothed time series Fig.6. Table of absolute deviations

7 Fig.7. The contents of the cells in the table Fig.6 8. Table of relative deviations Fig.9. The contents of the cells in the table Fig.8 Fig.10. Standard deviation table

8 Mean values standard deviation in the range of cells B41:D41 are obtained as follows: in cell B41 the formula is entered: =SQRT(SUMQDIS(B9:B15,C9:C15)/COUNT(B9:B15)), in cell C41 the formula is entered: =SQRT(SUMQDIS(B9 :B15;D9:D15)/COUNT(B9:B15)), enter the formula in cell D41: =SQRT(SUMDIFF(B9:B15,E9:E15)/COUNT(B9:B15)). It should be noted that in order to conduct a comparative analysis of errors for the 2, 3 and 4-month moving average, it was taken the same number observations. Conclusion. It follows from the above tables that in order to smooth the initial time series and make a forecast about the trend in the change in the company's profit, the 2-month moving average model is preferable, since it more accurately responds to fluctuations in the initial time series and has smaller forecasting errors (absolute, relative, rms ). The forecast value of the company's profit for the 12th month is 8325 thousand UAH. The Moving Average tool of the "Analysis Package" add-in The implementation of the process of smoothing and forecasting using the moving average method in the Excel environment can be carried out using the Moving Average tool of the "Analysis Package" add-in according to the following method: 1. On worksheet Z2, create a table in which the range of cells A5: B15 is filled with time series data from the original table (Fig. 1). 2. The range of cells C5:С15 is filled with the values of the smoothed series obtained from the data for the previous 2 months using the Moving Average tool of the "Analysis Package" add-in, and the range of cells D5:D15 is filled with the values of its standard errors. 3. Similarly, the ranges of cells E5:E15 and F5:F15 are filled with the values of the smoothed series obtained from the data for the previous 3 months, and the values of its standard errors, respectively. The technology for constructing a series of values, for example, for a 2-month moving average using the Moving Average tool of the "Analysis Package" add-on, is as follows: Select the Data Analysis command from the Tools menu. The Data Analysis dialog box will appear (Figure 11), which contains all the available data analysis tools. Select the Moving Average tool from the list and click the OK button. The Moving Average dialog box will appear (Figure 12). In the Input interval field, specify the range of source data on the Excel worksheet, that is, the range of cells B5:B15.

9 Fig.11. Dialog box Data Analysis Fig.12. The Moving Average dialog box In the Interval field, enter the number of months that are included in the calculation of the moving average, that is, the number 2 (because in this case the moving average is based on the data of the previous 2 months). In the Output interval input field, enter the range of cells in which the results will be displayed, that is, the range of cells C5:C15. When checking the boxes in the Graph output and Standard errors fields, a chart will be automatically created based on the results of the analysis and a column containing the statistical error estimate will be added to the result. The Labels field should be checked if the first row (column) in the input range contains titles. If the input range does not contain headers, then the checkbox must be cleared. We click on the OK button. Similarly, a series of values of a 3-month moving average and its standard errors are constructed. Figure 13 shows a table of values of 2- and 3-month moving averages and their standard errors, obtained using the Moving Average tool of the "Analysis Package" add-on, and in Figures 14a, 14b, the contents of the cells of this table, that is, used in formula solving process.

10 Fig.13. Smoothed series and their standard errors obtained using the Moving Average tool of the "Analysis Package" add-on Fig.14a. The contents of the cells of the table Fig. 13 (beginning)

11 Fig.14b. The contents of the cells of the table Fig.13 (continued) Fig.15. Graphs of the original time series and smoothed time series built using the Moving Average tool of the "Analysis Package" add-in Conclusion: comparison of standard errors from the range of cells D9:D15 with the corresponding standard errors from the range of cells F9:F15 (Fig. 13) allow us to read model 2 -x monthly moving average is preferable for smoothing and forecasting, since it is considered at all points

12 of the given time range has smaller standard errors. The predicted value of the company's profit for the 12th month will be the value contained in cell C15, that is, 8325 thousand UAH. Building trend lines using the linear filtering method For graphical analysis of data on a chart, you can use the construction of a trend line based on moving average points. Such a trend line allows you to build a smoothed curve, the graphical representation of which more clearly shows the existing pattern in the development of the data. For the original table of values (Fig. 2), we apply the linear filtering method (or the moving average method) and build trend lines. The technology for constructing a trend line is as follows: Using the data from the source table (Fig. 2), we will construct a graph by selecting the Point type in the Chart Type dialog box. Optionally, you can change the appearance of the constructed graph and its marker, line type, color and thickness. To do this, go to the editing mode of the resulting graph by double-clicking the left mouse button on the constructed graph. In the Format Data Series dialog box that appears, set the necessary parameters for changing the graph and press the OK button. Next, select this data series by clicking on the graph line with the right mouse button (the selection of the series will be made by black squares). In the context menu that appears, select the Add trend line menu item. Or, after selecting the series, by clicking any mouse button, select the Add Trendline command from the Chart menu. The Trend Line dialog box will appear on the screen (Fig. 16). On the Type tab, select the type of trend line Linear filtering (moving average). If you select the Linear filtering type, you must enter in the Period field the number of periods (points) used to calculate the moving average. Let's enter the number 2 in this field, because we draw a trend line for 2 months. We press OK. By analogy, we proceed when building a trend line for 3 months by entering the number 3 in the Period field. the constructed charts of the initial time series and the trend lines of the 2- and 3-month moving average are presented.

13 Fig.16. Dialog box Trendline Constructed trendlines can be formatted. To do this: select the trend line by clicking on it with the mouse, then right-click and select the Format trend line item from the context menu that appears. the Format trend line dialog box appears (Fig. 17), in which you can set the desired Trend type: line type, color, thickness; You can change the name of the smooth curve by opening the Options tab in the same dialog box. After setting the required parameters, click OK.

14 Fig. Figure 17. Trendline Format Dialog Box Note the following: Since the linear filtering method is implemented by plotting a trendline on the chart, its effect can be observed visually, but there is no way to get at your disposal numerical results, since they are not entered into a spreadsheet.

15 Fig. Fig. 18. Graphs of the initial time series and trend lines of the 2- and 3-month moving average Comparison of tools moving average using the AVERAGE() function and the 2-month moving average of the Analysis Package. Let's plot the original time series and the trend lines of the smoothed time series.

16 Fig. Fig. 19. Table of values of the 2-month moving average obtained using the AVERAGE() function and the Analysis Package Fig.20. Graphs of the original time series, the 2nd monthly moving average obtained using the AVERAGE function, the Moving Average tool of the Analysis ToolPak add-in with the addition of a trend line of the Linear filtering type

17 Comparing the values of the moving average in column C, obtained by directly entering formulas into the cells of the worksheet, with the values of the moving average in column D, calculated using the Moving Average tool of the Analysis ToolPak (Figure 20), you can see that the moving average values moving average in column C are shifted down one position compared to column D. This problem can be solved, for example, as follows: after the moving average values are calculated, all these values should be selected and shifted down one line of the worksheet. This action will allow you to link the forecasts exactly to the periods to which they refer. However, if the Show Graph check box in the Moving Average dialog box (Figure 12) is checked, the graph will place the forecast data according to the worksheet data. By shifting the values of the worksheet one row down, you must also edit the plot based on the forecast data. Let's note the advantages and disadvantages of making a forecast using the moving average method: Making a forecast using the moving average tool is quite simple and fairly accurately reflects changes in the main indicators of the previous period. Sometimes they are even more effective in forecasting than methods based on long-term observations. However, a simple moving average is fast, but not always exact way identifying general trends in the time series. When making moving average forecasts using the Analysis Package add-in, the forecast is generated one time period earlier. You can draw a chart that uses time series data to plot a moving average trendline, but the chart does not show the actual numerical values of the moving average. And also, there is no way to change the location of the trend line on the chart. Making forecasts based on a moving average does not produce a forecast that goes beyond the known data. You can move the estimate boundary to the future along the time axis using one of the statistical functions regression analysis Excel package. Literature 1. Carlberg K. Business analysis using Excel. K.: Dialectics, p. 2. Gavrilenko V.V., Parokhnenko L.M. Solving approximation problems using Excel // Computers + programs, S N.V. Makarova, V.Ya. Trofimets. Statistics in Excel: Tutorial. M.: Finance and statistics, p. 4. Yu.N. Tyurin, A.A. Makarov. Data analysis on a computer / Ed. V.E. Figurnova. M: INFRA-M, p.

Laboratory work 2 Topic: Technology of analytical modeling in DSS. Technologies of analysis and forecasting based on trends Purpose: to study the possibilities and develop the ability to use the universal

Practical work 3.7. Using the MS Excel Function Wizard. Construction of diagrams The purpose of the work. Having completed this work, you will learn how to: enter formulas into table cells; use MS Excel Function Wizard

Laboratory work 8. BUILDING GRAPHS AND CHARTS IN EXCEL The purpose of the work: to learn how to use the means of graphical display of information in the Excel environment, how to format and use it

FORECASTING THE VOLUME OF GASOLINE SALES BY THE METHOD OF EXTRAPOLATION OF TRENDS Puchkova V. S., Rasteryaev N.V. Don State Technical University (DSTU) Rostov-on-Don, Russia FORECASTING OF SALES VOLUMES

SOLUTION OF PROBLEMS OF DESCRIPTIVE STATISTICS USING THE ANALYSIS PACKAGE MS EXCEL The simplest problems of descriptive statistics can be solved using spreadsheet processors. All examples below are given for

Laboratory work on Excel (xls file on the page www.matburo.ru/sub_appear.php?p=l_excel) Creating, filling, editing and formatting tables What is mastered and studied? Input and formatting

3.4. Working with spreadsheets 3.4.1. Program user interface Microsoft Excel. Creating and editing tables A document in Microsoft Excel (MS Excel) is called a workbook,

Series Names Graphical Representation of Data Using Charts 1.1 Basic Concepts Any chart is drawn in a coordinate system defined by a horizontal axis called the category axis, and

Chapter 4. Working with Charts and PivotTables Representing data using charts allows you to provide a visual demonstration of the information located in the cells of the worksheet. So, with diagrams

WORKSHOP 5.2.4. DIAGRAM. TECHNOLOGY OF CONSTRUCTION AND EDITING WORKSHOP 5.2.4. DIAGRAM. TECHNOLOGY OF CONSTRUCTION AND EDITING... 1 OBJECTS OF THE DIAGRAM... 1 CONSTRUCTION OF THE DIAGRAM... 3 1st step. Selection

Charts and graphs Introduction to charting Building and editing charts and graphs Set the line color and style. Editing a chart Formatting text, numbers,

Number of newspapers Laboratory and practical work TOPIC: “MS Excel. Building, formatting and editing charts, graphs. PURPOSE OF THE LESSON: to learn how to build, format and edit charts, graphs.

Plotting functions and trend lines. Volchkov V.M., Styazhin V.N. cafe Applied Mathematics, VolgGTU Lesson 3 There are many specialized computer programs that allow you to build graphs

Laboratory work 5. Processing of experimental data in spreadsheets Task 1. On the first worksheet of the document, enter the initial data corresponding to the variant of the task. Build Graph

Laboratory work Microsoft Excel 2007. Working with charts 1. Inserting columns Call the context menu for a column and select Insert (a new column is added to the left of the selected one). 1.1. Selection

Using MS Excel for graphical processing of the results obtained (recommendations for students and teachers) MS Excel spreadsheet editor, included in the standard MS Office package,

AUTOMATION OF ECONOMETRIC MODELING T. A. Zayats EE "Belarusian Trade and Economic University of Consumer Cooperatives", Gomel economic conditions planning and management

MINISTRY OF HEALTH OF THE RUSSIAN FEDERATION SBEE OF HIGHER PROFESSIONAL EDUCATION AMUR STATE MEDICAL CADEMY E.V. RAINBOW ELECTRONIC EXCEL TABLES. METHODOLOGICAL INSTRUCTIONS

Lab 4 Tabulation of Functions and Plotting Objective: To acquire the skills of calculating a table of function values and plotting. Guidelines: Function tabulation is a calculation

Lesson 10 ET allow processing large arrays of numerical data. Unlike paper spreadsheets, spreadsheets provide

Practical work topics: Practical work 1. Entering data into cells, editing data, changing the column width, inserting a row (column) Practical work 2. Entering formulas Practical work 3.

LABORATORY WORKS IN MS EXCEL 2007 LAB 1.... 1 LABORATORY 2... 3 LABORATORY 3... 4 LABORATORY 4... 7 LABORATORY 5... 8 LABORATORY 6... 10

APPROXIMATION In practice, one often encounters the problem of smoothing experimental data, the problem of approximation. The main task of approximation is the construction of an approximate (approximating) function

Laboratory-practical work 13 "Linked tables in MS Excel 2007" Basic principles of workbook formation. For proper organization work in electronic Excel tables 2007 form layout

Excel. Range Names You may have worked with worksheets that used a formula like: =SUM(A5000:A5078). Have you wondered what is in cells A5000: A5078!? If in cells A5000: A5078

Real estate investment: economics, management, expertise

Chapter 1 Charting Basics Data in a spreadsheet is represented as rows and columns. When adding a chart, the value of this data can be enhanced by highlighting relationships and trends that are not

BASIC COMMANDS AND OPERATIONS! Check how you remember the studied material Windows system 7 and word processor MS Word Basic steps for working in Windows 7. Highlight the icon Click

Laboratory work Topic: Graphing functions Purpose of work: Studying the graphical capabilities of the Ms Ecel package Acquisition of skills in plotting a function graph on a plane using the package Assignment

CONSTRUCTION OF DIAGRAM. TABULATION OF FUNCTIONS The purpose of the work: to master the basic techniques for creating and editing diagrams; study the operation of copying formulas using filling; learn to solve calculations

1 Laboratory work 3 Problem solving. Selection of parameters, search for a solution 1. Implementation of the mathematical model in Excel Mathematical model is a description of the state of behavior of some real system (object,

Task Laboratory work 6. Construction of an empirical dependence of the heat capacity of a substance on temperature using the least squares method. Construct a graph of the temperature dependence of the heat capacity of a substance in

General information. Function tabulation is the calculation of function values (dependent variable) when the function argument (independent variable) changes from some initial value to some final

INTRODUCTION Tabulation of a function is the calculation of the values of the function (dependent variable) when the function argument (independent variable) changes from some initial value to some final

Practice Analysis of test results To analyze the test results, perform the following steps:. let's calculate GPA by group obtained during testing; by results matrix

28 Chapter 1. Getting Started with Microsoft Excel 2013 Inserting and Deleting Cells, Rows, and Columns

Chapter 8 Databases in OpenOffice.org Calc In this chapter, we will explore the possibilities of OpenOffice.org Calc when working with databases. Quite often there is a need to store and process data

Practical work 8 Topic: COMPUTATIONAL FUNCTIONS OF THE MICROSOFT EXCEL TABLE PROCESSOR FOR FINANCIAL ANALYSIS Purpose of the lesson. The study of information technology using embedded computing

PivotTable Basics PivotTable Definition Think of Excel as a big toolbox. Then the pivot table is just one of Excel's tools. Continuing the analogy with tools,

Laboratory work Initial acquaintance with Microsoft Office Excel 2007 As a result of this laboratory work, you will be able to: know the basic concepts and objects of a spreadsheet, compose

Topic 6.8. calculation definite integral didactic purpose. To introduce students to the methods of approximate calculation of a definite integral. educational goal. The topic of this lesson is

Laboratory work 5 Formatting text in the form of lists and columns Creating lists In text documents, enumerations of various types are formatted as lists. There are lists various types: numbered

Econometric Modeling Lab 3 Paired Regression

"MICROSOFT OFFICE EXCEL" Discipline "Professional activity software" Lecturer: Art. teacher of the department "Electric drive and electrical equipment" Voronina Natalya Alekseevna Appointment

The main ways to enter data in NormCAD: On the Data tab In the text of the report In dialog mode (automatic request for data when performing a calculation) On document tabs (in tables) Data entry on the tab

1 Lab 1 Editing a workbook. Building charts The purpose of the work: Studying how to work with data in a cell. Explore the possibilities of autocomplete. Construction of diagrams. Exercise 1.

6 goals for investing in IT (survey) Improving operational efficiency New products, services, business models Close contacts with buyers and suppliers Decision support Competitive

PP 6. Techniques for using the Analysis Package for statistical data processing 1. Testing hypotheses Very often population 1 must obey some parameters. For example, packaging

Combo chart in Excel A combo chart combines two or more types of standard charts. To create a combo chart, you need to follow several steps: Select

Practical work Creation of control systems by means Microsoft programs Excel Task 1 Create a student knowledge control system using Microsoft Excel, containing at least 3 test

1. Introduction Laboratory work 3 Selection of parameters When solving various tasks one often has to deal with the problem of choosing one value by changing another. For this purpose, it is very effectively used

Ministry of Education and Science Russian Federation federal state budgetary educational institution higher vocational education"Vladimirsky State University name

Laboratory work. MS Excel 1. Create a workbook, saving it under the name "Office Applications".!!! Do not forget to periodically save information. 2. Rename the first sheet by giving it

The task of allocating enterprise resources Meaningful statement of the problem The factory produces bags: women's, men's, travel. Data on the materials used for the production of bags and a monthly supply

Working with charts in MS Excel Charts are designed for graphical presentation of data. With the help of diagrams, the process of analyzing the dependencies between various indicators presented is greatly simplified.

Working with lists in MS EXCEL Purpose: To acquire the skills of searching and aggregating data in a list. Brief theory Computer Information Technology widely used for data analysis and management training

Graphical solution of systems of equations Analytic geometry studies geometric objects by their equations. MS Excel provides extensive visualization options various equations. In Excel

Chapter 7 Processing Experimental Results in OpeOffice.org Calc In this chapter, we will explore the possibilities of the OpeOffice.org Calc package for solving problems of processing experimental data. One of the common

Passportization. The certification system for boiler room equipment and elements of the heat supply system makes it possible to take into account individual specifications real objects when performing computational tasks.

I repeat. The behavior of the crowd is inertial. This means that the probability that the crowd tomorrow will behave the same as yesterday and the day before is much higher than the probability of a change in mood.

In order to track the behavior of the crowd in the market, there is an ancient MACD indicator. Its abbreviation stands for moving average convergence-divergence or if in Russian convergence-divergence of moving averages (meaning historical values share prices or other instruments).

The graphical meaning of the MACD histogram is to confirm the continuation of the trend (development direction) of the price movement. Roughly speaking, stocks continue to get cheaper or more expensive. The direction of price movement is determined as the difference between two adjacent bars.

We use excel to plot the MACD histogram.

1) First we need historical data for analysis. In a previous article, I gave an example where such data can be obtained. Let's follow this example and go to the broker data export page:

Having set the requirements for the format of the downloaded data, we get a file with data in the csv format, which excel understands. Also, historical data on the instrument of interest to us can be downloaded on the website of the broker ZAO FINAM po this link.

2) data should be formatted as described in .

The end result should be something like this:

3) Now let's create a new sheet in the excel book for calculations and plotting technical analysis. So let's call this sheet: "Calculation of MACD". Then copy the date column to this sheet and a column with closing price data . Like this:

4) Now let's calculate the exponential moving average with a window of 12 days (EMA 12). EMA 12 is calculated by the formula:

Put this formula in the column to the right of the closing price . To do this, we start writing to the cell with the “=” symbol, which tells the excel processor that a formula will be entered. For the first cell, the formula is slightly different than for the rest of the cells, due to the fact that instead of yesterday's EMA12, you should substitute today's closing price. Like this:

Let's copy the resulting formula into the cell below and edit it a bit: instead of the value from cell B3, in the second part of the formula, substitute the value from cell C2. C2 - this will be the EMA12 of the previous day.

It should turn out like this:

Now let's multiply the formula obtained in the second cell for the entire EMA12 column. To do this, click once in cell C3 so that a black bold frame appears around the cell, then move the cursor to the lower right corner of the black bold frame so that the cursor takes the form of a bold black cross and double-click the left mouse button to multiply the formula for the entire column. Like this:

Now, in a similar way, we calculate the exponential moving average with a window of 26 days (EMA 26). EMA 26 is calculated by the formula:

Let's put this formula in the column to the right of the calculated EMA12. To do this, we start writing to the cell with the “=” symbol, which tells the excel processor that a formula will be entered. For the first cell, the formula is slightly different than for the rest of the cells, due to the fact that today's closing price should be substituted for yesterday's EMA26. Like this:

Let's copy the resulting formula into the cell below and edit it a bit: instead of the value from cell B3, in the second part of the formula, substitute the value from cell D2. D2 - this will be the EMA26 of the previous day. It should turn out like this:

Now let's multiply the formula obtained in the second cell for the entire EMA26 column. To do this, click once in cell D3 so that a black bold frame appears around the cell, then move the cursor to the lower right corner of the black bold frame so that the cursor takes the form of a bold black cross and double-click the left mouse button to multiply the formula for the entire column. Like this:

Congratulations! We have dealt with the calculation of exponential averages. Now you should get a "fast" MACD line. To do this, subtract EMA26 from EMA12. Let's put this formula in the next column on the right:

Now we need to calculate the nine-day exponential moving average for the "fast" MACD line. The resulting line will be called the "signal" MACD line. We will calculate according to the following formula:

Similarly, we fill in the calculation formula in excel in the cell to the right of the "fast" MACD line:

In the cell of the bottom row, we correct the formula in the same way as we did when calculating the twenty-six-day and twelve-day exponential moving averages. This is what the formula should look like in cell F3:

And finally, we can calculate the last column of data to build the MACD histogram. The values of this column for building a histogram is the difference between the "fast" and "signal" MACD lines. We drive in the last formula for calculating data for constructing a histogram:

It is much more convenient to consider the MACD histogram next to the chart of price fluctuations for the analyzed instrument. In a previous article, I described in detail how to build such a graph. To build a price chart for an instrument, we will copy a selection of the necessary data onto a separate sheet. Something like this:

The easiest way to build a stock chart is right here, on this sheet. Then you should copy it to a separate sheet, the one on which we will place the MACD histogram.

We create a separate sheet for our charts. Paste the copied diagram from the clipboard and customize it a bit appearance. The graph window stretches and shrinks in length and width, similar to windows in Windows itself.

And by clicking with the left mouse button on the scale with price values, you can change the data format of the graph plotting axis. After such a poke, the scale of the values of the vertical (in our case) axis is highlighted with a rectangular frame. As soon as such a frame appears, you should press the right mouse button to call the context menu. In the context menu with the left mouse button, select the line<Формат оси…>, like this:

In the dialog box that opens for setting the parameters of the graph axis, set the minimum value (80) and the maximum value (160). These are the top two lines in the dialog box that opens. The figure below shows the desired position of the radio buttons and the values 80 and 160 are entered in the corresponding lines:

Under the price chart window, insert a window for the future MACD histogram. Select the tab in the main menu<<Вставка>> then submenu<<Гистограмма>> and select the top left histogram icon from the drop-down menu, this icon is highlighted in yellow in the screenshot below:

Most importantly, before inserting the second chart, do not forget to deselect the first one. Otherwise, one graph may be replaced by another, and we need both graphs.

Before calling up the menu<<Гистограмма>> it would be nice to move the cursor over cell A16 and press the left mouse button. After inserting the histogram, we need to specify our column with the calculated data of the MACD histogram. To do this, move the mouse cursor over the histogram and press the right mouse button to call the context menu for managing the diagram. In the context menu that opens, select the item<Выбрать данные>:

After pressing the button<<Добавить>> in the previous window, we should type the name of our chart - "MACD", and in the bottom row, press the button to the right of the row:

After pressing the button to the right of the bottom row, a narrow window “Change Row” opens. Without closing this window, move with the mouse to the sheet with the name MACD:

After the data column is covered by a thin dotted line in the "Change row" box, click the button on the right. This will open the Edit Row window with two lines. Here in this window you can click the button<> and go to the chart publishing window:

Returning to the sheet with the name "GRAPHS" in the data selection window for constructing a histogram, we also press the button<>:

You can play a little with the size of the windows for the charts and get the result that seems clearer:

And here are the same charts built by the QUIK trading system. It looks like we did it with you?

Dear reader! If you decide to build these graphs and something doesn’t work out for you, leave your question in the comments and together we will definitely figure it out and learn how to build graphs in excel.

The excel source files from which screenshots were taken and in which there are plotted graphs can be downloaded from .

Practical modeling of economic situations involves the development of forecasts. With the help of Excel tools, you can implement such effective forecasting methods as: exponential smoothing, building regressions, moving average. Let's take a closer look at using the moving average method.

Using Moving Averages in Excel

The moving average method is one of the empirical methods for smoothing and forecasting time series. Essence: absolute values a series of dynamics change to arithmetic mean values at certain intervals. The choice of intervals is carried out by the sliding method: the first levels are gradually removed, the subsequent ones are switched on. As a result, a smoothed dynamic range of values is obtained, which makes it possible to clearly trace the trend of changes in the parameter under study.

A time series is a set of X and Y values that are related. Х – time intervals, constant variable. Y is a characteristic of the phenomenon under study (price, for example, acting in a certain period of time), dependent variable. Using a moving average, you can identify the nature of changes in the value of Y over time and predict given parameter in future. The method works when there is a clear trend in the dynamics for the values.

For example, you need to predict sales for November. The researcher chooses the number of previous months to analyze (the optimal number m of moving average members). The forecast for November will be the average value of the parameters for the previous m months.

Task. Analyze the company's revenue for 11 months and make a forecast for the 12th month.

Let's form smoothed time series using the moving average method using the AVERAGE function. Find the average deviations of the smoothed time series from the given time series.

Relative deviations:

Standard deviations:

When calculating deviations, the same number of observations was taken. This is necessary in order to carry out comparative analysis errors.

After comparing the tables with deviations, it became clear that in order to make a forecast using the moving average method in Excel about the trend in changes in the company's revenue, a two-month moving average model is preferable. She has minimal errors forecasting (in comparison with three- and four-monthly).

The forecast value of revenue for the 12th month is 9,430 USD.

Using the Analysis ToolPak add-in

For example, let's take the same problem.

On the "Data" tab, we find the "Data Analysis" command. In the dialog box that opens, select "Moving Average":

We fill. The input interval is the initial values of the time series. The interval is the number of months included in the calculation of the moving average. Since we will first build a smoothed time series based on the data of the previous two months, enter the number 2 in the field. The output interval is the range of cells for displaying the results.

By checking the box "Standard errors", we automatically add a column with a statistical error estimate to the table.

In the same way, we find the moving average for three months. Only the interval (3) and the output range change.

Comparing the standard errors, we see that the two-month moving average model is more suitable for smoothing and forecasting. It has smaller standard errors. The forecast value of revenue for the 12th month is 9,430 USD.

Making moving average forecasts is simple and effective. The instrument accurately reflects changes in the main parameters of the previous period. But it is impossible to go beyond the known data. Therefore, other methods are used for long-term forecasting.

The moving average method is a statistical tool that can be used to solve various kinds 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 understand how the 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. The best way to apply the moving average method in Excel is with the help of a powerful statistical data processing tool called Analysis package. You can also use the built-in Excel function for the same purpose. 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 step, the package "Data analysis" activated, and the corresponding button appeared on the ribbon in the tab "Data".

And now let's look at how you can directly use the features of the package Data analysis to use 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 the table filled with data, as well as tools Analysis package.

Go to tab "Data" and click on the button "Data analysis", which is located on the ribbon of tools in the block "Analysis".

A list of tools available in the Analysis package. Choose a name from them "Moving Average" and click on the button OK.

The data entry window for moving average forecasting is launched.
In field "Input Interval" indicate the address of the range where the monthly amount of revenue is located without the cell in which the data should be calculated.

In field "Interval" specify the interval for processing values by the smoothing method. To begin with, let's set the smoothing value to three months, and therefore enter the number "3".

In field "Exit 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 more than the input interval.

You should also check the box next to "Standard Errors".

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

After all the settings are made, click on the button OK.

The program displays the processing result.

Now let's run smoothing over a period of two months to find out which result is more correct. For these purposes, we run the tool again "Moving Average" Analysis package.
In field "Input Interval" we leave the same values as in the previous case.

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

In field "Exit Interval" specify the address of the new empty range, which, again, should be one cell more than the input interval.

The rest of the settings are left the same. After that click on the button OK.

After that, the program calculates 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 the accuracy of the result obtained. As you can see, for all values, the standard error in calculating the 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: Use the AVERAGE function

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

Like last time, we will need to create a smoothed time series. But this time the actions will not be so automated. An average value should be calculated for every two and then three months in order to be able to compare the results.

First of all, we calculate the average values for the previous two periods using the function AVERAGE. We can do this only starting from March, since for more late dates there is a break in values.

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

Window is activated Function Wizards. Category "Statistical" looking for meaning "AVERAGE", select it and click on the button OK.

Operator Arguments window launches AVERAGE. Its syntax is the following:
AVERAGE(number1, number2,…)

Only one argument is required.

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

As you can see, the result of calculating the average value for the previous two 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, we become the cursor in the lower right corner of the cell containing the function. The cursor is converted 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.

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

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 field "Number1" enter the coordinates of the cells in the column "Income" from January to March. Then click on the button OK.

Using the fill handle, copy the formula to the table cells below.

So, we calculated the values. Now, as in the previous time, we will need to find out which type of analysis is better: with a smoothing of 2 or 3 months. To do this, calculate the standard deviation and some other indicators. First, we calculate the absolute deviation using standard function excel ABS, which instead of positive or negative numbers returns their module. This value will be equal to the difference between the actual revenue for the selected month and the predicted one. We set the cursor to the next empty column in the row for May. Calling Function Wizard.

Category "Mathematical" highlight the name of the function ABS. Click on the button OK.

The function arguments window is launched ABS. In the only field "Number" specify the difference between the contents of the cells in the columns "Income" and "2 months" for May. Then click on the button OK.

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

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

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

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

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 again use the possibilities offered by the operator ABS. This time, using this function, we divide the value of the absolute deviation using the 2-month moving average method by the actual income for the selected month.

But the relative deviation is usually displayed as a percentage. Therefore, select the appropriate range on the sheet, go to the tab "Home", where in the toolbox "Number" in a special formatting field, set the percentage format. The result of the relative deviation calculation is then displayed as a percentage.

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

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

Now we come to the calculation of 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 squared differences between the actual revenue and the moving average, divided by the number of months. In order to make a calculation in the program, we have to use a number of functions, in particular ROOT, SUMMQVARIAN and CHECK. For example, to calculate the standard deviation when using the smoothing line for two months in May, in our case, the following formula will be applied:
SQRT(SUMDIFF(B6:B12,C6:C12)/COUNT(B6:B12))

We copy it to other cells of the column with the calculation of the standard deviation using the fill marker.

A similar operation for calculating the standard deviation is performed for the moving average for 3 months.

After that, we calculate the average value for the entire period for both of these indicators by applying the function AVERAGE.

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

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

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

moving average or just MA (Moving Average), is the arithmetic mean of the price series. The general moving average formula is as follows:

Where:
MA - moving average;
n - averaging period;
X - stock price values.

For share price forecasting for several periods ahead, we use the formula. The price forecast in the next period will equal the moving average values in the previous period.

Let's predict using a moving average model share price companies Aeroflot (AFLT). To do this, we export stock quotes from the site finam.ru for half of 2009. There will be 20 values in total.

Aeroflot share price chart for the selected period of time is shown below.

Selecting the averaging periodnin the moving average model
The use of a larger MA(n) in the model leads to a strong distortion of the data, as a result of which the significant values of the price series are averaged, and as a result, the clarity of the forecast is lost, we can say that it becomes “blurred”. Using an averaging period that is too short adds more noise to the forecast. As a rule, the averaging period is selected empirically based on historical data.

Let's build a moving average with an averaging period of three months MA(3). To calculate the value of the moving average for a stock, we will use the Excel formula.

AVERAGE(C2:C4)

Column “D” contains the values of the moving average with an averaging period of 3.

After calculating the moving average build a forecast for 3 periods forward (three months ahead). Let's use the formula to determine the value of the stock price, the first predicted value will be equal to the last value of the moving average. The orange area is the forecast area. C22 will be equal to the value of the moving average, that is:

C22 = D21 C23 = D22 etc.

A moving next average is calculated from the new share price forecast data.

Let's build forecast values on the moving average for Aeroflot shares for three months in advance. Below is a chart and forecast values for the stock.