How I Get My Data

How I Get My Data

I was asked how I get my data to run my blog.  Here are a few pointers:

• For getting data from official website, that can be a little sticky as they implemented some code that disables cut and paste tools.  Lucky for me, my 20 yr old son is a whiz at computer science (attending Carnegie Mellon) and he has written a couple python scripts to pull data from official site.
• For pulling data into excel directly from the web, I primarily rely on SMFs (Stock Market Functions) written by Randy Harmelink (Randy BTW is just a great person and will certainly answer questions).  These use Excel Add-Ins, so you need to be relatively Excel capable to use them.  There are two spots I follow to get these add-ins: (1) Yahoo Groups (EXCEL Stock Market Functions Add-in - Yahoo Groups) and Randy now has a blog (https://smf-add-in.blogspot.com/).

• For running my own MFI screen, which I do about once a month, I have a complex Excel spreadsheet that pulls data from a number of websites.  The main one it uses is Fidelity for income statements and balance sheets.  Randy has a addin formula called RCHGetTableCell that can be used to pull in data (example:  =RCHGetTableCell("http://research2.fidelity.com/fidelity/research/reports/printPage.asp?printPage=financials&toPrint=true&statement=balance&period=quarterly&viewType=detailed&symbols="&$M2,1,"Long-Term Debt") where the cell m2 is the ticker symbol, will bring in long term debt for most recent quarter.  Like I said, the workbook is complex.
• So for my day to day tracking I use smfgetyahooportfolioview, which is a super function that brings in an array of very useful data for a vector of stock tickers.  Things like dividends and transactions I handle manually.
• For my MFI tracking, I need to automate more stuff than my personal portfolio as there are so many stocks (600 at any given time).  A super powerful formula I use is called RCHGetYahooHistory that I use to bring in about 18 months of price history from Yahoo for a given ticker.  The nice thing about this approach is it has a closing price column but also an Unadjusted price column.  If Yahoo does it correctly (never a sure thing), if you go back in time the difference between the two columns should be dividends paid.  It should also adjust for splits.

Look at DLX a year ago:

DLX
Date	Open	High	Low	Close	Volume	Unadj
2016-12-08	69.62	71.36	69.40	71.25	349100	72.49

S0 the difference between unadjusted (72.49) and close (71.25) are dividends $1.24.  It is not perfect (should be $1.20), but I use it until some one (or me) gets a better way.  So then in my tracking, I give DLX credit for the dividends using this approach.

• Anyone who wants to start doing this should know it does take some time and does require some cross checking and reasonability checks.  I have certainly found times where Yahoo does not adjust for a stock split (USNA recently) and then all the USNA numbers look like stocks suddenly dropped by 50%.
• But for me, the time is worth it as I have certainly enhanced my MFI returns from all my analysis and tracking.  Of course, I still keep expecting wheels to fall off as I just can't seem to commit more than 1/3 of my total net worth to MFI.

One final person I need to give a nod to is a reader of my Blog - Shane Kelly.  I have shared some of my workbooks with him (he is very good at Excel) and he recommended a few enhancements.  So thanks Shane!