Community Question - How have you been using the new Stocks and Geography Data Types?

Microsoft

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? 

 

The dot operator and autocomplete are how you use fields in a formula

 

Leave a comment here with your favorite examples and samples, if you'd like to share.

 

Thanks!

27 Replies

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.

I'm non-insider Office 365 subscriber, so I didn't see it so far!

I'm looking forward to using these new datatypes!

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.

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.  

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?

Hi Ofer,
It sounds like you might be referring to the Stock Connector Add-in available from the Office Store. That is an add-in created by a third party developer, where this is native functionality that will soon appear by default for all Office 365 subscribers. If you see buttons for “Stocks” and “Geography” in the Data tab of your ribbon, then that is this feature.

Hope this helps!
James
Hi Sanjay,
The feature is still only available to Office Insiders at this time. 100% of Insiders Fast should see the feature, while we are still only enabled for 50% of Insiders Slow (aka Monthly Channel - Targeted) right now. More information on the Insiders program can be found at http://aka.ms/officeinsider.

If you have previously used the feature as an Insider and are unable to see it on your new computer, I recommend verifying that you have signed in with the same account that you registered for Office Insiders. If you want to guarantee the feature appears, you will need to join Insiders Fast due to the 100% availability there that I mentioned above.

Thanks!
James
Hi Utpaal,
You are correct that you are only able to update the values manually via Data > Refersh at this time. However you can write a macro to refresh automatically using the existing RefreshAll command.

If we were to add an ability to refresh automatically in the future, can you share how you would like it to work? Some things to consider would be the interval at which we refresh and how you would like to switch back to manual refresh, and whether that needs to apply to everything or just some stocks.

Thanks for your feedback!
James
When will the Stocks and Geography Data be available to the general Office 365 users? I am using for the past year Google sheets and their functionality is simply superb. If you at Microsoft want to maintain your clients, you better move faster.

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.

 

https://support.office.com/en-us/article/Stock-quotes-and-geographic-data-61a33056-9935-484f-8ac8-f1...

 

this says...

 

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

Hello James Spotanski, 
 
I have been using excel for stock analysis since many years. Unfortunately I use i3 Intel processor and required to open 25 excel files simultaneously and use macros to update EOD data of individual file from an excel dashboard file and return the updated results back to the dashboard file. This process is smooth in office 2007, but did not work well with office 365. I need a better processor. So for temporarily , I have exited using office 365. 
 
I have been using google spreadsheet for live trading decisions, but recently found issue with stock price updations. For sometime that I used office 365, I found the Stock data types very useful. I think, the user should have an option to update prices at a certain interval and the refresh needs to be automatic. There should be a selection for data updation interval like 2 min, 5 min, real time and so on...I would combine my analysis with latest stock prices and accordingly trade with help of excel. I can only speak for stock feature and cannot comment on other data type.
 
Thanks
Utpaal

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:

 

Table headers changing.PNG

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

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

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.AutoRefresh issueAutoRefresh issue

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 Sub

 

 

 

Hi 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

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.