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.
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 latest stock 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
