Home MarketXLS
Dashboard MarketXLS
Screener MarketXLS
Options Profit Calculator MarketXLS
Stock Ranks MarketXLS
Spreadsheet Builder MarketXLS
Documentation MarketXLS
Logout MarketXLS

Williams %R Indicator for Technical Analysis

The Williams %R indicator is a momentum oscillator that helps identify overbought and oversold conditions in the market. With the WilliamsR function in MarketXLS, you can seamlessly calculate this indicator directly in Excel—a straightforward way to gauge price momentum and potential trend reversals without manual data manipulation or separate software.

Why Use This Function?

  • Helps identify market momentum and potential reversals.
  • Eliminates manual data calculation—focus on analysis and strategy instead.
  • Enables quick comparisons across different securities, time periods, and custom start dates.
  • Integrates seamlessly into your Excel-based trading or investment workflows.
  • Returns instantaneous results, plus easy refreshes if you need updated data.

How to Use in Excel

Use this function in any Excel cell with MarketXLS installed. Simply type the function, supply the required parameters, and press Enter:

=WilliamsR(Symbol, [Days], [StartDate])

Example:
• =WilliamsR("AAPL") – Uses default 14 days and the current date if omitted.
• =WilliamsR("AAPL", 14, "01-Jan-2023") – Calculates the Williams %R starting from January 1, 2023.

If Days or StartDate are not valid, the function returns "NA." You may also see "Refreshing" if data is still being updated in the background.

Parameters Explained

Parameter Description Example Values Notes
Symbol The ticker symbol (e.g., stock, ETF) to calculate the Williams %R for. "AAPL", "MSFT" Invalid symbols return "NA."
Days Optional. The number of periods for the Williams %R calculation. Defaults to "14" if omitted. 14, 20, 9 Some assets may use a longer or shorter period. If set to 1, the function returns "NA" as it’s not valid.
StartDate Optional. The date from which to begin the calculation. Defaults to today's date if omitted or if an invalid date is provided. "01-Jan-2023", "5/5/2023" Passing very old or no date reverts to a standard endpoint without a custom start date.

Example Usage

Basic Examples

  1. Basic 14-Day Calculation
    • Enter in a cell: =WilliamsR("AAPL")
    • This retrieves data for AAPL (Apple) and calculates its 14-day Williams %R ending at the most recent date available.
    • A single numerical result (e.g., -26.74) is returned, where values closer to -100 suggest oversold and closer to 0 suggest overbought.

  2. Specifying a Different Day Period
    • =WilliamsR("MSFT", 20)
    • Uses a 20-day period for Microsoft’s data.
    • This widens or narrows the oscillator’s sensitivity, depending on how many days you choose.

  3. Using a Custom Start Date
    • =WilliamsR("TSLA", 14, "01-Jan-2023")
    • Extracts data for Tesla from January 1, 2023, to the latest available date.
    • Useful for focusing on a specific timeframe in your analysis.

Advanced Scenarios

  1. Streamlined Comparisons
    • Place multiple formulas in adjacent cells, e.g., =WilliamsR("AAPL") in one cell and =WilliamsR("MSFT") in another.
    • Compare relative momentum or oversold/overbought conditions across multiple symbols at once.

  2. Integrating With Other Excel Functions
    • Use IF statements to highlight specific ranges. For example:
    =IF(WilliamsR("GOOG") < -80, "Oversold", "Neutral/Overbought")
    • This automatically flags oversold conditions within your spreadsheet.

  3. Trading Strategy Examples
    • Combine with other technical indicators (e.g., RSI) to build rules-based strategies in Excel.
    • Example:
    =IF(AND(WilliamsR("AAPL") < -80, RSI("AAPL",14) < 30), "Potential Buy Signal", "Hold")

Common Questions and Troubleshooting

• “Why does the formula return NA?”

  • The symbol could be invalid.
  • Days parameter might be set to 1 or missing in a way the system cannot parse.
  • StartDate might be in an invalid format.

• “Why am I seeing ‘Refreshing’?”

  • The data is being updated or refreshed on the server side. Wait a moment and re-check the cell.

• “Are there known date range quirks?”

  • Start dates prior to 1978 typically default back to standard historical data pulls. Ensure your date is valid to avoid unexpected results.

• “Can I see historical series data for multiple intervals?”

  • By default, the function returns a single numerical value for the most recent calculation. In some advanced setups, alternate parameters may retrieve a broader data series, but this typically requires more coding knowledge and is not always necessary for most users.

Remember:
• Williams %R is momentum-based, so it’s most helpful when combined with other confirmation measures.
• Values near -20 can indicate overbought conditions; near -80 can indicate oversold.
• Always use multiple analyses and signals for well-rounded trading decisions in real market scenarios.

By leveraging WilliamsR with MarketXLS, you streamline your technical analysis right inside Excel—saving time and enabling faster, data-driven decisions.

Get Access to 1 Billion Usable Market data points IN YOUR EXCEL SHEETS WITH EASY TO USE EXCEL FUNCTIONS

Get started today
MarketXLS Excel Add-in Tutorial - How to Use Willams R Indicator and Other Financial Formulas
How does MarketXLS work?