Query historical pricing data for AAPL

Hi Niccola,

I just spent some time poking around the Yahoo Finance GUI for AAPL pricing (here) and from what I can see, it looks like the price series in Yahoo Finance isn't being adjusted for dividends. It's totally possible that this is a setting that you can change in Yahoo Finance, but I haven't been able to figure out how to get it to adjust for dividends.

On Quantopian, the prices function returns pricing data that is adjusted for corporate actions as of the end_date argument. In the example above, the prices you are displaying from the Q API are being adjusted for dividends and splits that had occurred prior to 2016-01-01. For the Yahoo-sourced prices, I don't believe dividend adjustments are being applied. Another piece of supporting evidence that seems to corroborate this guess is the fact that pricing from yesterday from Yahoo Finance is within 6 cents of the price that we have for AAPL, which can be explained by the last trade price vs settlement price difference.

Does this make sense?

In the yahoo finance historical data section of the quote page there is a column called adj close that is adjusted for both dividends and splits.
**Adjusted close price adjusted for both dividends and splits. e.g
https://finance.yahoo.com/quote/AAPL/history?period1=1385884800&period2=1391241600&interval=1d&filter=history&frequency=1d

No, that does not make sense to me because the adj close is adjusted for both div and splits and those prices are even further off from the Quantopian prices:

2-Jan-14 66.71
3-Jan-14 65.24
6-Jan-14 65.60
7-Jan-14 65.13
8-Jan-14 65.54

Does anyone know how to explain this large difference?

It gets even more interesting. I just pulled prices for a longer time period with the same starting date, and now my prices are changing:

aapl_close = prices(  
assets=symbols('AAPL'),  
start='2014-01-01',  
end='2016-01-01',  
)
aapl_close.head()  
2014-01-02 00:00:00+00:00 76.109  
2014-01-03 00:00:00+00:00 74.432  
2014-01-06 00:00:00+00:00 74.832  
2014-01-07 00:00:00+00:00 74.293  
2014-01-08 00:00:00+00:00 74.769  

Longer Time Period

aapl_close = prices(  
    assets=symbols('AAPL'),  
    start='2014-01-01',  
    end='2018-01-01',  
)
aapl_close.head()  
2014-01-02 00:00:00+00:00    73.274  
2014-01-03 00:00:00+00:00    71.660  
2014-01-06 00:00:00+00:00    72.045  
2014-01-07 00:00:00+00:00    71.526  
2014-01-08 00:00:00+00:00    71.984  

I sent the reference url to help Jamie find the dividend adjusted prices in Yahoo finance. Hope it will be useful.

I see that, but the bigger problem seems to be that prices are changing depending on what time period I am pulling, see my post above. The price for AAPL on Jan 2nd 2014 is $73.274 for one case and then $76.109.

https://finance.yahoo.com/quote/AAPL/history?period1=1451635200&period2=1517472000&interval=div%7Csplit&filter=div&frequency=1d

Nov 10, 2017 0.63 Dividend
Aug 10, 2017 0.63 Dividend
May 11, 2017 0.63 Dividend
Feb 09, 2017 0.57 Dividend
Nov 03, 2016 0.57 Dividend
Aug 04, 2016 0.57 Dividend
May 05, 2016 0.57 Dividend
Feb 04, 2016 0.52 Dividend

@Niccola
This can help you better understand Quantopian split, merge and dividend-adjusted prices.

I see that, but again the bigger problem seems to be that prices are changing depending on what time period I am pulling, see my post above. The price for AAPL on Jan 2nd 2014 is $73.274 for one case and then $76.109.

@Niccola,

The difference that you specify as the “bigger problem” is the essence of the adjustment for dividends paid from 2016-01-01 to 2018-01-01.

You may learn from here and here how others do that.

I think we are misunderstanding each other. There are 2 separate issues:

1) Yahoo adjusts for Dividends and those prices DO NOT match Quantopians prices. You are claiming that Quantopian adjusts for Dividends but the adjustment is different than Yahoo's adjustment (and much larger than could be explained by the definition in the FAQ about the last trade vs last tick etc.)

2) (The Bigger Problem) : Quantopian offers different prices for the same time period, when I change the time span. To confirm this, try to to determine what the price for AAPL was on Jan 2nd 2014 using Quantopian's prices function using 2 different time spans and you will see that the price is not the same for Jan 2nd 2014.

I think there are two separate issues being raised in this thread. I'll try to identify and address them separately.

1) The pricing of an AAPL changes when the end_date argument to get_pricing is changed. This is expected behavior. The get_pricing function returns prices that are adjusted for all corporate actions that occurred prior to the end_date. If the end_date is changed, it's possible that there are more or fewer corporate actions included in the adjustment.

The get_pricing function was designed this way because we are very focused on providing data that is 'point in time' wherever possible on Quantopian. In this context, providing point in time data translates to the end_date representing a perspective date. If you make a call to get_pricing with start_date='2014-01-01' and end_date='2016-01-01', the query can be thought of as a "get me the last 2 years of pricing as though I was asking on January 1, 2016". Thus, any corporate actions that occurred after 2016-01-01 are not considered in the split and dividend adjustment.

The above approach is different from how most stock price visualization tools like Yahoo or Nasdaq display prices. In most other places, the default is for all pricing data to be adjusted with a perspective date of today. Because of this, Quantopian community members often expect this to be the default of get_pricing. In other words, someone might expect get_pricing with start_date='2014-01-01' and end_date='2016-01-01' to be interpreted as "get me pricing data from 2014 to 2016 as though I'm asking today". It's perfectly reasonable to expect this interpretation, especially given how most other sites operate. However, at Q, our primary focus is on supporting research that can lead to algorithms that perform well out-of-sample. We think that the ability to control the perspective date of a query is an important feature that helps our community achieve that type of research, so we make it the default behavior of each of our API functions.

2) Historical data on Yahoo Finance is adjusted for splits and dividends, but it still doesn't match up with Q pricing data, even if get_pricing is called with end_date='2019-06-23'. This one was a little harder to figure out. I poked around and looked at the link that Leo shared (thanks Leo!), but something still didn't match up between Q's adjusted pricing data and Yahoo's split and dividend adjusted pricing data.

I spent some time writing a notebook to compare the two and it looks to me like Yahoo might have a bug with how they compute dividend adjustment ratios. I'm far from an expert on the topic, but I worked through the problem using Yahoo's definition of how they adjust their pricing data and I think that Yahoo is using their split adjusted close prices in the dividend adjustment ratio calculation, which I don't think yields an appropriate ratio. I attached the notebook I used to figure this out along with some prose to explain what I did along the way. Let me know if this helps explain things or if you have any questions about the notebook.

P.S. Thank you @Vladimir for sharing the resources that help explain how Q does point in time price adjustments!

@Niccola: I must have been typing out my answer while you posted your last comment. I'm glad we identified the same two separate problems. Please note that I ordered mine differently. My problem 1) is your problem 2), and vice versa. Let me know when you've read through my answer to see if the answers explain both problems to your satisfaction.

Jamie, devious minds think alike ;-)Thank you so much for this detailed response. I was not aware of this type of distinction when querying prices, that is:
a) give me prices as of end date vs
b) prices for that time period as of today
That definitely explains the difference. I did also poke around Yahoo's adj prices a bit and as you pointed out, their adj price also changes when the interval is changed. That is when the interval ends today, then adj price is the same as not adj. price. Otherwise, big differences emerge.
The prices on Q become very large for the case of AAPL when I look at the first 10 days of 2014 (as in $500), Yahoo is showing a smaller adjustment, the price is $60, so it must be that Yahoo uses the split adjusted price (which has then look ahead bias obviously) so Quantopian's prices would be "cleaner" from a research perspective which tries to avoid any type of future information leaking into the analyzed period.

This has been very helpful!! Thank you so much Jamie.

No problem, Niccola. Thanks for asking the question. The answer wasn't immediately obvious to me so it was a worthwhile exercise to go through!

Given the differences in the way prices are calculated, is that some (simple) method that we can use to convert Yahoo price data to match that of Quantopian's??