so you may have heard the terms moving averages or weighted moving averages but what do those
actually mean usually when you hear these terms it will be probably associated with stock market
or maybe with some sort of currency like bitcoin or something like that but it could be really for
any type of data and to really understand what those things are you really need to learn how to
calculate those and luckily those calculations are not complicated so let's try to cover those
in this video and see what exactly you get out of these moving averages so let's just get some
stock data here so i'll just do google finance function i'll grab a ticker here let's
use for google i guess and we'll do closing price i'll start from january 1st 2021
up to let's just do november and see how much data we can get here it doesn't really matter
so as you can see we have some data over time in this particular example
and it starts from january 4th as you can see here and it goes all the way down to
october 22nd so what moving average is going to do it's going to grab the latest x number of data
points and get the average so often time you will hear something like seven day moving average or
four day moving average or 14 day moving average so let's do an example of seven day moving average
so if we wanted to do that we would grab the latest seven data points one two three four
five six seven sum them up and because i get seven of them i would take this number and divide it by
seven and that would be the average for the latest seven data points now we could do this using
average function here so we'll just remove this and do average and basically just grab from here
one two three four five six seven so now if we take this and drag it one up what it's going to do
if we look at this one it will basically just move this seven data points one up so now we get
previous seven data points average and then if we drag it another up it's gonna do the other seven
so we keep going backwards getting the average of previous seven days and this is what we call
seven day moving average so if i just keep dragging this up
and at some point we don't really have seven days to do this calculation so i get this reference
errors i'm just going to stay with that that's fine so i'll just call it 7 day moving average
so let's now try to understand what does this actually accomplish so to do that let's first just
do a graph of this first regular closing number so this is the actual price for this particular
stock so if we just do a graph i select this date and that price and i do insert and do a chart
it will basically be a line chart and you'll see it basically illustrates that price over time
so now what i'm going to do i'm going to basically take this and in addition to this i'm going to
also graph that 7 day average next to this to see what exactly that gives us so i'm gonna get
rid of this and i'm gonna highlight all of these three columns including this seven-day average
and now we have this right now if you look here we basically have our price for this basically stock
over time we started from january 4th went all the way up to october whatever the date was and you'll
see here the blue one which is this is the actual graph that we get for this particular ticker and
you can see that it's really just going up and down really quickly because we have a lot of
price changes from day to day now if you look at the seven day moving average it's more like line
that's more smooth that gives you the overall trend instead of having all those bumps going
up and down and the more days you choose to do the average the more smooth that line is gonna get so
let's do an example of that so you can see how that works so i'm gonna go ahead and do another
one i'll delete this one let's add one more so this was seven day moving average let's try to
do moving average of say 14 days so all i have to do just grab 14 data points so let's do that
again close parenthesis hit enter and then i'm gonna drag this up
so now i got 14 day and i got seven day and i got the actual
let's try to look at all of these together
let's close these
what you're going to see now the blue one is the actual so you can see there's a lot
of spikes going up and down then we have the red one which is our seven day average
so you can see that's our seven day average here and then we have 14 day average which is
this yellowish sort of line and then you can see it's even now smoother line that gives us again
the overall trend of this now one thing to notice with moving averages is that the response you get
out of this is delayed from the actual data itself so because of the nature of how we grab the last
seven data points and get the average what is always going to happen is that we get some sort
of trend happening here see like the original blue one we see that over here it's starting to go up
but then we see that correction for our seven day average a lot later from that so because of that
seven day average we do there's this delay of our moving average line adjusting and getting
closer to the actual line so while it's great to see our overall kind of trend over time
it may not be so great to predict what's going to happen in the near future now let's try to
change this 14 day moving average to a smaller number so i'm gonna go here and delete this
i'm gonna change this to say three day or four day moving average right so i'll just grab this
and let's do something like four and again i'm going to drag this up
and we'll try to graph this
now let's look again and what you're gonna notice now is that if you do less days like
four day moving average you'll see that we see our average line adjusting closer to our actual
line a lot faster so if you want something that gets closer to the actual graph you can reduce
the number of data points you use like in this case data points we have days number of days
and you will get something that's actually closer to the actual numbers and then the longer you go
then you get more like flat line that gives you the overall trend of the whole thing
so that's our moving average as you can see it's not that complicated now because of this delayed
response of what's happening with moving averages we have this thing weighted moving average and
the idea of that is we want to adjust faster to what's actually happening instead of having this
long delay to get closer to our real amounts and the way weighted average works you give
more importance to your later numbers and less importance to your earlier numbers so what that
means let's actually delete this one and i'll keep that seven day moving average here that's fine
so this is our seven day moving average right here let's just highlight that in a different color
and these are our actual numbers right here now the idea of weighted moving average is going to
be that we want to give more importance to our later data points like later days
than our earlier days and the way we do that let's say we want to do this for seven days again right
so what we would do we would grab those data points so i'm just gonna number them
one two three four five six seven those are our seven data points from here through here
and then if we just take this one two three four five this numbering and sum those numbers
right here
see we get 28. that's the total for the sequence i got here basically that 28. so now what i can do
i can take each one of these numbers right here this and divide it by that 28.
that i got here and if we drag it down we'll get some sort of percentages see this one is higher
percentage 0.25 like quarter pretty much then this is a little less less less so the further
we go from our latest one the less this percentage gets but what's gonna happen overall if we just
grab all of these and sum them up like if i had to just sum up all of these percentages down here
let's actually make sure we get all of them like this we should basically get hundred percent again
we get a one because we don't illustrate them as a percentage but if i just change them to percentage
you'll see that these are our basically weights we assign to each data point which is certain
percentage and if we add them all together we get 100 percent all combined i'm going to undo this
to get to just numbers without that percentage formatting so now at this point what we can do
once we get this weight we can go ahead and take each one of those
and multiply it by its respective value and with this if i drag it all up we're gonna get
these amounts so at this point if we just do some of all of these numbers
this would be your weighted moving average this number right here
so now i'm going to try to create one formula to calculate this number without having to do all
this several multiple steps i just did here so the first one is going to be to get this
sequence from one to seven and for that we can use sequence function
so here we can give it number of rows which is going to be seven
and number of columns which is going to be one
and if i hit enter we'll get our sequence from one to seven if you
wanted to do four day average you would do one to four in this case it's gonna be seven
now i'm gonna get that sequence one more time right next to this so let's grab that
same function copy paste it over here and if you remember what i did i got this 28
which was the sum of that sequence of numbers so if i just grab this and put it in some function
i will get that 28 which is this 28 right here and then the next thing i did if you
remember i took each one of these numbers in the sequence and i divided by that 28. so
i'll grab this calculation that gives me that 28 copy that hit escape go back to
this formula that gives me the sequence of numbers and simply divide it by that
number and now it's gonna be an array formula so i'm gonna do ctrl shift enter or command
shift enter to get array formula wrapper around this hit enter and i'm gonna get
those same percentages right here i don't need to keep this anymore i can get rid of that so these
are those percentages right here so far all in one formula now the next thing i did if you remember
once i got these percentages then i did this and i did that by taking each one of these data points
and i multiplied by the percentage right here so we have those here in this array
formula i am going to move this formula right now cut that out of here and move it over here
so it's easier to see everything in one spot so this was that formula i did let's just do
spaces i guess so this is that formula i got here so what i need to do now
i need to take those and multiply by these numbers over here so that's going to be as
simple as just going here and multiply them by our numbers the same 7 from that particular range
just like that if i hit enter you'll see i get exactly these same amounts and the final part
of this was to get this to sum them all up to get one total and for that i'm just gonna go back to
this formula right here and i'm just going to simply add some around this whole thing
there it is at this point i'm going to grab this formula cut that out of here and paste it
in the line where it actually should be so this is our weighted average for the last
seven days so now we should be able to just grab this and drag it up to get the rest of them
so i'm gonna get rid of all of these other formulas because we don't need them anymore
that was just to explain you what we're about to do and i'm gonna grab this and drag it up
let's just name this column
so again the whole idea of this was because we assign higher percentages to later data points
later days we should move our average closer to our recent
numbers so let's look at this on the chart and see what it looks like so we'll compare
our regular moving average to our weighted moving average
so now again the blue line is our regular graph which shows the real numbers over time
then we got our seven day moving average which is this red one over here this one and then we got
our seven day weighted moving average and you can see how the yellow line weighted moving average
it's going nearer that blue line a lot faster compared to the red one see so basically if you
want to move your regular moving average line closer to your latest data point
so basically this way you get it to react faster to those changes that are happening you can use
this weighted moving average and this is why people who are probably working with real time
data they might want to look at this because this is more likely to move faster near our real-time
events at the moment compared to our regular moving average which is kind of slower to react
so that's that and again you could calculate this for any number of days
right now i did seven days but i could obviously go back and grab that same
formula here copy paste over here and if i wanted to do not seven days but do 10 days
then i'll change this to 10 this to 10
and finally take this
and instead of highlighting seven days i'm going to highlight
10 and then drag it up and that's your 10 day weighted moving average
and that should do it thanks for watching please subscribe and i'll see the next video