07-24-2018 09:14 AM
07-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.
07-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.
07-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!
07-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.
08-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.
08-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?
08-23-2018 08:26 AM
08-23-2018 08:32 AM
08-23-2018 08:36 AM
08-23-2018 09:00 AM
08-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."
09-14-2018 05:21 PM
11-20-2018 09:41 PM - edited 11-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.
11-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,
11-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.
01-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.
01-18-2019 08:49 AM
01-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
01-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.
01-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.
02-15-2019 04:18 AM
From what I see, this Stocks feature became available around March 2018.
Office 2019 came out around October 2018, correct?
Why is this feature absent from Office 2019 which I recently purchased? Consumers expect the latest features to be in the latest software version. If you bought a new car yesterday, wouldn't you expect it to include features that were in the previous year's model?
It seems misleading and deceptive to exclude this from Excel 2019 and to only include it in the 365 version, especially so when the feature became active in March (in 365) and the Excel 2019 was released many months later.
What does Microsoft say?
02-20-2019 01:25 PM
Hey TampaTrader, So sorry for the frustration you are feeling with your new Office 2019.
Unfortunately, features like Ideas and new data types are services-backed features that will only be available for Office 365, commercial users and consumers. Those features are not going to be available in Office 2019, or any other "Office [Year]" versions of Office.
However, soon you will have access to Stocks data types in Excel Online which will be available to anyone who is signed in at office.com.
02-21-2019 06:54 AM
Thank you for responding. Just to clarify, since you mentioned "commercial users" does MS Office Professional 2016 include this feature?
Does Excel online integrate at all with Excel 2019? When might online version have the stocks feature?
It seems as if I wasted money purchasing Office 2019.
02-21-2019 01:48 PM
To further clarify, no MS Office Professional 2016 does not include Data Types. We have commercial and consumer versions of Office 365 that include this feature, but the consumer or commercial "perpetual" licensed versions "Office [year]" will not have them.
You might have access to Data Types in Excel online now https://www.office.com/launch/excel sign in, and open a new workbook then go to the Data Tab. If you see Data Types you are good to go. If you don't see it yet, my apologies. On the web with big features we roll out to a percentage at a time to make sure everything is working. Right now we are only at 10% of our online customers. The percentage will increase and we hope to be at 100% in a couple months. Sorry for the inconvenience.
I work on the product team, but am passing along your last feedback about 2019 to the appropriate parties. Hopefully we can make this less confusing in the future.
02-28-2019 12:30 PM
I understand this isn't an ideal option, but I found out that:
Again, sorry for the confusion when purchasing Office, we are hoping to address this issue in the future.
07-06-2019 06:56 AM
I am a longtime MSFT office user on Windows and about ten years ago switched to the Apple Mac platform. I use Bloomberg professionally but wanted to have a portable copy of my portfolio that I could use away from my Bloomberg terminal. I was very excited to hear that real-time global securities pricing was added to Excel. But then I was shocked to discover that this functionality was only available on the Windows version of excel not the MAC version. There is absolutely no technical reason why this should be so, so I must assume that the reason is political - to deprive functionality to MAC users in order to drive them toward adopting Windows instead. Is any of this true and is there a work-round to getting real-time securities pricing in excel on the MAC or do I need to drop excel all together? Thanks in advance for responding if you can...
07-06-2019 07:28 AM
IMHO, the only politics these days is to push users to use cloud infrastructure. Doesn't matter which on which client they are - Excel Windows Desktop, MacOS, Android or browser. In particular, new data types are available for Mac if you are Office365 subscriber
By the way, same for Windows.
08-30-2019 05:12 AM
I am pretty pleased with this service. Data is reliable except for some funds in Europe where the price is lagging two days behind.
I Cannot find the right wording or ticker for the Euronext stockexchange indices like AEX Amsterdam XAMS, CAC40 Paris XPAR, or BEL20 Brussels XBRU. I tried over a hunderd times but nothing seems to work. Any Idea???