Excel's Stock data type gets wonderful update

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.

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

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

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.