How do you tell if a stock is overbought or underbought? Apart from using the RSI indicator which we are all familiar with. There are two methods I can think of to experiment this. First is to calculate how far in % terms the price deviates away from the 50 moving average. Second is to use the Empirical Rule or the 68–95–99.7 rule in statistics.
Since I just passed my Power BI exam, I thought it would be interesting to incorporate python scripts into Power BI and do some cool stuff there. So this tutorial would be a mix of python codes, Power BI and statistics to give you that edge in the market.
1. Getting Stock Prices from Yahoo Finance
If you have been following my blog posts in the Python for Finance series, this would have been a fairly routine task for you. If not here is the python code below. I am using MSFT for this example.
The difference this time around is we are not doing in it Jupyter notebook. We are going to use the script below as a method to import data into Power BI.
To calculate the 50 moving average, we need to take the adjusted close column which is found on the 4th index column. Hence (iloc [:, 4]).
To calculate the % Change deviation from the 50 MA, we calculate the price gap between the closing price and the 50 MA.
And lastly, we have to do a reset index as the original data frame has the date as an index, not a column. Power BI only recognises columns in a data table, hence we got to reset the index and make the date index a column on its own.
Copying the above python script would import in a data table into Power BI.
I have tried using the “Get Web” option in my previous tutorial on stock fundamentals analysis. But that only works well for static tables, not historical price data where you have to keep scrolling down. Hence that method won’t work for importing in stock prices data.
2. Cleaning up Data
The second step is to clean up the data and keep only what we need for speed optimization.
After you select df, click on “Transform Data”. That would bring you into the Power Query factory where you do all your data cleaning.
First step: Select all columns and click “Detect Data Type” under (Transform). This would convert all your columns to the right data type.
Second step: Select either the”50-SMA” or “% Change” column and filter out null values. It should be the first 50 rows since we are doing a 50 moving average.
Third step: Add an index column under (Add Column). This is to help prevent grouping as that is the default for python visuals in Power BI. We want to treat each row as unique.
Finally, choose only the columns we need. Go to Home > Choose Columns > tick only index, date, adj close, 50-SMA and % change. The smaller the dataset, the faster Power BI runs.
This should be the final output if the above steps are followed correctly.
3. How to Tell If a Stock is Overbought?
Now that we have all the data, it is time to visualise all these data and tell if a stock is overbought.
Here are the charts that I have plotted out. Both the line charts are plotted using Power BI default visuals. But the histogram is plotted out using python as plotting vertical lines in the histogram are not possible in Power BI.
The first line chart is just a simple closing price vs the 50 moving average.
When the price deviates too far away from the 50 moving average. It tends to snap back down as it needs to breathe out. Then begs the question, how far is far?
3.1 Overbought Stock from % Change from 50 MA
The way we measure that is on the bottom left chart. It shows the % change of price in comparison to the 50 MA. For example, based on the last closing price, MSFT is only 2.2% away from the 50 MA. Definitely not overbought nor oversold.
When it is in overbought conditions, prices are 15-16% higher than the 50 MA. When it is oversold, prices can go as low as -19% during the march lows.
The red lines represent the limits which I set on my own. It is +/- 10% for the thicker red line and +/- 20% for the lighter red line. You can set whatever limits you feel comfortable with.
Generally speaking, those limits can be used as trigger points or warning signals. Example, when prices are 10% higher than the 50 MA, it is at overbought regions and correction is coming soon.
3.2 Overbought Stock from Histogram of % Change from 50 MA
The bottom right chart shows the histogram of the % change from 50 MA. The centre black line represents the average while the 4 dotted lines are the mean +/- 2 and 3 standard deviations away.
The histogram shows the frequency counts of the % change. It tells us how often prices are at x % above/below the 50 MA. You can see that for MSFT, prices often hover near 0% as expected with a normal distribution curve.
Using the 68–95–99.7 rule in statistics, we would assume that 95% of the data would fall between +/- 2 stdev from the mean. And 99.7% of the data would fall between +/- 3 stdev.
The red line represents where we are right now.
Hence if you find the red line crossing above or below the 3rd standard deviation line. It is an immediate buy or sell. Reason is because the probability of this happening is 0.3% statistically speaking. Similarly, when the red line is above or below the 2nd standard deviation line, it is also a good entry or exit point. Since the event of this happening is only 5%.
So what can we tell from Microsoft? It is boring, prices are flat. Last closing price is only 2.2% above 50 MA. The histogram red line is right beside the avearage mean. Hence, neither overbought nor oversold.
3.3 Plotting Histogram using Python Script in Power BI
For those who are keen, here is how you plot the histogram using python in Power BI.
Use the PY script symbol in Power BI, then drag the fields you want to plot. In this case, we are interested in the % change from 50 MA. But we also need the index else the data frame would only return a single column of % change values with no date context.
You need the date context because we need to grab the last value of % change from 50 MA on the LAST date.
Once you dragged in the fields, a python script editor prompt would appear at the bottom asking you to write your python codes.
Just follow the codes along and that histogram should appear.
4. Changing Stock Symbol
What if we want to look at other stocks instead of MSFT? The best practice in Power BI for dealing with such dynamic variables is to use parameters.
Here is how we can create a parameter.
Go into the Power Query factory again by clicking (Transform Data). Then create a new parameter. Name the parameter Stock Symbol and set the data type to text. I will put TSLA for now but this parameter can be changed anytime you want.
The idea here is we want to change our python source code depending on whatever stock symbols we typed in. This needs to be dynamic hence a parameter is being used.
To do that, go to advance editor and locate the area where the stock symbol is being input in.
Remember the original code we used to import data into Power BI? This is how it looks like inside the advance editor. To make a dynamic query, we have to REPLACE that MSFT text with the parameter that we just created.
Now the python source code would import in all the stock prices of the symbol that we select in our parameter.
Then click done, close and apply to return back to your visuals.
5. Is TSLA, SE, BTC, ETH, SQ, NIO, ARKG, DBS, S&P500 Overbought?
Let’s see if TSLA and the other hotties of 2020 are overbought.
TSLA’s closing price is 43.3% above the 50 MA, but you realise this is not new to TSLA. It has even been 100% up before and the recent highs have been 68.2%.
If you look at the histogram, it is skewed towards the right implying that most of the time, prices are above the 50 MA. This is also how you can tell whether a stock is a strong bull.
But at the moment, the red line is at the 2nd standard deviation mark. Though we might still have some room between the 2nd and 3rd stdev, I would say the peak is near.
Let’s try other hot stocks in 2020. To change the stock symbol, simply go to Transform Data > Edit Parameters. Then type in whatever stock symbol you want to check.
I have changed the limits based on dynamic percentiles rather than a static value of 10 and 20%. This is because each stock has different characteristics, as seen with TSLA and MSFT.
The limits would now be 10,20,80,90 percentiles.
Here are the charts for Sea Limited, BTC, ETH, SQ, NIO, ARKG, DBS, S&P500.
There is one other way to tell how much room there is left on the upside. Simply look towards the bar charts on the right after the red line. This will show you historically how many times the stock has been in that region.
You can change the parameters to test different scenarios. Note that I am justing using data from 2017-1-1 and the 50 MA. Everything you see above is based on the above 2 parameters. For more accurate results, it is better to include more data points say from 2010. And for those with a medium to longer term outlook, you can change it to be 150 rather than 50 MA.
Hopefully, this gives you some insights on how to tell if a stock is overbought? You can adjust and play around with different parameters to create your own investment decisions.
Finding the right quality companies to invest is the first step and I think that is not difficult. The difficult part is getting in at the right prices and sizing your positioning tactically.
Buying good quality companies at the wrong price is as bad as buying bad quality companies at a good price. You will get trapped and your profits will be red or low if lucky and it takes weeks or months to break-even.
I have made the impulsive mistake of getting in at MSFT at $226 due to the TikTok FOMO, or BABA due to Grab acquisition FOMO. It is that itchy finger syndrome again. Both have yet to break-even now as Ant listing got cancelled. So don’t be dumb and chase pumps like me.
I did not know of any tools back then, but with these simple codes, you can use this as an objective way, among others, to tell if a stock is overbought or oversold.