## Plot buy and sell points in an excel chart using two moving averages

Below is an animated gif demonstrating a stock chart 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.

**S&P500**

### 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:**

**Array formula in cell C25:**

**Array formula in cell D25:**

**How to create an array formula**

- Copy array formula
- Select cell B25
- Paste formula
- Press and hold Ctrl+ Shift
- Press Enter

**How to copy array formula**

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

### Calculating buy and sell points

Sheet: Calculation

**Array formula in cell K2:**

**Array formula in cell L2:**

**Formula in cell M2:**

**Formula in cell N2:**

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

### Further reading

If you want to know how to **plot buy and sell points in an excel stock chart**, read this post:

Add buy and sell points to a stock chart

**Download excel *.xlsm file**

Add buy and sell points to a stock chart

It is easy to create a stock chart in excel. In this post I am going to describe how to […]

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 […]

If you study a stock chart you will discover that sometimes significant trend reversals happen when a stock chart bar is […]

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 […]

### 3 Responses to “Plot buy and sell points in an excel chart using two moving averages”

### Leave a 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

Hi Oscar,

This post is awesome! But when I tired to play with the downloaded 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 downloaded following the above link is different all I must have missed something important. Keep wondering ...)

Thanks!

Smith

Thanks for letting me know. I have uploaded a new file:

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

See above.

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