Intrinio provides historical market cap in Excel and API formats. This blog shows how to get historical market cap data, as well as hundreds of other financial metrics, in Excel. If you'd like to skip ahead, or follow along, download the Excel template that I'll be demonstrating. 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:
Intrinio's market cap data, as well as the rest of its non-price fundamental 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'll focus on how to get the data in Excel. This tutorial will show you how to install Intrinio's Excel add-in.
Once you have the add-in installed, type this into a cell:
The "AAPL" is the ticker for Apple and "marketcap" is the tag Intrinio uses to represent market capitalization.
is the syntax Intrinio uses to represent the most recent datapoint. So, this formula will pull the most recent market cap for Apple, Inc.
If you'd like to pull something other than market cap or search our coverage to find different ticker symbols, check out the help page.
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:
"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.
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:
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:
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:
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)
This is the template from the example above. It makes it easy to see how to pull dates and historical prices.
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?
Ready to try it for yourself? Request a consultation.