Stock Data Type – Stock Quotes and More
Well, I have a great update for you! We have the new system for retrieving stock quotes that we’ve been waiting for. It is the Stocks data type. But, first, a quick background. Historically, we were able to retrieve stock quotes by using the MSN MoneyCentral web query. I wrote about that here. And, that worked fine for a long time. Then, around December 2014, it suddenly stopped working. Yikes! But, Microsoft quickly restored functionality and got it working again in January 2015. But, in August 2017, we got a notice that Microsoft was working on a new system for stock quotes. Well, that new system is here and it is beautiful!
Objective
Before we get too far, let’s confirm our goal. Our goal is to pull stock quotes into an Excel workbook. And, we’d also like to refresh it anytime. Oh, and, we also want to add various columns of related data, such as ticker symbol and previous close. Yikes! Sounds like a big request … and it is. But, it is super-duper easy now that we have the Stocks data type. See below:
And, we can easily add additional company information as well, such as number of employees, CEO, and more. So, let’s get to it. I’ve prepared a short video as well as a written narrative.
Video
Narrative
We’ll explore the Stocks data type with the following steps:
- Enter company name or ticker
- Add columns
- Refresh
Let’s get started by entering the company name.
Note: as of the date of this blog post, the Stocks data type is fairly new. So, depending on your version of Excel and when you are reading this, you may or may not have the new Stocks data type. If you have it, it will appear on the Data tab in the Data Types group.
Enter company name or ticker
We just begin by entering the company names or tickers into some cells. Maybe something like this:
Then, we select the range of company names/tickers, and select Data > Stocks from the Data Types ribbon group, shown below.
Note: depending on your version of Excel, you may not have the Stocks data type command.
The updated worksheet is shown below:
Note: if Excel doesn’t retrieve the desired company information, right-click the stock icon and select Data Type > Change.
As you can see, Excel changes the values we entered to the formal company name and added the little stock icon. This icon tells you that Excel is using the Stocks data type, and that is awesome, because we can easily add related information.
But before we do, we’ll convert this range to a table by using the Insert > Table command. Update below:
Now, let’s add some related company data.
Note: converting to a table isn’t strictly necessary, but, it does make it a bit easier to work with.
Add columns
When a stock cell is selected, Excel displays the Add Column button. We can use the button to add related data columns. This button is shown below.
When we click it, we see many options. Let’s say we want to show the ticker symbol next. We just click the Add Column button and select Ticker symbol. Bam:
To add the price, we just click Add Column and Price. Bam:
Want to see the previous close? No problem. Add Column > Previous Close. Bam:
Add, I think you get the idea. There are a bunch of choices and you’ll want to check them out.
And, if you didn’t necessarily need to see these values in a table, you can always just view the Card for any given company. One way to do that is to click the little stock icon. A sample is shown below:
You can also show the card by right-clicking on the stock icon, and selecting Data Type > Show Card from the resulting shortcut menu.
With our basic stock table set up, we can retrieve updated quotes or information at will.
Refresh
Excel will refresh the stock quotes at any time. One way to do this is to use the Data > Refresh All command. Another way is to right-click any of the company names and select Refresh, or, Data Type > Refresh.
Excel retrieves updated information and refreshes the table accordingly.
So, this is our new system for retrieving stock quotes and related information into Excel. To me, this is a far easier and more useful system that the previous web query method. Thanks Microsoft!
If you have any other fun stock data type tips, please share by posting a comment below…thanks!
Sample file: StockDataType.xlsx
Excel is not what it used to be.
You need the Excel Proficiency Roadmap now. Includes 6 steps for a successful journey, 3 things to avoid, and weekly Excel tips.
Want to learn Excel?
Our training programs start at $29 and will help you learn Excel quickly.
I have the stocks and geography commands, but when I select the range of company names and select stocks, I get an error that says “Cannot convert to data type”. Does this mean my excel version is not up to date? Thank you.
I haven’t seen that error, and can’t seem to reproduce it here. If you reply with the text values (names/tickers) you tried to convert, I can test those specific names/tickers here and see what happens.
Thanks
Jeff
How can I add stocks to my data types? I’m using Excel 2016.
It depends on your license … if you are on a perpetual license of Excel 2016 then feature updates aren’t included. If you are on a subscription license (O365) then feature updates are delivered periodically depending on your frequency preference.
Thanks
Jeff
This is a good first step but key data is not yet available such as EPS, Dividend, and Yield to name a few. Any idea if and when this data may become available?
This is a relatively new feature at this time, and it will be interesting to see how Microsoft decides to expand the overall Data Types capabilities of Excel. I don’t have any information about additional data columns, such as eps, dividend, yield, and so on, but I hope they continue enhancing the Stocks data type.
Thanks
Jeff
I agree. This is less than half of what we used to be able to pull in. I am not sure it is easier…. once you had the “setup” configured correctly, you could run your portfolios and have it refresh every few minutes….(15 minute delay). I also used to use it to help analyze/identify potential stocks to buy and what stocks I thought may be either fully valued.
I used the company names from your example to get that error:
google
msft
disney
Thank you
Melissa … hmmm, sorry to hear about that. I’m not sure what could be causing that issue, but perhaps there is a corporate IT policy that is blocking the online retrieval of the information or perhaps running an office repair could help. If I think of anything else I’ll follow up via email.
Thanks
Jeff
stocks work well for me. I need to get indeces such as DJI, S&P500, Russel 1000 etc.
Is there a way to get those?
Thank you in advance.
Tom
Hi Jeff
Can you confirm what version this is added to. I’m on O365 but haven’t got it yet, my version is 1806, thanks.
Kevin
It seems the list of stock tickers must be directly typed in. They cannot be derived from other lists or even directly referenced (e.g. “=A36”). This greatly limits the flexibility.
I’m also getting the same error message “Cannot convert to data type” when entering such symbols as AAPL and MSFT. Did anyone figure out why?
is there a way using this functionality to extract previous prices or values of the different variables? e.g. stock price in Jan-18, feb-18, CEO in Jun-2016?
Does this only work for US Stock Exchange, or for other exchanges internationally? How do I (If I need to) tell Excel which exchange to look for? (i.e. Singapore, Malaysia & HK Exchanges)
Hi
I am able to pick up few stocks (listed on Indian Stock Market ) and related data in excel using stock data type.
I am refreshing data from India where it is getting connected which site Indian Stock Market, Yahoo Finance, ….etc.
Secondly can I add column into Card Data or can I pickup data not listed in card ? if yes then how ?
Thanks
actually data is not shwing on real time basis rather it 15 minutes delayed so how i refresh data on real time basis
I have built myself a version of my old Google Finance Portfolio using your tips.
However, I have a minor but annoying problem.
I follow UK FTSE100 stocks.
My table has all 100 stocks included.
The MCAP data column shows each company MCAP but in different currencies.
When sorting, it doesn’t take account of the difference in currency.
Is there a way to acquire each MCAP in the same currency?
This is important to me as, by sorting the companies by MCAP, I can easily see which companies are likely to be kicked out of the FTSE100 next time changes are being made.
@stan I just created a formula for all other currencies by tracking the eurusd or cadusd rate (or usdchf etc) and then dividing or multiplying it
such as =A8.Price*G11 for a stock I have in euros and I have the eur/usd rate in a corner
since eurousd is 1.21 right now I can just multiply the stock price by 1.21 automatically based on the pulled eurusd rate, and then it will give the real value (With the wrong $ symbol still so that I can remember I did that)
it works i guess
This Data Type works great and I have been using it for months. However, recently I have been getting the error message “We refreshed some of your data, but we couldn’t refresh all of it because we changed or removed it on our side. For data we can no longer refresh, we’ve kept the most recent results.”
Is there a fix OR a way to determine which stock data is NOT being refreshed ?