Excel can return Live Quotes Stock for free. It's included in your Microsoft 365 subscription 😀🏆
Connect company name with Stock Data Type
- Let's write some companies' name in column A
- Select them
- Go to the menu Data > Data Type
- Select the icon Stocks
- After a couple of seconds, an icon appears in each cell.
- When you see the stock icon, you are connected to the Stock database 😀👍
- Also, Data Types adds the ticker code and the exchange abbreviation to the company name.
Note: In our example, 2 cells display a question mark, indicating a manual check is needed to link to the correct ticker. We will address this issue later in the article.
Add the last quotation value
Once you have the icon in your cell, you can easily add the last quotation
- Add your data in a Table (Insert > Table). While optional, there's an important reason for doing so (step 6)
- Select one of the cell of your table
- A small icon appears in the upper-right corner
- Click on this icon
- Select the property "Price"
- Excel automatically adds a new column with the latest quotation values. The column header is the same as the option selected because our data are in a Table 😉👍
- Add as many columns as you want.
Disclamer
Note that Microsoft does not own the financial market information. Fundamental company data, historical data, real-time and delayed prices, fund summary and performance data are provided by Refinitiv.
Live stock quotation or delayed in Excel?
Most of the quotations are delayed
- 5 minutes: National Stock Exchange of India
- 15 minutes: Nasdaq Stock Market, New York Stock Exchange, London Stock Exchange, Euronext, Deutsche Boerse
- 20 minutes: Australian Securities Exchange, Toronto Stock Exchange, Santiago Stock Exchange, Korea Stock Exchange, Mexican Stock Exchange, New Zealand Exchange Ltd
- 30 minutes: Johannesburg Stock Exchange, Shenzhen Stock Exchange (China), BM&FBOVESPA (Brazil), Bolsa de Comercio de Buenos Aires.
How to correct the question mark?
We observed that 2 cells display a question mark instead of the stock icon. This indicates that Data Type couldn't find the company name or select the appropriate marketplace.
- Check the company name in the Search textbox
- Click on the button corresponding to the marketplace you want to select
Correct the MarketPlace
In some cases, the marketplace may not be the one you want. After each company name, you have
- The exchange market code: XNAS, XNYS, XPAR, XMIL, ....
- The company's ticker (it's ID). This is the code to use with the STOCKHISTORY function to collect the history quotation for a stock value.
For the last example, FORD is listed on the Milan Exchange (XMIL), not the New York Stock Exchange (NYSE)
- Right-click on the cell with the marketplace mistake
- Select Data Type
- And Change
- After, select the appropriate exchange market in the right pane.
