Moving Average Excel

In this video, I'm going to show you how to calculate all the different types of moving averages in Excel. But before we go any further, make sure you hit the subscribe button and give this video a thumbs up so that you get notified of all of my future videos. Because I've got new trading content coming out all the time and I don't want you to miss a thing. Now, frankly, I don't know why you would want to do this, because your charting software should have all the moving averages in there. And if you're trading in using Excel for your analysis package, I got to tell you, you need to upgrade. Because trading packages like amibroker allow you to do all of your charting. It has all of the calculations for the indicators built in. It also allows you to back test and optimize your trading systems. So if you're using Excel, you're really selling yourself short. Move across to a proper trading package so that you can do the charting, do the back testing way more easily. Having said that, let's jump into Excel and have a look at how you calculate the different moving averages in Excel. Just so you know how the calculations work. Okay, so what I've done is I've extracted a few bars of data from the S&P 500 index and I've pasted them here. You can see I've got the SPX ticker, these are the dates I started in 1990, and I've got the closing price and the volume. I'm not using the volume, so we'll just ignore that for now. Let's talk about the simple moving average first. How do you calculate the simple moving average in Excel? Very, very easy. If we have a look at this formula here, the simple moving average is just the average of the last 10 bars. So we're going to use the average function. So, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, going to increase that up to there. So this is the average of those 10 numbers. So that's the 10 bar average. And then if I want to calculate the moving average for each of the following bars, all you need to do is drag this formula down. And you can see this one uses those 10, this one uses those 10. And you can see how the, the formula increments down on each new day. It drops off the last, the oldest day, and it picks up the newest day. So this is how the simple moving average formula works. You can see it's moving those 10 bars down, down, down for each new bar of data. And the moving average does use the Current day. So today's moving average uses today's closing price. So you can only calculate today's moving average after the market closes. Okay, so that's a simple moving average. Let's have a look at the exponential moving average. Exponential moving average is more complicated. So here's the formula here in Excel.

I'm going to just see if I can zoom in, make this a bit bigger for you. Let's have a look at the formula. So the formula is the Current price times 2 divided by n + 1, where n is the number of bars in the moving average. So closing price times 2 divided by n + 1 + the previous day's value of the exponential Moving average times 1 minus 2 over N plus 1. We saw this formula in a previous video. If you want to know how to. If you want to understand that formula better, watch this video where I explain the formulas for each of the different moving averages. But here, this is how you do it in Excel. So you can see it's the current price times 2 divided by n plus 1 plus the previous value of the exponential moving average times 1 minus 2 times 2 divided by n plus 1. There's the formula. And then if we drag this down, it'll just continue to calculate. So you can see that each day it refers to the new closing price and then the new previous value of the exponential moving average. So how do we calculate the weighted moving average in Excel? The weighted moving average is. Again, it's a pretty complicated formula in Excel. You can see there's a lot of terms in this, but basically what we're doing is we're taking today's price times 10 for a 10 bar moving average plus yesterday's price times 9 plus the day before times 8, plus the day before that times 7, and then the previous one times 6, previous one times 5, times 4, times 3, times 2 times 1. And then we're dividing all of that by n times n plus 1 divided by 2, as you saw in the previous video, where I explained the formulas for each of the different types of moving average. So that's how you calculate it in Excel. It's a bit laborious to put this together, to be completely honest. And I wouldn't do this in Excel, I would just do it in my charting package. Once you've got the formula, you can copy this down and it will just refer to each of the previous 10 bars, just like we saw on the simple moving average. Okay, how do we calculate the TEMA triple exponential moving average in Excel? Well, we're going to start with the exponential moving average. And we're going to build on that in layers. So let's have a look.

First thing we're going to do is remember that the TEMA formula for TEMA requires three exponential moving averages. So the standard exponential moving average, the EMA of the standard EMA and the EMA of the EMA of the standard ema, if that makes sense. So pretty confusing, but here we go. So we're going to use this value here, this column here, which is the standard moving average. Then we're going to calculate the exponential moving average of this one. So we're going to say today's value of the Exponential moving average times 2 divided by n +1 n is 10 in this case, you can see up here. Then we're going to add to that the previous day's value of the EMA2, the second EMA rather, and multiply that by 1 minus 2 divided by n plus 1, just like the formula we explained in the previous, previous video. And so that's the second EMA that you need to calculate tema. The third EMA is exactly the same thing and I'll make the same correction here, exactly the same formula, but applied to the second ema. So the third EMA takes the value of the second ema, times it by the same ratio, adds the previous day's value of that, times it by this same ratio you saw in the previous calculation. And then TEMA is a combination of this, this and this. So it's the triple exponential moving average. It takes into account all three. Right? So here's the formula. It's three times the standard ema, minus three times the second EMA that we've calculated, plus the third EMA that we calculated, and there's the value for tema. And then we can just drag this down and Excel will continue to calculate it. So as you can see, you can calculate all the different moving averages in Excel. But again, I'd encourage you, if you're using Excel for your technical analysis, it's time to move over to a proper charting and backtesting platform. Because Excel, while yes, it can do it, it's not designed for this sort of calculation. Whereas trading software like amibroker really is designed to calculate long time series of indicators, to combine them into trading systems and to use it. So have a think about that. If you're still using Excel in your analysis and if you want to know how to really use moving averages in your trading systems, then you need to watch this video. Because in this video I explain all the different ways of using moving averages in your trading strategies. So if you haven't yet been able to apply moving averages or you want to have some new ideas on how to use moving averages in your trading strategy, this is a video you need to watch next.