How I’m Building a Stock Forecasting Model

Yeah I found this image online. Totally no idea what those squiggly shapes mean

Quick Intro

In my last article, I lightly touched on how I used a spreadsheet filled with historic fundamental data to forecast that FB and AGI would break out (as of Friday, both were still climbing, with AGI up 5.64% and FB up 1.20%).

This article is going to deep-dive into my spreadsheet’s inner-workings.

Over all, I’ll cover:

  1. My Fundamental Measures
  2. My forecasting model
  3. Weak points and areas of improvement

My Fundamental Measures

Currently, my spreadsheet measures:

  • Market capitalization (big cap, mid cap, or small cap). This helps me assess price volatility to determine my desired level of exposure. I lean towards more secure big cap companies.
  • 14 Day Relative Strength Index (RSI). This indicates whether a company is a market leader. I generally avoid companies with an RSI below 70. While these companies can do perfectly well, they will have smaller breakouts relative to their 70–99 RSI competitors.
  • P/E Ratio (trailing twelve months (TTM) and quarterly). I use this to determine high or low industry support and momentum.
  • Debt to equity ratio. Companies with too much debt can be overburdened, dragging down earnings and weakening momentum. I avoid these companies.
  • Average monthly volume. I combine this with daily volume to determine whether a selloff is drying up or a breakout is on the verge. During a selloff, low daily volume (significantly below average monthly volume) indicates the end of the selloff. Paired with other positive measurements, including tight daily/weekly closing price-points, this can indicate an oncoming breakout. With that said, other fundamental measurements should be used to determine if the breakout is worth investing in. A company with other weak fundamentals is higher risk and may have a disappointing or failed breakout, possibly followed by a steep break.
  • Close price for previous day. This is used to help calculate the quarterly P/E ratio and other measurements.
  • Earning Surprises for 2016 Q1. Positive earning surprises indicate better-than-expected performance. Never a bad thing.
  • EPS for the last two back-to-back quarters (currently Q1 2016 and Q4 2015). I used this to measure YoY growth, which should be at least 18% for a strong, safe stock.
  • YoY EPS changes for those same quarters. What I said above.
  • Revenue for Q1 2016, Q4, 2015, and Q3 2014. Used to calculate multiple other measurements, including how much revenue has changed YoY for each quarter and whether the growth rate has increased or decreased over the past two years for matching quarters.
  • YoY Revenue % change for Q1 2016/15/14 and Q4 2015/14. I want to see revenue increase YoY — or at very least, not retreat YoY.
  • YoY growth rate increase between Q1 2015 and Q1 2016 (this is different than actual growth increase). A positive trending growth rate can offset the negative impact of weak revenue. If I see a company with weak revenue that is both improving YoY and improving at a faster rate each year, I see a recovering company that could be getting ready to break out. Other indicators must be considered, but this is a good sign.

My Forecasting Model

In the excel spreadsheet, the formula looks something like this:

=if(if(E6 = true,E6>(P6*20),F6>(P6*20)),if(J6>=0,if(O6>0,if(or(O6>=0.18),if(Y6>0,if(X6>0,”BUY”,”PROB DON’T BUY — Growth Rate Shrinking”),”Don’t Buy — Revenue is Negative”),”Maybe Buy — EPS is positive but growth rate is below 18%”),”Maybe Buy — Negative EPS, check rate”),”Maybe Don’t Buy — Negative Earning Surprise”),”Maybe Don’t Buy — PE is low -> no momentum”)

Without having access to the actual spreadsheet, this probably doesn’t mean much to you. At very least, you should be able to tell that it’s made up of overly simplistic if/then/else statements that may or may not be flawed in and of themselves. It doesn’t even consider every measure that I’m tracking in my spreadsheet. This is just a symptom of a work in progress. Regardless, let’s run through each step in the formula so you can see what I’m trying to accomplish.

  1. First, I check whether I have data for the TTM P/E Ratio
  2. If I do, I check whether it’s 20 times greater than earnings.
  3. If I don’t, I instead check whether the quarterly P/E is 20 times greater than earnings.
  4. If either of these P/E ratios are less than 20 times greater than earnings, I recommend right off the bat, “”Maybe Don’t Buy — PE is low -> no momentum.” This is one of the flaws in my formula. Low PE is not alone enough reason to avoid a stock, but the formula isn’t currently robust enough to continue analyzing the remaining factors.
This is a good point to stop and explain why my formula usually recommends “probably” buy/don’t buy, or “maybe” buy/don’t buy. It does this because it’s unable to consistently review all criteria, meaning a positive or negative recommendation is never truly be definitive. A “probably” recommendation means that the formula found one or two issues, but it’s not sophisticated enough to tell me exactly what they are and it’s unable to continue its analysis from that point. A “maybe” recommendation means that it found more serious issues and stopped its analysis. The rare, unqualified “Buy” or “Don’t buy” recommendation means all measurements equaled true, which gives me the most confidence. But in any of those cases, no matter what the formula recommends, I need to cautiously review all of the data manually for each company. In other words, the formula’s recommendation just helps me determine where I should focus my efforts.
  1. If that’s true, I move on to check whether the company did not have a negative earnings surprise for the last quarter. If it’s false (i.e., the earning surprise was negative), I recommend “Maybe Don’t Buy — Negative Earning Surprise.”
  2. If the company did not have a negative earnings surprise (i.e., the earning surprise was 0 or positive), I check on whether the last quarter’s EPS increased YoY.
  3. If the YoY EPS did not increase, I recommend “Maybe Buy — Negative EPS, check rate”
  4. If the EPS did increase, I check whether the increase was at least 18% YoY.
  5. If it did, I check on whether the revenue for the last quarter was positive. If it wasn’t, the formula recommends “Don’t Buy — Revenue is Negative.”
  6. If revenue is positive, I check whether the revenue’s growth rate between 2015 and 2016 also increased. I recommend “Buy” if the growth rate increased or “Prob don’t buy — Growth Rate Not Increasing” if it didn’t.

So that’s it — that’s all the formula currently does. It’s nothing special and I have weeks or months worth of work to do on improving it, but it’s a solid start.

Weak Points and Areas of Improvement

First, it’s important to point out that the formula fails to consider all of my spreadsheet’s measurements, including the RS Index and debt-to-equity ratio. I plan to include these shortly, along with other measurements that I expect to learning in the coming weeks.

Second, my formula needs to become vastly more robust. I need to add in dozens of nested if/then/else statements that will allow the formula to continue calculating remaining measurements after another fails. I also have a feeling that if/then/else statements aren’t even the best tool for writing this formula, and I should start learning advanced excel calculations if I want to do this right.

Third, and probably most important, is that I don’t know what I don’t know. I’ve been reading as fast as I can, but I’m still only 1/4 of the way through my first book. Given the possibility of a recession beginning in the next 12 months, I need to get through a lot more learning if I plan to survive it. My current strategy entirely assumes a bull market and would utterly fail in a bear market. I have hundreds or thousands of pages left to read (but limited time) before I feel truly confident in my analyses.

In Closing

I have a long way to go before I can confidently forecast a breakout stock, but I’m making real progress. I hope to improve on a weekly basis. Nonetheless, the spreadsheet has already helped me find two breakout stocks. Hopefully, I can repeat that success this week. If not, it’s just another learning experience.

That’s all for today. Have a good Sunday :)