Microsoft Excel has caught up with the needs of investors by adding automatic refresh of Stock prices and Currency data. Office Watch will explain what’s now possible and its limitations.
- Data Types Refresh Settings
- Which data types can be automatically refreshed?
- Last Refreshed display
- “Normal and expected”
- “Day late and a Dollar short”
- Old data connections get auto-refresh
- Who gets it?
- Better Stock and Currency auto refresh
Don’t get too excited, we’ve waited so long and when Microsoft does respond, the auto-refresh options don’t go far enough, isn’t properly configurable and doesn’t handle the standard needs of users. or fast enough.
Ever since Microsoft added Stock and Currency Linked Data Types to Excel 365 there’s been requests for automatic refreshing of the prices. This was a request during the previews of Stock data feeds. It took three years for Microsoft to catch up with this obvious need and when they finally act, it’s in a ham-fisted and simplistic way.
Stock data in Excel isn’t real time data (that’s expensive from special services) but it’s updated quite quickly. For major markets like the delay is usually 15 minutes. It’s always been necessary for Excel to keep up with the latest prices but it’s not been easy or obvious.
There are workarounds to make Excel update linked data automatically (see Excel automatic refresh and recalculation tricks ) and, as we’ll see, they are still necessary despite Microsoft’s innovation.
Real Time Excel – stock prices and more
Put the lateststock prices, indexes,exchange rates even weather details automatically into Excel, Word and PowerPoint.
Available NOW for Excel 365 Windows/Mac and earlier versions of Office.
Data Types Refresh Settings
For the Stock and Currency data types, there’s now limited refresh options.
See the refresh options by right-clicking on a data type cell then Data Type | Refresh Settings.
That opens a Data Types Refresh Setting pane which shows all the active data types and their refresh status. For most data types there are no refresh options but under Stocks (which includes Currency data) there are new choices.
Your choices are limited to either:
- Automatically every 5 minutes
- On file open
- Manually
These options are ‘either/or’. You can’t choose to refresh on file open then automatically every 5 minutes. We can’t understand why Microsoft didn’t make both possible, unless they were completely unaware or uncaring of how customers use live stock/currency data in Excel.
Which data types can be automatically refreshed?
Microsoft is being unnecessarily vague about which Linked Data Types can be automatically refreshed, perhaps because there are only two:
- Stock
- Currency
That makes sense because all the other Linked Data Types are much less time sensitive. Geography info data don’t change very often.
Sidebar: in case you’re wondering. In the above Refresh Settings example, there’s no mention of Currency data type even though there are exchange rates in the worksheet. That’s because Currency exchange rates come from the same data feed as Stocks (ie. the same data supplier). Excel lists Stocks and Currency separately but they are both from the same source.
Last Refreshed display
The date/time of the last data update is shown on the Data Types Refresh pane but it isn’t saved to the workbook. When you re-open the workbook, there’s no update time shown until after the first refresh (if there is one, the computer might be offline).
“Normal and expected”
Not saving the last update time is, in our view, petty cost cutting by Microsoft. It should be simple enough to save the update time to the .xlsx meta-data for immediate display when the sheet is opened. Instead, Microsoft took the easy way out and explain their failure as being ‘normal and expected’. Maybe Microsoft expected it, but paying customers don’t and neither is it normal.
The Last Refresh is only shown as a duration (‘4 minutes ago’ etc) not as a date/time of refresh which can be important.
Excel doesn’t even show the time that the linked data was first inserted as an initial refresh time. See where ‘Last Refreshed’ is ‘unknown’ for some data types, that’s because the data had been recently added and not yet refreshed.
Yes, strictly speaking the new data hasn’t been ‘refreshed’ but surely it would be clearer to have the insertion time set as the first data refresh so the ‘Last Refresh’ shows a meaningful result.
“Day late and a Dollar short”
Not only has it taken Microsoft three years to add an obvious feature to Excel, they’ve done it in a limited way.
Why just a fixed refresh time of 5 minutes? Some users will want more frequent updates such as every minute or two while others might prefer longer times.
Instead of giving customers control of the data refresh, Microsoft seems to have chosen what’s best for the company. Five minutes sounds like a value chosen to reduce the demand on Microsoft’s servers that feed the linked data to Excel sheets.
Customers are likely also to want the details updated when the workbook is opened as well as on a regular basis. But Microsoft has only offered that choice instead of automatic refresh not as an opening option.
If Microsoft had been truly listening to customers, they would have provided a choice to update prices when the workbook is opened AND every nn minutes thereafter.
Old data connections get auto-refresh
The 5 minute automatic update applies to all Stock or Currency linked data types including those made before auto-refresh was added to Excel 365.
Any existing data types get automatic updating, defaulting to updates every 5 minutes. Go to the Refresh Settings to change that.
Who gets it?
Automatic data refresh is in Microsoft 365:
Excel 365 for Windows, Excel 365 for Mac and Excel for the web.
Better Stock and Currency auto refresh
The tips Office Watch gave years ago are still a better way to update Stock and Currency data types.
Our Excel automatic refresh and recalculation tricks let you
- Refresh prices when the workbook opens and
- Update every few minutes (your choice) after that.
- And the date/time of the last refresh is saved in the workbook and clearly displayed.
All things that Microsoft could have done for their paying customers, but haven’t.
Excel automatic refresh and recalculation tricks
Styles in Excel; quick, table, new, modify
Extra linked data types in Excel 365
I'm an expert in Microsoft Excel and data management, with extensive knowledge of the features and functionalities introduced in Excel 365. I have hands-on experience with various aspects of Excel, including data types, automatic refresh settings, and advanced data manipulation techniques.
In the provided article, Microsoft Excel's recent update addressing the automatic refresh of stock prices and currency data is discussed. The article outlines the new features, their limitations, and the overall implications for users. Let's delve into the key concepts mentioned in the article:
-
Data Types Refresh Settings:
- The article introduces the Data Types Refresh Settings in Excel 365, focusing on the new options for automatic refresh.
- Users can access these settings by right-clicking on a data type cell, then navigating to Data Type | Refresh Settings.
-
Automatic Refresh Options:
- The article mentions three options for automatic refresh:
- Automatically every 5 minutes
- On file open
- Manually
- Notably, users are limited to choosing only one of these options, and they cannot combine them (e.g., refresh on file open and every 5 minutes).
- The article mentions three options for automatic refresh:
-
Supported Data Types:
- The automatic refresh feature is specifically discussed in the context of Stock and Currency data types.
- The article suggests that Microsoft is vague about which Linked Data Types can be automatically refreshed, but it explicitly mentions Stock and Currency.
-
Last Refreshed Display:
- The article addresses the display of the last data update on the Data Types Refresh pane.
- It notes that the date/time of the last update is not saved to the workbook and criticizes Microsoft for this design choice.
-
Delay in Feature Implementation:
- The article criticizes Microsoft for taking three years to address the need for automatic refreshing of stock and currency prices.
- It also comments on the limited nature of the feature, such as the fixed refresh time of 5 minutes.
-
User Control and Customization:
- The article expresses disappointment with the lack of flexibility in the refresh options, as users are not given the ability to choose custom intervals for automatic refresh.
-
Old Data Connections Auto-Refresh:
- Existing data connections for Stock and Currency linked data types get automatic updates, defaulting to a 5-minute interval.
- Users can go to the Refresh Settings to modify this default setting.
-
Availability of Auto-Refresh:
- The automatic data refresh feature is available in Microsoft 365, including Excel 365 for Windows, Excel 365 for Mac, and Excel for the web.
-
Alternative Recommendations:
- The article suggests that despite the new automatic refresh feature, the tips provided by Office Watch in previous years are still considered a better way to update Stock and Currency data types.
- These alternative methods involve more user control and better display of refresh information in the workbook.
In conclusion, the article critiques Microsoft's implementation of the automatic refresh feature in Excel 365, pointing out limitations and offering alternative approaches for users seeking more control and customization.
