1
$\begingroup$

I have a list of all S&P500 tickers, e.g. AAPL, GOOG, JPM.

I would like to get their market cap on 1.7.2013 (I don't have Bloomberg, only free internet).

Is there an excel addin or other tool/page where I can obtain historic market cap?

The page https://www.quandl.com/ seems to provide financial information, but I did not find how to retrieve the market cap by ticker.

$\endgroup$
2
$\begingroup$

I faced the same issue some years ago and I solved by implementing the R script reported here; now, with new Yahoo disclaimer rules, it seems to be broken, but, anyway you should be able to replicate the data mining process using that script together with this.

If you're pretty confident with R, you should be able to do that.

Alternatively, you can visit wolframalpha.com and download the data.

You can also do the same by using python and QUANDL API, but it should more complicated. The only one problem is about the data reliability in both cases, but, as usual, if database is for free, it is normal to get low-quality data.

$\endgroup$
  • 1
    $\begingroup$ Thanks for your info. Quite surprisingly, I had finally found it on Wolframalpha^^ wolframalpha.com/input/… $\endgroup$ – emcor Apr 23 '15 at 17:45
  • $\begingroup$ I updated the answer with your solution for completeness. Thanks you too for sharing that :) it will be useful in the future surely! $\endgroup$ – Quantopik Apr 23 '15 at 21:45
2
$\begingroup$

Some of the issues with this sort of request is:

a) Today's S&P 500 components are not the same from 1 Jul 2013. By using today's components you are introducing pre-inclusion/survivorship bias. Are you going to be able to find data on the delisted stocks? eg. Since 1 Jul 2013, Sprint Corporation, BMC Software, NYSE Euronext, Molex, Life Technologies, Beam Suntory, International Game Technology, Forest Laboratories, Safeway, Covidien, Allergan have all delisted (due to takeover/merger). There's also a lot more that have been removed as part of the quarterly rebalancing.

b) Today's shares outstanding are not the same as 1 Jul 2013. There may have been mergers/takeovers (where shares are issued as part of the takeover), demergers, buybacks, employee share issues, private placements etc. since that time.

c) Shares outstanding * share price does NOT equal market cap in all cases. Some companies (e.g. Berkshire Hathway, Google etc.) have multiple types of share classes, sometimes with different ownership levels, others with unlisted shares. Others have securities that convert to normal securities (Convertible Preference, Warrants etc.) - so these also need to be considered.

Due to these complexities, this sort of data is just not freely available. Given you only need a date a coupe of years back then the difference may not be crucial for your exercise, but if you need absolute accuracy then you need to look at more professional sources.

With any source you use, you must determine exactly what methodology is used to produce the metrics.

$\endgroup$
1
$\begingroup$

@emcor I would suggest downloading the Stock's daily Prices & then downloading Shares Outstanding or average shares outstanding. Then find the product of the two to arrive at market caps.

I don't know how reliable quandl's data is or if they have shares outstanding data, but if they do this can probably be done using R code since you will be downloading many ticker's data

$\endgroup$

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.