Forum Discussion
Community Question - How have you been using the new Stocks and Geography Data Types?
Hi there, I recently saw a new region of buttons in 'Data' one day and gave it a try.
Good
Stock data seems fine, since we mostly use US and HK stocks, but does not seem to cover exchanges such as Thailand (e.g. thai oil) or Malaysia (e.g. berhad). Vietnam companies appear however.
Geography: Useful to get macro data easily, but I dont know why obtaining a national anthem would be useful, but who knows.
Not so good
The normal functions of getting data is fine, but it seems to add a lot more computation/refreshes.
Additionally, some of our table headers are getting converted:
This is a table of Trades for portfolio. "Strike" becomes a Data Type, and automatically becomes some company's name "Strike Energy".
Time becomes a name of an ETF. I did not put a Data Type in this worksheet at all, but it appear out of nowhere.
Anyone facing the same issue? I like it, but I want to switch this off until it is fully stable - the Custom Ribbons tab in Options does not give me the option to.
Many thanks to anyone who knows how to prevent this.
It's correct that we have data for US, Hong Kong, and Vietnam stock exchanges, but not Thailand or Malaysia. You can see our full list of supported exchanges https://support.office.com/en-us/article/about-our-data-sources-98a03e23-37f6-4776-beea-c5a6c8e787e6.
Regarding table headers being converted, we do not allow Data types to exist in the headers of Ctrl+T Tables, but it looks like in your example they may exist in the cell that holds an autofilter for the column below it. If you would like to convert these cells back to text, you can right click > Data types > Convert to Text. Data types shouldn't "appear out of nowhere", but if you copy a cell containing a data type and then type over it, we will try to convert the text.
Lastly regarding the ribbon customization, I was able to reproduce this issue and have opened a bug. Thanks for the report!
Can you share any more details on what you mean by "add a lot more computations/refreshes"? The formulas that reference data types should be very fast since the data is already in your workbook, and a refresh will only occur when the user chooses Refresh All on the Data tab.
Thanks again for the feedback,
James
Hi James
No worries about the data, I like that it has a good coverage already.Regarding the table headers, it was not copied over from a cell that previously had a Data Type.
I also cannot convert the cells back - right clicking on the cell does not show a 'Data Type' or 'Convert To Text' option. The only options I see are the same as a normal cell. The table is in a separate worksheet from that of the ones I use to test the Data Types, so it did seem to appear out of nowhere.
Other than unable to hide the customization, is there a way to turn it off for the time being?
The computation seems to occur when the Data Types seem to query Knowledge Graph/Bing for the probabilistic result (i.e., Strike becoming Strike Energy, msft becoming Microsoft). I am also using Last Traded Time and Last Close fields.
Additionally, we do have Bloomberg fields getting data feed in other sheets. No volatile functions are being used, with exception of one Cell function to retrieve the filepath.
On save, the Excel does do a "Refresh All". With bloomberg on at the same time, the "Calculating.. x%" on the bottom right shows - it previously did not do that. Perhaps a way to toggle auto-refresh for some of the data feeds?
I think one or some of the users have suggested refreshing in N intervals, but I would guess that would be a big workaround on the queuing/scheduling for data feeds. Better if users can have a toggle, then write a macro for every N minutes to update it themselves.
The high computation only happened once Data Types were introduced - I also checked with other colleagues which had the feature pushed (without Bloomberg) that their worksheets were taking some time to compute as well. Thus until then, should we encounter any issues (e.g. not sure if its a Data Type or bloomberg or other data source affecting it), we can do some A/B testing to identify which one is causing it correctly.
Cheers
Thomas- Hope someone can share the macro code (( write a macro for every N minutes to update it themselves.))
