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.
Updated value $21,967. The CIA is looking pretty boring with just a 100 shares of SPY and the rest in cash earning a very paltry 3 basis points of interest.
I’m getting anxious to make this fun money more interesting. The VIX is pretty low — around 20 — so I’m not inclined to sell calls. Buying a SPY put position is a possibility. As is good old-fashioned stock picking.
I’ve crunched my first set of numbers. Specifically I’ve computed the beta of XOM (Exxon Mobil) vs. SPY (SPDR S&P 500 ETF) for 365 days ending Feb 4, 2010. My computed beta is 0.125. This is based on daily sampling of closing prices for a 365-day period. Not content with non-uniform sampling (e.g. discarding holiday and weekend data when the markets are not open), I recomputed beta over the same period with interpolated weekend/holiday data and came up with a beta of 0.117. I have not yet bothered to compute R-squared.
These are surprisingly low betas. Also interesting is the difference data interpolation can make… a not insignificant difference of 8.6%
Next I checked out reported betas from other sources. Yahoo Finance reports a beta of 0.35 for XOM (without specifying a time period, sampling method/frequency, or even reference index). MSN Money reports a beta of 0.43. This is a difference of about 23%. This could probably be accounted for by different time periods, etc. But what is most annoying is that these betas are presented without any such context.
I’ve only just started to explore this topic, but I think I’ve started to show that there is significant room for improvement in computing beta. And because beta underlies CAPM and modern portfolio theory, I think this is a big deal.
I’ve already got some more ideas for part III of this series, I just have to crunch some more numbers.