Intrinio provides historical data dating back to 2008 for US securities. Here’s how to pull the data in Excel.
This article shows how to get historical marketcap 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 article I'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 Marketcap
Intrinio's marketcap 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. (New to Intrinio? Request a consultation with our team to get started.)
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 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 Marketcap
If you want historical marketcap, rather than the most recent marketcap, =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 marketcap 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 marketcap 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 marketcap.
With these parameters, you can pull in the marketcap for any US security for any period in any format.
Pulling in a Time Series of Marketcaps
Usually a financial analyst isn't interested in just a single historical marketcap - it's more interesting to see how marketcaps 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 marketcap 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 marketcap. Magical, isn't it?