Yahoo Data: MRO and NOC split adjustments not applied
Author: coppola
Creation Date: 9/9/2014 8:08 PM
profile picture

coppola

#1
I have seen, that the yahoo data seems not right for some stocks.
for example for the stocks mro and noc, a split adjustment is not made!
mro for example had a split on july 1, 2011.

i activated in WL in the data manager the split adjusting. to be sure i also deleted the stock data in WL and made a fresh update again. but the data is always wrong ...


so the questions are:
1) whats is the particular problem with these stocks?
2) how is overall the data quality of yahoo? is maybe iqfeed a more reliable possibility?

thanks
profile picture

Eugene

#2
1) The problem with MRO and NOC comes from the data: it is inconsistent with the way Yahoo! manages splits. Upon having taken a look in the raw data, you'll notice that it's not being presented as expected.

Here's a normal split:

SBUX @ 2006

And here's MRO, for example:

MRO @ 2011

When you see a split like this, most always it's not a simple stock split and there may be other corporate action and spinoff involved. According to this article, there seems to be a spinoff in MRO in 2011. More of the similar stories:

Data: VOD (vodafone)
Verizon VZ Manual Split Correction required (pre-"Ignore selected splits" procedure)

Since then, a new feature was integrated in the Yahoo! provider: "Ignore selected splits". See how-to in the Wealth-Lab User Guide > Data > Data Manager > Create New DataSet > Data Providers > Yahoo! > Ignore Selected Splits.


2) Yahoo and IQFeed both have good quality but not top quality. And if there's demand, there will be supply.
profile picture

coppola

#3
thanks for the reply!

the yahoo data in WL for MRO is wrong, also when considering your spinoff point:

in WL, on the split/spinoff date of the 6/30/2011 the closing price is 253.52!
on the next day, the closing price is (in WL) 32.95.

in Yahoo(website!), on the 6/30/2011 the price is split adjusted(!) 52.68. this price is the right one because its this 16/77 stock split:
((253.52 /77) * 16)
the price on the next day matches the one with WL.

the spinoff explains only the drop from 52.68 to 32.95.

so, the WL data seems just wrong, not split adjusted.

also interesting is, that the historical prices on the yahoo website looked fine. so you either get your yahoo data into WL from another yahoo source, or something within WL is wrong?!

so the questions are then
1) what is really wrong with this MRO case?
2) what is a top quality data provider(stocks eod), which can be used now in WL?

thanks
profile picture

Eugene

#4
Add the MRO split on 07/01/2011 (not 6/30/2011) to the list of selected splits and the WL chart will match Yahoo's table.
profile picture

Cone

#5
Whenever you see a $0.00 Y! Dividend in conjunction with a split, a spinoff or some M&A activity has taken place. In this case, Marathon Oil Corp. spun off Marathon Petroleum Corp. (NYSE: MPC). When a spinoff occurs, you'll never get a contiguous transition in the data unless you backadjust it yourself.

Wealth-Lab is displaying Y! Data correctly. As Eugene pointed out, it's Y!'s inconsistent manner of managing these cases. You have to fix it yourself by using the "Ignore Split feature" that we added to the Yahoo! Provider. Then you can apply a Split correction in the following manner:

1. Enter the ex-date of the spin-off in "Ignore Splits"
2. Reload the chart
3. Write down close of the day before the ex-date of the corp action (6/30/2011): 52.68
4. Write down the open of the ex-date (7/1/2011): 33.28
5. Enter the split using integer values of the prices above on the day before the ex-date: 5268 : 3228

That's approximate, but the results for backtesting will be far better than not having done it.

Click for a list of some more recent spinoffs (and likely places for "cliffs" in your provider's data)
profile picture

Eugene

#6
Just to expand on Cone's recent spinoffs link: we have it in WL --

Upcoming Spinoffs
profile picture

coppola

#7
thanks for your replies, eugene and cone!

as i mentioned in detail in my last post, the spinoff problem is not my primary concern!
my main issue is, that the data before (!!) the split/spinoff is not spit adjusted in WL.
the raw prices in WL should reflect the split adjusted prices on the yahoo website (and other providers) exactly.
this seems not the case...
if this is true, and there are many such cases, yahoo in WL would be not very useful to me.


regards
profile picture

Cone

#8
In Wealth-Lab 4 , the Yahoo! Provider ignored the split and dividend values and used the "Adj Close" as the Closing price and applied the ratio between the Adj Close and Close to the rest of the data fields to build the bars. Although that takes care of this one instance of a necessary adjustment, the problems with that method were:

1. Forced to always use dividend-adjusted data. If you've actually done this, you'll understand why it's not desired for live trading.

2. Every time a new split or dividend occurs, the entire data set had to be reloaded; but it wasn't done automatically.

3. Loss of precision. Adj Close values are accurate to 2 decimals only, which is basically inaccurate after the first split or dividend adjustment.

4. Yahoo! doesn't adjust for dividends correctly. Being CSI data, they use the formula (1+dividend/close), which keeps percentages unaltered, but the first adjustment is not equal to the dividend amount. Our provider uses (1-dividend/close), which also keeps percentages unaltered and the first adjustment is correct too

5. Further, the Adj Close isn't guaranteed to give you a smooth transition after a spin off because the corp actions are never the same. Take for example the EXPE spinoff to TRIP on 12/21/2011. Here's the link (click) There's a $13 difference in the Adj Close from one day to the next.

QUOTE:
the raw prices in WL should reflect the split adjusted prices on the yahoo website (and other providers) exactly.
Be careful what you ask for. Imho, except for spinoffs (where both providers have shortcomings) WL's Yahoo! Provider gives a more accurate representation of prices than Yahoo! does themselves, and, WL provides more options to work with the data the way you want.
profile picture

coppola

#9
hi cone,

so to be really clear, let me ask you then these further questions:

1) since i have seen split adjusted prices in WL (for example aapl), it looks, that WL does not split adjust only for selected stocks.
i further assume then, that this handling(no adjustments) is then only the case for stocks, which have other corporate actions (like spinoff etc) at the split date.
is this true??

2) is this handling of the yahoo splits also happening in WL to other data feeds, for example iqfeed?

if it is really true, that this split handling is only done for stocks with additional corporate actions (see 1)), then some of your points make sense to me.
the main point there would be that these corporate actions could change the stock price dramatically (for example at a spinoff) and an adjustment has to be done in WL to reflect a reasonable price transition.


thanks

profile picture

Cone

#10
Is it true? Not the way you stated it.

WL's Y! Provider will adjust for the splits that are identified in Yahoo! data tables if:
a. "Perform Split Adjustment" is enabled in the Data Mgr's Yahoo! Data tab, and,
b. The symbol and date for a split is not identified in the "Ignore Selected Splits" table. (This is a relatively new feature of the provider, and we have preloaded only a few splits to be ignored.)

My main points are these:

1. Wealth-Lab is not a data provider. However, for your convenience, we have created many adapters for providers of data that you may choose to use (or not).

2. Yahoo! Finance (or any of the other free providers that I can think of) do not provide a consistent indication that would enable a data adapter to properly back adjust for M&A activity.

3. You can back-adjust [Yahoo! data] yourself after one of these events using the method I outlined above (post #5). If you need to be more accurate with the split factor, then you must research the details of the transaction in the SEC filings or elsewhere. That's probably not worth the effort since data for backtesting doesn't have to be perfect - it just needs to be representative of a market's dynamics. Removing "cliffs and improbable spikes" is definitely worth the effort.
profile picture

coppola

#11
hi,

QUOTE:
WL's Y! Provider will adjust for the splits that are identified in Yahoo! data tables if:
a. "Perform Split Adjustment" is enabled in the Data Mgr's Yahoo! Data tab, and,
b. The symbol and date for a split is not identified in the "Ignore Selected Splits" table. (This is a relatively new feature of the provider, and we have preloaded only a few splits to be ignored.)

really? I have activated split adjustment and in the ignored list, no change was done (still only AXP and VZ are there). why is then MRO not split adjusted????
profile picture

Eugene

#12
QUOTE:
why is then MRO not split adjusted????

Explained in my reply #2.
profile picture

Cone

#13
For me, the [reverse] 16:77 split is applied and the close of 6/30/2011 is 253.52.

Remember too that this is supposed to be static data. If you've entered the split in "Ignore" and then removed it, you may have to close the chart or even restart Wealth-Lab to see the result due to data caching. If you still don't see this split after a restart, then Reload the data. You may have manipulated the data in the past, maybe applying a manual split.
profile picture

coppola

#14
thanks for the replies.

so the handling of yahoo prices in WL seems to me:

1) you take their historical prices
2) when a split, occurs, you calculate (when 'perform split adjustment' is selected in the data manager) the split adjusted prices (according to the split factor) for the split date and the previous prices.
for example in the MRO case, the calculation for this reverse split at 6/30/2011 was 52.68 / 16 * 77 = 253.52.

and this seems fine to me.

thanks
coppola