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.

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.

Sharadar Launches Direct

Today we're please to launch our direct service to you 🚀 

Offering the same data you may have seen on Nasdaq Data Link with super easy sign-up and retrieval mechanisms:

• Fundamental data for US stocks with deep history and coverage (99% survivorship bias from since 1999).
• Originally curated from SEC filings with meticulous reconciliation to these records.
• Price history that is unadjusted, split adjusted, or split+dividend+spinoff adjusted (EOD OHLCV).
• Rich reference and metadata to classify and identify covered securities.
• Detailed corporate actions data that allow a comprehensive and coherent view over time.
• Complementary insiders and institutional holdings data.
• Effortless query-building when you want to extract precise records or bulk downloads when you want it all.
• Subscription options to suit your needs with independently useful sample data and easy sign-up and cancellation options on our site.

We've got a ton of new features and new datasets in the pipeline that we can't wait to share with you.

Institutional customers can continue to find us Nasdaq.

Questions? Please don't hesitate to reach out: [email protected]

The Great Stock Comp Swindle

The accounting requirements for stock compensation are broken and distort the underlying economics involved. Investors need to be aware of how it is broken, and make adjustment for it in their assessment of companies.

A notable recent example concerns the compensation of Tesla CEO, Elon Musk. Unless you're living under a rock you will have seen headlines concerning a Delaware judge voiding Musk's "$56 billion pay package". 

What you may not be aware of is that the actual expense recognised in Tesla's books is closer to just $2bn. 

The $56bn figure is closer to the actual expense to the company, that consists of granting 304 million options to purchase Tesla stock at an exercise price of around $23/share, or nearly 90% below the stock's current market price.

The reason for this dramatic disparity is that US GAAP accounting requires that the maximum cost of this compensation plan be determined at the inception of it, based on a Black-Scholes model. $2bn was the number this model spat out back in 2018. $56bn is the approximate market value of those options today.

This situation is like an options trader naked shorting call options and being able to cap any adverse P&L impact of doing so based on a Black-Scholes model at the time they open their naked short position. In doing so completely ignoring the actual economic cost. Obviously patently ridiculous but this is what US GAAP requires of companies.

This is separate from any discussion around the merit of the shareholder approved award to Musk. The award is subject to appeal in Delaware but there is also a shareholder movement to have the award ratified by shareholders again, in doing so addressing any inadequacies in the original approval - whether real or imagined by the Delaware judge. 

My suspicion is that a constraint to such a reapproval may be that this would require the real cost of the plan to be recognised in Tesla's financials, which would not make for pretty reading. $56bn represents about 200% of Tesla's retained earnings since inception.

It must also be noted that the misleading accounting here does not reflect a failure of Tesla, or any one company, but rather of the standards boards that set the accounting requirements. Tesla reflects merely one dramatic example of the economic distortion, but this situation is not unique to Tesla, and investors need to be aware of it when assessing businesses.

Another striking recent example that has received less press is that of Nvidia, whose Free Cash Flow generation can so easily be significantly misinterpreted as even more impressive than it really is.

In their recently reported financials Nvidia shows "Stock-based compensation expense" of $3.5bn, and "Payments related to the repurchase of common stock" of $9.5bn.

Taking a naive view of this one would expect therefore that, all else equal, there would be a net $6bn reduction in shares outstanding. But that is not what happened. 

The actual reduction was just 2m shares, which would equate to an average repurchase price of $3,000, or about 650% more than the average close price over the period. The reason for the disparity is that much of the share repurchases have simply been to offset dilution from stock-based compensation that is accounted for at a dramatically lower price. 

Investors need to be aware of this accounting and make adjustment for it. For example in the case of Nvidia one might adjust the Free Cash Flow figure (ordinarily calculated as Operating Cash Flow less CapEx) for the full value of stock repurchases, since these repurchases are really only offsetting stock-based compensation. This would drop the figure from $27bn ($28bn - $1bn) to $17.5bn ($28bn - $1bn - $9.5bn). And one should probably also adjust for the "Payments related to tax on restricted stock units" of $2.8bn too. This would drop Free Cash Flow down to $14.7bn ($28bn - $1bn  - $9.5bn - $2.8bn), which is barely half of the original figure.

- Vincent

 

Buckle Up for Virgin Galactic

Without looking too closely Virgin Galactic (ticker SPCE) struck me as a theme park company. Albeit a pretty darn fun one. And premium too. A cool quarter million for 5 minutes of weightlessness. This perception was further supported by the recent appointment of the former head of Disney's parks as the company's CEO. Galactic has teased that they might one day extend their product to point-to-point supersonic commercial travel. But it has been careful not to do so in any of its SEC disclosures. This trend continued Monday as the company dropped three pieces of news:

On the left is the announcement of a proposed $460m equity raising for "general corporate purposes". In the middle is the announcement of financial results, with zero revenue and more than $120m burned in the six months of this year to date (about a third of its remaining cash on hand). On the right is the announcement of a non-binding agreement with Rolls Royce to explore engine development for high speed commercial aircraft.

Company insiders chose to emphasise the latter news. Galactic Chairman, and all round slick talker, Chamath Palihapitiya shared some sexy renders:

The first two pieces of news are the meat, and the third is the potato. Rolls Royce recently announced a development agreement with Boom Supersonic, a company strictly focused on developing a supersonic commercial aircraft. There is no obligation to Rolls Royce and need not be any incremental effort from them while they garner some PR value from the Virgin Galactic news. Virgin Galactic accrues some hype to support its capital raising efforts. A cynical ploy that will probably work. But if Galactic was serious about supersonic travel there might be some mention of it in their SEC filings. There remains none as of this writing. And if the company were serious about technology development it might not have appointed a career theme park manager as CEO. 

Incidentally Boom Supersonic has raised just $150m to date, which has supported six years of development to the launch of its supersonic prototype in the coming year. It's difficult to tell what is really going on under the covers of these companies, but on the surface it appears Boom is vastly more capital efficient and focused on delivering commercial value than Virgin Galactic is. But that has not stopped the public markets giving Galactic about 10 times the capital and 5 times the valuation that the private markets have given Boom.

- Vincent

[Disclosure: I am a private investor in Boom Supersonic, and may or may not go long Virgin Galactic as a short term momentum play on this news] 

Navigating the Options Cycle on Tesla

Tesla is one of the most volatile large cap stocks in the market. It's a battleground stock with the war between longs and shorts waged in each buy or sell order, and each resulting fluctuation of its stock price. In about a year the price has gone from $377 in December 2018, halving to $177 in June 2019, subsequently tripling to close at $525 most recently on the 13th of January 2020.

The stock war is waged not just in the direct purchase or sale of the stock, or in the media, but in the options market too, where insufficient attention is paid by the ordinary investor, and where much descriptive value may be found. The options market can exhibit reflexivity, a circular relationship between cause and effect. The strike price of price of an option can act as a magnet pulling the underlying stock price, creating a virtuous, or vicious, cycle depending on where you have placed your own bets. The options activity in Tesla is unique in several respects, and understanding this will aide understanding of the volatility in the underlying stock price.

According to the Options Clearing Corporation the open interest in put options on Tesla stock amounted to 160 million units on the 13th of January. This is more than the number of shares in Tesla's float, an unusually large number of open options that suggests a large degree of influence over the underlying stock. There were 80 million call options on this date for a 2:1 Put-Call ratio. This is in contrast to an approximate 1:1 Put-Call ratio across the market and suggests a high degree of bearish sentiment toward Tesla stock.

The Put-Call ratio is a common, albeit crude, measure of investor sentiment. In the case of Tesla it is is significantly misleading, as less superficial analysis will reveal. Not all options are created equally and not all options should be weighted equally.

Two of the key factors in the relationship between a call or put option and the underlying stock price are the strike price of the option, and the expiration date. This is where two important facts will emerge.

1) Most put option volume on Tesla stock is currently deep out of the money, and most call option volume is in the money, as the following volume chart segmented by option strike price reveals:

Based on the 13th of Jan closing price of about $525 per share of Tesla stock, more than 99% of put options are out of the money, and 65% of call options are in the money.

2) Nearly half of call and put option volume expires on the 17th of Jan, as the following volume chart segmented by option expiry date reveals:

This means that there is only a narrow window of time remaining for movements in the underlying stock price to effect the intrinsic value of the outstanding options.

Combining the above facts under the assumption that counterparties have hedged their exposure, we can determine what has been the impact to the underlying stock demand of these contracts, and how this might change in response to changes in the underlying stock price. Options pricing theory provides just such a mechanism - Delta Hedging. For our purposes the most important thing to know about Delta Hedging is that the more likely an options contract is to be exercised, the greater the number of underlying shares the counterparty to that contract needs to transact in to hedge their exposure.

The chart above contrasts the gross number of options reported with the number of underlying share transactions that would currently be required to offset counterparty exposure to that option contract. The 80 million call options equate to 50 million share purchases. The 160 million put options equate to 5 million short sales.

The same options pricing theory that provided us with Delta-Hedging, the Black-Scholes model, also provides a mechanism to calculate the value of the call and put options, and the calculated values of these options track closely to recent trade prices of those options. This implies this is not simply a theoretical exercise but has some basis in reality with regards to the any underlying share transactions to offset the exposure on these options, and the observed activity in Tesla stock.

It's important to recognise that the number of shares required to offset an options position is not static and changes, most importantly, with the price of the underlying stock. The following chart shows the Delta Hedged volumes segmented by different underlying stock prices:

The chart shows that between the underlying stock price of $500 and $525 the number of share purchases required to offset the call position increases from 45 million to 50 million as more call options are deeper in the money and more likely to be exercised. Conversely, the number of short sales required to to offset the put position reduces from 8 million to 5 million as more put options are deeper out of the money and less likely to be exercised. This equates to a net increase in demand for Tesla stock of 8 million shares. Such a price move therefore induces as much demand for Tesla stock as the third largest institutional investor in Tesla, the Saudi Public Investment Fund, has accumulated over time.

The actual stock price increase of $47 on the 13th of Jan is enough to stimulate demand for nearly 17 million shares of Tesla stock, more than the single largest institutional investor in Tesla, Baillie Gifford, has accumulated, and all in a single day. This reflects a virtuous (or vicious) cycle where a stock price increase induces further demand for Tesla stock which all else equal will push the price up further as further counterparty share purchases are required to offset their options exposure. The reverse is also true, as a reduction in the share price would have the exact opposite effect. This self-reinforcing cycle explains the volatility and periods of momentum we have seen in Tesla stock to a significant degree, but there are caveats. There will be differences in timing and extent to which counterparties hedge their exposure. And other factors influence the stock price too, principally market participants deciding whether to continue to participate, or to place their chips elsewhere.

- Vincent

[Disclosure: long time holder of Tesla stock]

The Man Who Solved the Market

By now many of you will be familiar with the investment record of Renaissance Technologies. A book on the subject came out earlier this month.

For the uninitiated their Medallion Fund has compounded at 66% p.a. for multiple decades, 39% p.a. after the most delicious fees in the industry. $1 invested in 1988 would be worth $19,500 in 2018. That is if you were fortunate enough to remain invested, as many early LP's were not. The fund size has apparently been capped at around $10 billion, in order to preserve high percentage returns.

We can never completely rule out that this is a blind-folded monkey throwing darts. But if that is the case then it is one hell of a monkey. Or it's an 800 pound gorilla.

This performance is even more striking because the fund apparently employs few, if any, finance domain experts. Mathematician Isadore Singer described Renaissance as having "the best physics and mathematics department in the world". This capability is combined with a data-driven, quantitative approach across multiple asset classes, and a reported 2 day average holding period.

It's a powerful story of just what is possible with data, and I love it because of that. But unless you also have a team of world class scientists, their approach may not be one that you want to try to emulate exactly. There may or may not be room for a few more players deploying similar strategies to the Medallion fund, and at similar scale. But their very existence suggests that for many others with a quantitative focus, their efforts may best be expended by doing what the Medallion Fund does not.

- Vincent

Pininvest

Another day, another great investment site. This one is Pininvest which curates investment themes, amongst other things. It's a great place to generate ideas and insight into domains which are topical and which you find interesting.

- Vincent

[Disclosure: uses Sharadar data]