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? YesNo Help Do you wish to sort the numbers that are generated? NoYes: Least to GreatestYes: Greatest to Least Help How do you wish to view your random numbers? Place Markers OffPlace Markers WithinPlace Markers Across Help

Use the Randomizer form to instantly generate random numbers. See some examples of how Research Randomizer can be used for random sampling and random assignment. Visit links on random sampling, random assignment, and research methods. Learn more about Research Randomizer and read our User Policy. Add this tool to your website and generate your own number sets.

Copyright ©1997-2008 by Geoffrey C. Urbaniak and Scott Plous | Site Statistics

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.