Adjusting Yahoo Data

In my previous post, I had explained that Yahoo and Google EOD (End of Day) historical data does not match.

Let us take a look at XOM. On 12/30/1994, Google reports the close price as $43.85. Yahoo reports the close price of $87.35 and also reports an adjusted close of $35.53. Y! adjusted close prices are adjusted for any splits and dividends. On the other hand, Google only adjusts for splits.



From a technical analysis point of view, I am usually not interested in the effects of dividends and am interested only in the split adjustment factor. Also, I prefer to use Y! EOD data as it is better in quality than Google data. It is more complete and is available for longer periods of time.To use Y! data, I would have to make some adjustments. The split-adjusted price can be calculated as follows:

split_adjusted_close (y') = split_div_adjusted_close(as reported by yahoo adjusted close)/div_adjustment_factor

To get the div_adjustment_factor, I need to look at  all the dividend payouts. For XOM, the dividend table looks like:


Aug 11, 2010
$ 0.44 Dividend
May 11, 2010
$ 0.44 Dividend
Feb 8, 2010
$ 0.42 Dividend
Nov 9, 2009
$ 0.42 Dividend
Aug 11, 2009
$ 0.42 Dividend
May 11, 2009
$ 0.42 Dividend
Feb 6, 2009
$ 0.40 Dividend
Nov 7, 2008
$ 0.40 Dividend
Aug 11, 2008
$ 0.40 Dividend
May 9, 2008
$ 0.40 Dividend
Feb 7, 2008
$ 0.35 Dividend
Nov 7, 2007
$ 0.35 Dividend
Aug 9, 2007
$ 0.35 Dividend
May 10, 2007
$ 0.35 Dividend
Feb 7, 2007
$ 0.32 Dividend
Nov 9, 2006
$ 0.32 Dividend
Aug 10, 2006
$ 0.32 Dividend
May 10, 2006
$ 0.32 Dividend
Feb 8, 2006
$ 0.32 Dividend
Nov 8, 2005
$ 0.29 Dividend
Aug 10, 2005
$ 0.29 Dividend
May 11, 2005
$ 0.29 Dividend
Feb 8, 2005
$ 0.27 Dividend
Nov 9, 2004
$ 0.27 Dividend
Aug 11, 2004
$ 0.27 Dividend
May 11, 2004
$ 0.27 Dividend
Feb 9, 2004
$ 0.25 Dividend
Nov 7, 2003
$ 0.25 Dividend
Aug 11, 2003
$ 0.25 Dividend
May 9, 2003
$ 0.25 Dividend
Feb 6, 2003
$ 0.23 Dividend
Nov 7, 2002
$ 0.23 Dividend
Aug 9, 2002
$ 0.23 Dividend
May 9, 2002
$ 0.23 Dividend
Feb 7, 2002
$ 0.23 Dividend
Nov 7, 2001
$ 0.23 Dividend
Aug 9, 2001
$ 0.23 Dividend



For every dividend payout, I need to calculate a div_adjustment_factor. This is calculated as follows:

div_adjustment_factor = div/previous_close * previous_div_adjustment

For the Aug11 payout, the div_adjustment is :
div = $0.44
adjusted close on Aug 10 = $61.93
previous_div_adjustment = 1
div_adjustment = 0.44/61.93 = 0.007

When I make these calculation to the complete data set, I am able to match Y!EOD and Google EOD. I can then use this for any technical analysis work that I need to do

I have written a python script that download Y!data, adjusts it and saves it in a sqlite3 database. I can share this script with you, if you are interested. Drop me an Email at guru[at]kilotrader.com


No comments:

Post a Comment