Technical Indicator Library Excel Formula
Below we list Excel formula to call technical indicator library from your Excel. All these technical indicator functions are located inside TechnicalAnlaysis.xll file. TechnicalAnalysis.xll file is free to use and free to share without any limitation. We have put some friendly Copy Right Notice on the bottom of this document to protect the developers and contributor. In general, this TechnicalAnalysis.xll file and Technical Indicator library inside the file can be used by anyone and it is free of charge. To use these functions from your Excel, you should load the TechnicalAnlaysis.xll add-in to your Excel first. For the paid users for Quant Strategy Inventor, the installation can be done automatically when you first load our Quant Strategy Inventor. For free users of this TechnicalAnlaysis.xll, please follow the simple installation steps below.
1. Install TechnicalAnalysis.xll file
Download Market Forecasting Algorithm and Technical Analysis Library with Graphical User Interface. This is the entirely free Excel Add-Ins for you. With this tool, you have an access to 9 different exponential smoothing prediction + many Technical indicators including ZigZag indicator.
www.algotrading-investment.com/FreeStuffs/MarketForecastingAlgorithmRelease.zip
Full instruction for Market Forecasting Algorithm and Technical Analysis Library Add-Ins can be found from the link below:
In addition, if you are not an Excel person, then we also provide the similar but standalone Market Prediction Software built in Dot Net Framework. You can use this free standalone Market Prediction Software for your Stock Market and Forex Market Trading. Use them at your own risk.
Please note that above Market Forecasting Algorithm and Technical Analysis Library Add-Ins and the standalone software are absolute free tools to use. Hence, we do not provide you any support for the above free tools.
2. Example Usage of Functions
All the functions are array formula. Therefore, you have to enter these formula using “Ctrl +Shift+Enter” keys. You should include “=TA_” syntax before Function name.
For example, for following Bollinger Bands function below:
BBANDS – Bollinger Bands
upperband, middleband, lowerband = BBANDS(close, timeperiod=5, nbdevup=2, nbdevdn=2, matype=0)
You will enter “=TA_BBANDS(I7:I30, 12, 2, 2, 1)” to range L7:N30 assuming your price data are located at the range E6:K30.
We can take another example for CCI function as shown below.
CCI – Commodity Channel Index
real = CCI(high, low, close, timeperiod=14)
Here is how to put this CCI function in your worksheet. “=TA_CCI(G7:G30, H7:H30, I7:I30, 13)” to range L7:L30 assuming your price data are located at the range E6:K30.
_
_
3. Overlap Studies Functions
_
_
BBANDS – Bollinger Bands
upperband, middleband, lowerband = BBANDS(close, timeperiod=5, nbdevup=2, nbdevdn=2, matype=0)
_
_
DEMA – Double Exponential Moving Average
real = DEMA(close, timeperiod=30)
_
_
EMA – Exponential Moving Average
real = EMA(close, timeperiod=30)
_
_
HT_TRENDLINE – Hilbert Transform – Instantaneous Trendline
real = HT_TRENDLINE(close)
_
_
KAMA – Kaufman Adaptive Moving Average
real = KAMA(close, timeperiod=30)
_
_
MA – Moving average
real = MA(close, timeperiod=30, matype=0)
_
_
MAMA – MESA Adaptive Moving Average
mama, fama = MAMA(close, fastlimit=0, slowlimit=0)
_
_
MAVP – Moving average with variable period
real = MAVP(close, periods, minperiod=2, maxperiod=30, matype=0)
_
_
MIDPOINT – MidPoint over period
real = MIDPOINT(close, timeperiod=14)
_
_
MIDPRICE – Midpoint Price over period
real = MIDPRICE(high, low, timeperiod=14)
_
_
SAR – Parabolic SAR
real = SAR(high, low, acceleration=0, maximum=0)
_
_
SAREXT – Parabolic SAR – Extended
real = SAREXT(high, low, startvalue=0, offsetonreverse=0, accelerationinitlong=0, accelerationlong=0, accelerationmaxlong=0, accelerationinitshort=0, accelerationshort=0, accelerationmaxshort=0)
_
_
SMA – Simple Moving Average
real = SMA(close, timeperiod=30)
_
_
T3 – Triple Exponential Moving Average (T3)
real = T3(close, timeperiod=5, vfactor=0)
_
_
TEMA – Triple Exponential Moving Average
real = TEMA(close, timeperiod=30)
_
_
TRIMA – Triangular Moving Average
real = TRIMA(close, timeperiod=30)
_
_
WMA – Weighted Moving Average
real = WMA(close, timeperiod=30)
_
_
4. Oscillator Indicators
_
_
ADX – Average Directional Movement Index
real = ADX(high, low, close, timeperiod=14)
_
_
ADXR – Average Directional Movement Index Rating
real = ADXR(high, low, close, timeperiod=14)
_
_
APO – Absolute Price Oscillator
real = APO(close, fastperiod=12, slowperiod=26, matype=0)
_
_
AROON – Aroon
aroondown, aroonup = AROON(high, low, timeperiod=14)
_
_
AROONOSC – Aroon Oscillator
real = AROONOSC(high, low, timeperiod=14)
_
_
BOP – Balance Of Power
real = BOP(open, high, low, close)
_
_
CCI – Commodity Channel Index
real = CCI(high, low, close, timeperiod=14)
_
_
CMO – Chande Momentum Oscillator
real = CMO(close, timeperiod=14)
_
_
DX – Directional Movement Index
real = DX(high, low, close, timeperiod=14)
_
_
MACD – Moving Average Convergence/Divergence
macd, macdsignal, macdhist = MACD(close, fastperiod=12, slowperiod=26, signalperiod=9)
_
_
MACDEXT – MACD with controllable MA type
macd, macdsignal, macdhist = MACDEXT(close, fastperiod=12, fastmatype=0, slowperiod=26, slowmatype=0, signalperiod=9, signalmatype=0)
_
_
MACDFIX – Moving Average Convergence/Divergence Fix 12/26
macd, macdsignal, macdhist = MACDFIX(close, signalperiod=9)
_
_
MFI – Money Flow Index
real = MFI(high, low, close, volume, timeperiod=14)
_
_
MINUS_DI – Minus Directional Indicator
real = MINUS_DI(high, low, close, timeperiod=14)
_
_
MINUS_DM – Minus Directional Movement
real = MINUS_DM(high, low, timeperiod=14)
_
_
MOM – Momentum
real = MOM(close, timeperiod=10)
_
_
PLUS_DI – Plus Directional Indicator
real = PLUS_DI(high, low, close, timeperiod=14)
_
_
PLUS_DM – Plus Directional Movement
real = PLUS_DM(high, low, timeperiod=14)
_
_
PPO – Percentage Price Oscillator
real = PPO(close, fastperiod=12, slowperiod=26, matype=0)
_
_
ROC – Rate of change : ((price/prevPrice)-1)*100
real = ROC(close, timeperiod=10)
_
_
ROCP – Rate of change Percentage: (price-prevPrice)/prevPrice
real = ROCP(close, timeperiod=10)
_
_
ROCR – Rate of change ratio: (price/prevPrice)
real = ROCR(close, timeperiod=10)
_
_
ROCR100 – Rate of change ratio 100 scale: (price/prevPrice)*100
real = ROCR100(close, timeperiod=10)
_
_
RSI – Relative Strength Index
real = RSI(close, timeperiod=14)
_
_
STOCH – Stochastic
slowk, slowd = STOCH(high, low, close, fastk_period=5, slowk_period=3, slowk_matype=0, slowd_period=3, slowd_matype=0)
_
_
STOCHF – Stochastic Fast
fastk, fastd = STOCHF(high, low, close, fastk_period=5, fastd_period=3, fastd_matype=0)
_
_
STOCHRSI – Stochastic Relative Strength Index
fastk, fastd = STOCHRSI(close, timeperiod=14, fastk_period=5, fastd_period=3, fastd_matype=0)
_
_
TRIX – 1-day Rate-Of-Change (ROC) of a Triple Smooth EMA
real = TRIX(close, timeperiod=30)
_
_
ULTOSC – Ultimate Oscillator
real = ULTOSC(high, low, close, timeperiod1=7, timeperiod2=14, timeperiod3=28)
_
_
WILLR – Williams’ %R
real = WILLR(high, low, close, timeperiod=14)
_
_
5. Volume Indicators
_
_
AD – Chaikin A/D Line
real = AD(high, low, close, volume)
_
_
ADOSC – Chaikin A/D Oscillator
real = ADOSC(high, low, close, volume, fastperiod=3, slowperiod=10)
_
_
OBV – On Balance Volume
real = OBV(close, volume)
_
_
6. Volatility Indicators
_
_
ATR – Average True Range
real = ATR(high, low, close, timeperiod=14)
_
_
NATR – Normalized Average True Range
real = NATR(high, low, close, timeperiod=14)
_
_
TRANGE – True Range
real = TRANGE(high, low, close)
_
_
7. Price Transformation
_
_
AVGPRICE – Average Price
real = AVGPRICE(open, high, low, close)
_
_
MEDPRICE – Median Price
real = MEDPRICE(high, low)
_
_
TYPPRICE – Typical Price
real = TYPPRICE(high, low, close)
_
_
WCLPRICE – Weighted Close Price
real = WCLPRICE(high, low, close)
_
_
8. Cycle Indicator Functions
_
_
HT_DCPERIOD – Hilbert Transform – Dominant Cycle Period
real = HT_DCPERIOD(close)
_
_
HT_DCPHASE – Hilbert Transform – Dominant Cycle Phase
real = HT_DCPHASE(close)
_
_
HT_PHASOR – Hilbert Transform – Phasor Components
inphase, quadrature = HT_PHASOR(close)
_
_
HT_SINE – Hilbert Transform – SineWave
sine, leadsine = HT_SINE(close)
_
_
HT_TRENDMODE – Hilbert Transform – Trend vs Cycle Mode
integer = HT_TRENDMODE(close)
_
_
9. Pattern Recognition Functions
_
_
CDL2CROWS – Two Crows
integer = CDL2CROWS(open, high, low, close)
_
_
CDL3BLACKCROWS – Three Black Crows
integer = CDL3BLACKCROWS(open, high, low, close)
_
_
CDL3INSIDE – Three Inside Up/Down
integer = CDL3INSIDE(open, high, low, close)
_
_
CDL3LINESTRIKE – Three-Line Strike
integer = CDL3LINESTRIKE(open, high, low, close)
_
_
CDL3OUTSIDE – Three Outside Up/Down
integer = CDL3OUTSIDE(open, high, low, close)
_
_
CDL3STARSINSOUTH – Three Stars In The South
integer = CDL3STARSINSOUTH(open, high, low, close)
_
_
CDL3WHITESOLDIERS – Three Advancing White Soldiers
integer = CDL3WHITESOLDIERS(open, high, low, close)
_
_
CDLABANDONEDBABY – Abandoned Baby
integer = CDLABANDONEDBABY(open, high, low, close, penetration=0)
_
_
CDLADVANCEBLOCK – Advance Block
integer = CDLADVANCEBLOCK(open, high, low, close)
_
_
CDLBELTHOLD – Belt-hold
integer = CDLBELTHOLD(open, high, low, close)
_
_
CDLBREAKAWAY – Breakaway
integer = CDLBREAKAWAY(open, high, low, close)
_
_
CDLCLOSINGMARUBOZU – Closing Marubozu
integer = CDLCLOSINGMARUBOZU(open, high, low, close)
_
_
CDLCONCEALBABYSWALL – Concealing Baby Swallow
integer = CDLCONCEALBABYSWALL(open, high, low, close)
_
_
CDLCOUNTERATTACK – Counterattack
integer = CDLCOUNTERATTACK(open, high, low, close)
_
_
CDLDARKCLOUDCOVER – Dark Cloud Cover
integer = CDLDARKCLOUDCOVER(open, high, low, close, penetration=0)
_
_
CDLDOJI – Doji
integer = CDLDOJI(open, high, low, close)
_
_
CDLDOJISTAR – Doji Star
integer = CDLDOJISTAR(open, high, low, close)
_
_
CDLDRAGONFLYDOJI – Dragonfly Doji
integer = CDLDRAGONFLYDOJI(open, high, low, close)
_
_
CDLENGULFING – Engulfing Pattern
integer = CDLENGULFING(open, high, low, close)
_
_
CDLEVENINGDOJISTAR – Evening Doji Star
integer = CDLEVENINGDOJISTAR(open, high, low, close, penetration=0)
_
_
CDLEVENINGSTAR – Evening Star
integer = CDLEVENINGSTAR(open, high, low, close, penetration=0)
_
_
CDLGAPSIDESIDEWHITE – Up/Down-gap side-by-side white lines
integer = CDLGAPSIDESIDEWHITE(open, high, low, close)
_
_
CDLGRAVESTONEDOJI – Gravestone Doji
integer = CDLGRAVESTONEDOJI(open, high, low, close)
_
_
CDLHAMMER – Hammer
integer = CDLHAMMER(open, high, low, close)
_
_
CDLHANGINGMAN – Hanging Man
integer = CDLHANGINGMAN(open, high, low, close)
_
_
CDLHARAMI – Harami Pattern
integer = CDLHARAMI(open, high, low, close)
_
_
CDLHARAMICROSS – Harami Cross Pattern
integer = CDLHARAMICROSS(open, high, low, close)
_
_
CDLHIGHWAVE – High-Wave Candle
integer = CDLHIGHWAVE(open, high, low, close)
_
_
CDLHIKKAKE – Hikkake Pattern
integer = CDLHIKKAKE(open, high, low, close)
_
_
CDLHIKKAKEMOD – Modified Hikkake Pattern
integer = CDLHIKKAKEMOD(open, high, low, close)
_
_
CDLHOMINGPIGEON – Homing Pigeon
integer = CDLHOMINGPIGEON(open, high, low, close)
_
_
CDLIDENTICAL3CROWS – Identical Three Crows
integer = CDLIDENTICAL3CROWS(open, high, low, close)
_
_
CDLINNECK – In-Neck Pattern
integer = CDLINNECK(open, high, low, close)
_
_
CDLINVERTEDHAMMER – Inverted Hammer
integer = CDLINVERTEDHAMMER(open, high, low, close)
_
_
CDLKICKING – Kicking
integer = CDLKICKING(open, high, low, close)
_
_
CDLKICKINGBYLENGTH – Kicking – bull/bear determined by the longer marubozu
integer = CDLKICKINGBYLENGTH(open, high, low, close)
_
_
CDLLADDERBOTTOM – Ladder Bottom
integer = CDLLADDERBOTTOM(open, high, low, close)
_
_
CDLLONGLEGGEDDOJI – Long Legged Doji
integer = CDLLONGLEGGEDDOJI(open, high, low, close)
_
_
CDLLONGLINE – Long Line Candle
integer = CDLLONGLINE(open, high, low, close)
_
_
CDLMARUBOZU – Marubozu
integer = CDLMARUBOZU(open, high, low, close)
_
_
CDLMATCHINGLOW – Matching Low
integer = CDLMATCHINGLOW(open, high, low, close)
_
_
CDLMATHOLD – Mat Hold
integer = CDLMATHOLD(open, high, low, close, penetration=0)
_
_
CDLMORNINGDOJISTAR – Morning Doji Star
integer = CDLMORNINGDOJISTAR(open, high, low, close, penetration=0)
_
_
CDLMORNINGSTAR – Morning Star
integer = CDLMORNINGSTAR(open, high, low, close, penetration=0)
_
_
CDLONNECK – On-Neck Pattern
integer = CDLONNECK(open, high, low, close)
_
_
CDLPIERCING – Piercing Pattern
integer = CDLPIERCING(open, high, low, close)
_
_
CDLRICKSHAWMAN – Rickshaw Man
integer = CDLRICKSHAWMAN(open, high, low, close)
_
_
CDLRISEFALL3METHODS – Rising/Falling Three Methods
integer = CDLRISEFALL3METHODS(open, high, low, close)
_
_
CDLSEPARATINGLINES – Separating Lines
integer = CDLSEPARATINGLINES(open, high, low, close)
_
_
CDLSHOOTINGSTAR – Shooting Star
integer = CDLSHOOTINGSTAR(open, high, low, close)
_
_
CDLSHORTLINE – Short Line Candle
integer = CDLSHORTLINE(open, high, low, close)
_
_
CDLSPINNINGTOP – Spinning Top
integer = CDLSPINNINGTOP(open, high, low, close)
_
_
CDLSTALLEDPATTERN – Stalled Pattern
integer = CDLSTALLEDPATTERN(open, high, low, close)
_
_
CDLSTICKSANDWICH – Stick Sandwich
integer = CDLSTICKSANDWICH(open, high, low, close)
_
_
CDLTAKURI – Takuri (Dragonfly Doji with very long lower shadow)
integer = CDLTAKURI(open, high, low, close)
_
_
CDLTASUKIGAP – Tasuki Gap
integer = CDLTASUKIGAP(open, high, low, close)
_
_
CDLTHRUSTING – Thrusting Pattern
integer = CDLTHRUSTING(open, high, low, close)
_
_
CDLTRISTAR – Tristar Pattern
integer = CDLTRISTAR(open, high, low, close)
_
_
CDLUNIQUE3RIVER – Unique 3 River
integer = CDLUNIQUE3RIVER(open, high, low, close)
_
_
CDLUPSIDEGAP2CROWS – Upside Gap Two Crows
integer = CDLUPSIDEGAP2CROWS(open, high, low, close)
_
_
CDLXSIDEGAP3METHODS – Upside/Downside Gap Three Methods
integer = CDLXSIDEGAP3METHODS(open, high, low, close)
_
_
10. Statistics Functions
_
_
BETA – Beta
real = BETA(high, low, timeperiod=5)
_
_
CORREL – Pearson’s Correlation Coefficient (r)
real = CORREL(high, low, timeperiod=30)
_
_
LINEARREG – Linear Regression
real = LINEARREG(close, timeperiod=14)
_
_
LINEARREG_ANGLE – Linear Regression Angle
real = LINEARREG_ANGLE(close, timeperiod=14)
_
_
LINEARREG_INTERCEPT – Linear Regression Intercept
real = LINEARREG_INTERCEPT(close, timeperiod=14)
_
_
LINEARREG_SLOPE – Linear Regression Slope
real = LINEARREG_SLOPE(close, timeperiod=14)
_
_
STDDEV – Standard Deviation
real = STDDEV(close, timeperiod=5, nbdev=1)
_
_
TSF – Time Series Forecast
real = TSF(close, timeperiod=14)
_
_
VAR – Variance
real = VAR(close, timeperiod=5, nbdev=1)
_
_
11. Math Transform Functions
_
_
ACOS – Vector Trigonometric ACos
real = ACOS(close)
_
_
ASIN – Vector Trigonometric ASin
real = ASIN(close)
_
_
ATAN – Vector Trigonometric ATan
real = ATAN(close)
_
_
CEIL – Vector Ceil
real = CEIL(close)
_
_
COS – Vector Trigonometric Cos
real = COS(close)
_
_
COSH – Vector Trigonometric Cosh
real = COSH(close)
_
_
EXP – Vector Arithmetic Exp
real = EXP(close)
_
_
FLOOR – Vector Floor
real = FLOOR(close)
_
_
LN – Vector Log Natural
real = LN(close)
LOG10 – Vector Log10
real = LOG10(close)
_
_
SIN – Vector Trigonometric Sin
real = SIN(close)
_
_
SINH – Vector Trigonometric Sinh
real = SINH(close)
_
_
SQRT – Vector Square Root
real = SQRT(close)
_
_
TAN – Vector Trigonometric Tan
real = TAN(close)
_
_
TANH – Vector Trigonometric Tanh
real = TANH(close)
_
_
12. Math Operator Functions
ADD – Vector Arithmetic Add
real = ADD(high, low)
_
_
DIV – Vector Arithmetic Div
real = DIV(high, low)
_
_
MAX – Highest value over a specified period
real = MAX(close, timeperiod=30)
_
_
MAXINDEX – Index of highest value over a specified period
integer = MAXINDEX(close, timeperiod=30)
_
_
MIN – Lowest value over a specified period
real = MIN(close, timeperiod=30)
_
_
MININDEX – Index of lowest value over a specified period
integer = MININDEX(close, timeperiod=30)
_
_
MINMAX – Lowest and highest values over a specified period
min, max = MINMAX(close, timeperiod=30)
_
_
MINMAXINDEX – Indexes of lowest and highest values over a specified period
minidx, maxidx = MINMAXINDEX(close, timeperiod=30)
_
_
MULT – Vector Arithmetic Mult
real = MULT(high, low)
_
_
SUB – Vector Arithmetic Substraction
real = SUB(high, low)
_
_
SUM – Summation
real = SUM(close, timeperiod=30)
_
_
TechnicalAnlaysis.xll file is free to use for everyone and redistributable without any limitation. To protect the developers and contributors, the following copyright notice should be included when this file is redistributed or when the file is used.
THIS SOFTWARE IS PROVIDED “AS IS” AND ANY EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE ARE DISCLAIMED. IN NO EVENT SHALL THE DEVELOPERS AND CONTRIBUTORS BE LIABLE FOR ANY DIRECT, INDIRECT, INCIDENTAL, SPECIAL, EXEMPLARY, OR CONSEQUENTIAL DAMAGES (INCLUDING, BUT NOT LIMITED TO, PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES; LOSS OF USE, DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER CAUSED AND ON ANY THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY, OR TORT (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT OF THE USE OF THIS SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF SUCH DAMAGE.