Jul 24 2018 09:14 AM
Jul 24 2018 09:14 AM
How have you been using the new Stocks and Geography data types in Excel? Since the new Stocks and Geography data types in Excel have been available to Insiders for a little while now, we want to hear from you! How have you been using them so far?
Leave a comment here with your favorite examples and samples, if you'd like to share.
Jul 24 2018 10:21 AM
Blake, when it appears back on my Insider Slow I'll give the feedback. I'm interesting mainly in Geography data types. First impression it works fine mainly for USA. I guess you are interesting more in scenarios - intention was to involve the new data types into market share stats.
Jul 25 2018 08:20 AM
I'm non-insider Office 365 subscriber, so I didn't see it so far!
I'm looking forward to using these new datatypes!
Jul 26 2018 10:15 PM
I have tried using this feature. I have found it to be useless - the linkage between the stocks and the relevant spreadsheet cell is not fixed, and when one needs to move, add to delete data the whole spreadseet gets messy and inaccurate.
Additionally, as long as there will not be an easy way to add multiple stock (as a list) building a portfolio of several hundreds of shares is very difficult.
There was no link to other stock exchanges - Europe, London, Swiss, Tel Aviv, etc.
Generally speaking, the Google spreadsheet solution (=googlefinance(....)) is by far better.
I have removed the add-on from my system.
Aug 08 2018 07:53 AM
Excel has been a fabulous tool for stock analysis and I really appreciate the Data>Stock feature. I am just new and exploring. The properties for auto updating stock information is not active. I have to manually refresh the data connection. Maybe I am missing something , kindly guide me.
Aug 22 2018 02:30 AM
I love it - set it up for keeping on top of my Stock Portfolio ( the timing was perfect, as previously was getting data from Yahoo via WebService function but they turned off access).
however, just installed my Office 365 Home on new Surface and the DATA TYPES are not there... not sure when they will appear or how I can force it... I clicked on File - > Account - > Update Options - > Update Now and it says I am up to date but NO DATA TYPES :-( (Also Stock/Geo) is not there if I try going into Customise Ribbon.
how can I get them back?
Aug 23 2018 08:26 AM
Aug 23 2018 08:32 AM
Aug 23 2018 08:36 AM
Aug 23 2018 09:00 AM
Aug 24 2018 09:02 AM
Thanks. I understand now why it was not showing unless I am on the Insiders channel.
It has been 2 months since the post here - > which is why I thought it was ready now.
"Note: June 12, 2018: This feature is being made available to customers on a gradual basis over several days or weeks. It will first be available to Office Insider participants, and later to Office 365 subscribers. If you are an Office 365 subscriber, make sure you have the latest version of Office."
Sep 14 2018 05:21 PM
Nov 20 2018 09:41 PM - edited Nov 20 2018 09:44 PM
Hi there, I recently saw a new region of buttons in 'Data' one day and gave it a try.
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.
Nov 21 2018 01:22 PM
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 here.
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,
Nov 21 2018 05:12 PM
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.
Jan 18 2019 07:31 AM
Hello James , The stock prices are required to be refreshed manually and understand I can auto refresh thru connection properties and also able to select time interval. However , the properties in data is not active.
I read the following in Help ..
((Refresh every n minutes Select this check box to enable automatic external data refresh at regular
time intervals, and then enter the number of minutes between each refresh operation. Clear this check
box to disable automatic external data refresh.))
However , the properties link is inactive.
Jan 18 2019 08:49 AM
Jan 18 2019 10:07 AM
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
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 Sub
Jan 18 2019 11:13 PM
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.
Jan 20 2019 02:40 PM
Stock quotes is really good.
I only wish there were more fields. The spreadsheet could get really useful if we could add more data from Morningstar such as short interest, fully diluted share count, float, free cash flow, ev/debt. debt/equity, revenue and other valuation and ownership information.
I am aware the above data could be categorized monthly, quarterly or annually for different years so it may be cumbersome. But perhaps we could write our own syntax to retrieve the information we need?
I tried =A2.[short interest] and, understandably, nothing came up. I guess a time period needs to be identified. Or something else is missing.
Is there a way to make progress on this?
Thank you for making this tool available.