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 fileStockDataType.xlsx

 

 

 

Get a quick email notice when a new Excel article is available

  • This field is for validation purposes and should be left unchanged.

This article was written by Jeff Lenning

13 comments:

  1. Melissa Cloutier
    Reply

    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.

    1. Jeff Lenning Post author
      Reply

      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

  2. Terrence
    Reply

    How can I add stocks to my data types? I’m using Excel 2016.

    1. Jeff Lenning Post author
      Reply

      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

  3. Steve Weaver
    Reply

    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?

    1. Jeff Lenning Post author
      Reply

      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

    2. Darryl Worth
      Reply

      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.

  4. Melissa Cloutier
    Reply

    I used the company names from your example to get that error:
    google
    msft
    disney

    Thank you

    1. Jeff Lenning Post author
      Reply

      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

  5. Tom Weisz
    Reply

    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

  6. Kevin
    Reply

    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

  7. Brian Smith
    Reply

    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.

  8. Samuel Reckford
    Reply

    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?

Leave a Reply

Your email address will not be published. Required fields are marked *

For security, use of Google's reCAPTCHA service is required which is subject to the Google Privacy Policy and Terms of Use.

If you agree to these terms, please click here.