Home
Microsoft

Preview of Stocks and Geography, New Data Types in Excel

Hundreds of millions of customers have created truly amazing solutions in Excel – and they’ve done all this while working with cells that most often contain (or evaluate to) just text and numbers. What becomes possible if Excel evolved to a world where cells weren’t limited to just a single, flat, piece of text, but could instead hold a far richer concept? Today we are announcing a preview of new data types that will, over time, fundamentally transform what’s possible in Excel. We’re starting this journey with Stocks and Geography as the first two AI-powered data types, which will help users quickly turn complex data into action.

 

To see new data types in action, we’ll start with some text in cells – in this case the text represents countries from different places in the world (please forgive the typos – we’ll come back to those in just a moment…) -

 

Image1.pngThis is just text in cells at this point… 

 

Just click the data type we want to convert the text to (Geography in this case)…

 

new data types.jpgYou can find this feature on the Data tab…

 

 

  …and the cell is converted! It now holds a new data type – representing countries in this case. This content is rich – the cell isn’t holding just a single piece of text anymore – it’s been transformed into a new kind of value which has lots of information. Notice the icon next to the name of the country, signifying that this cell holds a data type – clicking that icon will display a card showing all the data in that cell:

 

Image3.pngCards are the way to view the full contents of the cell

Excel uses Microsoft Knowledge Graph, the same intelligent service that powers Bing, to provide the data. It recognizes, in context, what is meant by your text and converts that to the right type of data. It even fixed the typos and capitalization mistakes in the country names!

 

 

It’s easy to work with this data in the grid – if you happen to have this data in a table, you may notice a widget that lets you grab fields and pull them into a column of their own. 

Image4.pngIt’s easy to pull fields out to a column of a Table – Excel writes the formula for you

And Excel didn’t just copy that data out of the cell – it dereferenced it by writing a formula for you! You can use them in any function as well – just use the dot “.” operator to get a list of fields – as shown below, this time shown using City data. These are full, calculation enabled, first class data types in cells!

 

 

Image5.pngThe dot operator and autocomplete are how you use fields in a formula

 

These work with other features in Excel as well – for instance, here’s an example with all the U.S. states – with some U.S. Census data showing the % of population change pulled out into a column. You can create a Map Chart in a single click.

 

Image6.pngA map of the U.S. showing % population change

If you want to filter that column of States by time, you can tell the Filter to operate on Time Zone by changing the selection from the drop down at the top

 

 

Image7.pngThe Select field dropdown lets you work with a field from column of data types

…and just like that, the table and map update.

Image8.pngThe filter is applied, and the map updates, without needing to pull fields into the grid

It’s not just States or Countries either – we support things like Zip Codes, Cities, and other types like Stocks, Index Funds, and other financial data (to use these, just type in some ticker symbols, fund names, or company names, and hit the Stock button). This data is refreshable as well – for example, many of the Stocks will fetch up to date prices when the market is open and you Refresh.

 

As you try this feature, you may notice the intelligent conversions sometimes aren’t sure what to convert to. In those cases, Excel will ask you to specify which data type should be returned from the service. For example, the city Portland works fine when it’s a list of other cities that are nearby, but when it’s in a blank grid, with no other textual context, then Excel will ask you which Portland you meant. You can always change the data type via the right click menu as well. Read more how to use data types in this article.

 

Image10.pngAmbiguous cases are detected

With the introduction of these two data types, Excel moves beyond just Text and Numbers -into much richer data types that don’t always have to be single, flat, value. For now, we’re starting with these two domains of data, but we’ll be adding more over time, including those, based on data unique to your organization. Also, data types is only one of a new wave of intelligent features coming to Excel - Insights was another that we launched recently. Read about Insights and other intelligent features in Excel.

 

 

To stay connected to Excel and its community, read Excel blog posts, and send us ideas and suggestions for the next version of Excel through UserVoice. You can also follow Excel on Facebook and Twitter.

           - John Campbell, PM Manager, Excel

 

The new data types is starting to roll out in preview to Office 365 subscribers enrolled in the Office Insiders program, in the English language only, this month.

29 Comments
Occasional Visitor

I'm an Office 365 subscriber, Pro Plus I think.  How do I get the new Stocks and Geography data?

 

Thanks,

David  

@DAVID BAHR

 

You can follow the instructions in the below article.  Be sure to pay close attention to the portion about editing the XML configuration in Notepad:

 

http://www.knowledgewave.com/blog/office-365-tips-how-to-become-a-microsoft-office-insider

Microsoft

@DAVID BAHR@Matt Mickle has got it right - to try it now, you need to be a subscriber who is getting the fast version of the Office insider builds. We're steadily rolling it out over time to more and more insider customers - and making improvements based on customer usage and input along the way. Eventually it will be deployed to all subscribers.

Occasional Contributor
Would be useful to know the version number that gets this feature. I'm on Excel build 9126.2116 (click to run, monthly channel targeted) and don't see this.
Senior Member
Wow! This is really something! Great job Excel team!
Microsoft

@Calum Steen this is for folks who are on the "Insider" builds (formerly known as Insiders Fast). It will come to the monthly targeted channel too, but is currently only in the Insiders ring. See What is Office Insider for more information. One final thing to keep in mind - we deploy it to each channel over a period of time until eventually all users have the feature turned on - so some people may currently be on the Insider channel but may not see it just yet - they should soon as it gets turned on for more and more Insiders over the coming days.  

Contributor

As I'm just on a monthly channel.

What is the version number to get this update. Actually I'm on the 1803 version (built 9126.2116)

Microsoft

@Mehdi HAMMADI you can't use it currently on the monthly channel, it's not just a matter of build number. See my comment above - you'd need to switch from the monthly channel to the Insiders channel. On the insider channel, I believe the more current builds that have it are based off of 9226. That said, it's still rolling out. What I mean by that is that even if a customer has the correct build, the feature may be turned off - we turn it on for a wider and wider set of customers, monitoring for quality issues as we go, in an effort to reduce bugs/issues. So there may be some customers who are using Insiders builds that don't see it yet - in that case, please be patient a little longer as the feature will light up for 100% of Insiders soon, and then will begin rolling out to the monthly channel after that. 

Contributor

Thank you for the reply and I will waiting for It.

Occasional Contributor

 I'm now on Excel Version 1805 (Build 9327.2006 click-to-run) but this feature remain elusive...

This is absolutely great functionality and I really love how it's implemented.

HOWEVER, Excel currently crashes every time the Stocks data is refreshed... Initially, the stock data is picked up without issues but, on the attempt to refresh, Excel crashes and restarts. I have tried under variety of scenarios but always the same.

 

Another user has reported this issue at:

https://answers.microsoft.com/en-us/msoffice/forum/msoffice_excel-mso_win10-mso_365hp/refreshrefresh...

The above issue has now been fixed (requires updating to the very latest version) - thanks a lot to MSFT team.

Microsoft

@Leonid Klimov - thanks again for bein an Excel customer and for taking the time to report the issue - as of the roll out from this morning, everything should be fixed now, glad to see that you got the change. @Calum Steen, you should be seeing the feature if you are on the "insiders" build as noted in the replies above - please let me know if you still aren't seeing it - all Insiders should have had access since earlier this month. 

Occasional Visitor

This is a great addtion by Microsoft developers...wish I could take advantage of it. Followed the instructions for becoming an "Insider" and got an error when attempting to run the Development Tools setup program, with the correct configuration.xml file:

https://www.reddit.com/r/Windows10/comments/8szehd/office_365_insider/?utm_content=full_comments&utm...

Occasional Visitor

Thank you for making available the New Data Types in Excel for Stocks and Geography. The available data is very useful but more is needed. Please consider adding EPS, dividends, and yield to make this an even greater feature.

 
Microsoft

@Marvin Miller thanks for the reddit link and for reporting the issue. I've asked someone from the install team to take a look and report back as it sounds like there's an issue.

 

@Steve Weaver I'm glad you're finding it useful so far - thank you for the suggestions! We'll take these into consideration as we work on plans to continue to improve the feature. 

Microsoft

@Marvin Miller Sorry to hear you had an issue installing. If you're still having trouble, please try downloading the latest version of the ODT, available here: https://www.microsoft.com/en-us/download/details.aspx?id=49117

Stock data looks good so far.  Had an issue getting data for an etf.  Doesn't look like that data is available yet.  Excel defaulted to the next closest stock ticker.  I also agree with @Steve Weaver above that we need EPS yield.  

 

I like the progress.  I can now start to migrate sheets I created in Google sheets over to Excel.

Microsoft

@Demetrius Frazier thanks for trying the feature out - I'm glad you're finding it valuable. Sorry to hear we didn't have the ETF you were looking for. Please feel free to let me know (offline if you'd like) what symbols or properties you were looking for. We're working hard on making the feature better, and we'd value your input if you'd like to share the specifics. 

@John Campbell  The three I tried that were successful were $IEUR, $IEUS and $SDIV.  The cool thing about $SDIV was that it has multiple listings across exchanges and a session window popped up to allow me to choose the right one.

 

The ones that didn't work however were $HYG, $XLE, $XLF.  These are pretty popular funds and it was odd that they didn't pop up.

Microsoft

Thanks for the additional details @Demetrius Frazier - I'll pass this along to the PM who is working on the financial data types so the team can consider this as they evolve the feature - your feedback is appreciated! 

Microsoft


@Demetrius Frazier - I didn't run in to any problems with HYG, XLE, or XLF when I tried converting them.  Could you let me know what exactly you were typing in when you tried to convert?  When I included the "$" at the beginning, I wasn't able to find results for anything (that's something we can fix).  But when I typed in all of your symbols I was able to get results after using the Data selector pane.

 

image.pngimage.png

 

Thanks for the responses!

 

@James SpotanskiYour visual helped a lot.  I created a column of ticker symbols, no $.  I did not create a table. So when I selected the tickers and hit the "Stocks" button it converted some but not all.  But when I converted the column to a table first it worked perfectly.  That made the difference.  I can see what you see now.  Thanks for the help!

Occasional Visitor

I still don't have the Data Types feature on my Excel Data tab.  The Help page indicates that it was supposed to be rolled out over several weeks beginning June 1, 2018 - it's now nearly 3 months later.  My Excel version is 1808 Build 10730.20088.  

I was on the phone w/ Tech Spt regarding this, and after speaking with 5 people for over an hour - who had no idea what I was talking about - they couldn't help me.

When can I hope to see this feature?

Microsoft

I'm really sorry to hear about the difficulties @Joyce Family

 

It sounds like we all at Microsoft need to do a better job of getting tech support routed to the right places - I'll be following up with people from around the company to explore how we can do better w.r.t features in O365 and fielding tech support calls.

 

In terms of the feature availability, everyone that is on the fastest Insiders should have the feature, and many of the people that are on the monthly updates should have it. Note that it is only rolled out to a percentage of monthly customers so far. We should have something to say in the next couple of weeks about when the feature will be available to all users of Excel on O365.

 

 But until we have a firm announcement about full availability, the only way to ensure you have the feature is to make sure you are on the fastest update (insiders) channel for O365 (there are some posts on this article with a few links that you may find useful). If you'd like to do that, but are running into trouble, please message me offline and I'll see if I can help you through it.

I have been using this functionality for a few weeks and every once in a while I find a few stock shares that will not show up right away.  I assume because they are newer listings?

 

Two examples I have found are:

Tilray (Stock ticker TLRY)

Nio (Stock Ticker NIO) <--- Just listed this week.

 

I use the functionality to not only monitor my current holdings but as a watchlist hence the IPO stocks.

Occasional Visitor

I am so looking forward to this functionality!  However, I can't seem to turn it on.

 

Following the suggestions above and on other MS support sights, I have:

 

  1. Installed O365 - Home
  2. Checked Windows settings - Apps & Features: "Microsoft Office 365 -en-us"
  3. Turned on OIS (trust center settings -> Privacy -> "Get designs....."
  4. set myself up as fast insider (level = "Insider")
  5. Performed an "Update Now" - received 1810 (build 10910.20007)
  6. Created a table with a few ticker symbols; selected those ticker symbols
  7. held my breath..... (OK, this wasn't on any support site)
  8. went to the data tab - but no "Stocks & Geography" icons

What am I missing?

 

Thanks,

Ken

Occasional Visitor

 Reboot!  And then it started to work.  I forgot - when all else fails :-)

Microsoft

@Demetrius Frazier sorry to hear about the delay in getting the data - thanks for sending the feedback along. We're looking into improving the feature by getting the updates for new financial instrument listings in much faster. 

 

@Ken Wiens Glad you got it turned on - sorry for the trouble. I'm working with the teams responsible for the engineering systems to see how we can improve the process moving forward. There's been a number of comments on this blog, and feedback in other channels on the issue of making it easier to get to the preview features, or at least in helping understand why they aren't showing up. This is something we're going to be working on.