Skip to content
All library documents

Retrieving Equity Market Capitalization and Avoiding Survivorship Bias

Article Quant Q&A · Author: Zach
Read the original

Summary

The document presents a simple spreadsheet-based approach to obtaining a company’s current market capitalization: use a stock ticker to retrieve the relevant field from an online finance page, then repeat the process for a list of index constituents. It also suggests collecting an S&P 500 constituent list through a separate source. This is a lightweight method for a visualization project when a dedicated data API is not readily available.

The approach depends on a third-party webpage and spreadsheet import functionality, so the data source and retrieval method may change or fail. The document does not discuss update frequency, data validation, licensing, or how to calculate market capitalization from share count and price. Its main research caveat is survivorship bias: index membership changes over time, so applying today’s constituent list to historical analysis can omit companies that were later removed or delisted and distort results.

Key ideas

  • A spreadsheet can retrieve a company’s current market capitalization from an online finance page using its ticker.
  • The retrieval can be repeated across a list of index constituents.
  • A changing index membership list may be unsuitable for historical analysis.
  • Using current constituents in past periods can introduce survivorship bias.

Tags

EquitiesUS markets
Full text
# Is there an API that can return the current market cap of a publicly-traded company?


# Is there an API that can return the current market cap of a publicly-traded company?












I am trying to find an API which will return the current market cap of US stocks for a financial data visualization project, and I haven't had much luck finding anything. Preferably, I'd like to have market cap data for all S&P 500 stocks, though I realize that something like this might not be available and that I may just need to create that list myself with other data.

Has anyone had luck getting market cap data from an API, or is there a relatively simple way that I could calculate the market caps?

I've checked out this old post where someone suggested Yahoo finance and YQL, but the links are unfortunately dead and it appears as though this service is no longer offered.

Thanks for any insight/advice!

## Answer by Joel Alcedo (score 1)

https://quant.stackexchange.com/a/49629

If you are looking for a quick and easy solution, I have found a combination of Google Sheets + Yahoo Finance URLs to be relatively easy to implement.

Here's an example you could try yourself. Let's say you have a stock ticker, "AAPL" in cell A1.

```
=INDEX(IMPORTHTML( CONCATENATE("https://finance.yahoo.com/quote/", A2,"?p=",A2,"&.tsrc=fin-srch")  ,"table",2),1,2)
```

Basically, the importhtml function I've specified above will go to Yahoo Finance, then look for this table, splitting it out into 2 columns illustrated in yellow and only pull in the observation found in the first row, second column (hence the 1, 2 in the function parameters):

This approach could effortlessly be extended for all S&P500 companies - you would just put their corresponding ticker in cell A3 onward.

In order to pull in a list of all S&P500 constituents, you could go to this wiki to get a list using a similar formula. Be warned that if you intend to do any historical analysis, companies get listed/delisted all the time and your analysis could reflect suvivorship bias.

Shown in full with attribution under the source's licence. Licence: CC BY-SA 4.0 (Stack Exchange)

This summary was written by Stratmill's research agent from the original; it is not a copy of the source.