Skip to main content
No. of Recommendations: 0
I use WFC-PL to secure intermediate term [3-6 years] withdrawal requirements, so it's a significant factor in my long-term investment strategy. For many months I've been unable to obtain dividend-adjusted historical prices for this stock from Yahoo Finance. I have been able to automate this without cost via Excel macro for the dozens of common stocks on my watchlist, but not for the PL. Can anyone suggest a way of doing this?

Alternatively, what would be the formula for converting unadjusted historical closing prices to adjusted, based upon the fixed $75/year dividend applicable to WFC-PL? I've tried reducing the unadjusted daily prices by (75/365.4) per day, but that appears not to work properly.

Thanks for any assistance with this.
Tom
Print the post Back To Top
No. of Recommendations: 8
tommurphy9:
For many months I've been unable to obtain dividend-adjusted historical prices for [WFC-PL] from Yahoo Finance. I have been able to automate this without cost via Excel macro for the dozens of common stocks on my watchlist, but not for the PL. Can anyone suggest a way of doing this?

There's probably a fairly trivial bug in your Excel macro, but in the meantime there's this:

http://gtr1.net/2013/?!!QlpoMTFBWSZTWexV!2FeoAAD2fgAIGcPAJRM...

Select "Signal Values" under Report Controls, click "Run Backtest" and download the report spreadsheet.

Robbie Geary
Print the post Back To Top
No. of Recommendations: 1
Alternatively, what would be the formula for converting unadjusted historical closing prices to adjusted, based upon the fixed $75/year dividend applicable to WFC-PL? I've tried reducing the unadjusted daily prices by (75/365.4) per day, but that appears not to work properly.

They pay a dividend of $18.75 per share on 3/15, 6/15, 9/15, 12/15. If any of those dates come out on a weekend, they pay on the next market day. You could make your price adjustment base on those payout dates, although it may be more accurate to make them on the ex date which is earlier. The ex date is the date on which the market price is actually adjusted for the dividend. If you own the shares on the ex date you get the dividend, even if you've sold the shares before the payment date.

Elan
Print the post Back To Top
No. of Recommendations: 1
. . .If you own the shares on the ex date you get the dividend, even if you've sold the shares before the payment date.


To nitpick, true as long as you bought BEFORE the ex date and not ON the ex date.

BLancaster
Print the post Back To Top
No. of Recommendations: 4
BLancaster:
To nitpick, true as long as you bought BEFORE the ex date and not ON the ex date.

To nitpick a little further, true for each share that you bought before the ex-date AND that you didn't SELL before the ex-date. Even selling at the 8pm close of the after-hours trading session on the entitlement date (the term for the day before the ex-date, also knows as a cum-dividend date) will disentitle you to the dividend.

Robbie Geary
Print the post Back To Top
No. of Recommendations: 0
Thanks for your help with this, Robbie, Elan and the others. I had actually been able to get the adjusted closing prices back to the beginning of 2015 by array-entering Randy Harmelink's = smfGetYahooHistory(Ticker, DateFirst, DateLast, "d", "c", 0). However, I've been unable to get any earlier data, and GTR1 also only returned data back to the beginning of 2015. I suppose it all comes from Yahoo, and that's all they have on offer.

So I'm working on a macro that derives the adjusted prices from the unadjusted closing prices, given the fixed dividend, and using the data back to 2015 to confirm its accuracy.

Tom
Print the post Back To Top
No. of Recommendations: 3
tommurphy9:
I suppose it all comes from Yahoo.

It all comes from Commodity Systems Inc. You can check which stocks CSI covers and how far back at https://apps.csidata.com/FactsheetListing.aspx . I see that they have data on WFC+L back to 2008. Thus I would suggest that you contact ron at csidata.com and ask why Yahoo! only shows historical data back to 2015 when their historical data goes back to 2008 (don't waste your time contacting Yahoo!).

CSI will also sometimes extend their own historical data for a symbol back on request.

Robbie Geary
Print the post Back To Top
No. of Recommendations: 0
Thanks again Robbie.

Tom
Print the post Back To Top
No. of Recommendations: 0
Sorry to belabor this, but I've spent a good bit of time trying to figure out how to derive dividend adjusted historical prices from unadjusted closing prices. It seems as though this should be relatively straightforward, so I'm mystified at the moment. Either there's something wrong with my thought process, or Yahoo's adjusted historical closing prices are incorrect.

To illustrate the calculation problem...

Yahoo's unadjusted closing price for 1/2/15 is $1215.00 [per Randy's SMF function].

As of the 11/22/19 close, Yahoo's adjusted closing price for WFC-PL on 1/2/15 is $917.32 [as returned by both SMF and GTR1].

1215 - 917.32 = 297.68 [presumably the total of all intervening dividends]

There have been 19 quarterly dividends of $18.75 each between 1/2/15 and 11/22/19.

19 * 18.75 = 356.25

1215.00 - 356.25 = 858.75

I've been unable to explain the $58.57 difference between Yahoo's 917.32 and my calculated 858.75. Might it be some sort of inflation adjustment? Any help would be much appreciated.

Tom
Print the post Back To Top
No. of Recommendations: 4
To illustrate the calculation problem...

Yahoo's unadjusted closing price for 1/2/15 is $1215.00 [per Randy's SMF function].

As of the 11/22/19 close, Yahoo's adjusted closing price for WFC-PL on 1/2/15 is $917.32 [as returned by both SMF and GTR1].

1215 - 917.32 = 297.68 [presumably the total of all intervening dividends]

There have been 19 quarterly dividends of $18.75 each between 1/2/15 and 11/22/19.

19 * 18.75 = 356.25

1215.00 - 356.25 = 858.75

I've been unable to explain the $58.57 difference between Yahoo's 917.32 and my calculated 858.75. Might it be some sort of inflation adjustment? Any help would be much appreciated.


I think your problem is because the price adjustments are not additive. They are multiplicative.

The price on 1/2/15 was 1215. When the first dividend of 18.75 was paid on 1/15/15 the prior price was adjusted down by 18.75 to 1196.25. Now let's go forward to 4/15/15 when the next 18.75 dividend was paid, and let's assume that the price on that day had risen back to 1215. That's a dividend of 1.54321%. To make the adjustment to the 1/2/15 price, you don't subtract 18.75 from the price. You reduce the price by 1.54321% which gives you a new adjusted price of 1177.789, not 1196.25-18.75=1177.75. That difference grows over time.

Elan
Print the post Back To Top
No. of Recommendations: 5
I've been unable to explain the $58.57 difference between Yahoo's 917.32 and my calculated 858.75. Might it be some sort of inflation adjustment? Any help would be much appreciated.

You have to adjust WHEN the dividend is paid, for the price at the time the dividend was paid. That is, if a 10% dividend is paid, ALL HISTORICAL DATA needs to be reduced by 10%. An adjustment at any given point is not an additive accumulation of dividends paid since then, but a multiplicative accumulation of adjustments made since then.

Or, think of it this way -- if a $100 stock has paid out a $10 dividend for the last 20 years, would you adjust historical prices to be negative?

This is what the dividend adjustments look like:

   Date         Open      High       Low     Close Volume     Unadj Div Adj Split Adj Dividend
2019-08-29 $1,465.00 $1,475.00 $1,464.50 $1,468.00 13361 $1,468.00 1.0000 1.0000 $18.75
2019-05-30 $1,303.42 $1,311.75 $1,303.42 $1,311.75 5923 $1,328.59 0.9873 1.0000 $18.75
2019-02-27 $1,258.78 $1,259.51 $1,253.86 $1,253.86 13125 $1,288.00 0.9735 1.0000 $18.75
2018-11-29 $1,222.45 $1,222.45 $1,212.22 $1,212.22 35337 $1,263.33 0.9595 1.0000 $18.75
2018-08-30 $1,216.36 $1,223.71 $1,216.36 $1,223.71 7597 $1,294.17 0.9456 1.0000 $18.75
2018-05-30 $1,168.93 $1,171.92 $1,167.73 $1,170.53 11791 $1,256.00 0.9319 1.0000 $18.75
2018-02-27 $1,167.47 $1,167.47 $1,160.13 $1,166.10 22153 $1,270.00 0.9182 1.0000 $18.75
2017-11-29 $1,210.60 $1,216.03 $1,199.88 $1,206.25 11516 $1,333.20 0.9048 1.0000 $18.75
2017-08-29 $1,189.36 $1,195.59 $1,184.45 $1,195.59 7685 $1,339.98 0.8922 1.0000 $18.75
2017-05-26 $1,111.70 $1,116.40 $1,111.70 $1,113.93 4236 $1,266.00 0.8799 1.0000 $18.75
2017-02-24 $1,062.65 $1,063.77 $1,059.23 $1,060.38 6924 $1,223.07 0.8670 1.0000 $18.75
2016-11-28 $1,036.59 $1,040.85 $1,034.88 $1,039.51 7493 $1,217.42 0.8539 1.0000 $18.75
2016-08-29 $1,141.00 $1,141.00 $1,130.91 $1,139.73 4914 $1,355.50 0.8408 1.0000 $18.75
2016-05-26 $1,025.26 $1,025.89 $1,018.00 $1,019.26 13408 $1,229.02 0.8293 1.0000 $18.75
2016-02-25 $945.88 $950.04 $943.50 $949.79 12680 $1,162.69 0.8169 1.0000 $18.75
2015-11-25 $937.27 $939.47 $932.44 $937.26 7105 $1,166.00 0.8038 1.0000 $18.75
2015-08-27 $930.39 $930.83 $924.78 $928.03 13483 $1,173.11 0.7911 1.0000 $18.75
2015-05-27 $940.19 $942.05 $939.80 $942.05 9433 $1,209.89 0.7786 1.0000 $18.75
2015-02-25 $932.44 $935.64 $927.65 $935.06 4565 $1,219.67 0.7667 1.0000 $18.75
Print the post Back To Top
No. of Recommendations: 0
Many thanks Randy, Elan and Robbie for your excellent assistance.

Tom
Print the post Back To Top
No. of Recommendations: 0
Elan: The price on 1/2/15 was 1215. When the first dividend of 18.75 was paid on 1/15/15 the prior price was adjusted down by 18.75 to 1196.25. Now let's go forward to 4/15/15 when the next 18.75 dividend was paid, and let's assume that the price on that day had risen back to 1215. That's a dividend of 1.54321%. To make the adjustment to the 1/2/15 price, you don't subtract 18.75 from the price. You reduce the price by 1.54321% which gives you a new adjusted price of 1177.789

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Randy: You have to adjust WHEN the dividend is paid, for the price at the time the dividend was paid. That is, if a 10% dividend is paid, ALL HISTORICAL DATA needs to be reduced by 10%. An adjustment at any given point is not an additive accumulation of dividends paid since then, but a multiplicative accumulation of adjustments made since then.


I get that now with your help. However, I've still been unable to come up with an Excel formula that produces that result. It could be either a VB macro or an in-cell formula. I suspect the latter would be challenging without the aid of helper columns. When I multiply the cumulative dividend adjustments, I get something different than what's reported.

I apologize for my mathematical inadequacy, even while inviting your clarification of exactly how such a transformation might be accomplished.

Tom
Print the post Back To Top
No. of Recommendations: 4
However, I've still been unable to come up with an Excel formula that produces that result. It could be either a VB macro or an in-cell formula. I suspect the latter would be challenging without the aid of helper columns. When I multiply the cumulative dividend adjustments, I get something different than what's reported.

Looking at the last dividend:

   Date         Open      High       Low    Close* Adj Close** Volume
2019-08-29 $1,465.00 $1,475.00 $1,464.50 $1,468.00 $1,468.00 13,361
2019-08-29 $18.75 Dividend
2019-08-28 $1,464.00 $1,490.33 $1,464.00 $1,479.44 $1,460.69 10,391

Note that their adjusted close on 8/28 is $1,460.69. That's really just the actual close of $1,479.44 less the $18.75 dividend. To determine the adjustment factor, it's just:

$1,460.69 / $1,479.44 = 0.987326286

So all O/H/L/C prices prior to 8/29 need to be adjusted by that amount.

Now, looking at the next dividend:

   Date         Open      High       Low    Close* Adj Close** Volume
2019-05-30 $1,320.15 $1,328.59 $1,320.15 $1,328.59 $1,311.75 5,923
2019-05-30 $18.75 Dividend
2019-05-29 $1,337.43 $1,339.04 $1,334.99 $1,338.50 $1,303.02 17,873

Again, the adjustment factor for all historical data prior to 5/30 would be:

$1,303.02 / $1,338.50 = 0.973492716

Now, this is where the multiplicative effect would come in if you were doing in yourself. The first adjustment was:

(1479.44-18.75)/1479.44 = 0.987326286

The second adjustment is:

(1338.5-18.75)/1338.5 = 0.985991782

So, the cumulative adjustment is those two factors multiplied by each other:

((1479.44-18.75)/1479.44) * ((1338.5-18.75)/1338.5) = 0.973495604

But if you're getting the historical quotes from Yahoo, it's just easiest to take the ratio of their adjusted closing quotes to the actual closing quote and apply that ratio to the O/H/L/C for that day.

Now, you might complain that 0.973492716 is not 0.973495604, but that's a limitation of the dollars and cents displayed on the Yahoo presentation page. In their download file, the $1,303.02 adjusted closing price is actually $1,303.023804.
Print the post Back To Top
No. of Recommendations: 0
Muchas gracias, Randy, for your very clear description of a process that even I may be able to implement. FYI, this is a key piece of an important chart in my portfolio management system, and your excellent elucidation is very much appreciated. Thanks again for so diligently maintaining your essential SMF functions.

Tom
Print the post Back To Top
No. of Recommendations: 0
Randy wrote: Now, this is where the multiplicative effect would come in if you were doing in yourself. The first adjustment (8/28/19) was:

(1479.44-18.75)/1479.44 = 0.987326286

The second adjustment (5/29/19) is:

(1338.5-18.75)/1338.5 = 0.985991782

So, the cumulative adjustment is those two factors multiplied by each other:

((1479.44-18.75)/1479.44) * ((1338.5-18.75)/1338.5) = 0.973495604

But if you're getting the historical quotes from Yahoo, it's just easiest to take the ratio of their adjusted closing quotes to the actual closing quote and apply that ratio to the O/H/L/C for that day.


I've implemented the derivation of dividend adjusted closing prices from unadjusted closing prices using the info you provided. It tracks perfectly back to the 8/29/17 dividend nine quarters ago. From then on back to 2/24/15 Yahoo applies the adjustment a day earlier, introducing a progressively increasing difference. Unless there's been some sort of adjustment in the earlier methodology, there appears to be a systematic error in Yahoo's pricing. Any thoughts about that?

By the way, I failed to mention earlier that I have WFC-PL unadjusted closing prices back to the beginning of 2011 as part of my portfolio management system, and that's the reason for my interest in obtaining adjusted prices earlier than 2015.

Tom
Print the post Back To Top
No. of Recommendations: 0
Unless there's been some sort of adjustment in the earlier methodology, there appears to be a systematic error in Yahoo's pricing. Any thoughts about that?

Offhand, it wouldn't surprise me.

Do your unadjusted closing prices match theirs?
Print the post Back To Top
No. of Recommendations: 0
Going back to the beginning of 2015, aside from a handful of days with variances of a few cents—(usually $0.01), there's only one unadjusted closing price with a significant difference, and those could as well be Yahoo errors that I froze in when I copied the data from then-contemporaneous SMF retrieval formulas to values.

Tom
Print the post Back To Top
No. of Recommendations: 6
I've implemented the derivation of dividend adjusted closing prices from unadjusted closing prices using the info you provided. It tracks perfectly back to the 8/29/17 dividend nine quarters ago. From then on back to 2/24/15 Yahoo applies the adjustment a day earlier, introducing a progressively increasing difference.

There is a bigger problem here---comparing theoretical ideal performance to real-world occurrence. Yahoo and everybody else computes as if you reinvested the dividends at the price on the ex-dividend date. But even so....which price? Open price or close price?

But the real world doesn't work that way.
There is the ex-div date and there is the payment date. For example:
"The board of directors of AT&T Inc. (NYSE: T) today declared a quarterly dividend of $0.51 a share on the company’s common shares. The dividend is payable on May 1, 2019, to stockholders of record at the close of business on April 10, 2019."

Indeed, these dates are shown at a few places. (https://www.streetinsider.com/dividend_history.php?q=t) But not at the common places like Yahoo. Yahoo lists only "Ex-Dividend Date 2019-10-09".

AT&T (T) closed at 37.05 on 10/9/2019 and at 38.95 on 11/1/2019.

You could not have invested the dividend at the 37.05 price because you had not actually received the dividend yet. In fact, just because AT&T paid the dividend on 11/1 doesn't mean that you received it on 11/1. More likely you would have not actually received it until at least a couple of days later. So your actual re-investment price would have been more like $39.20 than $37.05.

For backtesting purposes, everybody just uses the ex-div date....because that's the only data we have. But don't think that the real world works that way. Don't fret too much if you can't get your computations to match us with Yahoo's data. They are *both* a fantasy.
Print the post Back To Top
No. of Recommendations: 17
RayVT is of course quite right that reinvesting dividends at the close of the ex-dividend date (i.e., the dividend reinvestment strategy I abbreviated as PXD in https://boards.fool.com/intro-to-event-partition-linearizati...) is practically impossible for a portfolio seeking 100% investment in non-marginable positions at all times, especially in retirement accounts where all positions are non-marginable. And even where margin is available, PXD assumes "free leverage" (as I called it in https://boards.fool.com/gtr1-trading-suspensions-20765833.as...) on the dividend amounts from ex-dividend date close to payment date close. However, when designing GTR1 linearization, I considered these mostly non-issues for reasons that I didn't go into in great detail in the otherwise very thorough posts (possibly the longest two posts in the history of the message board!) that I just linked to:

1. The impracticality only really exists with ordinary cash dividends. For special dividends and distributions of other stock, the payment date is almost always the entitlement date, a.k.a. the cum-distribution date, i.e., the market day before the ex-distribution date. Thus it is perfectly practical to re-invest a special dividend at the next day's close, or to sell a spin-off and reinvest the proceeds in the distributing stock at the next day's close.

2. What minor practical impossibilities there are (mainly "free leverage") in PXD as applied to ordinary cash dividends can be mitigated by assuming a 1-2% cash buffer is reserved in the portfolio at all times for PXD. While doing this would slightly reduce CAGR, it would also slightly reduce drawdowns and other risk metrics as well, and most importantly for those whose primary performance metric is Sharpe Ratio, adding a permanent cash buffer as a percentage of the portfolio value has, by design, zero impact on Sharpe Ratio, which is invariant under changes in assumed allocations between the underlying investment and cash or margin (subject to certain other assumptions, of course).


However, RayVT is wrong on a couple points:

1. The reason "we" use PXD with ordinary cash dividends is not that it's "the only data we have". In designing GTR1 linearization, I could have simulated reinvesting all ordinary cash dividends at the close of payment dates by making every ex-dividend date a "critical date" in the language of EPL. The reason I opted against this wasn't lack of data (I have declaration dates, record dates, ex-distribution dates and payment dates on all historical dividends), but that it would result in massively larger linearized databases while having negligible effects on backtests for the reasons just given.

2. Yahoo! Finance (or rather, their historical data source, Commodity Systems Inc.), does not use PXD (and has not used it for quite some time). The adjustment method detailed by Randy is a correct description of how CSI, Norgate, QuoteMedia, and virtually all other data sources besides the GTR1 backtester and most academic databases (e.g., CRSP and Compustat) adjust for distributions, but if you think about it carefully, it does not correspond to reinvestment of the distribution at the close of the ex-distribution date. Some of these sources actually claim to use "the CRSP method" of adjusting for distributions, but these claims are false.

So what reinvestment strategy does the CSI method underlying Yahoo!'s data assume? It turns out to be one that is far less practical than PXD. Furthermore, the impracticality of the reinvestment strategy impacts all distributions, not just ordinary cash dividends, and in the case of large special dividends, it can result in a significant distortion of returns. The net result is actually a measurable distortion of returns to the downside as a result of the asymmetry of leveraged daily stock returns.

The reinvestment strategy underlying the CSI method of adjustment (which is really everyone's except GTR1 and academia) is this: At the close of the entitlement date (i.e., market day before the ex-dividend date), purchase enough additional shares of the distributing security on margin (notionally on margin, if the portfolio has cash available) so that the entitled distributions, as valued on the entitlement date, exactly pay off the notional margin debt, keeping in mind that any shares purchased at the entitlement date close are also entitled to the same distributions. Furthermore, if the distributions include other stock, then short the entitled number of shares (using when-issued shares, if necessary) at the close of the entitlement date (again, keeping in mind that the additionally purchased shares in the distributing security are themselves entitled to the distribution of stock, and also that one is liable for any distributions to which the shorted shares are entitled) so that the short positions are exactly closed out on the ex-distribution date.

If that sounds complicated, it is, and because of that, I'll omit the proof that these assumptions are equivalent to the CSI method of adjustment. And if it sounds like a ridiculously convoluted and impractical method of actually reinvesting distributions, you're of course right. But as I've said, this is the "industry norm" outside of academia and the GTR1 backtester (and possibly the Portfolio123 backtester also, given that it uses Compustat data, though of course Portfolio123 is free to apply their own linearization method, and they may well go with the "industry norm").


The CSI Method and Return Distortion

To demonstrate the kind of distortion that the CSI method entails, suppose that a stock trading around $20 per share is to pay out a $10 special dividend as a first step in liquidation (not an uncommon scenario) or buy-out. Assume the stock closes at exactly $20 at the close of the entitlement date (which is identical to the payment date for a dividend this large) of 11/25/2019, and the next day (the ex-distribution date), the stock closes at $8.

Using PXD (the GTR1/academic method), the total return on the ex-dividend date is simply ([$8 value of 1 remaining share at the close of 11/26/2019] + [$10 dividend available for reinvestment at the close of 11/26/2019])/[$20 value of 1 share held on 11/25/2019] = 18/20 = 0.9, i.e., -10%.

Using the CSI ("industry norm") method, the total return on the ex-dividend date is [$8 value of 1 remaining share at the close of 11/26/2019]/([$20 value of 1 share held on 11/25/2019] - [$10 dividend]) = 8/10 = 0.8, i.e., -20%.

Why is the loss on the ex-dividend date twice as bad using the CSI adjustment method versus the PXD method? Simply put, because the position went into the ex-distribution date 2x leveraged using the implied CSI reinvestment method.

Of course, the implied leverage of the CSI method can also distort returns to the upside. However, there are two critical points: First, for reasons that I won't go into unless there is interest, "the market's" recognition of returns more closely matches PXD assumptions than those of the CSI adjustment method, and second, leveraged returns are asymmetrical on a compounding basis.

For example, if the same stock distributed another special dividend of $4 per share with entitlement date/payment date 11/26/2019, but this time the stock closed up (on an adjusted basis) on the ex-dividend date at price of $4.8889, then PXD produces a total return of (4 + 4.8889)/8 ~= 11.11%, which by design, cancels out the previous day's loss. However, by the CSI method, the investor is still in the hole after the gain on 11/26/2019: The return on the second ex-dividend date is only 4.8889/(8 - 4) ~= 22.22%, which is insufficient to compensate for the -20% loss on a compounded basis.

A hypothetical example wasn't even necessary. The issue of the CSI method distorting returns has come up on this message board before when screen picks have made large special dividends (for example, see https://boards.fool.com/pip-squeak-32566361.aspx?sort=whole#...).


So, why is the CSI method the "industry norm" when it so distortionary?

The CSI adjustment method is universally applied outside of academia because it has one very appealing property for traders and real-time data consumers in general: Throughout the ex-dividend date (and in fact, from the moment the market closes on the entitlement date), the adjusted closing price of the distributing security on the entitlement date is fixed. That is, the "previous close" at 3pm on the ex-dividend date is the same as it was at 11am, or at any other time of the day. By contrast, PXD has the peculiar (and undesirable, for traders) property that the adjusted previous closing price for the distributing security fluctuates throughout the ex-dividend date and doesn't settle down until the market closes on the ex-dividend date. This fluctuation would cause massive confusion if PXD were used in real-time stock quotes on brokerage websites, in trading software, etc, so for that reason alone, PXD is completely impractical, in spite of it actually being more realistic for both backtesting and market valuation.


Robbie Geary
Print the post Back To Top
No. of Recommendations: 0
There is a bigger problem here---comparing theoretical ideal performance to real-world occurrence. Yahoo and everybody else computes as if you reinvested the dividends at the price on the ex-dividend date. But even so....which price? Open price or close price?

I'd agree it's impractical relative to real-world performance, because of the gap between when you are entitled to the dividend versus when you can reinvest the dividend. That could be a significant difference.

However, the adjustment in stock price has to be based on the closing price just prior to the ex-dividend date, because that is when the devaluation of stock price occurs.

Just look at an extreme example -- suppose a $100 stock is paying a $50 dividend. The person who owns the stock at the end of the day is the one that gets the $50 dividend. No prices before the close or after the close matter. The next day, barring any other changes, the stock is only worth $50, because the owner of the stock has a $50 stock and will get a $50 cash dividend (at some point in the future).
Print the post Back To Top
No. of Recommendations: 2
Interesting thread-
How big of a problem is this when
using an EFT such as XLK SPY DIA
which can contain dozens/hundreds of stocks?

I would suppose that compared to an
individual dividend paying stock, using
the adjusted value of and EFT from Yahoo
that adjusts for splits and dividends would be
okay. YES/NO?
Print the post Back To Top
No. of Recommendations: 5
Responding to rgearyiii & rharmelink:

1) No question that the stock price & value goes down by the amount of the dividend on the ex-date.

2) The point I wanted to bring to attention is that the price you get on the reinvestment of the dividends is *not* the price on the ex-dividend date. You get the price of a few days later.

3) No method of trying to take this into account is accurate. Or even can be accurate. All the complexities of PXD or CSI or whatever are just attempts to reduce some of the inaccuracies. But it is a futile attempt. First, historical data of payment dates is shaky. Heck, even accurate data for dividend amounts and dates older than the last 20 or so years is often difficult to track down. I remember going to the Schaumburg library in the 1990's digging through the archives for the Dow 30 stocks, when Timberfool was working on validating the Foolish Four strategy. And approximately NOBODY was tracking payment dates, just ex-dividend dates.
Th ex-div date is important because that's when the money belongs to you. The payment date is not as important, because that's just the date when the money happens to get into your hand. Actually, not even in your hand.....it's when the check gets put in the mail.

4) Even when the money appears in your brokerage account, for the broker to do their reinvestment program for you, the exact timing of when they make the purchase is unknown. They may sit on it for minutes, hours, or days.

4) It doesn't matter. This is a case where "we don't have to be perfect, we just need to be good enough." We cannot know the historical reinvestment prices accurately, and a complicated scheme to try to compute it doesn't change that fact.

4a) It is what it is.

5) A simple method of adding a small slippage factor to the dividend reinvestment would arguably be as valid as these other complex approaches.
Print the post Back To Top
No. of Recommendations: 7
4) It doesn't matter. This is a case where "we don't have to be perfect, we just need to be good enough." We cannot know the historical reinvestment prices accurately, and a complicated scheme to try to compute it doesn't change that fact.

Right. This thread began with a puzzle about why the adjustment for regular cash dividends of WFC-PL drifted significantly from Yahoo's calculation over time. The author wanted to figure out what he was doing wrong, and rightly so. None of us want systemic errors to distort our calculations to any significant degree.

The subsequent discussion has drifted into arcane considerations that in the regular course make differences of small fractions of 1% in price adjustments. I acknowledge and admire Robbie's deep understanding and penchant for accuracy. We all have relied on him to provide an authoritative resource for all our backtesting.

However, in this instance it seems to me that the discussion has drifted into something akin to the question of how many angels can dance on the head of a pin. :-)

Elan
Print the post Back To Top
No. of Recommendations: 2
<< ....the discussion has drifted into something akin to the question of how many angels can dance on the head of a pin. :-) >>

A) it depends on the size of their boots

B) it depends on how many pieces are in the band.


Alan
Print the post Back To Top
No. of Recommendations: 1
A simple method of adding a small slippage factor to the dividend reinvestment would arguably be as valid as these other complex approaches.

Depends on the tendency of the stock post-ex-dividend date.

I knew a guy that had a very successful system buying dividend stocks (weeks) prior to the ex-dividend date and then selling them when he had a 3% profit. I got banned from his group because I criticized him for claiming a 3% profit per month because everything he sold that month had a 3% gain (even if something had been held for several quarters). However, when I examined his multi-year log of 595 trades, he was actually getting a CAGR on all of those trades of about 34%.

I always had to be careful with TGT when I did covered calls based on ex-dividend dates, because they usually reported quarterly earnings a few days after the ex-dividend date. So at time the ex-dividend date and earnings date could both occur prior to the option expiration date. Sometimes good. Sometimes bad.
Print the post Back To Top
No. of Recommendations: 11
RayVT:
2) The point I wanted to bring to attention is that the price you get on the reinvestment of the dividends is *not* the price on the ex-dividend date. You get the price of a few days later.

Unless you reinvest the dividend on the ex-dividend date instead of a few days later--then you get a price on the ex-dividend date, and not a price from a few days later :-D

And before you fire off your next reply about what a "fantasy" that is, re-read my last post carefully. Note that for special dividends--the only dividends that are (1) large enough for cash management to be anything close to a significant consideration and (2) large enough for this discussion to concern anything other than tiny fractions of a percent of one's portfolio, the dividend is paid on the entitlement date (or more precisely, exchange rules require the ex-dividend date to be the day after the payment date), thus appearing in one's account no later than the morning of the ex-dividend date. It is therefore completely reasonable for backtests to assume these are reinvested at the close of the ex-dividend date.

As for ordinary cash dividends, even there I would argue that reinvestment at the close of the ex-dividend date is at least a reasonable assumption. It would take a lot of diligence and careful cash management planning to keep track of dividends as they are announced and have the cash ready to reinvest on the ex-dividend dates, but it could be approximated fairly well. Just because you, personally, enroll in DRIP plans, which makes this impossible, does not make it an unreasonable assumption in backtests. But, to the extent that this is impractical, I agree with Elan: It is inconsequential due to the relative size of the amounts involved.

If you are still going to argue that this assumption amounts to "fantasy" because it's not what you personally do, then I would simply ask, then what backtest isn't a fantasy? Have you always traded precisely at open or close prices? Do you always rebalance at exactly the same interval? Philosophically, it seems you are entering the same radical subjectivist quagmire that tpoto is in when he argues that the only backtests that are relevant are those that assume trading on precisely the same dates on which he personally has traded.


3) No method of trying to take [dividend reinvestment] into account is accurate. Or even can be accurate.

By this standard, no backtest is accurate, or even can be accurate.


First, historical data of payment dates is shaky. Heck, even accurate data for dividend amounts and dates older than the last 20 or so years is often difficult to track down. I remember going to the Schaumburg library in the 1990's digging through the archives for the Dow 30 stocks, when Timberfool was working on validating the Foolish Four strategy. And approximately NOBODY was tracking payment dates, just ex-dividend dates.

This is clearly coming straight out of your ass, and doesn't warrant a reply, but I quote it as an another example of the "radical subjectivism" I referred to above. Just because you, personally, cannot find data, does not make the data "shaky", less real, or hard for others to obtain.


The ex-div date is important because that's when the money belongs to you. The payment date is not as important, because that's just the date when the money happens to get into your hand.

Again, given the provenance of your statements, this one doesn't warrant a reply either, except that I feel compelled to point out for everyone else's benefit that the IRS deems dividends to be paid, and thus taxable, as of the payment date, not the ex-dividend date, which is irrelevant. (There are, however, exceptions for some special end-of-year distributions entitled on the last day of the year, paid by some ETFs. If in doubt, simply check your brokerage 1099 statement.) This has been the case since the resumption of dividend taxation in 1950s, and was also the case during the prior period of dividend taxation during the Great Depression. So if you're completing your tax returns according to the RayVT doctrine, you're doing them wrong. And contrary to what he states, there is nothing fuzzy about dividend payment dates and they are not at all difficult to find (I scrape dividend data daily from six high-quality sources, and all six sources give the payment dates). The Wall Street Journal and New York Times have been publishing daily declared dividends, including payment dates, since at least the 1920s. Better sources, like Mergent (which has been the gold standard for over a century), have historical dividend data going back even further.

Whether it makes sense to tax dividends as of their payment dates rather than their entitlement dates, or even whether they should be taxed at all, is of course another subject.


elann:
The subsequent discussion has drifted into arcane considerations that in the regular course make differences of small fractions of 1% in price adjustments.

This is true for the vast majority of dividends, but not for special dividends, where the adjustment method can make a huge difference. I gave an example of this in my post, which you must have glossed over. I also linked to a thread on this forum where a large special dividend was discussed because of the wild distortion that CSI's adjustment method was producing in Yahoo!'s prices. And there is nothing "arcane" about what causes this distortion: It arises from the fact that

 A + C      A
------- != --- (Unless C = 0 or A = B).
B + C B

In words, ratios aren't preserved by addition of constants (except for the constant zero, or the ratio 1).

The correct total return on the ex-dividend date for a dividend reinvested at the close of the ex-dividend date is

 Px + D
--------
Pe

where Px is the price of the stock on the ex-dividend date and Pe is the price of the stock on the entitlement date. However, according to the way CSI/Yahoo! adjusts prices for dividends, the total return on the ex-dividend date comes out to

   Px
--------
Pe - D

This is not the same: Subtracting D from the numerator and denominator does not produce the same ratio. When D is small relative to Px and Pe, or when the ratios are close to 1, they are close, but for large special dividends, they can be wildly different.

While there is nothing wrong, in theory, with the way CSI/Yahoo! adjust for dividends (as RayVT states, there is no single correct way), the only way to arrive at their resulting total returns on ex-dividend dates is to assume "leveraging out" each dividend with a purchase of more stock on margin at the close of the entitlement date. For large special dividends, I do not consider this to be a "reasonable assumption" for backtests, so instead, GTR1 linearization uses the correct adjustments that correspond to reinvestment at the close of the ex-dividend date ("PXD").

Twenty years ago, discussions with this level of mathematical detail weren't considered "arcane", and were in fact quite common. What has changed? It could be related to this: https://www.sciencedaily.com/releases/2019/10/191029131506.h... (Binghamton University. "Early retirement can accelerate cognitive decline." ScienceDaily. ScienceDaily, 29 October 2019.). This is, after all, a message board dedicated to retiring early, and some here have done just that (e.g., RayVT).


Robbie Geary
Print the post Back To Top
No. of Recommendations: 4

A + C A
------- != --- (Unless C = 0 or A = B).
B + C B



In words, ratios aren't preserved by addition of constants (except for the constant zero, or the ratio 1).


There was a physics instructor at a place we called "no-cal tech" who used to make deliberate errors in his blackboard (they were green) presentations. In an equation where 16/64 appeared, he crossed out the 6 in the numerator and the 6 in the denominator because they were "common factors" and went on to get the correct answer. It would not work for most other fractions. He did things like that whenever he could.
Print the post Back To Top
No. of Recommendations: 0
elann:
The subsequent discussion has drifted into arcane considerations that in the regular course make differences of small fractions of 1% in price adjustments.

This is true for the vast majority of dividends, but not for special dividends, where the adjustment method can make a huge difference. I gave an example of this in my post, which you must have glossed over.


Fair enough. I may have glossed over, but not intentionally. Simply, large special dividends are very rare in my personal experience.

Twenty years ago, discussions with this level of mathematical detail weren't considered "arcane", and were in fact quite common. What has changed? It could be related to this: https://www.sciencedaily.com/releases/2019/10/191029131506.h...... (Binghamton University. "Early retirement can accelerate cognitive decline." ScienceDaily. ScienceDaily, 29 October 2019.). This is, after all, a message board dedicated to retiring early, and some here have done just that (e.g., RayVT).

Hey, I resemble that remark! Been retired about 18 years. I dread to think what happens after 20. :-)

Elan
Print the post Back To Top
No. of Recommendations: 5
Been retired about 18 years. I dread to think what happens after 20. :-)

Don't worry, it's OK.
I hit 20 years about 3 weeks ago.
(Ah, the internet bubble. Good times)

As for the risk of cognitive decline, well, I read something about that once. Must be around here somewhere.

Jim
Print the post Back To Top
No. of Recommendations: 0
The subsequent discussion has drifted into arcane considerations that in the regular course make differences of small fractions of 1% in price adjustments.
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
This is true for the vast majority of dividends, but not for special dividends, where the adjustment method can make a huge difference. I gave an example of this in my post, which you must have glossed over.


Actually, if my calculations of regular dividends are correct, and I've seen nothing to suggest otherwise, Yahoo's error reaches 1.48% over the five years since the beginning of 2015. That strikes me as significant, even aside from special dividends.

Tom
Print the post Back To Top
No. of Recommendations: 2
"Early retirement can accelerate cognitive decline."
Elan: Hey, I resemble that remark! Been retired about 18 years. I dread to think what happens after 20. :-)


Ouch, I too resemble that remark, by 19 years.
It is especially bothersome because I reside in a community made up largely of retired professors and
other professionals. Many of us aren’t playing quite as fast as we were 20 years ago but what is
unnerving is there are a significant number that are declining much faster than typical. Neighbor who an
engineer who published several internationally recognized papers now hard to have a conversation
with. Better to make the most of what time we have left.

RAM
Print the post Back To Top
No. of Recommendations: 1
make differences of small fractions of 1% in price adjustments.

Actually, if my calculations of regular dividends are correct, and I've seen nothing to suggest otherwise, Yahoo's error reaches 1.48% over the five years since the beginning of 2015. That strikes me as significant


That's about 0.3% a year, a small fraction of 1%. Considering that Yahoo's errors are systemic - going in the same direction for every dividend, the difference when comparing various investments tends to be even smaller. IMO that's insignificant because it is unlikely to affect any of my investment decisions.

Elan
Print the post Back To Top
No. of Recommendations: 0

As for the risk of cognitive decline, well, I read something about that once. Must be around here somewhere.


When my wife tells me that I can't hear and need to get a hearing aid, I always answer in a loud voice, "What??" Drives her crazy. Especially when we are in the checkout line at the grocery store.

13 1/2 years here since I retired. I still like to tell people that when I got laid off that I had to walk myself out the door...because none of the managers wanted to come in on the day before a holiday to walk me out. I stuck my badge and key card under my group leader's keyboard.
Print the post Back To Top