Excel VBA Relative Strength Index (RSI)

Banking, Finance and Investment Industry

VBA / Software Developer: Francis Lim

Project Categories: Data-Mining,  Data Cleansing, Financial Modelling, Numerical Analysis

Freeware: RSI Historical Stock Data

System Requirements

Windows XP, Windows Vista, Windows 7

Microsoft Excel 2003 or above

This Excel Spreadsheet downloads historical stock prices provided by Yahoo Finance. A freeware Spreadsheet. It is written in Visual Basic Applications (VBA), a macro programming language for Microsoft Office - Access, Excel, Word, FrontPage, Outlook, PowerPoint, and Visio.

To know your stock ticker quotes, please refer to yahoo.finance.com. The downloaded stock data contains Date and Close in ascending date order.

How to Use MACD-RSI Excel Sheet

You will need to enable macros for this spreadsheet to work. To enable macros, go to Excel Menu - Tools / Options, click on the Security tab and go to Macro Security. Change the setting to Medium, close and reopen the workbook It will ask you if you want to enable Macros, click 'Yes' to enable it.

User can input the following cells in the MACD Excel Sheet:-

B1 Cell Row - Yahoo Ticker

Enter a valid stock ticker code. If you're not sure of the stock symbol, click on 'Yahoo Symbol Lookup' to Yahoo Finance website.

B2 Cell Row - EMA (Fast) for MACD

Input the Exponential Moving Average (Fast), default is value 12.

B3 Cell Row - EMA (Slow) for MACD

Input the Exponential Moving Average (Slow), default is value 29.

B4 Cell Row - Signal Line for MACD

Input the Signal line of MACD, default is value 9.

B5 Cell Row - SMA (RSI)

Input Simple Moving Average (Gain/Loss), default is value 14.

B6 Cell Row - Start Date

The start date for the historical data.

Just Press "Retrieve Data" Button, it will download Historical Data from Yahoo Finance website run the MACD calculation and chart.

What is RSI?

The Relative Strength Index (RSI) is a trading indicator in the technical analysis of financial markets. It is intended to indicate the current and historical strength or weakness of a market based on the closing prices of completed trading periods. It assumes that prices close higher in strong market periods, and lower in weaker periods and computes this as a ratio of the number of incrementally higher closes to the incrementally lower closes.

The Relative Strength Index was developed by J. Welles Wilder and published in a 1978 book, New Concepts in Technical Trading Systems.

The RSI method may be classified as a momentum oscillator measuring the velocity and magnitude of directional price movements. Momentum is the rate of the rise or fall in price.

RSI Calculation

For each trading period an upward change (U) or downward change (D) is calculated. Up periods are characterized by the close being higher than the previous close,

U = closenow − closeprevious

D = 0

Conversely, a down period is characterized by the close being lower than the previous period's (note that D is nonetheless a positive number),

U = 0

D = closeprevious − closenow

If the last close is the same as the previous, both U and D are zero. An average for U is calculated with an exponential moving average using a given N-period smoothing factor, and likewise for D. The ratio of those averages is the Relative Strength,

This is converted to a Relative Strength Index between 0 and 100,

This can be rewritten as follows to emphasize the way RSI expresses the up as a proportion of the total up and down (averages in each case),

The EMA, in theory, uses an infinite amount of past data. It is necessary either to go back far enough, or alternately at the start of data begin with a simple average of N periods instead,

and then continue from there with the usual EMA formula,

Interpretation

As you can see from the chart below, the RSI ranges from 0 to 100. An asset is deemed to be overbought once the RSI approaches the 70 level, meaning that it may be getting overvalued and is a good candidate for a pullback. Likewise, if the RSI approaches 30, it is an indication that the asset may be getting oversold and therefore likely to become undervalued. 

A trader using RSI should be aware that large surges and drops in the price of an asset will affect the RSI by creating false buy or sell signals. The RSI is best used as a valuable complement to other stock-picking tools.