Forum Discussion
Community Question - How have you been using the new Stocks and Geography Data Types?
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.))
Hi Utpaal,
You are not alone in wanting your stock data types to automatically refresh - we have heard the feedback from users like you and we're working on plans to support it now. As you've noticed, the stock data types are not integrated with the query properties that you were trying to set - that feature is for queries like those to SQL Server databases, OLAP cubes, or Excel workbooks.
You can refresh data types automatically using VBA today - the one line you need is
ActiveWorkbook.RefreshAll
which can then be wrapped in a timer. My full code is here - you can easily assign StartTimer and StopTimer to Excel shapes to create buttons.
Option Explicit Private Declare Function GetSystemTimeAdjustment Lib "kernel32" (lpTimeAdjustment As Long, lpTimeIncrement As Long, lpTimeAdjustmentDisabled As Long) As Long Dim TimerActive As Boolean Sub StartTimer() Start_Timer End Sub Sub StopTimer() Stop_Timer End Sub Private Sub Start_Timer() TimerActive = True Application.OnTime Now() + TimeValue("00:00:03"), "Timer" Range("C3").Value = "Auto-refresh enabled" End Sub Private Sub Stop_Timer() TimerActive = False Range("C3").Value = "Auto-refresh disabled" End Sub Private Sub Timer() If TimerActive Then ActiveWorkbook.RefreshAll Application.OnTime Now() + TimeValue("00:01"), "Timer" End If End SubHi James,
Thanks for taking time to be so helpful.
I tried to implement the macro to the best of my knowledge, but seems something is amiss.
I get this error..
*****
Cannot run macro.... The Macro may not be available in this workbook or all macros maybe disabled.
******
I modified the timer by a minute and I get this error after a minute, when it is trying to re run macro. After some search for solution, I tried the following but it the error persist.
I have enabled ( Trust access to the VBA project model )
I have enabled ( Enable all macros )In any case, I am happy to know that Refresh will be automated in the near future.
Thanks
Utpaal
