馃攳
How to Calculate and use the MACD Indicator in MS Excel - YouTube
Channel: Mark Ursell
[2]
hello this is mark from trade informed
[5]
comm welcome to this video on how to
[9]
calculate and use the MACD indicator in
[12]
Microsoft Excel
[15]
the spreadsheet that I'm showing on the
[18]
screen is the spreadsheet from my
[20]
previous video on how to use Excel to
[23]
backtest a trading strategy using an ATR
[26]
stop-loss the data that we've got in
[31]
here is from the S&P 500 and it dates
[34]
back from 1950 to the start of 2013 so
[40]
about 63 years worth of weekly data and
[45]
I'm going to calculate the MACD I'm
[47]
going to create some new columns and
[49]
calculate the MACD based on the close
[51]
data so I'm going to put in a column
[55]
here for the fast EMA slow EMA and then
[62]
the difference between them
[70]
and we're also going to have a signal
[73]
line okay so the settings I'm going to
[76]
use for the MACD are 12 + X + 9 which
[81]
are the standard settings to calculate
[84]
an exponential moving average we first
[87]
we need to calculate the factor which is
[91]
the formula for this is 2 divided by the
[95]
number of periods plus 1 and then I can
[104]
just copy this across to the 3
[110]
EMAS the next thing we need to calculate
[115]
the EMA is a previous day's average I
[119]
put that in here we just use the simple
[123]
average the mean to calculate this this
[130]
to two decimal places and then the next
[133]
cell we're going to start our EMA and
[135]
the formula for this is equals the close
[140]
or current period - previous period
[145]
multiplied by our factor and we use F 4
[149]
to make this a reference cell plus the
[154]
previous day's average put this again to
[161]
two decimal places I'm going to
[163]
highlight the new columns in blue so
[165]
we've got a better contrast between the
[167]
new and the existing information and
[170]
then we can just copy this down to all
[172]
the cells below by double clicking on
[175]
the bottom right hand corner of the cell
[177]
so we do the 26 period moving average in
[181]
exactly the same way we use equals
[185]
average to create our previous days
[193]
average and then we use the formula
[196]
equals close-
[199]
previous day multiplied by our factor as
[205]
a reference cell plus the previous day
[212]
and we copy this down again tall cells
[216]
below then we calculate the difference
[219]
between the fast EMA and the slow your
[224]
lane and copy this down and then we can
[230]
calculate the signal the signal line for
[233]
the map D and we use another exponential
[238]
moving average we calculate the average
[240]
of these equals current minus the
[247]
previous average multiplied by our
[251]
factor as a reference cell plus the
[255]
previous day and papi these down okay
[261]
the next thing we we would like when
[263]
we're calculating a Mac do is we want it
[266]
to be in the histogram for it's the most
[269]
easy form to to read it and to
[272]
understand it and that we do simply by
[275]
taking the difference - the signal line
[279]
and when we use the MACD for a trading
[285]
strategy we often use the crossing of
[288]
the histogram as a signal for a trade
[290]
entry and in fact that's what I'm going
[293]
to do now so I'm going to put in a final
[297]
column broad trade entry I'm going to
[304]
use an if function here and the function
[308]
is going to show is going to look at
[311]
whether or not we've had a crossover of
[314]
the histogram from negative to positive
[316]
I'll calculate this this equals is
[322]
greater than zero and also if this is
[329]
lower than zero the previous pay is
[331]
lower than zero then we've had crossover
[334]
I'm last Excel just to put the word text
[338]
and if not then do nothing so we've got
[345]
our formula here our if formula to copy
[347]
down and we can see that here we've had
[350]
a crossover in the MACD and MACD
[355]
histogram and so we've got a trade entry
[357]
and another one down here so there we
[362]
have the MACD calculated in Excel and
[365]
also a nested if statement showing how
[368]
we can use it to signify the trade entry
[371]
the next video that I'm going to do in
[373]
this sequence follows on from this and
[375]
uses the same spreadsheet to back test
[378]
and make the trading strategy and see
[382]
how profitable it would have been on the
[384]
S&P 500 for more information and trait
[389]
about trading and back testing please go
[393]
to WWE and calm
Most Recent Videos:
You can go back to the homepage right here: Homepage