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!
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.
We’ll explore the Stocks data type with the following steps:
- Enter company name or ticker
- Add columns
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.
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.
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