Historical Market Cap and Other Data in Excel For Free

Intrinio is on a mission to make financial data affordable and easy to access, and one way we do that is by providing historical market cap in Excel and API formats. Our support team, who you can chat with by clicking the green icon on the bottom right-hand corner of this page, get a lot of questions about market capitalization, so we figured it must be a data point that many analysts and developers need.

This blog shows how to get historical market cap data, as well as hundreds of other financial metrics, in Excel for free. If you'd like to skip ahead, or follow along, download the Excel template that I'll be demonstrating here. By the end of this blog you'll be able to pull in historical time series data for any US security.

Feel free to start with this video:

Getting The Most Recent Market Cap

Intrinio's market cap data, as well as the rest of its non-price fundamentals data, is updated daily with the latest filings and goes back to 2008. To access the latest or historical data in API format, check out this article that explains how to get started. In this blog, we will focus on how to get the data in Excel. This tutorial will show you how to install Intrinio's Excel add-in and set up an account for free.

Once you have the add-in installed, type this into a cell:

=intriniodatapoint("AAPL", "marketcap")

The "AAPL" is the ticker for Apple and "marketcap" is the tag Intrinio uses to represent market capitalization. =intriniodatapoint  is the syntax Intrinio uses to represent the most recent datapoint. So, this formula will pull the most recent marketcap for Apple, Inc.

If you'd like to pull something other than marketcap or search our coverage to find different ticker symbols check out the help page.

Getting a Historical Market Cap

If you want historical market cap, rather than the most recent market cap, =intriniohistoricaldata  is the function you need to use in Excel. Here is an example:

Historical Market Cap in Excel

The documentation for historical data explains how this syntax works but let me break it down for you to make it easy:

"AAPL" is in quotes because it's text and specifies the ticker we are interested in.

"marketcap" is the tag for market capitalization, just like with the =intriniodatapoint  syntax earlier.

0 represents the number of periods back. Jumping ahead you will see "daily" is the specified period, so this would give us the historical market cap from 0 days ago. 1 would move it back a day. This number doesn't need to be in quotes.

"2008-01-01" is the start date and this represents how far back we can go. You can set this as any date between today and 2008.

"2016-11-29" is the end date and this represents how far forward we can go. In a little bit I'll show you how to set this as a dynamic variable.

"daily" gives us the frequency. This could be weekly, monthly, quarterly, or yearly. If you put in "yearly" for example, the period number would go back in increments of years, not days.

"TTM" specifies the type of historical market cap you are interested in. In this case, we are looking at trailing twelve months, but you could also set it to calculate a quarterly, fiscal year, or year to date market cap.

With these parameters, you can pull in the market cap for any US security for any period in any format.

Pulling in a time series of market caps

Usually a financial analyst isn't interested in just a single historical market cap- it's more interesting to see how market caps change over time.

To do this, you will need to start using relative references in your spreadsheet:

Time Series of Market Cap in Excel

In this syntax, I'm using relative references so that I can click and drag my formula down to get Apple's market cap for a series of dates going back as far as I'd like. The start date, end date, period, item, and ticker don't change, I've used $ to hard code them in. The only varying parameter in this case is the sequence.

The sequence is set to increase by 1 in each new row:

Entering Historical Market Cap in Excel

For the end date, I am using the function =TODAY() so that each time I open the spreadsheet, the date will be current for the end date parameter ensuring my data updates automatically.

You can see in cell F5 that I have scaled my spreadsheet to include another stock, Amazon, and it is very easy to keep adding more to my list by simply dragging my formula down to fill in the historical data.

If you are interested in graphing the historical trend, you can easily add the date:

Adding a Date to Historical Market Cap in Excel

I'm using another function, =intriniohistoricalprices, to obtain the dates- if you are interested in historical stock prices, check out the IntrinioSecurityPrices template that came in your Excel add-in download. An alternative method for getting dates is to use the same historical data syntax, but add a parameter at the end, show date, set to true:

=intriniohistoricaldata("AAPL", "close_price", 0, "2015-01-01", "2016-01-01", "daily",,TRUE)

Notice that I skipped the second to last parameter. This functionality will return the date for this data point so you can easily match up dates to your data:

Historical Dates Via API

Pulling in historical dates to match up with the data

This is the template from the example above. It makes it easy to see how to pull dates and historical prices.

Is this really free?

It really is. Intrinio's US Public Company Financials data feed provides a free plan that gives you 500 daily API calls, meaning you can pull in 500 different market cap data points in a day. This article explains what API calls are and how they are counted.

It may have occurred to you that if you change "marketcap" in cell B5 to another tag, perhaps "beta" or "revenue" or "basiceps", your entire spreadsheet will update with that data instead of market cap. Magical, isn't it?

 

  • Brent L. Daniel

    This is great! Only trouble I have is that the second I try to use a relative reference, it doesn’t work. The adjacent cells are all identical to the one I start with. Any ideas?