Guide To Excel For Finance: Technical Indicators
  1. Guide To Excel For Finance: Introduction
  2. Guide To Excel For Finance: Goal Seek
  3. Guide To Excel For Finance: PV And FV Functions
  4. Guide To Excel For Finance: HLookup And VLookup
  5. Guide To Excel For Finance: Linking Yahoo! Finance and Other Outside Financial Data To Excel
  6. Guide To Excel For Finance: Ratios
  7. Guide To Excel For Finance: Technical Indicators
  8. Guide To Excel For Finance: Valuation Methods
  9. Guide To Excel For Finance: Advanced Calculations
  10. Guide To Excel For Finance: Conclusion

Guide To Excel For Finance: Technical Indicators

Microsoft offers sophisticated statistical and engineering analysis capabilities with its Analysis ToolPak. Stock market charts and related technical indicators can be manipulated using this service, but there aren't really any specific technical analysis features directly in the Excel application.

However, there are a number of third-party applications that can be purchased and used as "add-ins" to supplement Excel's statistical package. Additionally, a number of technical indicators can be created using basic charts and formulas in Excel. Below is an overview of a number of primary technical indicators, and how they can be created in Excel.

Pivot Points
Pivot points (PP) are closely related to support and resistance levels, which are covered in more detail below. In its most simple form, a pivot point is calculated by taking the average of the high, low, and closing price for a stock or financial asset (below is an example in Excel.) Trading levels can be easily entered into Excel, or through data downloads from Yahoo! Finance, as detailed in the previous section. This pivot point forms the basis for support and resistance levels, as detailed next.


Support and Resistance
Support and resistance levels are used to indicate the points at which a stock might not fall below or trade above, without a certain amount of difficulty. At these levels, a stock may see some support, or might break right through it on its way to either new lows or highs.

Using pivot points, the first resistance level is calculated by doubling the pivot point, then subtracting out the low point of the trading interval used. The first support level also doubles the pivot point value, but subtracts out the high trading price.

A second level of resistance can be calculated by adding the difference of the high and low trades to the pivot point. The second support level subtracts the difference of the high and low trades from the pivot point.

The third level of resistance and support is calculated as follows:

Third resistance = High + 2(PP - Low)
Third support = Low - 2(High - PP)

Pivot Tables
In Excel, Pivot Table reports help summarize, analyze, explore and present basic summary data. As such, it is customizable to analyzing technical indicators in financial markets. According to Excel, here is an overview of what they can help a user do:

  • Query large amounts of data in many user-friendly ways.
  • Subtotal and aggregate numeric data, summarize data by categories and subcategories and create custom calculations and formulas.
  • Expand and collapse levels of data to focus your results and drill down to details from the summary data for areas of interest to you.
  • Move rows to columns or columns to rows (or "pivoting") to see different summaries of the source data.
  • Filter, sort, group and conditionally format the most useful and interesting subset of data to enable you to focus on the information that you want.
  • Present concise, attractive and annotated online or printed reports.
Bollinger Bands®
A Bollinger Band is a band plotted two standard deviations away from a simple moving average. Below is a chart of Bollinger Bands:


A blog providing an overview of technical analysis for beginners recently provided an overview of how a Bollinger Band can be created in Excel. Below is an overview of the primary inputs:

Column , values and formulas to create:
A = Company Name/date
B = Open
C = High
D = Low
E = LTP/close
F = Volumes

Moving Averages
A moving average is used to track trends in the way a stock or financial asset trades. It is intended to smooth out daily fluctuations and indicate if the asset might be trading above, at or below certain trends over time.

With existing data that includes a date and daily trading levels, a moving average can be calculated in Excel. The "AVERAGE" function in Excel will be used to calculate moving averages for certain intervals, such as a 50-day or 200-day moving average. Then, it can be determined how the asset is currently trading in relation to this moving average.

Relative Strength Index
The relative strength index, or RSI, can be calculated in Excel via a straightforward cell calculation. The RSI is a technical momentum indicator that compares the magnitude of recent gains to recent losses in an attempt to determine overbought and oversold conditions of an asset.It is calculated using the following formula:

RSI = 100 - 100/(1 + RS*)

RS is equal to the Average of x days' up closes, divided by the Average of x days' down closes. Guide To Excel For Finance: Valuation Methods

  1. Guide To Excel For Finance: Introduction
  2. Guide To Excel For Finance: Goal Seek
  3. Guide To Excel For Finance: PV And FV Functions
  4. Guide To Excel For Finance: HLookup And VLookup
  5. Guide To Excel For Finance: Linking Yahoo! Finance and Other Outside Financial Data To Excel
  6. Guide To Excel For Finance: Ratios
  7. Guide To Excel For Finance: Technical Indicators
  8. Guide To Excel For Finance: Valuation Methods
  9. Guide To Excel For Finance: Advanced Calculations
  10. Guide To Excel For Finance: Conclusion
RELATED TERMS
  1. Pivot Point

    A technical analysis indicator used to determine the overall ...
  2. Forex Pivot Points

    A set of indicators developed by floor traders in the commodities ...
  3. Bollinger Band®

    A band plotted two standard deviations away from a simple moving ...
  4. Structural Pivot

    A price-bar formation that gives real-time price signals of support ...
  5. Relative Strength Index - RSI

    Relative Strength Indicator (RSI) is a technical momentum indicator ...
  6. Broadening Formation

    A pattern that occurs during high volatility, when a security ...
RELATED FAQS
  1. What are the differences between a Pivot and a Pivot Point?

    Understand the basics of pivot trading and the key difference between the calculation of the pivot and the pivot points derived ... Read Answer >>
  2. How do I perform a financial analysis using Excel?

    Find out how to perform financial analysis through Microsoft Excel, which is probably the most widely used software among ... Read Answer >>
  3. How do I calculate forex pivot points?

    Pivot points were originally developed by floor traders in the equity and commodity exchanges. They are calculated based ... Read Answer >>
  4. What are the best technical indicators to complement a forex Pivot Point strategy?

    Learn the best technical indicators used by traders and analysts to complement a forex trading strategy based on daily pivot ... Read Answer >>
  5. Why are forex Pivot Points important for traders and analysts?

    See why pivot point analysis is particularly applicable to the forex market and what traders consider when they use pivot ... Read Answer >>
  6. How are Pivots interpreted by analysts and traders?

    Find out why traders and analysts use pivots in their analysis of price movements and why pivots can be used to create trading ... Read Answer >>

You May Also Like

Hot Definitions
  1. Return On Invested Capital - ROIC

    A calculation used to assess a company's efficiency at allocating the capital under its control to profitable investments. ...
  2. Law Of Demand

    A microeconomic law that states that, all other factors being equal, as the price of a good or service increases, consumer ...
  3. Cost Of Debt

    The effective rate that a company pays on its current debt. This can be measured in either before- or after-tax returns; ...
  4. Yield Curve

    A line that plots the interest rates, at a set point in time, of bonds having equal credit quality, but differing maturity ...
  5. Stop-Limit Order

    An order placed with a broker that combines the features of stop order with those of a limit order. A stop-limit order will ...
  6. Keynesian Economics

    An economic theory of total spending in the economy and its effects on output and inflation. Keynesian economics was developed ...
Trading Center