At Sharadar, we want our cake and we want to eat it too. We also want to offer comprehensive end-of-day (EOD) price data that is easy to access, survivorship bias free, has deep history and different ways of accounting for corporate actions, and without that data becoming unwieldy and outright painful to use.
So here is how we've structured our offering to you. We offer two separate tables that represent a common distinction investors, analysts and your great uncle Stan make when using this type of data:
stocks - provides prices for common stock and other equity securities for operating companies (the AAPLs, GOOGLs etc of the world)
funds - provides prices for funds and fund related securities (the SPYs, QQQs etc of the world)
That one move cuts the problem near in half. If on the rather odd chance you do want to look at stocks and funds together you can always aggregate them (basically a bulk copy and paste) which will be much simpler than disaggregating them if they were instead already aggregated (bascially like panning for gold but less fun).
Within the stocks and funds tables we then provide Open, High, Low, Close, Volume (OHLCV) figures.
But which OHLCV figures exactly? Are they adjusted for stock splits (also sometimes called stock dividends - same thing) or are they unadjusted? Are they adjusted for cash dividends or are they unadjusted? Are they adjusted for spinoffs or are they unadjusted?
The answer is YES.
Well there must be lots and lots of columns then (3x OHLCV plus identifiers)?
Not quite.
Open, High, Low, Close and Volume
In our tables the Open, High, Low, Close, and Volume fields are adjusted for stock splits, but not adjusted for cash dividends or spinoffs. This is the most common way of looking at these fields.
CloseUnadj and Imputations
Then we provide the CloseUnadj field which is (you guessed it) not adjusted for stock splits nor cash dividends nor spinoffs. This is the C in the unadjusted OHLCV, but where are the OHL and V?
You impute the other unadjusted fields as follows: OpenUnadj = Open * CloseUnadj / Close
Repeat for the H, the L and the V - et voila! you have the unadjusted Open, High, Low, Close and Volume
CloseAdj and Imputations
Then we provide the CloseAdj field which is (of course adjusted) for stock splits and cash dividends and spinoffs.
Again, you impute the other fully adjusted fields as follows: OpenAdj = Open * CloseAdj / Close
Corporate Adjustment Overview
How are split, dividend and spinoff adjustments calculated?
I'm glad you asked! Here's a quick rundown.
Adjustments are applied on a backwards basis. This means today's adjusted price will always equal the price traded in the market. Adjustments apply only to historical data; the further back you go into the past, the greater the cumulative adjustments are likely to be, as corporate actions accumulate.
Cash Dividends Adjustments
Adjustment Ratio = (Close Price + Dividend Amount) / (Close Price)
Example: AAPL had a dividend of $0.47 on 2014-08-07. The close price on that day was $94.48. The unadjusted close price for the previous day was $94.96. The adjusted historical prices are calculated like this:
dividend_amount = 0.47
current_close = 94.48
adjustment_ratio
= (current_close + dividend_amount) / current_close
= (94.48 + 0.47) / 94.48
= 1.0049745977984759
previous_close = 94.96
adjusted_previous_close
= previous_close / adjustment_ratio
= 94.96 / 1.0049745977984759
= 94.490
and the same adjustment propagates all through the stock's preceding history.
Stock Dividend Adjustments
Adjustment Ratio = (New Float) / (Old Float)
Example: BIOL had a 0.5% stock dividend on 2014-03-12. Shareholders were given 1 new share per 200 shares of BIOL already held.
new_float = 1.005 * old_float by definition
adjustment_ratio
= new_float / old_float
= 1.005
current_close = 2.9
previous_close = 2.83
adjusted_previous_close
= previous_close / adjustment_ratio
= 2.83 / 1.005
= 2.8159
Stock Split Adjustments
Adjustment Ratio = (New Float) / (Old Float)
The calculation method is the same as stock dividends but the magnitude is typically different.
For example, CPK had a 3 for 2 split on 2014-09-09. Shareholders were given 3 shares per 2 shares previously held of CPK.
2 * new_float = 3 * old_float
adjustment_ratio
= new_float / old_float
= 3/2
= 1.5
current_close = 45.11
previous_close = 69.41
adjusted_previous_close
= previous_close / adjustment_ratio
= 69.41 / 1.5
= 46.273
Spinoff Adjustments
For spinoffs, we assume that you sell the spin-off stock at its open price, and use the proceeds to buy back the parent stock at its open price. This is the methodology used by most stock data providers. A few stock data providers assume that you sell the spin-off stock at the close price, and buy back the parent stock at its close price. The difference between the two methods is usually minimal but it's non-zero.
Adjustment Ratio = 1 + (Spinoff Open Price * Spinoff Shares) / (Parent Open Price * Parent Shares)
For example, ADP spun off CDK on 2014-10-01. ADP shareholders were given 1 share of CDK for every 3 shares of ADP they held.
parent_open_price = 73.03 <-- ADP
spinoff_open_price = 30.13 <-- CDK
spinoff_shares / parent_shares = 1 / 3 per the terms of the spinoff
adjustment_ratio
= 1 + (spinoff_open_price / parent_open_price) * (spinoff_shares / parent_shares)
= 1 + (30.13 / 73.03) * (1 / 3) = 1 + 0.13752
= 1.13752 parent_previous_close
= 83.08 parent_adjusted_previous_close = parent_previous_close / adjustment_ratio = 83.08 / 1.13752
= 73.036
Date, Ticker and LastUpdated
To round it off we provide these fields to identify and filter for the period and security which each line item relates, as well as for recent updates to the table.
