Excel's Stock data type gets wonderful update

Office for Mere Mortals
Your beginners guide to the secrets of Microsoft Office
Invalid email address
Tips and help for Word, Excel, PowerPoint and Outlook from Microsoft Office experts.  Give it a try. You can unsubscribe at any time.  Office for Mere Mortals has been running for over 20 years, we've never, ever revealed or sold subscriber details.  Privacy policy

Insiders (Fast or Slow) will start seeing a major change to the date information they get from the Excel Stock Data type.

All Excel for Windows ‘Insiders’ can try out the Stock and Geo data types.  They are a new way to get external information into Excel without the hassle of data connections.

Date Format change

What’s changed in the last few days is the date format, especially for the vital ‘Last Trade Time’ field for stock prices.

Until now, the date has been in plain text which had to be parsed into an Excel serial date for calculations.

Now, Last Trade Time arrives as an Excel serial date.  Existing worksheets will look screwy, probably showing the Excel serial number.

excels stock data type gets wonderful update 23682 - Excel's Stock data type gets wonderful update

All you need to do is select a Date/Time cell format you like, and the date/time will look OK.

excels stock data type gets wonderful update 23684 - Excel's Stock data type gets wonderful update

Any conversion formulas to make the old Last Trade Time into an Excel date/time can be dropped.

excels stock data type gets wonderful update 23685 - Excel's Stock data type gets wonderful update

More important, it also makes data calculations a lot easier but not as easy as they could be.

All Insider software

This change affects all Insiders regardless of level (Fast / Slow) or the exact Excel software build.

That’s because it’s a change to the format of the incoming data feed, not the Excel software itself.

UTC time

All Last Trade Time values are in UTC time.

That’s why we published two useful articles to get the current UTC time and your local UTC offset into Excel.  Using the GimmeUTC() custom function, it’s possible to show how long since the last trade data was recorded.

excels stock data type gets wonderful update microsoft excel 23703 - Excel's Stock data type gets wonderful update

subs profile e1563205311409 - Excel's Stock data type gets wonderful update
Latest news & secrets of Microsoft Office

Microsoft Office experts give you tips and help for Word, Excel, PowerPoint and Outlook.

Give it a try. You can unsubscribe at any time.  Office Watch has been running for over 20 years, we've never, ever revealed or sold subscriber details.  Privacy policy
Invalid email address