Are you buying the most undervalued **Loyal3** stocks?

Also check out the **Dividend Aristocrats Best Value Rankings!**

I dollar cost average into a number of stocks on a monthly basis, and buy bigger lots when I see value in particular companies. Since Loyal3 charges no fees, every one of my dollars goes toward buying stocks, that’s why I love this brokerage.

Loyal3 offers a limited number of stocks to invest in. They tend to partner with companies that have a strong fan base. It gives loyal customers the chance to “own what they love”, making it very simple to buy stocks in an elegant web interface.

With a selection of just 66 Loyal3 stocks, I thought I’d compare them all to see if I’m putting my money into the best buys. I found this exercise helpful for my own investment strategy and figured out a way to share my ranking system for the best value Loyal3 stocks with my readers.

However, the table needs to be sorted manually, so the ranking order may not always be updated, but the score is (if anyone knows how to automate a sort on a table like this, please send me a note).

The dynamic ranking system below is designed to point out the Loyal3 stocks that may be undervalued at a given point in time and are worthy of additional research. I’m currently using a 30 point system to rank the stocks based on my own investment philosophies and objectives.

Please don’t consider this ranking system a recommendation to buy or sell individual stocks. But do use it as a starting point for further due diligence and research. I also make no guarantee of the accuracy of this data. Sometimes the data feeds are spotty, but for the most part, it works well.

# Methodology

Up to five points is awarded to each stock based on the following criteria:

- Current P/E Ratio
- Forward P/E Ratio
- Current Yield
- PEG Ratio (Price to earnings growth)
- Price to Book Ratio

The specific details on the scoring system can be found below the table.

These are six valuation measures I use in my own investing valuation. I’m using these six data fields to start and may adjust this ranking system as I watch how it performs over time, and as I figure out how to get more data. The ranker is skewed toward value and income rather than growth. I may add a growth stock ranking in the future if the feedback here is positive and the need is there.

I’m sourcing the data from Google Finance and Yahoo Finance and using Google Sheets to assemble the data and calculate the points. Below you can see the vital data, but I’m calculating the scoring behind the scenes because it doesn’t all fit.

If you have any suggestions for additional metrics, please write them in the comments section. Since this is new, I didn’t want to overdue it, and some data is difficult to source. Data that is N/A or unavailable is scored as zero.

With that, here is my Loyal3 Stocks Best Value Rankings. Please provide feedback so I can make this even better.

# Loyal3 Stocks Scoring Metrics

## Current P/E Ratio (Yahoo Finance)

- 0-10, 5 points
- 10.01-15, 4 points
- 15.01-20, 3 points
- 20.01-30, 2 points
- 30.01-40, 1 point
- >40, 0 points

## Forward P/E Ratio (Yahoo Finance)

- 0-10, 5 points
- 10.01-15, 4 points
- 15.01-20, 3 points
- 20.01-30, 2 points
- 30.01-40, 1 point
- >400, 0 points

## Dividend Yield (Yahoo Finance)

- >4%, 5 points
- 3.01%- 4.0%, 4 points
- 2.01%-3.0%, 3 points
- 1.01%-2.0%, 2 points
- 0.01%-1.0%, 1 point
- No dividend, 0 points

## PEG Ratio (Yahoo Finance)

- 0.0-1.0, 5 points
- 1.01-2, 4 points
- 2.01-3, 3 points
- 3.01-4, 2 points
- 4.01, 5, 1 point
- >5.0, 0 points

## Price to Book (Yahoo Finance)

- 0-2, 5 points
- 2.01-4, 4 points
- 4.01-6, 3 points
- 6.01-8, 2 points
- 8-10, 1 point
- >10, 0 points

# Specific Spreadsheet Formulas

**PLEASE NOTE: This section no longer describes how I’m getting the Loyal3 stocks data. You can still view the template to see the old formulas, however Google Sheets has been slow, unreliable, or not working at all for these functions. I’m now using Yahoo Finance data and manually bringing it into the ranker sheet. **

Due to popular demand, I’ve created a Stock Rankings template Google Sheet for readers to see the behind the scenes scoring system. I’ve laid that out above, but I’ve had a lot of requests to see the details. Please note, this version contains the Payout Ratio not included in the Loyal3 List, but is included in the Dividend Aristocrats rankings (a 30 point scale). Click the button below to check out the template page. If you use this format or one derived from this on your own website, please attribute and link.

Below is the information or formula in each cell of the first row with data (row 2). A manual sort is done by highlighting the table from Column B to Column L. The data is otherwise self-updating by using the GOOGLEFINANCE function and Yahoo Finance API calls.

You don’t need to be technical to do this on your own Google spreadsheet. Just copy these lines and add the attribute you need. I also add the “iferror” function so that temporary bad data doesn’t throw off the methodology. The spreadsheet is only as good as the data, and sometimes Yahoo Finance does not provide the correct information. You can look directly on the site to validate a problem.

Column A - Static Numbers (manual sort done without this column) Column B - Company Name (typed) Column C - Symbol (typed) Column D - =iferror(importData(concatenate("http://finance.yahoo.com/d/quotes.csv?s=",$C2,"&f=l1"))) Column E - =iferror(importData(concatenate("http://finance.yahoo.com/d/quotes.csv?s=",$C2,"&f=r"))) Column F - =iferror(importData(concatenate("http://finance.yahoo.com/d/quotes.csv?s=",$C2,"&f=r7"))) Column G - =iferror(importData(concatenate("http://finance.yahoo.com/d/quotes.csv?s=",$C2,"&f=d"))) Column H - =iferror(importData(concatenate("http://finance.yahoo.com/d/quotes.csv?s=",$C2,"&f=y"))) Column I - =iferror(importData(concatenate("http://finance.yahoo.com/d/quotes.csv?s=",$C2,"&f=r5")),0) Column J - =iferror(importData(concatenate("http://finance.yahoo.com/d/quotes.csv?s=",$C2,"&f=p6")),0) Column K - =iferror((index(ImportHTML("http://finance.yahoo.com/q/ks?s="&C2&"", "table", 22), 5,2))) Column L - Sum of unseen columns using multiple "if" statements to calculate the Scoring Metrics.

Payout Ratio - =iferror((index(ImportHTML("http://finance.yahoo.com/q/ks?s="&C2&"", "table", 32), 7,2)))

Note: Column K uses a the ImportHTML function looking in a certain table in the Key Statistics area. I couldn’t find a guide on those fields but was able to figure out the location of the data point through trial and error. Good the function and you should be able to find what you need.

# Yahoo Finance API Attributes

Simply replace copy a formula above and replace the Z’s in this part of the formula, “&f=ZZ”, with the desired attribute below.

a | Ask | a2 | Average Daily Volume | a5 | Ask Size | ||

b | Bid | b2 | Ask (Real-time) | b3 | Bid (Real-time) | ||

b4 | Book Value | b6 | Bid Size | c | Change & Percent Change | ||

c1 | Change | c3 | Commission | c6 | Change (Real-time) | ||

c8 | After Hours Change (Real-time) | d | Dividend/Share | d1 | Last Trade Date | ||

d2 | Trade Date | e | Earnings/Share | e1 | Error Indication (returned for symbol changed / invalid) | ||

e7 | EPS Estimate Current Year | e8 | EPS Estimate Next Year | e9 | EPS Estimate Next Quarter | ||

f6 | Float Shares | g | Day’s Low | h | Day’s High | ||

j | 52-week Low | k | 52-week High | g1 | Holdings Gain Percent | ||

g3 | Annualized Gain | g4 | Holdings Gain | g5 | Holdings Gain Percent (Real-time) | ||

g6 | Holdings Gain (Real-time) | i | More Info | i5 | Order Book (Real-time) | ||

j1 | Market Capitalization | j3 | Market Cap (Real-time) | j4 | EBITDA | ||

j5 | Change From 52-week Low | j6 | Percent Change From 52-week Low | k1 | Last Trade (Real-time) With Time | ||

k2 | Change Percent (Real-time) | k3 | Last Trade Size | k4 | Change From 52-week High | ||

k5 | Percebt Change From 52-week High | l | Last Trade (With Time) | l1 | Last Trade (Price Only) | ||

l2 | High Limit | l3 | Low Limit | m | Day’s Range | ||

m2 | Day’s Range (Real-time) | m3 | 50-day Moving Average | m4 | 200-day Moving Average | ||

m5 | Change From 200-day Moving Average | m6 | Percent Change From 200-day Moving Average | m7 | Change From 50-day Moving Average | ||

m8 | Percent Change From 50-day Moving Average | n | Name | n4 | Notes | ||

o | Open | p | Previous Close | p1 | Price Paid | ||

p2 | Change in Percent | p5 | Price/Sales | p6 | Price/Book | ||

q | Ex-Dividend Date | r | P/E Ratio | r1 | Dividend Pay Date | ||

r2 | P/E Ratio (Real-time) | r5 | PEG Ratio | r6 | Price/EPS Estimate Current Year | ||

r7 | Price/EPS Estimate Next Year | s | Symbol | s1 | Shares Owned | ||

s7 | Short Ratio | t1 | Last Trade Time | t6 | Trade Links | ||

t7 | Ticker Trend | t8 | 1 yr Target Price | v | Volume | ||

v1 | Holdings Value | v7 | Holdings Value (Real-time) | w | 52-week Range | ||

w1 | Day’s Value Change | w4 | Day’s Value Change (Real-time) | x | Stock Exchange | ||

y | Dividend Yield |

Again, please submit your suggestions and criticisms to this Loyal3 stocks ranking system in the comments section below or by contacting me via email. If you like what you read, please subscribe to my blog.

I read your blog almost daily…thank you for putting this together and maintaining it as it has helped me in my financial goals as well.

Never hurts to ask, :), but would you consider sharing the entire copy of your google sheets doc for the Loyal3 Rankings with me so that I may download a copy of it or the formulas you are using, for my personal use? I love Loyal3 and am trying to make sure that I stay on top of it as well and this is a great spreadsheet.

Thanks for considering and keep up the great work on this blog.

J

James,

Thanks for commenting. I mostly use the Yahoo Finance API to gather the data automatically. The score is then done with a bunch of “if” statements that are not displayed. I’ll add a list of the formulas to the end of the post for each column and the Yahoo Finance API attributes.

-RBD

Thanks for taking the time to do all of the work for me. I’m pretty good with Excel and have most of my spreadsheet set up to do this as well but didn’t know about the “ifError” statement so thank you very much for that. Do you prefer Yahoo to Google’s stock info?

The adding up all of the info to get the score is the one that I’m kinda stuck on so will keep digging around to get that tweaked. Sometimes I get too lazy and want someone else to do all the work for me. 🙂

Thanks again for helping us all out on this and pointing me in the right direction.

Don’t mention it. Figured it would help a lot of people. I built this entirely in Google Sheets, but use Excel for every else. I do prefer Yahoo Finance because that is what I most frequently use for stock research. The rest is just “if” statements. But it takes some time to get it all straight.

-RBD

Thanks for sharing your work on this. It’ll also be useful for analysis outside of Loyal offerings. Just curious why you’re reluctant to share a copy of the actual Google sheet. Especially since you offer formulas already.

It seems BRK-B does not work with =GOOGLEFINANCE(C2). Google uses BRK.B instead. Since most of the data comes from Yahoo I am eliminating all Google requests and pulling everything from Yahoo. Added formula to look up the company name based on (C2) instead of typing it.

I concluded the same thing. I figured it would be more consistent to get all the data from one source.

does this update automatically?

RAnn,

Yes it updates automatically. The only thing I do manually is sort by the Score field every few days. Aside from that, the formulas I use are listed at the end of the post. The Score is calculated in the background using basic “if” statements.

-RBD

Thanks so much for putting this table together. This is exactly what I was looking for! Very helpful!

No problem Chris. Updates are real-time so new data all the time.

-RBD

Would really like to see the scoring formula

Rick,

I’ve had a lot of requests for this. The formula is written out in the post. But next chance I get I’ll put together a shareable spreadsheet so that people can download it.

-RBD

I got it all taken care of, with some improvements and auto sorted. https://docs.google.com/spreadsheets/d/1SHgTXAjvnrWKpDcOTb63_i6m8AfcGHTaywniLsgEYN8/edit?usp=docslist_api

I think the only additional figure that would help me is to be able to see the percentage down from the company’s 52 week high. I.E. -5%, -15%, etc.. Depending on what else is going on with a company, that number really effects the amount that I’m looking to buy. I love a good 20% off sale, depending on the reason. 🙂

Thanks for putting this together. It’s a great bookmarkable resource!

Donna,

Thanks for that suggestion. I like a good bargain too. When I get a chance I’ll see if I can add that data point.

-RBD

Is there a formula for Google spreadsheet to come up with the score? I have everything else in place and working just can’t seem to get the score figured out.

Thanks

Dave,

Due to popular demand, I’ve put a template version of the spreadsheet linked toward the end of the page. I hope this helps you and everyone else that has requested the scoring formulas.

-RBD

Seems to be something wrong with the formulas on the Loyal3 list.. Lots of REF errors.

Yes, the feed from Yahoo has been spotty the last few days. Sorry. Nothing I can do at this point that I know of. Hopefully they will fix it soon.

-RBD

One correction, Viacom on Loyal3 is actually class B, VIAB

Thanks for the comment. I’ll look into it and make the change.

RBD, looks like the tables are bugged again, at least for me most columns show no data in them. Side note, I know you have been a big backer of LC, thoughts on the shake up and what it may mean for the industry?

IFF,

Yeah, sorry the feed is bad again. I’ve done some tests to see if it’s just me, but appears to be Google again. Hoping it comes back up soon. Need to check in with some others to make sure it’s widespread.

As for LC, hugely disappointing. Also confusing as not much detail is provided. We know the team there is top notch, so this could be a prime time to invest as the core business is growing steadily. Earnings looked quite good. Seems like an excellent time for a buyer to swoop in.

-RBD

This is a good tool. Really seeing Kohls and Macy’s Perform this month. Thanks for your work.

Thanks, glad it’s useful to you and so many others. Best Buy was another big one. Nice pop this week. I’ll be sure to update this weekend.

-RBD