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

Popular categories

Dashboard reporting with excel This e-book teaches you how to create your own Excel dashboard reports, starting from scratch. Learn how to create mini-charts, how to use Excel's Camera tool, how to set up Excel databases, and a lot more.

Excel Worksheet-Formula Add-Ins allows worksheet formulas to change chart settings. For the first time ever, your formulas can create traffic-light charts, highlight chart elements, assign number formats, and much more.

Get Digital Help > Excel > Charts > Plot buy and sell points in an excel chart using two moving averages

← Previous post -

Filed in Charts, Excel, vba on Jun.29, 2012. Email This article to a Friend

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:

=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
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:

=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