weighted moving average

Weighted Moving Average: Formula and Calculation with Excel

·

·

In this article, we embark on a comprehensive exploration of the weighted moving average (WMA) indicator, delving into its formula, diverse applications, and practical calculation methods. We will also demonstrate how to implement WMA in Excel for efficient backtesting, empowering traders with valuable insights into market trends and dynamics.

Understanding Weighted Moving Average

Before delving into the intricacies of WMA indicator, it’s crucial to grasp the concept of moving average. Moving average is a technical analysis tool that smooths out price data by creating a constantly updated average price. This average is taken over a specific period, with each data point having equal weight. The purpose of moving average is to identify trends and potential reversal points in the market.

Traditional moving averages, such as the simple moving average (SMA), calculate the mean of a set of prices over a defined period. While SMA is effective in smoothing out price fluctuations, it may lag the latest price action due to its equal weighting system. The weighted moving average, on the other hand, addresses this limitation by assigning different weights to each price data point. Unlike SMA, where each data point has equal significance, WMA gives more weight to recent prices. This means that the latest prices have a greater impact on the average, resulting in a quicker response to price movements.

Weighted Moving Average EMA SMA comparison

The comparison of 30-period WMA, SMA, and EMA reveals that the response rate of WMA to price changes falls between that of SMA and EMA.
The chart source: TradingView

The Weighted Moving Average Formula

The formula for calculating a weighted moving average is as follows:

Weighted Moving Average Formula

Where:

  • Pn​ – price of the n-th data point
  • wn​ – weight of the n-th data point. The sum of all weights is equal to 1
  • n – number of periods

The weights assigned to each data point typically follow a linear or exponential pattern. In a linear WMA, the weights increase linearly with each successive data point. In an exponential WMA, the weights increase exponentially, with the most recent data point having the highest weight.

Example of The Weighted Moving Average Calculation

Let’s illustrate the calculation process with a hypothetical example. Suppose we have a series of closing prices for a stock over five days:

  • Day 1 (P1) = $50
  • Day 2 (P2) = $52
  • Day 3 (P3) = $55
  • Day 4 (P4) = $58
  • Day 5 (P5) = $60

For simplicity, let’s assume a linear weighting scheme where each data point’s weight increases by 1 for every successive period. Thus, the weights for our five-day period would be:

  • Weight for Day 1 (w1) = 1
  • Weight for Day 2 (w2) = 2
  • Weight for Day 3 (w3) = 3
  • Weight for Day 4 (w4) = 4
  • Weight for Day 5 (w5) = 5

Now, we apply the WMA formula substituting the values from our example:

WMA = ($50*1) + ($52*2) + ($55*3) + ($58*4) + ($60*5)

Calculating each term:

WMA = ($50) + ($104) + ($165) + ($232) + ($300) = $851

Finally, we divide the sum of the weighted prices by the sum of the weights to obtain the weighted moving average:

WMA = $851 / (1 + 2 + 3 + 4 + 5) = $851 / 15 ≈ $56.73

Free Backtesting Spreadsheet

Applications in Trading

Weighted moving average indicator holds significant relevance across various trading strategies and timeframes. It serves as a multifaceted tool in market analysis. WMA aids in trend identification, where its placement on price charts allows traders to discern prevailing market directions. When shorter-term WMA intersects above longer-term one, an uptrend is signaled; conversely, a cross below indicates a downtrend. Moreover, by calculating the rate of change of WMA, traders can derive momentum indicators such as the moving average convergence divergence (MACD). These indicators help identify the strength of a trend and potential trend reversals.

WMA indicator serve as valuable tools for determining entry and exit points in trades. Traders may enter long positions when the price crosses above the WMA and exit when it crosses below. Conversely, for short positions, entry occurs below the WMA, with exit above.

Limitations of WMA

While weighted moving average indicator is highly beneficial in many aspects of technical analysis, it’s essential to acknowledge its limitations.

Like other trend-following indicators, WMA can produce false signals, particularly during ranging markets. These false signals can result in whipsaw trades, where traders are caught in rapid price reversals, leading to potential losses. Additionally, while WMA generally responds more swiftly to price movements compared to SMA, it may still lag behind the latest price action, particularly in highly volatile market conditions. This lag can potentially hinder the effectiveness of WMA in providing timely signals for trading decisions, especially during periods of rapid price fluctuations.

Therefore, traders should exercise caution and consider these limitations when incorporating WMA into their trading strategies, employing additional tools or techniques to enhance signal accuracy and mitigate potential risks.

The Weighted Moving Average Calculation with Excel Spreadsheet and Backtesting

Incorporating weighted moving averages into backtesting strategies enhances trading analysis. Below you will find out how to implement WMA calculations using Excel for historical data assessment.

1. Excel Spreadsheet Setup

Create a new sheet within your Excel workbook dedicated to WMA calculations. Input historical price data into separate columns, including date, open, high, low and close prices. It’s essential to ensure that the data is accurate, reliable, and covers a sufficiently long-time horizon to capture diverse market conditions. You can start with Investing.com which provides free historical data in 1D time frame.

Calculate the WMA values based on your chosen weighting scheme, assigning appropriate weights to each data point. Utilize Excel formulas to compute weighted moving averages for each period in the dataset. Implement formulas that apply the specified weights to the relevant price data, summing up the weighted values and dividing by the total weight to derive the WMA value for each period.

Weighted Moving Average Excel template

You can download Excel template for WMA indicator which was created for your convenience.

2. Signal Generation and Analysis

Once WMA values are calculated, analyze the resulting data to identify potential trading signals. Consider integrating WMA signals with those generated by the RSI indicator or the stochastic oscillator for enhanced trading strategies. Evaluate the performance of WMA-based trading strategies. Assess key performance metrics such as total return, maximum drawdown, win rate, and risk-adjusted returns.

3. Sensitivity Analysis and Optimization

Conduct sensitivity analysis by adjusting parameters such as the weighting scheme and lookback period for WMA calculations. Assess how changes in these parameters impact strategy performance and stability. Optimize WMA-based strategies based on the insights gained from sensitivity analysis. Visualize the performance of WMA-based strategies using charts and graphs in Excel. Plot equity curves, drawdowns, and trade distribution to gain insights into strategy behavior and performance dynamics.

Final Thoughts

Weighted moving average stands as a versatile and effective tool for analyzing price trends, identifying potential reversals, and making informed trading decisions. Incorporating WMA into backtesting procedures using Excel spreadsheets enhances the depth and accuracy of trading analysis. By mastering WMA calculations and integrating them with other technical indicators such as the relative strength index, traders can develop robust trading strategies with improved performance and profitability.

Share on Social Media:


Leave a Reply

Your email address will not be published. Required fields are marked *

: