Saturday, September 30, 2006

How Yahoo Calculates the Adjusted Closing Price

For calculating adjusted prices, there are commonly two kinds of adjustments to consider -- stock splits and dividends. Stocks splits are straightforward. Since a stock split doesn't change the intrinsic value of the company, you recalculate historical per-share data (like stock price) to reflect the latest number of outstanding shares. Everyone seems to agree on how to do that, but adjusting for dividends is a different story.

There are situations where you may or may not want dividend-adjusted prices. For example, using adjusted prices makes it much easier to calculate the rate of return for a long-term investment (a stock held for years) since dividend payouts are folded into the adjusted prices. However, percentage profit and loss calculations of a short-term trade could be skewed by adjusted prices. That most likely wouldn't matter much for a trade happening right now, but it could profoundly affect backtesting results, especially for time frames that were years ago. So depending on your objective, you will need to decide whether or not you want to use dividend-adjusted prices and what kind (more below).

Not all charting sites adjust their charts for dividends. For example, StockCharts.com adjusts for dividends, but BigCharts.com and Prophet.net don't. Interestingly, Yahoo charts don't adjust for dividends, but Yahoo historical prices do provide a single adjusted closing price that includes both splits and dividends. Yahoo's technique for calculating the adjusted closing price looked a bit strange to me when I first saw it. I was expecting the dollar difference between the actual close and the adjusted close to stay the same for stocks that never split. But that's not how they do it.

There are two general approaches on how dividend adjustments are calculated. The first approach, described in this Investopedia article, is simply to subtract the dividend out of the stock price going back to the IPO and adjusting the dividend adjustment for stock splits along the way. It's as if the dividend never existed in the company. This approach is simple and gives accurate absolute profit and loss numbers when calculating the return from holding a stock long-term, over several dividend distributions. But there is a problem -- due to the effect of inflation, it's possible that the dividend adjustment will cause some adjusted historical prices to go below zero. Once that happens, percentage rate of return numbers are trickier to calculate, and negative stock prices in general don't make sense intuitively.

The second approach involves calculating adjustments in percentage terms instead of absolute dollar value. Yahoo uses this type of approach for its adjusted closing price. It avoids the negative adjusted stock prices but as a result skews profit and loss calculations. There is probably more than one way to apply percentage adjustments, so depending on the exact technique, results would vary. I looked closely at Yahoo's adjusted prices and came up with what I believe is their formula. Here it goes..

Yahoo's adjusted close calculation goes like this:
  1. For the latest available trading day, the actual closing price and the adjusted closing price are the same. (Base case)
  2. For every other day ("today"), determine what percentage today's closing price is over yesterday's closing price, excluding the effect of a dividend and/or a stock split, if today is the ex-date.
  3. You calculate yesterday's adjusted close as being the same percentage down from today's adjusted close as the percentage calculated in step 2.
  4. Repeat steps 2 and 3 for all other historic days.
Expressed as a formula:

A-1 = A0 + A0( ((P-1/S) - P0 - D) / P0)

where
  • A0 is today's adjusted price. A-1 is yesterday's adjusted price.
  • P0 is today's actual price. P-1 is yesterday's actual price.
  • S is the split ratio, if today is a split ex-date. For example, a 3-to-2 split means S is 1.5. S is 1 if today is not a split ex-date.
  • D is the actual dividend, if today is a dividend ex-date. D is 0 when not a dividend ex-date.
In the case of Yahoo, the D term in the above equation is the tricky part. It turns out that Yahoo reports dividend amounts in split-adjusted terms. So you have to unadjust the dividend amount before using it in the above equation! You can either multiply the Yahoo-reported dividend amount by the cumulative split factor, or you can get the actual dividend amount from another web site like earnings.com.

I ran a couple spreadsheets using that formula and was able to calculate exactly the same adjusted closing prices that Yahoo has. Great! Now I can use the same formula to get adjusted prices for the open, high, and low prices. Then it's onward to building more advanced technical analysis screens!

8 Comments:

At 4:58 PM, March 23, 2011, Blogger Merv said...

Using the same Start Date and various End Dates should show the same data for Jan 3, 2011, but the data is Yahooed.
Date Open High Low Close Avg Vol Adj Close*
Start Date: day Year 4-Jan-88 24.05 25.78 23.26 25.78 69,600 2.44
End Date: day Year 3-Jan-11 35.67 36.59 32.93 33.3 575,400 33.03
End Date: day Year 3-Jan-11 35.67 36.59 34.66 34.98 437,100 34.7
End Date: day Year 3-Jan-11 35.67 36.59 35.64 35.93 597,000 35.64
End Date: day Year 3-Jan-11 35.67 36.59 32.93 34.15 426,000 33.88

 
At 7:18 AM, April 22, 2011, Blogger Mr Market said...

Per your comment - "Once that happens, percentage rate of return numbers are trickier to calculate, and negative stock prices in general don't make sense intuitively."

What is the approach to calculating the return if we are using the actual P&Ls (i.e. see historical negative numbers)?

 
At 8:42 AM, August 02, 2012, Blogger Howard Richoux said...

I know this is ancient to you, but I can't find any discussions of "adjusted close" anywhere else.

I am impressed that you were able to verify the formula Yahoo uses, but, unless I am missing something obvious, it CAN'T be mathematically valid. There is no real-world relationship between cash dividends paid and the closing price of a stock.

The only way I have figured out to construct an "adjusted close" is to subtract the sum of the dividends paid between the dates in question (current day versus date in the past). Stock splits and stock dividends make this harder to compute, but are totally independent from cash dividends.

The easy way to see that Yahoo's computation is "wrong" is to examine a block of adjusted closes versus reported close. Subtracting the adjusted close from the reported close "ought" to produce the cumulative dividends paid if there were no splits during the period. The numbers it does produce are somewhat near the dividends number, but wrong well beyond rounding errors. (I know that my proposed verification is precisely my contention, but I can't see any definition of "adjusted close" that is different than what I describe - The only financial use I can see for "adjusted close" is to compute a "total return" --> capital gain plus cash dividends received)

-- Puzzled...

 
At 4:19 PM, October 09, 2012, Blogger PaulC said...

Here is a dividend-adjustment workssheet. Adjustment implies that the dividends/cap gain/etc. are reinvested in new shares of the same issue See http://www.fasttrack.net/newhelp/DistributionWorkSheet.asp

 
At 4:20 PM, October 09, 2012, Blogger PaulC said...

Here is a dividends-adjustment works sheet. The adjustment implies that the dividends/cap gain/etc. is reinvested in new shares of the same issue See http://www.fasttrack.net/newhelp/DistributionWorkSheet.asp

 
At 12:26 AM, August 27, 2013, Blogger GOD FATHER OF INDIAN TRADING said...

IS ADJUSTED CLOSE PRICE & CLOSING PRICE IS SAME FOR INDEXES NIFTY AS THERE IS NO SPLITS AS WELL AS DIVIDENDS,IF IT IS NOT PLEASE HELP ME OUT TO FIND ADJUSTED CLOSE PRICE OF IT.

 
At 8:31 AM, January 29, 2014, Blogger Elle said...

The formula that makes more intuitive sense to me is:

A2 = A1 + A1*(P2*S+D-P1) / P1

where
P1 = real-life price close on day 1
A1 = adjusted close on day 1

P2 = real-life price close one day later, day 2
A2 = adjusted close one day later, day 2

S = 0 when day 2 is not a split day
S = split factor when day 2 is a split day

D= 0 when day 2 is not an ex-dividend day.
D = amount of dividend when day 2 is an ex-dividend day

This seems to work to within a penny. It applies to dates before the most recent ex-dividend date.

Note that (P2*S+D-P1) / P1 is the one day total return of the stock.

 
At 8:38 AM, January 29, 2014, Blogger Elle said...

Same result but more (doh) efficient version:

A2 = A1 * (P2*S+D) / P1

 

Post a Comment

<< Home