Plot buy and sell points in an Excel Chart based on two moving averages

Author: Oscar Cronquist Article last updated on September 06, 2019

This article demonstrates how to display buy and sell signals on an Excel chart based on two moving averages, the workbook lets you change how these moving averages are calculated. There is a get link below.

Above is an animated gif demonstrating a stock chart of S&P 500 with monthly prices. Two moving averages #1, #2, buy and sell points are plotted in this chart.

When the moving averages intersects a buy or sell point is created. If you use the value 7 in cell C1, moving average #1 uses the average of 7 months.

As you can see using a 15 month average (#1) returns better and fewer buy and sell points.

Extracting buy and sell points

Not only is the chart refreshed as you type new values in cell C1 and C2, the data below the chart is also instantly refreshed.

Array formula in cell B25:

=IFERROR(INDEX(Calculation!$D$2:$D$247, LARGE(IF((IFERROR(Calculation!$M$2:$M$247, "")<>"")+(IFERROR(Calculation!$N$2:$N$247, "")<>""), MATCH(ROW(Calculation!$M$2:$M$247), ROW(Calculation!$M$2:$M$247)), ""), ROW(A1))), "")

Array formula in cell C25:

=IFERROR(IF(INDEX(Calculation!$M$2:$M$300, MATCH(B25, Calculation!$D$2:$D$300, 0))="", "Sell", "Buy"), "")

Array formula in cell D25:

=IFERROR(INDEX(Calculation!$H$2:$H$300, MATCH(B25, Calculation!$D$2:$D$300, 0)), "")

How to create an array formula

Copy array formula above
Select cell B25
Paste formula to cell
Make sure you see the prompt in the cell, double press with left mouse button on the cell if you don't.
Press and hold Ctrl+ Shift simultaneously
Press Enter once
Release all keys

How to copy array formula

Select cell B25
Copy cell (not formula in formula bar)
Select cell range B26:B50
Paste

Explaining formula in cell B25

Step 1 - Remove errors

The IFERROR function removes errors and returns an array containing values or nothing, errors are filtered out.

IFERROR(Calculation!$M$2:$M$247, "")

Step 2 - Check if cell is empty

The less than and greater than sign combined means "not equal to", in this case the logical operators make sure that the value is not equal to nothing. It returns boolean values TRUE or FALSE.

IFERROR(Calculation!$M$2:$M$247, "")<>""

Step 3 - Check column N as well

Actions described in step 1 and 2 are also applied to column N.

(IFERROR(Calculation!$N$2:$N$247, "")<>"")

Step 4 - If cells are not empty return the corresponding relative row number

The + sign between the logical expressions performs an OR calculation meaning if the cell in column M is not equal to nothing OR if the cell in column N is not equal to nothing then return TRUE, both logical expressions must return FALSE in order to return a FALSE.

(IFERROR(Calculation!$M$2:$M$247, "")<>"")+(IFERROR(Calculation!$N$2:$N$247, "")<>"")

The IF function then returns the second argument if the logical expression returns TRUE.

IF((IFERROR(Calculation!$M$2:$M$247, "")<>"")+(IFERROR(Calculation!$N$2:$N$247, "")<>""), MATCH(ROW(Calculation!$M$2:$M$247), ROW(Calculation!$M$2:$M$247)), "")

The second argument is the corresponding row number and the third argument is nothing.

MATCH(ROW(Calculation!$M$2:$M$247), ROW(Calculation!$M$2:$M$247)

Step 5 - Extract the k-th largest row number

The LARGE function extracts the k-th largest value in the array. LARGE( array, k)

LARGE(IF((IFERROR(Calculation!$M$2:$M$247, "")<>"")+(IFERROR(Calculation!$N$2:$N$247, "")<>""), MATCH(ROW(Calculation!$M$2:$M$247), ROW(Calculation!$M$2:$M$247)), ""), ROW(A1))

Argument k is a relative cell reference that changes when the cell is copied, this makes it possible to extract different values.

Step 6 - Return value from column D based on row number

The INDEX function returns a value from a cell range based on a row and column number.

INDEX(Calculation!$D$2:$D$247, LARGE(IF((IFERROR(Calculation!$M$2:$M$247, "")<>"")+(IFERROR(Calculation!$N$2:$N$247, "")<>""), MATCH(ROW(Calculation!$M$2:$M$247), ROW(Calculation!$M$2:$M$247)), ""), ROW(A1)))

Calculating buy and sell points

Sheet: Calculation

Array formula in cell K2:

=AVERAGE(OFFSET(H2, 0, 0, Overview!$C$1))

Array formula in cell L2:

=AVERAGE(OFFSET(H2, 0, 0, Overview!$C$2))

Formula in cell M2:

=IF((L3<K3)*(L2>K2), K2, "")

Formula in cell N2:

=IF((L3>K3)*(L2<K2), K2, "")

Copy cells and paste down as far as needed.

The calculation sheet imports historical prices using this user defined function:
Excel udf: Import historical stock prices from yahoo – added features

Get the Excel file

excel-stock-chart-two-moving-averagesv2.xlsm

Stock market trend category

Add buy and sell points to a stock chart
The image above shows an Excel chart of the S&P 500 with buy and sell signals based on a 50 […]

Follow stock market trends – Moving Average
In my previous post, I described how to build a dynamic stock chart that lets you easily adjust the date […]

How to calculate and plot pivots on an Excel chart
If you study a stock chart you will discover that sometimes significant trend reversals happen when a stock chart […]

Follow stock market trends – trailing stop
This time I want to demonstrate an alternative way to identify a major trend in the stock market. The previous post […]

Functions in this article

IFERROR INDEX LARGE IF MATCH ROW AVERAGE OFFSET

More than 1300 Excel formulas

Excel formula categories

Average Choose Combine Merge Compare Concatenate Conditional Formatting Count Count values Dates Duplicates Extract Filter Filter records Filtered values Filterxml Hyperlink If cell Index Match Logic Lookups Match Max Min Numbers in sum OR logic Overlapping Partial match Records Sequence Small Sort by Sort values Sum Sumifs Sumproduct String manipulation Time Unique distinct records Unique distinct values Unique records Unique values Vlookup Vlookup return values

Excel categories

Home page

Latest updated articles.

Excel Functions

More than 300 Excel functions with detailed information including syntax, arguments, return values, and examples for most of the functions used in Excel formulas.

Excel Formulas

More than 1300 formulas organized in subcategories.

Excel Tables

Excel Tables simplifies your work with data, adding or removing data, filtering, totals, sorting, enhance readability using cell formatting, cell references, formulas, and more.

Advanced Filter

Allows you to filter data based on selected value , a given text, or other criteria. It also lets you filter existing data or move filtered values to a new location.

Data Validation

Lets you control what a user can type into a cell. It allows you to specifiy conditions and show a custom message if entered data is not valid.

Drop Down List

Lets the user work more efficiently by showing a list that the user can select a value from. This lets you control what is shown in the list and is faster than typing into a cell.

Named Ranges

Lets you name one or more cells, this makes it easier to find cells using the Name box, read and understand formulas containing names instead of cell references.

Excel Solver

The Excel Solver is a free add-in that uses objective cells, constraints based on formulas on a worksheet to perform what-if analysis and other decision problems like permutations and combinations.

Charts

An Excel feature that lets you visualize data in a graph.

Conditonal Formatting

Format cells or cell values based a condition or criteria, there a multiple built-in Conditional Formatting tools you can use or use a custom-made conditional formatting formula.

Pivot Tables

Lets you quickly summarize vast amounts of data in a very user-friendly way. This powerful Excel feature lets you then analyze, organize and categorize important data efficiently.

VBA

VBA stands for Visual Basic for Applications and is a computer programming language developed by Microsoft, it allows you to automate time-consuming tasks and create custom functions.

Macros

A program or subroutine built in VBA that anyone can create. Use the macro-recorder to quickly create your own VBA macros.

UDFs

UDF stands for User Defined Functions and is custom built functions anyone can create.

4 Responses to “Plot buy and sell points in an Excel Chart based on two moving averages”

Smith says:

December 8, 2013 at 9:17 am

Hi Oscar,

This post is awesome! But when I tired to play with the attached Excel sheet, I got a blank plot on "Overview tab" with #DIV/0! displayed under the columns of ave, buy and sells on "Calculation tab".
The Excel version I'm using is 2007 and I've tried quite long time to get it work in vain. Really appreciate if you can point out what could be the issues. (I thought it could be caused by the fact that the AVERAGE function is trying to work on the retrieved stock price in "text/date" format while "number" format is actually required. However, your post shows the screen capture of the Excel in action. Unless the version I got following the above link is different all I must have missed something important. Keep wondering ...)

Thanks!

Reply
- Oscar says:
  
  December 9, 2013 at 1:16 pm
  
  Smith
  
  Thanks for letting me know. I have uploaded a new file:
  excel-stock-chart-two-moving-averagesv2.xlsm
  See above.
Warren Goff says:

November 25, 2016 at 12:42 am

Your site is wonderful and educational. I wanted to thank you for your generosity. I'm new at Excel and Investing so it's going to take some time mastering VBA. All the programming I've done is VB6 and there is a lot of overlap.

Thanx

Warren

Reply
Owain says:

October 9, 2021 at 9:24 pm

Hi There, is there a chance you could update using STOCKHISTORY as Yahoo unavailble

Reply

How to comment

How to add a formula to your comment
<code>Insert your formula here.</code>

Convert less than and larger than signs
Use html character entities instead of less than and larger than signs.
< becomes < and > becomes >

How to add VBA code to your comment
[vb 1="vbnet" language=","]
Put your VBA code here.
[/vb]

How to add a picture to your comment:
Upload picture to postimage.org or imgur
Paste image link to your comment.

Contact Oscar

You can contact me through this contact form