Excel Formula for SAR (Stop and Reverse)

Objective

The objective is to create an Excel formula to calculate the Stop and Reverse (SAR), which is a trend-following indicator used in technical analysis to determine potential price reversals in the market.

Formula Overview

The SAR is calculated based on the prior values of the indicator, the extreme point (the highest high or the lowest low), and a predefined acceleration factor. The formula may vary slightly depending on whether the market is trending upwards or downwards.

General Formula

• For Uptrend: SAR(n) = SAR(n-1) + AF * (EP - SAR(n-1))

• For Downtrend: SAR(n) = SAR(n-1) + AF * (EP - SAR(n-1))

Where:

• SAR(n): Current SAR
• SAR(n-1): Previous SAR
• AF: Acceleration Factor (e.g., 0.02, increases by 0.02 with each new extreme point)
• EP: Extreme Point (highest high for uptrend, lowest low for downtrend)

Implementation Steps

1. Data Preparation

   • Organize your data in a tabular format with columns for Date, High, Low, and Close prices.

2. Initialize Values

   • Starting SAR: Use the initial low point for the first downtrend or the initial high for the first uptrend.
   • Initial AF: Set to 0.02.

3. Calculate SAR

   • Create a column for SAR based on the previous day’s SAR and the current day’s EP and AF.

Example Formulas

Uptrend Calculation

Assuming SAR starts at cell D2, EP at H3, and AF is constant at 0.02:

1. Initial Calculation:

   • D2 = Initial High Price (first day of the uptrend)

2. Next Calculation:

   • In D3 (for the next day): = D2 + 0.02 * (H3 - D2)

Downtrend Calculation

Assuming SAR starts at cell D2, EP at H3, and AF is constant at 0.02:

1. Initial Calculation:

   • D2 = Initial Low Price (first day of the downtrend)

2. Next Calculation:

   • In D3 (for the next day): = D2 + 0.02 * (H3 - D2)

Detailed Explanation

• The D2 cell represents the previous SAR, which is updated every day based on whether the trend is upwards or downwards.
• H3 is where the Extreme Point for the relevant trend (highest high or lowest low) is referenced.
• The formula computes the new SAR by adjusting it based on the difference between the current EP and the previous SAR, modifying it by the acceleration factor.

Practical Example

Conclusion

This guide provides you with the necessary calculations to implement the SAR indicator using Excel. For further learning and advanced techniques, consider exploring courses offered on the Enterprise DNA platform to enhance your Excel and data analysis skills.