Stock Beta Computation.

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 :).

  1. Create a low expense-ratio ETF that tracks a passive covered-call index like BXM.  (No, and not an ETN… I want collateral!)
  2. 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?
  3. Glue together this format with cool graphing software like Open Flash Charts and/or something HTML5 based.
  4. 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.

Leave a Reply

Your email address will not be published. Required fields are marked *