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.
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!