Sharadar Stock Prices, Fund Prices and Adjustments

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.

The Stocks Table

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 unadjusted Open, High and Low as follows: OpenUnadj = Open * CloseUnadj / Close

Repeat for the H and the L - et voila! you have the unadjusted Open, High, Low and Close.

Volume is the exception. Split-adjusted prices divide history by the split ratio; split-adjusted volume multiplies history by it (and divides it for a reverse split). That keeps dollar volume comparable: Close * Volume lines up with CloseUnadj times the shares that actually traded.

So tape-reported volume is the reciprocal:

VolumeUnadj = Volume * Close / CloseUnadj

Example: AAPL 4-for-1 split, effective 2020-08-31. On the last pre-split session (2020-08-28): Close = 124.808, CloseUnadj = 499.23, Volume = 187,630,000. Tape volume = 187630000 * 124.808 / 499.23 = 46,907,500.

Example: GE 1-for-8 reverse split, effective 2021-08-02. On the last pre-split session (2021-07-30): Close = 103.6, CloseUnadj = 12.95, Volume = 7,497,000. Tape volume = 7497000 * 103.6 / 12.95 = 59,976,000.

On and after the split effective date, Close equals CloseUnadj, so Volume is already the tape number. Cash dividends and spinoffs do not change Volume; do not use CloseAdj / Close on it.

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

Repeat for the H and the L. Not the V: cash dividends and spinoffs do not change Volume.

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

We treat the value of a spin-off distribution as a dividend and assume that this value is reinvested in the parent stock at its closing price on the ex-date.

To value the distribution, we use the spun-off security's prior-session closing price if available. Otherwise, we use its when-issued security's prior-session closing price. If neither is available, we use the spun-off security's opening price on the ex-date. We multiply the selected price by the number of shares distributed per parent share.

Distribution Value per Parent Share = Selected Spinoff Price × Shares Distributed per Parent Share

Adjustment Ratio = 1 + Distribution Value per Parent Share / Parent Ex-Date Close

We divide prices before the ex-date by this adjustment ratio. The ex-date price itself is not adjusted for that distribution.

For example, GE distributed one GE HealthCare (GEHC) share for every three GE shares on January 4, 2023. GEHC's prior-session when-issued closing price was $57.39, giving a distribution value of $57.39 / 3 = $19.13 per GE share. GE closed at $70.20 on the ex-date:

Adjustment Ratio = 1 + 19.13 / 70.20 = 1.27250712

The distribution value is reported as spinoffdividend in the actions table. It represents the value assigned to the distributed security per parent share, adjusted for subsequent parent stock splits; it is not a cash payment.

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.