https://docs.google.com/spreadsheets/d/1bJ888cQ7-i4XuHkjKIs2W52pO30eh5R_5v5LxIOznN4/edit?usp=sharing
{EDIT: I have locked most of the formulas, so all you can do is enter stocks in Column A and sort as needed}

I’m new to the Stock Market. So new in fact, that I haven’t even made my first deposit into my newly opened brokerage account yet. I am taking a slow approach; getting my research in first, and then I’ll likely be ready to start investing in the coming weeks/months. I’ve built a tracking spreadsheet on Google Sheets to keep an eye on some stocks (keeping my selections under and around the $150/share range for now), but I’d really like some help in teasing out some metrics that will help me develop a ranking system.

Allow me a minute to explain what I’ve got here, and then I’d love to hear some ideas from the community to help improve this tool…

* When you enter a ticker symbol in Column A, the spreadsheet will populate the rest of the data automatically using Google Finance. Most of the formulas are pretty straight forward; no crazy math going on. I can’t figure out how to get some stats to pull from the web (Sector, ROI, ROE, etc) even after extensive research into importing from various sites. If anyone can help with this, I’d be grateful!
* Columns D-H are pretty self explanatory, although I’d like to know how to utilize Beta score and P/E in my ranking metric. This will also be paired with data that I will ultimately pull from the company’s financial data in their formal documentation.
* Speaking of Ranking Metric (columns K&L), mine is so simple, it’s just downright stupid. My boss/mentor has been showing me a system he uses where he tracks a stock for no less than 3 years and watches to see what is steadily growing with low risk. His portfolio is growing 20% year over year, so there’s gotta be something to it; but at the same time, I think he’s leaving some money on the table. For right now, I’m using his metric, which is basically an aggregation of growth rates over the spans of 1,3,5 years. I think I’d like to massage the metric a bit to show recent momentum (higher being preferred), volatility (lower being preferred), and value (stocks on sale, if you will).
* The Market Multiplier field is a ratio of how well the stock is performing compared to the Dow & Nasdaq. The second tab in the sheet calculates how well the Dow & Nasdaq are performing, and provides an aggregate rate of growth. That number is then compared to each individual stock. If the stock is right in line with the market, then the number would be 1. If it’s performing 2x the market, the number is 2, 10x the market, it’s a 10; and so on… This is then just marking the adjacent cell “Better” or “Worse” with some conditional formatting.
* To the right of Column M is historical data, including a sparkline that shows the momentum of growth (these lines are NOT representative of the historical stock price).

I understand that there isn’t one golden metric, and I affirm that my current formula is extremely simple, but that’s hopefully where YOU come in. I’m genuinely asking for a review and some advice. I probably won’t be able to take EVERYONE’s advice, but I will cobble together what I can to hopefully improve this resource. I also recognize that Google’s stock API is just ok. There are a lot of issues with how the information loads and sometimes the spreadsheet takes a bit to fully load the data, but overall, I think I’m headed in an ok direction.

View Reddit by shamgar_bnView Source