Thursday, December 25, 2014

A Small Christmas Present

A Small Christmas Present

Merry Christmas everyone.  I did get up early today. I have placed some presents under the tree. I have had some coffee and raisin bran.  I am listening to probably my favorite Christmas album of all time.

I decided to create a small Excel Spreadsheet to help me with my new MFI Formula routine.  As my readers will recall, I have committed to once a quarter randomly pick five stocks from the MFI official screen.  The pick is done as follows:


  1. Take the to 50 stocks over $100 million.
  2. Take a subset of that list: stocks with market cap > $600 million and dividend yield > 2.5%.
  3. Use Randomizer.org to randomly pick 5 stocks from that list.
It is almost time for my 2nd tranche - next Wednesday.  So I created a small worksheet to do the work.  It is actually pretty cool.

Step 1 - download data from official website (Magic formula investing - Official Site) into cell a1.

The spreadsheet will then create the following table automatically:

Ticker Market Cap Price Dividend Yield
ADMS                      249         16.10                    -   0.0%
AFOP                      268         14.55                0.15 1.0%
ANIK                      555         38.26                    -   0.0%
APOL                  3,677         34.07                    -   0.0%
AGX                      507         34.80                0.70 2.0%
AVID                      553         14.36                    -   0.0%
AWRE                      101           4.45                1.75 39.3%
BCOR                      572         13.90                    -   0.0%
BKE                  2,509         52.18                2.08 4.0%
CA                13,796         31.31                1.00 3.2%
CSCO              143,115         28.30                0.74 2.6%
COH                10,252         36.97                1.35 3.7%
DLX                  3,115         62.88                1.15 1.8%
DEPO                      890         15.54                    -   0.0%
EBIX                      632         16.93                0.30 1.8%
FLR                  9,479         60.30                0.84 1.4%
GME                  3,785         33.39                1.32 4.0%
GILD              135,391         91.29                    -   0.0%
GORO                      158           3.06                0.12 3.9%
ONE                      202           4.22                    -   0.0%
IQNT                      608         18.30                0.45 2.5%
IDCC                  2,085         54.33                0.60 1.1%
IILG                  1,184         20.89                0.44 2.1%
ITRN                      475         22.10                0.98 4.4%
KING                  5,201         16.27                0.46 2.8%
LCI                  1,444         42.29                    -   0.0%
LBMH                      148           2.82                0.13 4.5%
LFVN                      135           1.34                    -   0.0%
LQDT                      249           8.19                    -   0.0%
MSB                      227         17.59                1.77 10.1%
NHTC                      145         11.30                0.03 0.3%
NSR                  1,775         27.59                    -   0.0%
NUS                  2,638         45.27                1.38 3.0%
PDLI                  1,270           7.61                0.60 7.9%
PFMT                      335           6.85                    -   0.0%
PETS                      287         14.23                0.68 4.8%
PBI                  5,055         25.22                0.75 3.0%
RPXC                      780         13.64                    -   0.0%
SPOK                      379         17.40                0.50 2.9%
STRA                      800         73.86                    -   0.0%
RGR                      691         35.48                1.62 4.6%
TTWO                  3,265         28.60                    -   0.0%
TDC                  6,869         45.22                    -   0.0%
TZOO                      190         12.97                    -   0.0%
UIS                  1,508         29.77                    -   0.0%
USNA                  1,269      103.51                    -   0.0%
VEC                      311         29.96                    -   0.0%
VIAB                32,005         76.89                1.29 1.7%
WTW                  1,537         27.19                    -   0.0%
WILN                      355           2.96                0.16 5.5%

This is done with standard excel formulas along with Randy Harmelink's terrific stock market functions (EXCEL Stock Market Functions Add-in).  I use the RCHGetYahooPrices with "l1" as the parameter for price and "d" as the parameter for dividend.

Step 2

I wrote a small macro which is an Advanced Filter of this table.  Advanced Filters are a neat tool that allow you to take a subset of a database using certain criteria and then copies that subset into another spot in worksheet.

Here is my criteria:

Market Cap Yield
>600 >2.4%
(Pretty simple - just in a couple cells).

I set up the quick key f to run the macro:

Sub Macro1()
'
' Macro1 Macro
'
' Keyboard Shortcut: Ctrl+f
'
    Application.Goto Reference:="Ticker"
    Range("G4:K54").AdvancedFilter Action:=xlFilterCopy, CriteriaRange:=Range( _
        "H1:K2"), CopyToRange:=Range("O2:S2"), Unique:=False
End Sub

This is not rocket science. Ticker is the name of the top left-hand cell of my database. G4:K54 is the range with the data.  My criteria are in cells h1:k2. Then the final result is copied to cells starting O2.

Here is what the output looks like (after I hit f):

Number Ticker Market Cap Price Dividend Yield
1 BKE                  2,509         52.18                2.08 4.0%
2 CA                13,796         31.31                1.00 3.2%
3 CSCO              143,115         28.30                0.74 2.6%
4 COH                10,252         36.97                1.35 3.7%
5 GME                  3,785         33.39                1.32 4.0%
6 IQNT                      608         18.30                0.45 2.5%
7 KING                  5,201         16.27                0.46 2.8%
8 NUS                  2,638         45.27                1.38 3.0%
9 PDLI                  1,270           7.61                0.60 7.9%
10 PBI                  5,055         25.22                0.75 3.0%
11 RGR                      691         35.48                1.62 4.6%

So if I wanted to lower the allowable dividend yield to 1%, I just change it in the criteria and hit f again.

Number Ticker Market Cap Price Dividend Yield
1 BKE                  2,509         52.18                2.08 4.0%
2 CA                13,796         31.31                1.00 3.2%
3 CSCO              143,115         28.30                0.74 2.6%
4 COH                10,252         36.97                1.35 3.7%
5 DLX                  3,115         62.88                1.15 1.8%
6 EBIX                      632         16.93                0.30 1.8%
7 FLR                  9,479         60.30                0.84 1.4%
8 GME                  3,785         33.39                1.32 4.0%
9 IQNT                      608         18.30                0.45 2.5%
10 IDCC                  2,085         54.33                0.60 1.1%
11 IILG                  1,184         20.89                0.44 2.1%
12 KING                  5,201         16.27                0.46 2.8%
13 NUS                  2,638         45.27                1.38 3.0%
14 PDLI                  1,270           7.61                0.60 7.9%
15 PBI                  5,055         25.22                0.75 3.0%
16 RGR                      691         35.48                1.62 4.6%
17 VIAB                32,005         76.89                1.29 1.7%
Pretty snappy.  Back to the original table, which had 11 choices.  I now go to Research Randomizer to randomly pick 5 distinct integers between 1 and 11.



To generate random numbers, enter your choices below (using integer values only):
How many sets of numbers do you want to generate?
Help

How many numbers per set?
Help

Number range (e.g., 1-50):From: 
To: 
Help

Do you wish each number in a set to remain unique?
Help

Do you wish to sort the numbers that are generated?
Help

How do you wish to view your random numbers?
Help
Site Overview
Generate Random Numbers
Use the Randomizer form to instantly generate random numbers.
Tutorial on Random Sampling and Random Assignment
See some examples of how Research Randomizer can be used for random sampling and random assignment.
Related Links
Visit links on random sampling, random assignment, and research methods.
About Research Randomizer
Learn more about Research Randomizer and read our User Policy.
Add a Research 
Randomizer Box to Your SiteAdd a Research 
Randomizer Box to Your Site
Add this tool to your website and generate your own number sets.

Social Psychology Network


Cool - I just saw where I can add a Randomizer Box.  I may try that.

Randomizer output:

Research Randomizer Results
1 Set of 5 Unique Numbers Per Set
Range: From 1 to 11 -- Unsorted
Job Status: 
     
Set #1:
10, 5, 2, 1, 11

I then will buy stocks 10, 5, 2, 1 and 11.  Here is the table (abbreviated) again:

Number Ticker
1 BKE
2 CA
3 CSCO
4 COH
5 GME
6 IQNT
7 KING
8 NUS
9 PDLI
10 PBI
11 RGR
So BKE, GME, RGR,CA and PBI.  Of course this is an example. I will run the official list next week.  
Merry Christmas everyone.





2 comments:

Karl Nelson said...

Very good info. I am going to try to recreate some of your spreadsheet work. Particularly the data retrieval .
Thanks, Karl

Paul T said...

I'm curious why you:
"Take the to 50 stocks over $100 million" then proceed to throw out ever stock under $600M

Why not just take the top 50 over $600M, then you would likely have even more stocks to randomize