yahoo finance - adjusted price data incorrect?

Discuss all general (i.e. non-personal) investing questions and issues, investing news, and theory.
datamonkee
Posts: 59
Joined: Wed Sep 07, 2011 10:40 pm

yahoo finance - adjusted price data incorrect?

Post by datamonkee » Tue May 16, 2017 7:35 am

i was pulling price history data from finance.yahoo.com, and i noticed that the "adjusted close price" is the same as the "close price" even before/after dividend payments. Is there a bug in the site?

livesoft
Posts: 56391
Joined: Thu Mar 01, 2007 8:00 pm

Re: yahoo finance - adjusted price data incorrect?

Post by livesoft » Tue May 16, 2017 7:37 am

I'm not sure what adjusted closing price is supposed to be anyways. Are you?
This signature message sponsored by sscritic: Learn to fish.

GLState
Posts: 63
Joined: Wed Feb 15, 2017 10:38 am

Re: yahoo finance - adjusted price data incorrect?

Post by GLState » Tue May 16, 2017 7:49 am

Do you have a specific example?

Adjusted Close IBM...

edit: This is not correct either


Code: Select all

May 08, 2017   152.80   153.47   152.20   153.03   153.03   7,492,000
May 08, 2017   1.5 Dividend
May 05, 2017   153.52   155.78   153.00   155.05   155.05   12,521,300
Last edited by GLState on Tue May 16, 2017 8:31 am, edited 1 time in total.

zeugmite
Posts: 892
Joined: Tue Jul 22, 2008 11:48 pm

Re: yahoo finance - adjusted price data incorrect?

Post by zeugmite » Tue May 16, 2017 7:57 am

datamonkee wrote:i was pulling price history data from finance.yahoo.com, and i noticed that the "adjusted close price" is the same as the "close price" even before/after dividend payments. Is there a bug in the site?


I've seen this. You can just do your own calculations though, and anyways adjusted prices are almost useless, being attached to a floating date. The only things you should trust are the actual prices and the reported dividends. Sometimes the dividends reported are late, too, so you may need to come back and backfill the data.
Last edited by zeugmite on Tue May 16, 2017 7:59 am, edited 1 time in total.

datamonkee
Posts: 59
Joined: Wed Sep 07, 2011 10:40 pm

Re: yahoo finance - adjusted price data incorrect?

Post by datamonkee » Tue May 16, 2017 7:58 am

adjusted close represents a price that includes the dividend, i think. So, when you are calculating returns, the returns % value includes the dividend.

here's an example
Date Open High Low Close Adj Close* Volume
Mar 24, 2017 120.42 120.74 119.66 120.16 120.16 2,305,400
Mar 24, 2017 0.542 Dividend
Mar 23, 2017 120.66 121.39 120.47 120.69 120.69 2,257,100

notice the 2nd to right column prices are identical to the 3rd to right column.

GLState
Posts: 63
Joined: Wed Feb 15, 2017 10:38 am

Re: yahoo finance - adjusted price data incorrect?

Post by GLState » Tue May 16, 2017 8:07 am

from investopedia:
"An adjusted closing price is a stock's closing price on any given day of trading that has been amended to include any distributions and corporate actions that occurred at any time prior to the next day's open. The adjusted closing price is often used when examining historical returns or performing a detailed analysis on historical returns."

datamonkee is correct. Yahoo's adjusted close is not reflecting recent dividends. However, when i look at IBM data from 1962, the adjusted close is different, but can't be correct if current dividends are not being taken into account.

Code: Select all

Jan 03, 1962   7.63   7.69   7.63   577.00   7.69   288,000
Jan 02, 1962   7.71   7.71   7.63   572.00   7.63   387,200


Also, at some point, Yahoo has changed the format of their csv files. They used to be HLOCVA ...they are now HLOCAV.
Last edited by GLState on Tue May 16, 2017 9:20 am, edited 1 time in total.

User avatar
House Blend
Posts: 4225
Joined: Fri May 04, 2007 1:02 pm

Re: yahoo finance - adjusted price data incorrect?

Post by House Blend » Tue May 16, 2017 8:59 am

datamonkee wrote:i was pulling price history data from finance.yahoo.com, and i noticed that the "adjusted close price" is the same as the "close price" even before/after dividend payments. Is there a bug in the site?

I've noticed this issue. At least for mutual fund data, the "adjusted" price isn't adjusted when viewed in the browser window. (I thought it might be a cookie issue--I block cookies by default.)

However, if you use the download button to grab a CSV file the issue goes away. You get the adjusted prices.

livesoft
Posts: 56391
Joined: Thu Mar 01, 2007 8:00 pm

Re: yahoo finance - adjusted price data incorrect?

Post by livesoft » Tue May 16, 2017 9:02 am

And so ... are you saying that the Adjusted Price in the CSV file depends on the starting date of the data in the CSV file?

[Also note that the the CSV file is sorted now on date in opposite way than last year. And downloaded file seems to have "-2" at end of name perhaps to indicate a change in the format or version.]
This signature message sponsored by sscritic: Learn to fish.

GLState
Posts: 63
Joined: Wed Feb 15, 2017 10:38 am

Re: yahoo finance - adjusted price data incorrect?

Post by GLState » Tue May 16, 2017 9:39 am

livesoft wrote:And so ... are you saying that the Adjusted Price in the CSV file depends on the starting date of the data in the CSV file?

[Also note that the the CSV file is sorted now on date in opposite way than last year. And downloaded file seems to have "-2" at end of name perhaps to indicate a change in the format or version.]


The adjusted close does not depend on the starting date of the data. The adjusted close is recalculated for all historic data each time a dividend or stock split occurs. The adjusted close makes it easy to calculate total return between any two points in time. If yahoo is not adjusting current adjusted close prices, the IBM data from 1962 will also not be readjusted.

I don't see a -2 on the end of the file name when I download IBM data from yahoo. I just see IBM.

Adjusted Close Price vs Close Price ... http://finance.zacks.com/adjusted-closing-price-vs-closing-price-9991.html

johnohh
Posts: 19
Joined: Wed Dec 25, 2013 7:01 pm

Re: yahoo finance - adjusted price data incorrect?

Post by johnohh » Tue May 16, 2017 6:45 pm

datamonkee wrote:i was pulling price history data from finance.yahoo.com, and i noticed that the "adjusted close price" is the same as the "close price" even before/after dividend payments. Is there a bug in the site?


This has been true off and on for 18 months on yahoo. Very frustrating. This information is useful in comparing investments that have different dividends, like say, SDY and SPY. Some sites like stockcharts.com allow you to switch between total return (i.e. "adjusted close") and price only, but AFAIK only yahoo provides for data downloading of both types. I actually live in Sunnyvale and get invited in regularly for yahoo user feedback sessions, but even then, cannot get anyone at yahoo to act on my user feedback. lol.

To the guy above who mentioned IBM, if on yahoo you select "stock spilts only", you will see the reason IBM's adjusted close price is different than the closing price. Yahoo DOES correctly adjust price for stock splits, but does not (today anyway, as they will probably fix it eventually) adjust for dividends.

GLState
Posts: 63
Joined: Wed Feb 15, 2017 10:38 am

Re: yahoo finance - adjusted price data incorrect?

Post by GLState » Tue May 16, 2017 7:29 pm

This evening, I can't download any Yahoo stock or fund prices into Excel, Python, or R. Hopefully just a short-term problem. Downloading prices from google into R works ok.

datamonkee
Posts: 59
Joined: Wed Sep 07, 2011 10:40 pm

Re: yahoo finance - adjusted price data incorrect?

Post by datamonkee » Tue May 16, 2017 8:38 pm

thanks, all, for your responses.

Good point about the date sort order changing on the downloaded CSV file. this is the first time i noticed it. I wonder if Yahoo changing the way they do something has resulted in the incorrect adjusted close price.

i just downloaded some data from yahoo. it is true that the adjusted price still takes into account splits but not dividends, yet.

hopefully they fix this soon.

User avatar
F150HD
Posts: 1081
Joined: Fri Sep 18, 2015 7:49 pm

Re: yahoo finance - adjusted price data incorrect?

Post by F150HD » Tue May 16, 2017 9:44 pm

used to use Yahoo Finance some years ago, really liked it. Then they changed the format and it just downright sucked. Odd scripts kept freezing my browser, it was loaded w/ ads, clunky, detailed info I used to use was gone etc....

Now I no longer go there.

GLState
Posts: 63
Joined: Wed Feb 15, 2017 10:38 am

Re: Yahoo downloads not working. Changes to yahoo's API

Post by GLState » Wed May 17, 2017 7:20 am

As I understand it, a few weeks ago yahoo switched from http to https. They have now changed the historic data download url. R's quantmod developer is working on a fix. Likely, there will also be fixes forthcoming for Python datareader. Excel macro users will have to edit their macros.

NibbanaBanana
Posts: 194
Joined: Sun Jan 22, 2017 10:34 pm

Re: yahoo finance - adjusted price data incorrect?

Post by NibbanaBanana » Wed May 17, 2017 7:39 am

F150HD wrote:used to use Yahoo Finance some years ago, really liked it. Then they changed the format and it just downright sucked. Odd scripts kept freezing my browser, it was loaded w/ ads, clunky, detailed info I used to use was gone etc....

Now I no longer go there.


Exactly the same. MSN doesn't seem to cause any problems but Yahoo locks up my computer. I'm guessing too many ads and videos. Used to be my favorite for financial info but now I just can't use it.

rkhusky
Posts: 4315
Joined: Thu Aug 18, 2011 8:09 pm

Re: yahoo finance - adjusted price data incorrect?

Post by rkhusky » Wed May 17, 2017 8:43 am

House Blend wrote:However, if you use the download button to grab a CSV file the issue goes away. You get the adjusted prices.


Thanks. I had given up on using Yahoo since it didn't appear that they were providing the adjusted price. I'll download instead.

GLState
Posts: 63
Joined: Wed Feb 15, 2017 10:38 am

Re: yahoo finance - adjusted price data incorrect?

Post by GLState » Wed May 17, 2017 9:14 am

rkhusky wrote:
House Blend wrote:However, if you use the download button to grab a CSV file the issue goes away. You get the adjusted prices.


Thanks. I had given up on using Yahoo since it didn't appear that they were providing the adjusted price. I'll download instead.


Yahoo still gives an "adjusted price" but it is no longer adjusted for dividends, only for stock splits. In the IBM example below, IBM paid a dividend on May 8th, but the adjusted close and the close are still the same ( 155.05 & 159.05) for previous dates. The .csv file with show the same data.

Code: Select all

May 08, 2017   1.5 Dividend
May 05, 2017   153.52   155.78   153.00   155.05   155.05   12,521,300
May 04, 2017   158.89   159.14   158.36   159.05   159.05   4,280,600

rkhusky
Posts: 4315
Joined: Thu Aug 18, 2011 8:09 pm

Re: yahoo finance - adjusted price data incorrect?

Post by rkhusky » Wed May 17, 2017 9:50 am

GLState wrote:Yahoo still gives an "adjusted price" but it is no longer adjusted for dividends, only for stock splits.

You're right. Brief hope dashed.

Gene S
Posts: 24
Joined: Mon Jul 27, 2015 12:27 pm

Re: yahoo finance - adjusted price data incorrect?

Post by Gene S » Wed May 17, 2017 12:21 pm

This is bad, especially for those who track bond funds. Without the adjusted price, the Yahoo Finance price data, chart, and historical data for the Vanguard Total Bond Index (VBMFX) is totally useless, because the fund makes most of its money through dividends. Oh well...

johnohh
Posts: 19
Joined: Wed Dec 25, 2013 7:01 pm

Re: yahoo finance - adjusted price data incorrect?

Post by johnohh » Wed May 17, 2017 2:32 pm

Gene S wrote:This is bad, especially for those who track bond funds. Without the adjusted price, the Yahoo Finance price data, chart, and historical data for the Vanguard Total Bond Index (VBMFX) is totally useless, because the fund makes most of its money through dividends. Oh well...


I've been creating my own "adjusted close" column while waiting for yahoo to get its act together. You just combine their price and dividend values then add a column for adjusted close. Here's a sample using VBMFX - the formula is spelled out in column E.
Image

GLState
Posts: 63
Joined: Wed Feb 15, 2017 10:38 am

Re: yahoo finance - adjusted price data incorrect?

Post by GLState » Wed May 17, 2017 3:49 pm

CRSP method for adjusting prices for stock splits and dividends.
http://www.crsp.com/products/documentation/crsp-calculations

FactualFran
Posts: 428
Joined: Sat Feb 21, 2015 2:29 pm

Re: yahoo finance - adjusted price data incorrect?

Post by FactualFran » Wed May 17, 2017 4:06 pm

johnohh wrote:I've been creating my own "adjusted close" column while waiting for yahoo to get its act together. You just combine their price and dividend values then add a column for adjusted close. Here's a sample using VBMFX - the formula is spelled out in column E.
Image

The total return for 05/01/2017 using those adjusted close values does not correspond to the actual total return.

Suppose someone purchased one share on 04/28/2017. The price paid would have been 10.74. The next day the person would still have one share, a share bought on 04/28/2017 would not have gotten the dividend paid that day. On 05/01/2017 the share would be worth $10.72. The actual total return for the day was -0.19% (= 10.72/10.74 - 1).

However, according to the adjusted close values the return for the day was +0.01% (= 10.72/10.719 - 1).

An appropriate formula for cell D4 is D5*B4/(B5*(1+C5/E5)). It avoids having the distribution on a day incorrectly affecting the adjusted NAV for that day.

User avatar
House Blend
Posts: 4225
Joined: Fri May 04, 2007 1:02 pm

Re: yahoo finance - adjusted price data incorrect?

Post by House Blend » Wed May 17, 2017 5:27 pm

GLState wrote:
rkhusky wrote:
House Blend wrote:However, if you use the download button to grab a CSV file the issue goes away. You get the adjusted prices.


Thanks. I had given up on using Yahoo since it didn't appear that they were providing the adjusted price. I'll download instead.


Yahoo still gives an "adjusted price" but it is no longer adjusted for dividends, only for stock splits. In the IBM example below, IBM paid a dividend on May 8th, but the adjusted close and the close are still the same ( 155.05 & 159.05) for previous dates. The .csv file with show the same data.

Code: Select all

May 08, 2017   1.5 Dividend
May 05, 2017   153.52   155.78   153.00   155.05   155.05   12,521,300
May 04, 2017   158.89   159.14   158.36   159.05   159.05   4,280,600

Hmm, yes, something about yahoo finance got broken in the last few days.

Now when I enter VTSAX at Yahoo Finance, click on the "historical data" tab, and then click on the "Apply" and "download" buttons, I only get unadjusted prices.

Worse, I also have scripts that use the ichart interface to grab historical data. For example, this URL should retrieve a CSV file of the entire price history of vtsax:

https://ichart.finance.yahoo.com/table.csv?s=vtsax

Instead it gives me an error page: "Our engineers are working quickly to resolve the issue."

Not clear if I should wait, or try to fuss with new scripts that go through finance.yahoo.com. (For most purposes all I want are unadjusted price histories.)

dodonnell
Posts: 397
Joined: Wed Oct 29, 2008 6:48 pm

Re: yahoo finance - adjusted price data incorrect?

Post by dodonnell » Wed May 17, 2017 5:58 pm

House Blend wrote:..snip..

Worse, I also have scripts that use the ichart interface to grab historical data. For example, this URL should retrieve a CSV file of the entire price history of vtsax:

https://ichart.finance.yahoo.com/table.csv?s=vtsax

Instead it gives me an error page: "Our engineers are working quickly to resolve the issue."

Not clear if I should wait, or try to fuss with new scripts that go through finance.yahoo.com. (For most purposes all I want are unadjusted price histories.)

Same here. Except slightly different string. I was using real-chart ... instead of ichart.
... waiting patiently for Yahoo.

johnohh
Posts: 19
Joined: Wed Dec 25, 2013 7:01 pm

Re: yahoo finance - adjusted price data incorrect?

Post by johnohh » Wed May 17, 2017 6:34 pm

FactualFran wrote:
johnohh wrote:I've been creating my own "adjusted close" column while waiting for yahoo to get its act together. You just combine their price and dividend values then add a column for adjusted close. Here's a sample using VBMFX - the formula is spelled out in column E.
Image

The total return for 05/01/2017 using those adjusted close values does not correspond to the actual total return.

Suppose someone purchased one share on 04/28/2017. The price paid would have been 10.74. The next day the person would still have one share, a share bought on 04/28/2017 would not have gotten the dividend paid that day. On 05/01/2017 the share would be worth $10.72. The actual total return for the day was -0.19% (= 10.72/10.74 - 1).

However, according to the adjusted close values the return for the day was +0.01% (= 10.72/10.719 - 1).

An appropriate formula for cell D4 is D5*B4/(B5*(1+C5/E5)). It avoids having the distribution on a day incorrectly affecting the adjusted NAV for that day.


My VMBFX adj close values for 4/27, 4/28, and 5/1 are
10.709, 10.719, and 10.720. What do you propose they should be?

Note that the common practice of total return analysis is that the present day (5/1 in this example) should produce an adjusted close value equal to the present day close value, or 10.720. This is also how yahoo does it, btw.

User avatar
Oicuryy
Posts: 1128
Joined: Thu Feb 22, 2007 10:29 pm

Re: yahoo finance - adjusted price data incorrect?

Post by Oicuryy » Wed May 17, 2017 7:53 pm

FWIW, this thread prompted me to write this little macro to use Yahoo's dividend data to adjust Yahoo's price data.

No guarantees. Use at your own risk. Blah, blah, blah.

Code: Select all

 Sub adjustclose()
'  Put Yahoo historical price data starting in cell A1 sorted in ascending order by date
'   row 1 is column headers
'   column A is date, column E is closing price, column F is closing price to be adjusted
'
'  Put Yahoo dividend data starting in cell I1
'   row 1 is column headers
'   column I is date, column J is dividend amount
'
   Dim pricerow As Range
   Dim divrow As Range
   Dim reinvdate As Range
   Dim adjfactor
   
   For Each divrow In Range("I2", Range("I2").End(xlDown))  ' dividends
      Set reinvdate = Range("A2", Range("A2").End(xlDown)).Find(divrow(1, 1))  ' find the dividend date in the price table
      adjfactor = 1 + divrow(1, 2) / reinvdate(1, 5)     ' dividend divided by reinvest price
      For Each pricerow In Range("A2", reinvdate(0, 1))  ' prices before reinvestment to be adjusted
         pricerow(1, 6) = pricerow(1, 6) / adjfactor
      Next
   Next
   
End Sub


Ron
Money is fungible | Abbreviations and Acronyms

User avatar
jimb_fromATL
Posts: 2187
Joined: Sun Nov 10, 2013 12:00 pm
Location: Atlanta area & Piedmont Triad NC and Interstate 85 in between.

Re: yahoo finance - adjusted price data incorrect?

Post by jimb_fromATL » Thu May 18, 2017 9:21 am

Gene S wrote:This is bad, especially for those who track bond funds. Without the adjusted price, the Yahoo Finance price data, chart, and historical data for the Vanguard Total Bond Index (VBMFX) is totally useless, because the fund makes most of its money through dividends. Oh well...


I happen to have the adjusted data from Yahoo for some funds -- which I collected while it was working a while back (after about a year or more of intermittent unreliability).

To illustrate how useless the data is without readjustment, here's a report including dividend adjustments:

    VBMFX VG Total Bond

    Using monthly returns for VBMFX for 29. years from 12/1987 through 12/2016 the CAGR (Compound Annual Growth Rate, or APY) for a lump sum investment has been 6.079%. $10,000 would have grown to $58,042. DCA contributions of $400 per month would have grown to $325,035 with an APY of 5.17%.

    For 337 rolling 12 month periods the average for a lump sum has been 6.19%. On the average, $10,000 would have grown to $10,637.

    The 12 month rolling average APY for DCA monthly contributions has been 6.16% compounded monthly. DCA contributions of $833 per month would have grown to $10,340 for your investment of $10,000.
    12/1995.
Now let's see how it would look with the data from Yahoo today:

    VG Total Bond WITHOUT DIVIDENDS

    For VBFMX-NODIV for 29. years from 12/1987 through 12/2016 the CAGR (Compound Annual Growth Rate, or APY) for a lump sum investment has been 0.505%. $10,000 would have grown to $11,576. DCA contributions of $400 per month would have grown to $147,560 with an APY of .38%.

    For 337 rolling 12 month periods the average for a lump sum has been .6%. On the average, $10,000 would have grown to $10,061.

    The 12 month rolling average APY for DCA monthly contributions has been 0.61% compounded monthly. DCA contributions of $833 per month would have grown to $10,033 for your investment of $10,000.

How about the S&P500?

    VFINX VG S&P 500 Index

      For VFINX for 29. years from 12/1987 through 12/2016 the CAGR (Compound Annual Growth Rate, or APY) for a lump sum investment has been 9.663%. $10,000 would have grown to $162,995. DCA contributions of $400 per month would have grown to $620,947 with an APY of 8.58%.

      For 337 rolling 12 month periods the average for a lump sum has been 9.52%.
      The 12 month rolling average APY for DCA monthly contributions has been 9.66% compounded monthly.

    VFINX-NODIV VFINX NO DIVIDENDS

      For VFINX-NODIV for 29 years from 12/1987 through 12/2016 the CAGR (Compound Annual Growth Rate, or APY) for a lump sum investment has been 7.353%. $10,000 would have grown to $83,801. DCA contributions of $400 per month would have grown to $411,191 with an APY of 6.45%.

      For 337 rolling 12 month periods the average for a lump sum has been 7.24%.
      The 12 month rolling average APY for DCA monthly contributions has been 7.38% compounded monthly.

On the other hand, maybe Yahoo screwing up their data AGAIN is a blessing in disguise. if you can't wait until the monthly or even yearly data has been compiled, maybe you're worrying about it too much anyway. Like John Bogle said, "Don't just do something. Stand there".

jimb

User avatar
jimb_fromATL
Posts: 2187
Joined: Sun Nov 10, 2013 12:00 pm
Location: Atlanta area & Piedmont Triad NC and Interstate 85 in between.

Re: yahoo finance - adjusted price data incorrect?

Post by jimb_fromATL » Thu May 18, 2017 10:29 am

datamonkee wrote:i was pulling price history data from finance.yahoo.com, and i noticed that the "adjusted close price" is the same as the "close price" even before/after dividend payments. Is there a bug in the site?

GLState wrote:CRSP method for adjusting prices for stock splits and dividends.
http://www.crsp.com/products/documentation/crsp-calculations


HERE is how Yahoo say says they do it ... or at least how they used to do it before they broke it again. It does at least prove that their software is not working correctly now.

jimb

FactualFran
Posts: 428
Joined: Sat Feb 21, 2015 2:29 pm

Re: yahoo finance - adjusted price data incorrect?

Post by FactualFran » Thu May 18, 2017 2:06 pm

johnohh wrote:My VMBFX adj close values for 4/27, 4/28, and 5/1 are
10.709, 10.719, and 10.720. What do you propose they should be?

The adjusted close value should be: 10.709, 10.74, and 10.72. 10.74 is the value that results from the formula I suggested for cell D4: D5*B4/(B5*(1+C5/E5)). When that formula in D4 is copied to D3, with the cell address changes usually done when copying a formula, the result is 10.709.

zeugmite
Posts: 892
Joined: Tue Jul 22, 2008 11:48 pm

Re: yahoo finance - adjusted price data incorrect?

Post by zeugmite » Thu May 18, 2017 6:25 pm

Adjusted price is not that useful, as I stated previously. What you should be concerned with is daily return. For days with a dividend distribution, just add the distribution back into the price*. Once you have all the daily returns, you can find whatever price sequence you want.

* Yahoo reports the distribution as being on the ex-dividend date, so the returns thus obtained are are correct.

johnohh
Posts: 19
Joined: Wed Dec 25, 2013 7:01 pm

Re: yahoo finance - adjusted price data incorrect?

Post by johnohh » Thu May 18, 2017 7:15 pm

FactualFran wrote:
johnohh wrote:My VMBFX adj close values for 4/27, 4/28, and 5/1 are
10.709, 10.719, and 10.720. What do you propose they should be?

The adjusted close value should be: 10.709, 10.74, and 10.72. 10.74 is the value that results from the formula I suggested for cell D4: D5*B4/(B5*(1+C5/E5)). When that formula in D4 is copied to D3, with the cell address changes usually done when copying a formula, the result is 10.709.

You're right, my mistake. Knowing that the dividend is usually offset by a corresponding closing price drop which produces a smooth adjusted close trend, I added the dividend to the same days' closing price. Would have been more obvious had I been looking at say, NLY.

User avatar
Oicuryy
Posts: 1128
Joined: Thu Feb 22, 2007 10:29 pm

Re: yahoo finance - adjusted price data incorrect?

Post by Oicuryy » Thu May 18, 2017 8:00 pm

What is the best way to calculate the daily return when a distribution occurs? Is it
(ex-divided price + dividend) / previous price
Or is it
ex-dividend price / (previous price - dividend)

If I understand the link in jimb_fromATL's post correctly, Yahoo uses the second method when adjusting prices.

Does it make any difference?

Ron
Money is fungible | Abbreviations and Acronyms

johnohh
Posts: 19
Joined: Wed Dec 25, 2013 7:01 pm

Re: yahoo finance - adjusted price data incorrect?

Post by johnohh » Thu May 18, 2017 9:43 pm

Oicuryy wrote:What is the best way to calculate the daily return when a distribution occurs? Is it
(ex-divided price + dividend) / previous price
Or is it
ex-dividend price / (previous price - dividend)

If I understand the link in jimb_fromATL's post correctly, Yahoo uses the second method when adjusting prices.Does it make any difference? Ron


The first one is correct. If you had 1 share with an unchanging NAV of $10 and it paid a $1 dividend, your first equation says you made 10%, which is correct. But your second equation would say you made 11%.

User avatar
Oicuryy
Posts: 1128
Joined: Thu Feb 22, 2007 10:29 pm

Re: yahoo finance - adjusted price data incorrect?

Post by Oicuryy » Thu May 18, 2017 11:27 pm

Thank you, johnohh.

Ron
Money is fungible | Abbreviations and Acronyms

datamonkee
Posts: 59
Joined: Wed Sep 07, 2011 10:40 pm

Re: yahoo finance - adjusted price data incorrect?

Post by datamonkee » Sat May 20, 2017 4:07 pm

i just checked the site. prices are still wrong.
boo....

User avatar
House Blend
Posts: 4225
Joined: Fri May 04, 2007 1:02 pm

Re: yahoo finance - adjusted price data incorrect?

Post by House Blend » Sun May 21, 2017 9:24 am

I've been kicking the tires in the historical data provided by Google Finance. It's easily accessible, but I notice that it has gaps:

For VTSAX it is currently missing the closing price for last Monday (5/15), and for VLCAX is it missing both Monday and Thursday. :oops:

User avatar
jimb_fromATL
Posts: 2187
Joined: Sun Nov 10, 2013 12:00 pm
Location: Atlanta area & Piedmont Triad NC and Interstate 85 in between.

Re: yahoo finance - adjusted price data incorrect?

Post by jimb_fromATL » Mon May 22, 2017 4:48 pm

datamonkee wrote:i just checked the site. prices are still wrong.
boo....
Yep, the data is still not adjusted, and the various API's are still not working. Incidentally, when they broke it last year, to the best of my knowledge it was several months before they fixed it. (Not sure whether they fixed it on purpose or by accident, either. Just happened to notice that it was working right again a month or two ago.)

A lot of the old less-cumbersome, faster, and less crash-prone data screens were still available if you knew the URL addresses until recently, but those also disappeared a couple of weeks ago ... along with most or all of the APIs that make automated downloading to spreadsheets possible.

However, there are some work-arounds that avoid Yahoo altogether for a lot of purposes. Read on.

Here are some newer threads on the subject.
viewtopic.php?f=1&t=219490&p=3378719&hi ... a#p3378719
Hector wrote:Where can I see historical data for stock price adjusted for dividends and splits? I used to see this at yahoo finance, but it does not show it anymore (closing price and adjusted closing prices are displaying the same and it is not adjusted for dividend or split)
viewtopic.php?f=10&t=219433&p=3377893&h ... a#p3377893
TagmoreClock wrote:Any idea where i can get dividend adjusted historical closing data for vangard funds in csv format (for free) from fund inception dates?

I used to get from Yahoo, but it is not dividend adjusted any more :-( https://finance.yahoo.com/quote/VBMFX/history?p=VBMFX). More over when I download csv, dividends are not included, hence I am not able to adjust those myself.

Google has it, but only from Jan 2000.

Any pointer will help.
Thanks
Oicuryy wrote:Yahoo still has the dividends but you have to download them separately.
https://finance.yahoo.com/quote/VBMFX/h ... equency=1d
Ron
The problem is that since they broke it again, you’ll need to download the data in three steps. One for the closing data; another for the dividends, and another for the splits. This is time consuming and tedious because they also broke -- or perhaps intentionally disabled(?)-- the API that allowed you to download it directly to your spreadsheet. So now you have to go through their new cumbersome and crash-prone data screens three times.

Then once you have all the data, you’ll need to build a complicated lookup table or vba subroutine to correlate the closing price and dividend or splits just to end up with the adjusted data that Yahoo was already providing in one single table up until two weekends ago.

Fortunately, if you want historical adjusted data to calculate and compare the total returns for a given period of time in modern times, you don't need the Yahoo daily or monthly data anyway.

At least for now, the yearly and monthly total total returns data for a lot of stocks, mutual funds and ETFs is included in the data you can download from backtesting in Portfoliovisualizer. The download process can also be semi-automated from your spreadsheet, provided you're registered (for free) and logged in there.

By the way, PortfolioVisualizer is only online source I know of that allows you to do DCA (Dollar-Cost-Averaging) with regular periodic contributions like a 401(k) or IRA instead of the hypothetical "growth of $10,000" which is not very realistic for most investors, and which can vary widely depending on whether the starting period was at a peak or in a valley.

Here's a sample call to Portfolio Visualizer to preload the menu for a fund for annual contributions:

https://www.portfoliovisualizer.com/bac ... ion1_1=100


You'll probably need to convert the year and month data from PortfolioVisualizer for monthly data to a more usable date in Excel or other spreadsheets, but that is pretty simple compared to correlating the multiple files that you now need from Yahoo.

I"ve done spot comparisons on perhaps a dozen sample funds with adjusted monthly total performance data (which I happen to have from Yahoo before they broke it) and the monthly and annual performance data from PortfolioVisualizer. In every case I've tested, the difference is virtually insignificant ... not significantly more than rounding errors or precision of fractional percentages. The same goes for the yearly total performance data I've checked for the shorter time frames that are readily available from Yahoo, Morningstar, and Vanguard themselves.

The data from PorfolioVisualizer only goes back to around 1985-1986, but if you want data on a daily basis from more than 30 years ago, my main question would be why ?

The latest thirty years include some of the high inflation of the 80s and 90s; the irrational exuberance of the dot-com era; and the crashes of 2002/3 and 2008. Economic times have changed so much that anything earlier may never happen again -- and I have hard time imagining any case where total performance for an increment of less than a month could have much significance for any kind of performance comparisons.

jimb

GLState
Posts: 63
Joined: Wed Feb 15, 2017 10:38 am

Re: yahoo finance - adjusted price data incorrect?

Post by GLState » Mon May 22, 2017 5:46 pm

The yahoo data has changed and it doesn't look like it will be back as we knew it. In the one of the Yahoo support forums, a mod responded:

Code: Select all

Nixon
  Official 'Hoo Staff Nixon  Official 'Hoo Staff
‎05-17-2017 02:59 PM
 Re: Is Yahoo! Finance API broken?  
Hi All - This feature was discontinued by the Finance team and they will not be reintroducing that functionality. 
https://forums.yahoo.net/t5/Yahoo-Finan ... 1312#M3123

If you use Python, there is a temporary download fix https://pypi.python.org/pypi/fix-yahoo-finance/0.0.4
If you use R, Joshua Ulrich has a download fix for quantmod that is not yet pushed to CRAN. I installed it with: (note: devtools has to be installed)

Code: Select all

devtools::install_github("joshuaulrich/quantmod")
The "new" yahoo data is adjusted for splits, but not dividends.

User avatar
jimb_fromATL
Posts: 2187
Joined: Sun Nov 10, 2013 12:00 pm
Location: Atlanta area & Piedmont Triad NC and Interstate 85 in between.

Re: yahoo finance - adjusted price data incorrect?

Post by jimb_fromATL » Mon May 22, 2017 7:13 pm

GLState wrote:The yahoo data has changed and it doesn't look like it will be back as we knew it. In the one of the Yahoo support forums, a mod responded:

Code: Select all

Nixon
  Official 'Hoo Staff Nixon  Official 'Hoo Staff
‎05-17-2017 02:59 PM
 Re: Is Yahoo! Finance API broken?  
Hi All - This feature was discontinued by the Finance team and they will not be reintroducing that functionality. 
https://forums.yahoo.net/t5/Yahoo-Finan ... 1312#M3123
Brilliant marketing strategy. First they modified the screens to make them slower, crash often, have less information in a less useable format that made them virtually useless compared to what they had been for many years. However, it would appear that did not drive away enough customers, so now they have taken away the API and removed the adjusted return data which were virtually the only features left that would cause most folks to go there instead of to competitors in the financial news and information field. :confused

jimb

Gene S
Posts: 24
Joined: Mon Jul 27, 2015 12:27 pm

Re: yahoo finance - adjusted price data incorrect?

Post by Gene S » Mon May 22, 2017 9:21 pm

Sadly, I think the days of using Excel, R, and other programs to automatically download free end of day stock data are over. Yahoo Finance is not the only company that has stopped automatic downloading of historical stock data. I think Google has stopped it as well. I suspect that it is Yahoo's data providers, not Yahoo, that are requiring this.

Yahoo's "buggy" web site may not be by accident. Perhaps the "bugs" are there to prevent you from performing automatic or scripted downloads.

Because it is time consuming to manually download the price and dividend data from Yahoo Finance, I only calculate the adjusted price for a few mutual funds, such as Vanguard's Intermediate Treasury Fund VFITX. I use these funds as proxies to extend the Stock, Bonds, Bills and Inflation total return asset class indexes. I calculate the adjusted price from the oldest data to the newest data on a daily basis, then I convert the daily data to obtain the end of the month adjusted price. For the funds I use, I don't have to worry about price splits, as mentioned in the previous two post by jimb. jimb makes it sound like Yahoo's Finance historical price and dividend data are not adjusted for stock splits.

FactualFran
Posts: 428
Joined: Sat Feb 21, 2015 2:29 pm

Re: yahoo finance - adjusted price data incorrect?

Post by FactualFran » Tue May 23, 2017 2:39 pm

Gene S wrote:Sadly, I think the days of using Excel, R, and other programs to automatically download free end of day stock data are over. Yahoo Finance is not the only company that has stopped automatic downloading of historical stock data. I think Google has stopped it as well. I suspect that it is Yahoo's data providers, not Yahoo, that are requiring this.
With some adjustments, I have been able to continue to download end of day prices from Yahoo. However, I don't do it from software that automatically imports the data. The response to opening the URL

http://download.finance.yahoo.com/d?s=v ... &f=sl1d1t1

is

"vtsax",59.89,"5/22/2017","8:00pm"
"vbltx",13.92,"5/22/2017","8:00pm"

A potential problem is that the web server identifies the content type as application/octet-stream. Software that tries to automatically import the data may not realize that the response is in CSV format.
Last edited by FactualFran on Sat May 27, 2017 11:38 am, edited 1 time in total.

datamonkee
Posts: 59
Joined: Wed Sep 07, 2011 10:40 pm

Re: yahoo finance - adjusted price data incorrect?

Post by datamonkee » Sat May 27, 2017 9:22 am

finance.yahoo.com price watch - week2
still broken....

datamonkee
Posts: 59
Joined: Wed Sep 07, 2011 10:40 pm

Re: yahoo finance - adjusted price data incorrect?

Post by datamonkee » Mon May 29, 2017 10:41 am

i was just trying to formulate an excel calc sheet to convert available yahoo data (prices & div) to the way the data used to be. I discovered another Yahoo error (at least i think it's an error).

https://finance.yahoo.com/quote/TIP/his ... equency=1d

if you look up dividends on some etfs or funds (as with TIP in the above link), you will notice a dividend but no actual dividend value. So, yahoo is missing data. I am not mistaken here, right?

dodonnell
Posts: 397
Joined: Wed Oct 29, 2008 6:48 pm

Re: yahoo finance - adjusted price data incorrect?

Post by dodonnell » Mon May 29, 2017 11:14 am

datamonkee wrote:i was just trying to formulate an excel calc sheet to convert available yahoo data (prices & div) to the way the data used to be. I discovered another Yahoo error (at least i think it's an error).

https://finance.yahoo.com/quote/TIP/his ... equency=1d

if you look up dividends on some etfs or funds (as with TIP in the above link), you will notice a dividend but no actual dividend value. So, yahoo is missing data. I am not mistaken here, right?
Mistaken.

Data from the authoritative source: BlackRock's iShares TIPS
https://www.blackrock.com/investing/pro ... bond-etf#/

Image

Image

datamonkee
Posts: 59
Joined: Wed Sep 07, 2011 10:40 pm

Re: yahoo finance - adjusted price data incorrect?

Post by datamonkee » Tue May 30, 2017 7:32 am

thank you so much for finding that. that is actually reassuring in that the data is still good such that we can calculate the adjusted prices.

dodonnell
Posts: 397
Joined: Wed Oct 29, 2008 6:48 pm

Re: yahoo finance - adjusted price data incorrect?

Post by dodonnell » Tue May 30, 2017 8:40 am

Trust, but (always) verify

NASDAQ TIPS Dividends

You can download 10 years worth of daily price data using the link at the bottom of this page (unadjusted :( )
http://www.nasdaq.com/symbol/tip/historical

johnohh
Posts: 19
Joined: Wed Dec 25, 2013 7:01 pm

Re: yahoo finance - adjusted price data incorrect?

Post by johnohh » Tue Jun 06, 2017 6:00 pm

June 6, 2017. yahoo adjusted close prices appear to be working again....

abhikush
Posts: 5
Joined: Mon Jan 07, 2013 6:04 pm

Re: yahoo finance - adjusted price data incorrect?

Post by abhikush » Wed Jun 07, 2017 3:24 pm

johnohh wrote:June 6, 2017. yahoo adjusted close prices appear to be working again....
I checked BND (https://finance.yahoo.com/quote/BND/history?p=BND) and 'Adj Close' is still not adjusted for dividends.

johnohh
Posts: 19
Joined: Wed Dec 25, 2013 7:01 pm

Re: yahoo finance - adjusted price data incorrect?

Post by johnohh » Wed Jun 07, 2017 3:59 pm

yahoo is such a tease. I had checked spy, nly, and tlt last night and all were showing correct adjusted closes. But right now? They're all incorrect again, back to close = adj close.

User avatar
jimb_fromATL
Posts: 2187
Joined: Sun Nov 10, 2013 12:00 pm
Location: Atlanta area & Piedmont Triad NC and Interstate 85 in between.

Re: yahoo finance - adjusted price data incorrect?

Post by jimb_fromATL » Wed Jun 07, 2017 4:31 pm

johnohh wrote:yahoo is such a tease. I had checked spy, nly, and tlt last night and all were showing correct adjusted closes. But right now? They're all incorrect again, back to close = adj close.
I also spot-checked a couple of funds and they were showing adjusted closing data. Did not check whether the numbers seemed to be correct or not. But given Yahoo's track record, I wouldn't bet a lot of money on it.

jimb

Post Reply