In the March 2025 issue of Technical Analysis of STOCKS & COMMODITIES magazine John Ehlers, President of MESA Software, presented "A New Solution, Removing Moving Average Lag."
The article featured a solution to smoothing market data using a technique that reduced the lag inherent to using a simple moving average. Ehlers points out that a moving average is an average of a block of prices and the average price is plotted as a dot in the center of the block of prices. The result is the moving average lags the prices on the chart by half the length of the lookback period of the average.
Ehlers solution is to remove the lag of the moving average by "adding the slope times half the length of the average to the average itself." The new study is the Projected Moving Average.
Projected Moving Average = Average + Slope*Length/2
The slope is the slope of a linear regression line fitted to the prices.
This image below displays a 30-day simple moving average (blue line) versus the 30-day PMA (black line).
This post provides an Excel spreadsheet for calculating the PMA and a CQG PAC to install a page with the PMA applied to a chart using CQG's XL Time Series study.
The Excel spreadsheet pulls in the market data. Column G calculates the simple moving average using an RTD function that pulls the data from CQG:
= RTD("cqg.rtd",,"StudyData", $N$2, "MA", "InputChoice=Close,MAType=Sim,Period="&$N$14&"", "MA",$N$4,$A2,$N$6,,,$N$8,$N$12)
The slope is using an RTD function that pulls the data using a CQG function MLRSlope:
= RTD("cqg.rtd",,"StudyData","MLRSlope("&$N$2&",Period:="&$N$14&",InputChoice:=Close)", "BAR", "", "Close", $N$4, $A2, $N$6,$N$10,,$N$8,$N$12)The PMA is calculated in column J:
=G2+(H2*($N$14*0.5))
The Date, Time and PMA are presented on Sheet2:
The XL Time series custom study uses the spreadsheet name, the sheet name and the cell references:
XLTS1(@,"RTDBarDataOHLC_PMA.xlsx","Sheet2","A1:B1001",0)
It is important the everything matches: spreadsheet name (including extension), sheet name and cell references.
Both the spreadsheet and CQG Pac to install a page with the PMA used is included in the post.
Requires CQG Integrated Client or CQG QTrader, and Excel 2016 or more recent.