Building Buy/Sell indicator in Google Sheets. A great tool to help traders understand when to trade shares.
- Sanzhi Kobzhan
- Jun 17
- 6 min read
Updated: Jun 18
Table of contents:
Understanding when is the best time to buy shares is a great skill a trader can possess. Unfortunately, we cannot accurately forecast the share price, but we can forecast the strength of the trend and understand when it can be an optimal time to buy shares. In this article, I want to show how you can build your own buy/sell indicator that is based on trend strength forecast, so that you can trade more confidently. But still remember, since no one can forecast stock prices accurately, never hold a single share and learn portfolio management to better protect your investments and consider applying both technical and fundamental analysis before buying shares as you want to make sure you hold a strong stock that can rebound fast. In this article, I suppose that you already understand fundamental analysis and have a list of strong stocks to buy. And now let’s build our powerful yet simple indicator that can help you understand when exactly you can buy your chosen stocks and when you can sell it.
Initial preparations.
Let’s divide the process into a few simple steps. First, you would need to extract stock prices for the period with volumes and also build EMA 9 and EMA 21 indicators. Don't worry, you don't have to build them manually; just read my guide on How to Use EMA, Google Sheets, and FMP’s Technical Indicators to Forecast the Strength of the Trend, where I describe how to do that. Just read how to extract values without building graphs.
After extracting prices and building both EMA 9 and EMA 21 indicators, it’s time to proceed to the next step. Let’s make a few simple calculations that will serve as a basis for our indicator. At this point, you already should have two separate tables with EMA indicators, one for EMA 9 and the other one is for EMA 21. From 10th of March to the 10th of June. As shown in the picture below.
Now take the difference between EMA 9 and EMA 21 for all days in our observations. The difference is calculated as EMA9 minus EMA21. The example can be seen in the image below (highlighted column).
The next step is to calculate the EMA standard deviation but only for the positive values. For that, use the Google Sheets formula =STDEV.S(FILTER(Q7:Q70, Q7:Q70>0)). Do the same step for calculating standard deviation for negative values using the formula =STDEV.S(FILTER(Q7:Q70, Q7:Q70<0)). To make this formula work correctly, your data should be placed exactly how it is shown in the below picture, exactly in the same cells; otherwise, modify your formula a bit to reflect the correct cells. You can find my calculations for the standard deviation in the image below.
The next step is to take the average volume for 2 weeks (14 trading days) using the =AVEDEV(O7:O20) formula, where O7 and O20 are the range for volume.
Building a Buy/Sell indicator in Google Sheets.
Now let’s start with the most exciting part of the article. First, we need to create an indicator that would show if it’s a good time to buy your chosen share or maybe a hold would be better. A hold means to wait until a better buying opportunity will appear. I am using the following formula =IF(AND(Q7>0, Q7>(S7*Q7),O7>U7,B102>0.5), "BUY", "HOLD")
Let me explain step-by-step what this formula means. First, we have 4 different conditions. If they satisfy, then Google Sheets assigns a Buy. If not, then it assigns a Hold. First, we check that the EMA 9 exceeds EMA 21, heading upwards (Q7 >0 is how we check it). Then we multiply our standard deviation on the EMA difference to see the standard deviation's absolute value and check if it’s less than the EMA difference (Q7>(S7*Q7)). We do this to make sure that EMA 9 exceeds EMA 21 by more than its normal deviation, to indicate a stronger buying opportunity. We also check if volume is higher than the 2 weeks’ average volume to see if the demand for the stock is high. The last condition is to check if social sentiment is higher than 0.5, indicating that our chosen stock is mentioned in the positive key more than in the negative key (B102>0.5). Cell B102 is the result for social sentiment. To get social sentiment, you can use the FMP formula =fmpSocialSentiment("AAPL",1,"symbol,twitterSentiment"). Make sure to connect FMP with Google Sheets before applying this formula and get your custom API key. Then replace the stock ticker with your desired ticker. AAPL was taken as an example.
The formula with its application is shown in the image below
Do the same for the Sell indicator. But the formula would change a bit. This is my formula: =IF(AND(Q7<0, Q7<(T7*Q7),O7>U7,B102<0.5), "SELL", "HOLD"). We also have 4 conditions, but this time we check if EMA diff is lower than 0, to make sure that the downside trend has started. As EMA9 would cross EMA 21 downwards, the sentiment (B102 cell in my case) should also be lower than 0.5, to reflect lower traders’ confidence. The last day’s volume (last day in our observations) should be higher than the average volume, to reflect sellers’ high interest. We also take the standard deviation from negative EMA values, multiply it by EMA diff, and it should be higher than the EMA diff itself to make sure the EMA 9 exceeds EMA 21 by more than its average deviation. If all those conditions satisfy, then we get a SELL rating. If not, then a hold rating, meaning that it can be too early to sell the stock and it still has a good chance of bouncing back. The image with my calculations is shown below.
After completing all the steps, you can have a nice-looking indicator that can define a good time to buy shares based on trend analysis. The image is below.
From the How to Use EMA, Google Sheets, and FMP’s Technical Indicators to Forecast the Strength of the Trend article, you know that you should have a heading with all your necessary information to extract stock info. This is how it should look (highlighted cells on the below image).
Customizing your indicator.
Now you can customize your buy/sell indicator by changing the info in your heading. You can change the EMA periods, to make it more flexible. You can also change the indicator Type if you prefer SMA or WMA indicators, for example. Change the Time from day to 1 hour or 5 minutes if you are a quick trader (intraday trader), and of course, a period, to reflect the current date or the last trading date.
This is how you can build a simple, yet powerful indicator. This indicator is a good choice for shorter-term and intraday traders, but please don't forget to use other indicators, like VWAP, for example, and also read company news and undertake fundamental analysis to see the full picture and make sure you are buying a strong share. If your trading style is not only short-term but also a mid-term and a longer term, you can also try using the Stocks 2 Buy free iOS application that can help you analyze your chosen shares, see if they are strong fundamentally, and define the best time to buy and sell shares with the help of Japanese Candlestick analysis. You can read on how to do all of that by reading my article on Cheap Stocks To Buy Now.
If you want to see a video guide on how to build a simple stock buy-sell indicator in Google Sheets, watch the below video:
I hope my guide was useful, and now you know better what can be the best time to buy your chosen stocks. Please make sure you apply both technical and fundamental analysis and always keep this buy/sell indicator that we just created as your valuable helper that can support your decision or tell you that this is not the best time to buy. Thanks for reading the article.
Comments