Microsoft recently introduced in-built live stock data into Excel. No more cumbersome work involving data extraction or using external sources manually. A specific Stocks Data Type has been introduced to allow users to track investments, latests stock prices and other indicators. In this article we will guide you on how to get this real time stock data into Excel up and running for free.
Extracting live stock data
To check if your version of Excel is compatible with this addition, go to the Data tab, here, you should have a Stocks option available as depicted.
To get started with stock data, you will need to guide Excel on which data to configure. Type any stock ticker or name in a cell. Now, your text must be converted into the rich entity that contains related information on the stock.
To do this, click Stocks while the corresponding cell is selected and you will link a data source.
The data source will appear as in the image below. To extract data points corresponding your stock ticker, select the data cell and click the insert data icon next to it.
Which stock information can Excel provide in real-time?
Currently, Excel stock data supports well-known financial indicators, the most significant are displayed in the table below. Next to these daily indicators, some measures such as P/E ratio and Market Cap are available as well.
|Price||Latest stock price (Delayed by fixed offset, see information further down)|
|High||Daily highest stock price|
|Low||Daily lowest stock price|
|Open||Opening price of latest trading day|
|Volume||Daily trading volume|
|Change||Daily stock price change|
Microsoft state that stocks in Excel data includes amongst others bitcoin, bonds, currency and after and before hours trading data as well as mutual funds and stock indices data.
We obtain the Bitcoin/USD spot rate using BTCUSD, and we can find information on popular ETF’s such as Vanguards S&P 500 ETF using the ticker VOO. However, we have not yet been able to retrieve commodity price information.
What is the delay on the stock information?
Allthough the data is somewhat real-time, the information is delayed by a fixed number of minutes differing per stock exchange. For US, UK and European stocks, the information is delayed by a fixed 15 minutes. The exact stock delay numbers are provided by Microsoft