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

To install TechnicalAnlaysis.xll file, go to Options in your Excel. Then select Add-ins.

s17

When the Add-Ins manager pop up, click on Browse button and select the TechnicalAnalysis.xll file from your hard drive.

s18

Once TechnicalAnalysis.xll files are loaded in your Excel. You can call any of User Defined Function below to build various trading strategies from your Excel. Above installation step can be skipped for paid users of our Quant Strategy Inventor. Below, we list the all the available Technical and Mathematical Function you can call with TechnicalAnalysis.xll file.

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.

s19

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.

s20

_

_

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.