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

 

 

 

Posted in ,

Jeff Lenning

I love sharing the things I've learned about Excel, and I built Excel University to help me do that. My motto is: Learn Excel. Work Faster.

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.

20 Comments

  1. Melissa Cloutier on November 28, 2018 at 8:28 am

    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.

    • Jeff Lenning on November 28, 2018 at 8:59 am

      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 on November 28, 2018 at 8:32 am

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

    • Jeff Lenning on November 28, 2018 at 8:54 am

      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 on November 28, 2018 at 8:41 am

    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?

    • Jeff Lenning on November 28, 2018 at 8:57 am

      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

    • Darryl Worth on November 28, 2018 at 9:02 am

      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 on November 28, 2018 at 9:40 am

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

    Thank you

    • Jeff Lenning on November 28, 2018 at 10:01 am

      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 on November 28, 2018 at 12:20 pm

    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 on November 29, 2018 at 9:37 am

    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 on November 29, 2018 at 10:09 am

    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 on December 11, 2018 at 2:57 pm

    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?

  9. Jose on January 23, 2019 at 4:58 pm

    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?

  10. Shankar on April 29, 2019 at 9:23 pm

    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)

  11. Parag on August 24, 2019 at 5:03 am

    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

  12. shailesh Kumar on September 16, 2020 at 1:09 am

    actually data is not shwing on real time basis rather it 15 minutes delayed so how i refresh data on real time basis

  13. Stan Stan on January 8, 2021 at 4:44 pm

    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.

  14. FC on February 16, 2021 at 3:36 pm

    @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

  15. THOMAS ROBERTSON on April 14, 2021 at 4:54 pm

    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 ?

Leave a Comment