Angle of Simple Moving Average line in excel

[excelcurse]

I was hoping you good folks could help with a slope/angle question which has stumped me (I'm a complete newbie so bear with me):


I have 2 columns: Col_A has time and Col_B has price and I'm able to plot these on a line graph and add a 7 point simple moving average (SMA_7) using the excel trendline tool.


Moving away from charts and working from values alone, I now need to establish if SMA_x is on the up or down (i.e. +Ve or -Ve) and also calculate the steepness.
From this, basic logic can be deduced e.g. do not buy if the SMAn is negative! Or consider buying because SMAn is 'steep' and this asset is really on the move.


Here is my plan:


Firstly, 'smooth out' the SMA so it forms a straight line (not sure how to do this). Then introduce an imaginary reference line from a given reference point and the angle is then basic trigonometry.


My question is, how could I expand my data-set to achieve the above? In any case, I'd have to add begin with a SMA column which is easy enough but from there, I'm stuck. How do I 'smooth out' the 'waviness' of the SMA values?.


I'm not interested in formulas at this stage - but a logical explanation as to how it could be done. Is my plan even sound or is there a better way? I've attached a picture for reference:

 

[offthelip]

I have done a lot of work on testing trading strategies in EXCEL and my immediate thought on your query is that combining a slope calculation with a SMA doesn't make any sense to me at all. The reason is that calculating a SMA creates a lag in the information, calculating the slope of a function will also create a lag,( a different lag) so you are calculating a single function with two different lags. You might just as well calculate an SMA with a longer lag and check whether it is increasing or decreasing.
Using two lags does make sense when you calculate then separately because you can subtract one from the other and get a lagged rate of change which can be very useful. Or you can check whether one is above the other.
You can also calculate the slope of a set of prices directly lfrom the price using the linest function. Doing it this way as well as determining the slope you can also determine how much it is varying around the slope value. Linest porduces a number of statistics I have found that a useful one to use when looking at the slope of prices is the Coefficient of Determination,
I have written this VBA sub which will calculate the 20 day slope of a series of prices for each day going down a column of prices.
Put a series of close prices in column E from row 2 to row 87 in a blank workbook Sheet1 then run this subroutine.
You can quite easily change few number in the routine to calcualate a slpe of a different number of days. or you could use it to do what you originally intended of calculating the slope of a SMA.

Sub calcslope()
Dim yarr(1 To 20) As Double
' Y= mx +b
' V(1,1)= Slope
'(V2,1)= error
'V3,1)= COD
'V4,1)= F statistic
'V5,1)= sum of squares
'V1,2) =b


Worksheets("Sheet1").Select


inarr = Range(Cells(1, 5), Cells(87, 5))
Range(Cells(1, 8), Cells(87, 14)) = ""
outarr = Range(Cells(1, 8), Cells(87, 14))
i = 1
    outarr(i, 1) = "slope"
    outarr(i, 2) = "error"
    outarr(i, 3) = "COD"
    outarr(i, 4) = "F statistic"
    outarr(i, 5) = " Sum of squares"
    outarr(i, 6) = "b"








For i = 22 To 87
  For y = 1 To 20
    yarr(y) = inarr(i - 20 + y, 1)
  Next y
    V = Application.WorksheetFunction.LinEst(yarr, , , True)


    outarr(i, 1) = V(1, 1)
    outarr(i, 2) = V(2, 1)
    outarr(i, 3) = V(3, 1)
    outarr(i, 4) = V(4, 1)
    outarr(i, 5) = V(5, 1)
    outarr(i, 6) = V(1, 2)
 Next i
 Range(Cells(1, 8), Cells(87, 14)) = outarr


End Sub

 

[excelcurse]

appreciate the reply and I'll definitely be checking the script in the morning. Just a quick reply to your comment on 'combining a slope calculation with a SMA doesn't make any sense to me at all'


I use over 20 conditions to a make a BUY decision of which one is very simple: if the SMA200 slope of a given asset is negative, I don't touch it.
The opposite is also true in that if the asset is on the up, I'd expect to see a SMA line pitched at >5 deg. If the pitch is >30deg, I plough more funds into the order because to me, that signals the asset is really on the up.


To say the above is an over simplification would be an understatement but you get the idea.