Here we are again with an ‘awesome’ excel sheet, which, interestingly, is based on the Awesome Oscillator. You may be thinking that I am a little bit exaggerating by calling this excel sheet ‘awesome’ but soon you will find out the reason behind it. I am saying so because not only it automatically calculates the Awesome Oscillator values but also scans the potential trading setups in real-time. This all-new scanning feature makes this excel sheet unique among its kind and truly ‘awesome’. Continue reading to find more about this excel sheet.

Also Read: Fibonacci levels calculator excel sheet

What is Awesome Oscillator?

The Awesome Oscillator is an indicator which is used to measure the momentum of the market. It was first developed by Bill Williams. It is plotted in charts in the form of histogram displaying the market momentum of a recent number of periods compared to the momentum of a larger number of previous periods. Because it is an oscillator, its value fluctuates above and below zero-line.  Awesome Oscillator is calculated by subtracting 34 periods simple moving average of the mid-points of the bars ((High + Low)/2) from the 5 periods simple moving average of mid-points of bars. The generated values are plotted in the form of a red and green histogram. A green bar indicates that the bar is higher than the previous bar. A bar is red when its value is lower than the previous bar.

How to trade using Awesome Oscillator?

There are 3 most common ways in which traders use AO for trading. Let’s explore each of them.

ZERO- LINE CROSSOVER

• When AO crosses above the zero-line, it indicates an upward or bullish momentum and a potential buying opportunity.
• When AO crosses below the zero-line, it indicates a downward or bearish momentum and a potential selling opportunity.

SAUCER SETUP

A  Bullish Saucer Setup is formed when the AO histogram is above 0 and two consecutive red bars (with the second bar being lower than the first bar) are followed by a green Bar. Such a formation will represent a buying opportunity.

A Bearish Saucer Setup is formed when the AO histogram is below 0 and two consecutive green bars (with the second bar being higher than the first bar) are followed by a red bar. Such a formation shows a selling opportunity.

TWIN-PEAKS SETUP

A Bullish Twin-Peaks Setup occurs when the AO is below zero and two swing lows of the awesome oscillator are formed with the second low being higher than the first. The histogram bar after the second low must be green to validate the setup. This setup will present a good buying opportunity.

A Bearish Twin-Peaks Setup occurs when the AO is above zero line and two swing highs of the awesome oscillator are formed with the second high being lower than the first. The histogram bar after the second high should be red to validate the setup. This setup will present a good selling opportunity.

Also Read: How to Build an automated Trading robot in Excel

Awesome Oscillator Excel Sheet with Scanner Overview

This is an automated real-time excel sheet which calculates Awesome Oscillator values for the data entered, analyses them and automatically scans for the occurrence of trading setups mentioned above.

See the below screenshot:

How to use this Excel Sheet?

Follow the below steps to use this Excel sheet:

Step 1: Download the Excel sheet “AWESOME OSCILLATOR SCANNER.xlsm” from the end of this post.

Step 2: Open the Excel sheet, accept any warnings or ‘Enable content’ prompt.

Step 3: In the parameters worksheet, enter the symbol name and time interval for which you want to download data.

For US stocks, you can just enter the symbol name. While for other exchanges you would need to append the exchange code after the symbol. For example, to download data for stock “INFY” listed in NSE, you have to enter “INFY.NS”

Data is available in 1min, 5min, 15min, 30min, 60min, and daily timeframes. Use the time interval drop down to select your desired timeframe.

Step 4: Specify timezone offset from EST (eastern standard time). AlphaVantage natively provides data only in EST timezone, so this step is necessary to convert the timestamp in your local timezone. For example, use offset 9 hours 30 min to convert data from EST to IST timezone

Step 5: Click on the ‘Refresh Data’ button. Once you do that, the excel sheet would connect to AlphaVantage API and fetch data in real-time.

When you do this for the first time, you may receive few popups as below:

In the first popup, select https://www.alphavantage.co in the level drop down and click on connect.

In the second popup, select ‘public’ in both the drop downs and click on save.

Step 6: Once you have specified all the parameters correctly, the Excel will download the data in columns A to F of Sheet1. The Awesome Oscillator values would be automatically calculated and the Scanner will let you know whenever there is a formation of Zero-line cross, Saucer or Twin-Peaks Setup.

Step 7: Refresh the data regularly to sense the momentum of the market and perceive potential trading opportunities.

Note: You would need to download and install Microsoft Excel 2016 to use this utility. For 2010 or 2013 edition of Excel, you will require the latest PowerQuery Add-in.

Download Link

Use the below link to download this Excel sheet. Let us know in the comments section if you face any issues:

Awesome Oscillator Scanner Excel Sheet

---