I’ve now got a spreadsheet where I can enter 3-year daily stock price data and get a beta versus the S&P500 as modeled by the ETF SPY. There are still a few finer points I don’t like about the modeling. The biggest remaining gap in the computation is that the model doesn’t account for dividends on either the stock or the S&P. Ideally it would add the dividend into the price on the ex dividend date. If I could find a source where that data is built in or I took the time to merge the data it I would be set… but I haven’t bothered to do so. Another nice feature would be an R-squared computation.
So now what? Probably nothing for a while. I’m already thinking about different things. Most are things I wish other people would do :).
- Create a low expense-ratio ETF that tracks a passive covered-call index like BXM. (No, and not an ETN… I want collateral!)
- Create an open-source format for storing and sharing stock, index, portfolio, bond, ETF data. Perhaps XML-based. Nice features would be handling of splits, ticker symbol changes, dividend and ex-dividend dates, and market holidays. Support for different time periods would be a must. Support for earnings, book values, revenue and other supporting data would be nice. Perhaps such a format already exists?
- Glue together this format with cool graphing software like Open Flash Charts and/or something HTML5 based.
- Open source statistical tools to work with this format to compute volatility, beta, R-squared, P/E ratios, etc.
Until next time, happy financial modeling.
Balhiser.com 