Preview of Stocks and Geography, New Data Types in Excel
Published Mar 29 2018 09:00 AM 70.9K Views
Microsoft

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…) -

 

This is just text in cells at this point…This is just text in cells at this point… 

 

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

 

You can find this feature on the Data tab…You 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:

 

Cards are the way to view the full contents of the cellCards 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. 

It’s easy to pull fields out to a column of a Table – Excel writes the formula for youIt’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!

 

 

The dot operator and autocomplete are how you use fields in a formulaThe 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.

 

A map of the U.S. showing % population changeA 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

 

 

The Select field dropdown lets you work with a field from column of data typesThe Select field dropdown lets you work with a field from column of data types

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

The filter is applied, and the map updates, without needing to pull fields into the gridThe 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.

 

Ambiguous cases are detectedAmbiguous 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.

70 Comments
Copper Contributor

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

 

Thanks,

David  

Bronze Contributor

@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.

Brass 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.
Copper Contributor
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.  

Brass 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. 

Brass Contributor

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

Brass Contributor

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

Copper Contributor

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

Copper Contributor

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. 

Copper Contributor

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

Copper Contributor

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

Copper Contributor

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. 

Copper Contributor

@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!

 

Copper Contributor

@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!

Copper Contributor

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.

Copper Contributor

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.

Copper Contributor

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

Copper Contributor

 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. 

Deleted
Not applicable

i have done everything I can to get the data types I am a fast insider but still don't have the option and I need the geography data 

Copper Contributor

WHEN IS THIS GOING TO BE AVAILABLE.

 

I COULD USE THIS TODAY.

 

DENNIS

ALREADY A MEMBER IF THE INSIDERS PROGRAM

dennisweinstein@gmail.com

Microsoft

@Deleted @Dennis Weinstein, and others that are trying to get the feature turned on - good news! We just announced at Ignite last week that this feature will be making its way to general availability over the next few weeks.

 

In the meantime, verify that you are on the Insiders channel, and if you still aren't seeing the feature try exiting all Office apps and restarting Excel - sometimes a reboot will help the features show up.

Copper Contributor

Great new feature, can't wait for it.

 

Just such a shame they still can't get the Autosave feature to work properly. Even though everything is saved in my OneDrive folder (as instructed), autosave still doesn't appear and the file never autosaves.

 

Great work.

Copper Contributor

Have been looking forward to the new data features for a while. In the last update, Oct 2018, I got the new GUI menu ribbon. Strangely, some days it's on and some days it reverts to the old GUI? The temperamental menu switching is over all the products in the subscription: Word, Outlook, Excel etc. Then, for Excel, I checked for data types and I still don't have stocks and geography in the menu ribbon. :( Called support, they told me to reinstall Office. I did, only to find they had me reinstall the same build I had originally??? Again no new data types. (sigh) I have Office 360 Home premium subscription. I am based in Montreal, Canada. Maybe the rollout has not reached us yet? My current Excel office build is Version 1809 (build 10827.20181) click-to-run.

 

In the new data sets do you cover all exchanges and geographies, or only the US?

 

Many thanks for any indications or help.

Microsoft

Hi Alan, I'm seeing two issues mentioned in your comment. First was the new icons, where sometimes you would see the old icons and others you would see the new versions. That's certainly not expected, but that update comes from a team other than Excel, so I'll need to talk with them to see if they're aware of why this might be happening.

 

Second is about the new data types. In order to see those options appear in your ribbon right now, you'll need to make sure that (1) you have an English editing language for Office enabled (note that this is independent of your UI language), and (2) that you are an Office Insider. If you are on the Monthly Channel (Targeted) ring, there is a 50% chance that you will get to see the feature now. That said, we hope that the feature will be generally available to all users in the next month or so. Information about the "Insider" programs can be found at http://aka.ms/officeinsider.

 

Lastly, regarding the coverage of our data, we do support many international financial exchange and global geographic data. The full list of supported stock exchanges is here: https://support.office.com/en-us/article/about-our-data-sources-98a03e23-37f6-4776-beea-c5a6c8e787e6 Thanks for your comments!

 

James Spotanski

Copper Contributor
Thank you James. It appears that the menu old vs new has been resolved. Unfortunately, the Stock and Geography data types on the menu ribbon are not active or present Neither is the "Ideas" AI function. Office had an update last week. My build version now is Office 365 MSO (16.0.11001.20070) 64bit. You mentioned the editing language set to "English", I am not sure what was meant by that. Do I have to set the English to the US English language version versus say Canadian English? I have US English set as my default and Canadian English as a secondary (enabled but not default). However, on your point (2), I am not an Office Insider member. Maybe the new new data types are active only for Insiders for now... I was under the impression that October 2018's Office update would broadcast as a general release to all MS Office subscribers. Thank you for your help.
Microsoft

Regarding the editing languages, any form of English will meet the criteria to enable the data types, so either Canadian English or US English is fine. The feature right now is still limited to Office Insiders, but we're hoping to make the feature generally available in the very near future. Regarding build numbers and updates - this can actually be a bit confusing these days. It's not always possible for us to verify whether you should see a feature or not based purely on a build number, since we have the ability to selectively enable features for an arbitrary percentage of our users in order to run experiments (e.g. if we turn on the feature for 50% of our users, do they start crashing more?).

 

This also means that users will occasionally see new features appear without having to install a new build, since we can turn on features as soon as they're ready for you. Right now, the only way to guarantee that you'll see this feature immediately is to become an Office Insider. If you'd prefer to stay in your current ring, that's great too, and I expect that the feature will be generally available to all users in the near future.

 

Thanks again,

James

@James Spotanski,

Right now, the only way to guarantee that you'll see the feature immediately is to become an Office Insider

Not always. Dynamic arrays is a good example - I'm not sure if it's available for all Office Insiders right now. Same with other functionality - it's not necessary what you as Office Insider receive new functionality with next update after the announcement. On the other hand yes, you wait not few months as on Monthly channel, but few weeks. If you are lucky few could be equal to zero. 

 

Microsoft

Thanks @Sergei Baklan, that is correct.  I updated the wording in my post to be more clear that I am only referring to this feature, since it is the case that this is available to 100% of Insiders right now.  But as I also mentioned, we are often running experiments where only some percentage of an audience can see a feature.  So right now, Data types are available to 100% of Office Insiders, and 50% of the Monthly Channel (Targeted) ring.

 

-James

James, to confirm by practical experience - just today new data types appeared on my monthly channel build without any updates, exactly same build as it was on previous week.

Does someone know what is the logic in showing time zones for the Geography data types? UK for example:

image.png

For the cities there is the mix of daylight saving times (BST, ITZ,..) and absolute time, for the countries only absolute time (GMT). Actually all of above are in exactly same time zone, in UTC+0 as for today. It's practically impossible to filter/sort by time zones if we take locations with actually different time zones.

 

Microsoft

@Sergei Baklan, Thanks for raising this concern about consistency within the Time Zones field. Unfortunately, I don't have a good answer for the logic behind why inconsistent time zone names are listed in the field. This is more of a byproduct of how we are curating our data from the web. It is most likely that for these subdivisions we found multiple time zones attributed to them with the various names, and for the countries we may have found the data from a different sources that specifically only mentioned GMT. Again, thanks for raising this, we have discussed what we can do here to help enable you to Sort and Filter (and do math on times) with ease, but we haven't found a great solution. What would you want to see in this case? (Just GMT or everything?) What about regions that span multiple Time zones? I suspect that our solution to this isn't time zone specific, but really relates to how we provide sort and filter on these appended lists. 

@KayceeSue, thank you for the clarification. With that Time Zone(s) field is for information purposes only, like Leader(s) or Description. Perhaps useful in some scenarios, but no way to do any further analysis. For that it could be suitable the field with Offset Time (UTC plus minus offset). I guess the latest may be received based on latitude, longitude and current date. On the other hand not sure such field is demanded, but who knows, may add to user voice.

Copper Contributor

Hi - just installed Office Home 2019 for Mac and signed up for the fast insider ring.. however no Stocks data type available.

Is this meant to be enabled for Mac users or Windows only?

Microsoft

@WillBrad Thanks for being eager to have the Stocks data type! Right now it is only available on Windows Desktop. It will be coming to Mac for the fast Office Insider ring soon. 

Brass Contributor

maybe i missed some thing but we downloade few weeks ago a French edition of Office 2019 (Windows Desktop) and we did not find the Data Type group. 

Brass Contributor

Using an O365 version of Excel I tried the Stocks Data Type to retrieve data about Algerian's companies listed on the "Algiers Stock Exchange" but unfortunately no data was found (and we have just 05 companies). How to fix that or how to tell Bing where it can find the data.

Copper Contributor

The STOCKS option under the Data Toolbar might be a great help for me in keeping track of my investments.  I do not have any individuas stocks, but it appear to be able to handle all my mutal funds.  Currently I am using an Internet posted Google spread sheet to move the price data into my Excel sheet, but I will go over to use the Stocks option later when I feel that it is stable enough.

 

I would like for the STOCKS to handle Currency Exchange Rates as well, but I have not been able to make it work.  I have entered the currencies in the standard ISO format, such as USD/EUR, but will get a USD stock tickersymbol for 'ProShares Ultra Semiconductors', and if I enter USDEUR I will get a #FIELD! error.  Are there any plans to include currency rates in STOCKS, or to add another function, such as CURRENCIES under the Data Toolbar?

 

In the meantime I will use Google or import data from X-rates.com.    

Microsoft

@Mehdi HAMMADI - there are a few things you'll need in order to see the Data Type group in the ribbon.  First, the feature is only available to Office 365 subscribers, so if you are using a one-time purchase of Office 2019, you will not see the feature.  Second, you will need to have an English editing language enabled since the data in Data Types is only available in English at this time.  Regarding the Algiers Stock Exchange, we do not support it at this time, but will take it in to consideration.  You can view an up to date list of our supported exchanges here

 

@BoBoSlacke - is there anything specific about the Stocks data type that you feel is not stable enough yet, or are you waiting just to be safe?  Regarding currencies, we're working on adding exchange rates in these forms (e.g. USDEUR) now and they should be available in the next few months.  Apologies for this being confusing right now

 

Thanks for your comments!

James

Copper Contributor

My reason for not quite trusting the 'Stocks' is that some Mutal Fund prices does not agree with data from GoogleFinance, Morningstar, Yahoo!Finance, and my portfolio managers data.

For example, at noon today BWLYX was priced at $27.45 by all the above, but your price was $27.57, which is $0.12 higher.

CSSRX was $15.77, you had $15.84 - +$0.07

CLDIX was $15.50, you had $15.59 - +$0.09

 

Thank you for the information about that currencies will be included at a later time.  I am looking forward to have a completely integrated system for Stocks, Mutal Funds, and Currency Exchanges. 

Version history
Last update:
‎Apr 03 2018 02:43 PM
Updated by: