The image above shows an Excel chart of the S&P 500 with buy and sell signals based on a 50 day average.
It is easy to create a stock chart in Excel. In this article, I am going to describe how to insert buy and sell points to an Excel chart.
To simplify things I will use a 50 day moving average as an indicator. If the 50day average changes from negative to positive a buy signal is generated and a sell signal when the average moves from positive to a negative trend.
I have copied stock data from the Yahoo Finance website, you can easily pick a stock or index and get historical data.
Calculating moving average
The AVERAGE function calculates the moving average, remember to use the last 50 days as an argument. I am using a relative cell reference that changes accordingly when the cell is copied to cells below.
In cell K2:
Copy cell K2 and paste down as far as needed.
Find buy and sell dates
The following formulas check if the 50-day average is higher or lower than the previous day and the day before that, this will return the close value only if there is a change in trend.
In other words multiple buy or sell signals won't be displayed, only the first one. The first IF function has two logical expressions (K2-K3>0) and (K3-K4<0), both these conditions must be true to return the value in K2.
In cell L2:
=IF((K2-K3>0)*(K3-K4<0), K2, "")
In cell M2:
=IF((K2-K3<0)*(K3-K4>0), K2*1, "")
Copy cell L2 and M2 and paste down as far as needed.
Extract year and month from date
Let's extract year and month from dates to avoid a mess on the x axis in our stock chart. The YEAR function returns the year based on an Excel date.