Sidebar: The sine-weighted moving average The formula is a ratio. The numerator is the sum of the closing price multiplied by the sine weightings over the lookback period. The denominator is the sum of the sine weightings. The sine function in Excel assumes the angle is in radians. To convert the angle to degrees, multiply each argument by PI/ 180. The formula requires a counter of n = 1 to 5, so enter the “1” through “5” in cells F2 through F6, respectively. Next, enter the following formula for the numerator into cell C6 and copy down: =SIN($F$2*180/6*PI()/180)*B6+SIN($F$3*180/6*PI()/ 180)*B5+SIN($F$4*180/6*PI()/180)*B4+SIN($F$5*180/6*PI()/ 180)*B3+SIN($F$6*180/6*PI()/180)*B2 The denominator is a constant, but here, we are using the same calculation as in the article. Enter the following formula into cell D6 and copy down: =S I N ($F $2 *1 8 0 /6 *P I ()/1 8 0 )+S I N ($F $3 *1 8 0 /6 *P I ()/ 180)+SIN($F$4*180/6*PI()/180)+SIN($F$5*180/6*PI()/ 180)+SIN($F$6*180/6*PI()/180) The five-point sine-weighted moving average is calculated in column E. Enter the following formula into cell E6 and copy down: =C6/D6 For more information, see this month’s Traders’ Tips column. — T.H. THE SINE-WEIGHTED MOVING AVERAGE The five-point sine-weighted moving average can be calcu-lated in Excel. Sidebar Figure 1 shows an example of the output from the formula presented in the article. The data used is the closing price for Microsoft; column A is the date, while column B is the closing price. SIDEBAR FIGURE 1: MICROSOFT. Here’s an example of the five-point sine-weighted moving average calculated using Excel. |
FOR THOSE ORDERING ARTICLES SEPARATELY: *Note: $2.95-$5.95 Articles are in PDF format only. No hard copy of the article(s) will be delivered. During checkout, click the "Download Now" button to immediately receive your article(s) purchase. STOCKS & COMMODITIES magazine is delivered via mail.
|
