Notebook

Reconciling Differences Between Q and Yahoo Adjusted Pricing Data For AAPL

Using 2014-02-01 as a perspective date, the Q get_pricing API is reporting the close price of AAPL to have been \$553.23 (this was prior to the 1-for-7 split in June 2014). Note that this price is not adjusted for any splits or dividends, it's the price as it was on 2014-01-02. This is because there were no splits or dividends for AAPL between 2014-01-01 and 2014-02-01.

In [1]:
aapl_pricing_2014 = get_pricing(
    symbols('aapl'),
    start_date='2014-01-01',
    end_date='2014-02-01',
)
In [2]:
aapl_pricing_2014.loc['2014-01-02'].price
Out[2]:
553.23000000000002

If we adjust the above price for the split that occured 7 months later, we get a split adjusted price price of \$79.03, which is very close to the \\$79.02 reported by Yahoo and Nasdaq (make sure to choose a timeseries of 6 years or more to see back far enough). Note that this is not adjusted for dividends.

In [3]:
# Divide by 7 to adjust for 1-for-7 split.
aapl_pricing_2014.loc['2014-01-02'].price / 7
Out[3]:
79.032857142857139

If we switch to use today as our perspective date, the Q get_pricing API returns a price that is adjusted for all splits and dividends that occurred between 2014-01-01 and today. In this case, the AAPL close price for 2014-01-02 returned by get_pricing is \$71.606.

In [4]:
aapl_pricing_today = get_pricing(
    symbols('aapl'),
    start_date='2014-01-01',
    end_date='2019-06-24',
)
In [5]:
aapl_pricing_today.loc['2014-01-02'].price
Out[5]:
71.605999999999995

To see how the above price was derived, we can get the previous day's close on each dividend ex-date for all AAPL dividends since 2014-01-01. The pipeline below gets us all previous day's close prices since 2014-01-01.

In [6]:
from quantopian.pipeline import Pipeline
from quantopian.pipeline.data import EquityPricing
from quantopian.research import run_pipeline

pipe = Pipeline({'yesterday_close': EquityPricing.close.latest})

df = run_pipeline(pipe, '2014-01-01', '2019-06-24')

Pipeline Execution Time: 7.37 Seconds

We can then compute a dividend adjustment ratio using the method that Yahoo describes here. To compute the adjustment, we can use the dividends that Yahoo reported for AAPL here.

In [7]:
# AAPL dividends since 2014-01-01, as reported by Yahoo.
divs = [
    (0.77,'2019-05-10'),
    (0.73,'2019-02-08'),
    (0.73,'2018-11-08'),
    (0.73,'2018-08-10'),
    (0.73,'2018-05-11'),
    (0.63,'2018-02-09'),
    (0.63,'2017-11-10'),
    (0.63,'2017-08-10'),
    (0.63,'2017-05-11'),
    (0.57,'2017-02-09'),
    (0.57,'2016-11-03'),
    (0.57,'2016-08-04'),
    (0.57,'2016-05-05'),
    (0.52,'2016-02-04'),
    (0.52,'2015-11-05'),
    (0.52,'2015-08-06'),
    (0.52,'2015-05-07'),
    (0.47,'2015-02-05'),
    (0.47,'2014-11-06'),
    (0.47,'2014-08-07'),
    (3.29,'2014-05-08'),
    (3.05,'2014-02-06'),
]

The below cell computes the dividen adjustment ratio to be applied to our 2014-01-02 raw AAPL price.

In [8]:
ratios = []

for div in divs:
    div_amount = div[0]
    date = div[1]
    share_price = df.xs(24, level=1).loc[date].yesterday_close
    adjustment_ratio = 1 - (div_amount / share_price)
    ratios.append(adjustment_ratio)
In [9]:
div_adjustment_ratio = reduce((lambda x, y: x * y), ratios)
print(div_adjustment_ratio)
0.905600947992

Our split adjustment ratio is 1/7.

In [10]:
split_adjustment_ratio = float(1)/7
print(split_adjustment_ratio)
0.142857142857

From the first code cell in this notebook, we found that the raw (as traded) price for AAPL on 2014-01-02 was \$553.23.

In [11]:
raw_price_2014_01_02 = 553.23

We can compute the adjusted AAPL price from 2014-01-02 adjusted for all dividends and splits between 2014 and today by multiplying our adjustment ratios by the raw price from that day. Doing this results in an adjusted price of \$71.57, which is very close to the Q adjusted price of \$71.61, but very different from the Yahoo adjusted price of \\$66.71.

In [12]:
adjusted_price_2014_01_02 = raw_price_2014_01_02 * split_adjustment_ratio * div_adjustment_ratio
print("Adjusted AAPL price for 2014-01-02 from today's perspective: %f" % adjusted_price_2014_01_02)
Adjusted AAPL price for 2014-01-02 from today's perspective: 71.572230

On a hunch, I tested a theory as to why Yahoo's adjusted price is different. My guess was that Yahoo is calculating the wrong dividend adjustment ratio for dividends that were paid out to AAPL shareholders prior to the 1-for-7 split.

In the divs list above, you can see that the two dividends paid in 2014 prior to the split even (occurred in June 2014) were significantly larger than the rest. In fact, they were roughly 7 times larger than other dividends. The 2 dividend payments greater than \$3 were paid out when the AAPL stock price was greater than \\$500, so the % of the stock price was roughly the same, but the actual dollar value of the dividend was higher. To get an appropriate dividend adjustment ratio, the \$3.29 and \\$3.05 dividends should have been divided by the price as it was at the time, which was \$500. However, it looks like Yahoo is instead computing the dividend adjustment ratio by dividing the dividend amount by the split adjusted ratio. To verify this, we can just multiply the last two dividends by 7 or divide the raw price we used in the adjustment calculation by 7. Below, we multiply the two dividends by 7 because it is a little easier to follow in the code.

In [13]:
# AAPL dividends since 2014-01-01, assuming incorrect dividend adjustment calculation from Yahoo.
divs_yahoo_method = [
    (0.77,'2019-05-10'),
    (0.73,'2019-02-08'),
    (0.73,'2018-11-08'),
    (0.73,'2018-08-10'),
    (0.73,'2018-05-11'),
    (0.63,'2018-02-09'),
    (0.63,'2017-11-10'),
    (0.63,'2017-08-10'),
    (0.63,'2017-05-11'),
    (0.57,'2017-02-09'),
    (0.57,'2016-11-03'),
    (0.57,'2016-08-04'),
    (0.57,'2016-05-05'),
    (0.52,'2016-02-04'),
    (0.52,'2015-11-05'),
    (0.52,'2015-08-06'),
    (0.52,'2015-05-07'),
    (0.47,'2015-02-05'),
    (0.47,'2014-11-06'),
    (0.47,'2014-08-07'),
    # Note that the two dividends listed below are multiplied by 7
    # to see how it affects the adjusted price.
    (3.29*7,'2014-05-08'),
    (3.05*7,'2014-02-06'),
]

In the following cells, we go through the same adjustment process as earlier, but the dividend adjustment ratio is different due to the change we made to the oldest two dividends in our list.

In [14]:
ratios_yahoo_method = []

for div in divs_yahoo_method:
    div_amount = div[0]
    date = div[1]
    share_price = df.xs(24, level=1).loc[date].yesterday_close
    adjustment_ratio = 1 - (div_amount / share_price)
    ratios_yahoo_method.append(adjustment_ratio)
In [15]:
div_adjustment_ratio_yahoo_method = reduce((lambda x, y: x * y), ratios_yahoo_method)
print(div_adjustment_ratio_yahoo_method)
0.843465999619

And when we apply this ratio, we get an split and dividend adjusted price of \$66.66, which is very close to the Yahoo reported value of \$66.71.

In [16]:
adjusted_price_2014_01_02 = raw_price_2014_01_02 * split_adjustment_ratio * div_adjustment_ratio_yahoo_method
print("Adjusted AAPL price for 2014-01-02 (Yahoo method): %f" % adjusted_price_2014_01_02)
Adjusted AAPL price for 2014-01-02 (Yahoo method): 66.661528

My interpretation of this result is that there is a bug in the way that Yahoo computes its split and dividend adjusted prices.