How to code Parabolic SAR Calulation on Excel?

Referring to Parabolic SAR, I would like to create a formula within Excel to determine the value of Parabolic SAR under column F,

[A]    [B]   [C]   [D]  [E]     [F]

Date  Open High Low Close Parabolic SAR

Does anyone have any suggestions?

Thanks in advance for any suggestions

Eric

 

Formula
The following steps are for calculating Parabolic SAR for a long position.

On the day that the trade is entered, the SAR is the lowest point taken from your previous trade. The extreme point is the highest high from your previous trade.
Gradually the SAR will moved higher and higher whether the price moves higher or not.
The Parabolic SAR is such that the faster the price curve moves, the faster the SAR and price converge.
The SAR is not placed within the range of the previous or the current period. The range is the vertical distance from the highest high to the lowest low. If calculations necessitate it, the lowest low over the 2 day period is used as the SAR for the following day. Otherwise, the Parabolic SAR for the next period is calculated with the following steps:

Subtract the current SAR from the EP, extreme point (highest high ) and multiply what's left by the acceleration factor.
The acceleration factor starts at 0.02 and increments by 0.02 everytime a new extreme point is achieved. The acceleration factor is capped at 0.20, afterwhich it just stays at 0.20.
Add the result to the current SAR.

In your formula the Step is the acceleration factor

SAR(next period)= SAR(current) + Acceleration Factor * (Extreme Point - SARcurrent)

When you open the indicator settings on a platform the default settings are Initial 0.02
Increment 0.02
Maximum .20

What those refer to are the acceleration factors. Initial .02 then you have it max out at 20%

 

Question Info


Last updated October 22, 2018 Views 12,674 Applies to:
Dana wrote:

here is an observation on the code used in Trade Station Securities.

The March 22 bar is a little unique. (QQQ using a daily chart, March 2010)[.]  At the close of the bar on 22 Mar, the High hit a new high, but the low also went below the stop loss that was calculated the previous trading day (19 Mar)[.]  The Stop Loss takes priority, and therefore it reverses.  The new SAR value becomes the High for that day (48.11).


Thanks.  That confirms the change to the algorithm that I suggested.  I uploaded an updated version of "parabolic SAR.xls".

Dana wrote:

The next day, the prices went higher, and touched our new stop loss, and reversed again to the upside.

It is mentioned that the Parabolic SAR doesn't work well when the pattern is not trending.

I believe that date is a moment where it didn't trend too well.


The wikipage I cited mentions (in paragraph #2) this sort of weakness of the parabolic SAR.  It (that is, Wilder) suggests using the Average Directional Index to test the strength of the trend.  Then again, ADI is another Wilder invention.  "Fool me twice, shame on me" ;-).

Did this solve your problem?

Sorry this didn't help.

Great! Thanks for marking this as the answer.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this response?

Thanks for your feedback.

Hi.   If interested, here is an observation on the code used in Trade Station Securities.

The March 22 bar is a little unique. (QQQ using a daily chart, March 2010)

 

At the close of the bar on 22 Mar, the High hit a new high, but the low also went below the stop loss that was calculated the previous trading day (19 Mar)

 

The Stop Loss takes priority, and therefore it reverses.

The new SAR value becomes the High for that day (48.11).   This is the value that gets plotted in a chart.  The tread reversal never developed too well because we hit a new high at the same time we touched our stop loss.

 

The next day, the prices went higher, and touched our new stop loss, and reversed again to the upside.

 

It is mentioned that the Parabolic SAR doesn't work well when the pattern is not trending.

I believe that date is a moment where it didn't trend too well.

 

This doesn't apply to Excel, but you may find it interesting...

Here is just a section of the code.  It would be interesting to convert this to Excel's VBA.

I've added some comments.  The 'o at the beginning of a variable indicates that we are changing the value within the calling routine.  (Similar to ByRef in Excel vba)

 

if oPosition = 1 then   // If we are long
    { CHECK FOR INCOMING LONG POSITIONS }
 begin
 if Low <= oParOp then   //  If the bar's low went below our stop loss (SAR)...
    { ie, if OPENING STOP for this bar was breached }
  begin
  oPosition = -1 ;  //  Indicate we are short
  oTransition = -1 ;  // Indicate we switched.   If called from a spreadsheet, plot value of count (skip if it's a chart)
    { Rev pos & alert - this is a LONG TO SHORT reversal bar }
  oParCl = TradeHH ;   //   initially set the new SAR stop to the Highest High..
    { Set the CLOSING STOP for this bar }
  TradeHH = High ;   // We note that Mar 22, these are the same...
    { Re-initialize TradeHH with the current HIGH }
  TradeLL  = Low ;
    { Re-initialize TradeLL with the current LOW }
  Af = AfStep ;    // ie back to 0.02
    { Re-initialize the Af }

// etc.

 

I've never really looked at this code before, so that's for mentioning it.  :>)

 

Did this solve your problem?

Sorry this didn't help.

Great! Thanks for marking this as the answer.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this response?

Thanks for your feedback.

PPS: I wrote (emphasis added):
after applying that rule and adjusting the SAR accordingly, another rule applies, to wit:  "If the next period’s SAR value is inside (or beyond) the next period’s price range, a new trend direction is then signaled. The SAR must then switch sides.  Upon a trend switch, the first SAR value for this new trend is set to the last EP recorded on the prior trend".
[....]

[....]
However, since 47.32 is greater than tomorrow's low (47.25), a reverse trend is signaled.  Thus, SAR# becomes -1, the beginning of a downtrend.  (The negative number is my own convention for encoding the trend direction.)  And tomorrow's SAR is set to the EP for today (47.95).

That is consistent with the rule in the wikipage.  But it seems odd that in this case, the first SAR of the new downtrend is less than the high on that day.  Normally, that would trigger a reverse trend again ;-).  An infinite loop!

I think the flaw is in the rules.  Whether that is a mistake in the wikipage or in Wilder's algorithm, I cannot say.

In any case, I believe the SAR should always be above or on the price range of the downtrend (or below/on the price range of an uptrend).  That is the spirit of the Tentative SAR calculation.

So I would rewrite the rule to say:  the first SAR of a downtrend is set to the larger of the previous EP and high of the current line ("tomorrow"); the first SAR of an uptrend is set to the smaller of the previous EP and the low of the current line.

Thus, the formula in the SAR column would become (row 12, for example):

=IF(A12=-1,MAX(H11,C12),IF(A12=1,MIN(H11,D12),F12))

EDIT:  For 3/22, that would set SAR to 48.11 instead of 47.95.

I don't have the "authority" (knowledge of the parabolic SAR) to make that change.  But if we look closely at the chart for the online example, it does seem that the author made a similar adjustment.  (Although we might be fooled by display resolution.)

Did this solve your problem?

Sorry this didn't help.

Great! Thanks for marking this as the answer.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this response?

Thanks for your feedback.

PS: I wrote:

[....]
Since the current period (i.e. through today's data, 3/19) is an uptrend (SAR# > 0), the tentative SAR for tomorrow (3/22) is the smallest of the calculated SAR (47.37), today's low (47.32) and yesterday's low (47.55).  So it is 47.32.

However, since 47.32 is greater than tomorrow's low (47.25), a reverse trend is signaled.  Thus, SAR# becomes -1, the beginning of a downtrend.  (The negative number is my own convention for encoding the trend direction.)  And tomorrow's SAR is set to the EP for today (47.95).

It might be helpful to note that the order of calculation in a line is:  Tentative SAR (and Calculated SAR), then SAR#, then SAR, EP and AF.

Also note a subtle nuance of the formulas.  Tentative SAR and SAR# in the current line ("tomorrow") are based on SAR# in the previous line ("today").  But the final SAR, EP and AF in the current line are based on SAR# in the current line.  In effect, SAR# in the current line is a helper cell as well as a critical part of the result.

Also note that EP and AF in the current line ("tomorrow") are not used in calculating SAR in the current line.  Instead, we are merely preparing them for use in calculating SAR in the next line ("day after tomorrow" :->).

If you are confused by the wikipage's use of the terms "today" and "tomorrow" v. the respective terms "previous" and "current" lines, join the club :-).

Did this solve your problem?

Sorry this didn't help.

Great! Thanks for marking this as the answer.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this response?

Thanks for your feedback.

-----------------------------
Office 365 on Windows 10

Did this solve your problem?

Sorry this didn't help.

Great! Thanks for marking this as the answer.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this response?

Thanks for your feedback.

BillGregg wrote:
The excel spreadsheet looks great. Graphed the Close and SAR for the spreadsheet everything looks good

I hope you graphed the high and low price (i.e. the daily price range), not the closing price.  The closing price is not a factor in determining the parabolic SAR.

BillGregg wrote:
[...] except for one date.  On 3/22 the calculated SAR is more than yesterdays or todays low price and the SAR line crosses over an uptrend line. I think this is one of the exceptions that Wikipedia refers to - "If the next period’s SAR value is inside (or beyond) the current period or the previous period’s price range, the SAR must be set to the closest price bound. For example, if in an upward trend, the new SAR value is calculated and if it results to be more than today’s or yesterday’s lowest price, it must be set equal to that lower boundary." [....] Please let me know if you agree

I agree that the rule applies.  But after applying that rule and adjusting the SAR accordingly, another rule applies, to wit:  "If the next period’s SAR value is inside (or beyond) the next period’s price range, a new trend direction is then signaled. The SAR must then switch sides.  Upon a trend switch, the first SAR value for this new trend is set to the last EP recorded on the prior trend".

The line for 3/22 correctly reflects the application of both rules.  I will explain below.

But first, I suggest that you re-download "parabolic SAR.xls" using the same link posted before.

I improved the comments; you might take a second look.  I also added the column "Calculated SAR" to make it easier to understand the behavior of the formula in the column "Tentative SAR".  And I added a chart that shows the parabolic SAR plotted relative to the high-low stock bars.

With those changes, we have the following data:



In the parlance of the wikipage (and perhaps Wilder's text), 3/18 is yesterday's data, 3/19 is today's data, and we are calculating the SAR for tomorrow's data, 3/22.  (I do not like that nomenclature.)

Since the current period (i.e. through today's data, 3/19) is an uptrend (SAR# > 0), the tentative SAR for tomorrow (3/22) is the smallest of the calculated SAR (47.37), today's low (47.32) and yesterday's low (47.55).  So it is 47.32.

However, since 47.32 is greater than tomorrow's low (47.25), a reverse trend is signaled.  Thus, SAR# becomes -1, the beginning of a downtrend.  (The negative number is my own convention for encoding the trend direction.)  And tomorrow's SAR is set to the EP for today (47.95).

This is consistent with the online example that I cited in my previous posting.  To wit:



Disclaimer:  I am not familiar with Wilder's paraboloic SAR beyond what I read in the cited references.  So I cannot say with inpunity that the online example is correct.  However, it is consistent with my interpretation of the wikipage.

Did this solve your problem?

Sorry this didn't help.

Great! Thanks for marking this as the answer.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this response?

Thanks for your feedback.

BillGregg wrote:
Have you figured out the excel formulas to calculate a Parabolic SAR? I have been working currently on a spreadsheet for several days and I'm also having problems. Has anyone done this in excel?

Download "parabolic SAR.xls" (click here) [1].

I relied on the definitions in the "parabolic SAR" wikipage (click here) [2].  I confirmed my interpretation of the algorithm by comparing with online examples (click here) [3].

Hopefully the file is self-explanatory.  Primarily, look at the "complete example" worksheet.  The other worksheets are provided for completeness.  Pay close attention to the cell comments.  Post here if you have any questions about the xls file.

Implementation note:  I use a helper column, "tentative SAR", for efficiency.  It could be avoided; but it would mean replicating its formula in the several cells that reference it.  The helper column could be hidden, if you wish.  But that would hide the implementation of a critical part of the algorithm.


-----
[1] https://www.box.com/s/gbtrjuoktgyag56j6lv0
[2] http://en.wikipedia.org/wiki/Parabolic_SAR
[3] http://stockcharts.com/school/doku.php?id=chart_school:technical_indicators:parabolic_sar


Thanks for your help! The excel spreadsheet looks great. Graphed the Close and SAR for the spreadsheet everything looks good except for one date. On 3/22 the calculated SAR is more than yesterdays or todays low price and the SAR line crosses over an uptrend line. I think this is one of the exceptions that Wikipedia refers to - "If the next period’s SAR value is inside (or beyond) the current period or the previous period’s price range, the SAR must be set to the closest price bound. For example, if in an upward trend, the new SAR value is calculated and if it results to be more than today’s or yesterday’s lowest price, it must be set equal to that lower boundary." I have some experience with excel formulas but I not sure how to build the exceptions into the formula's. Please let me know if you agree and thanks for taking the time to answer my question.

 

Did this solve your problem?

Sorry this didn't help.

Great! Thanks for marking this as the answer.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this response?

Thanks for your feedback.

BillGregg wrote:
Have you figured out the excel formulas to calculate a Parabolic SAR? I have been working currently on a spreadsheet for several days and I'm also having problems. Has anyone done this in excel?

Download "parabolic SAR.xls" (click here) [1].

I relied on the definitions in the "parabolic SAR" wikipage (click here) [2].  I confirmed my interpretation of the algorithm by comparing with online examples (click here) [3].

Hopefully the file is self-explanatory.  Primarily, look at the "complete example" worksheet.  The other worksheets are provided for completeness.  Pay close attention to the cell comments.  Post here if you have any questions about the xls file.

Implementation note:  I use a helper column, "tentative SAR", for efficiency.  It could be avoided; but it would mean replicating its formula in the several cells that reference it.  The helper column could be hidden, if you wish.  But that would hide the implementation of a critical part of the algorithm.


-----
[1] https://www.box.com/s/gbtrjuoktgyag56j6lv0
[2] http://en.wikipedia.org/wiki/Parabolic_SAR
[3] http://stockcharts.com/school/doku.php?id=chart_school:technical_indicators:parabolic_sar

6 people were helped by this reply

·

Did this solve your problem?

Sorry this didn't help.

Great! Thanks for marking this as the answer.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this response?

Thanks for your feedback.

Have you figured out the excel formulas to calculate a Parabolic SAR? I have been working currently on a spreadsheet for several days and I'm also having problems. Has anyone done this in excel?

2 people were helped by this reply

·

Did this solve your problem?

Sorry this didn't help.

Great! Thanks for marking this as the answer.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this response?

Thanks for your feedback.

Can you put some sample data against your description, showing what you have and what you want to achive.

Regards

 Steve

Did this solve your problem?

Sorry this didn't help.

Great! Thanks for marking this as the answer.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this response?

Thanks for your feedback.