Get Live Stock and ETF Data in Excel 365 – Really Fast!

Last Updated on January 26, 2023 by Mark Ursell

It is really useful to have live price data in Excel. And Microsoft has now made it much easier to import this data into our spreadsheets.

Once you have set up the spreadsheet you can customize it and use it to track the markets and monitor your strategies and investments. 

In this article, I demonstrate the steps you need to create a custom portfolio monitoring dashboard, give you some tips for further ideas and there is also a Video showing the steps.

Step 1: Type the Name or Ticker of the Stocks in Your Portfolio

Write a list of stocks that you want in your portfolio. You can use either the ticker symbol or the name.

Step 2: Highlight the Names and Click Stocks

Select the Data tab and click on Stocks. If Excel recognizes the name of the stock, it will fill in the full name and insert the symbol.

Important: Make Sure the Stock Data is Correct

Now you need to check that Excel has got the right name. When I typed Ford, it brings up Forward Industries Inc. I tried again using the ticker F and it correctly showed Ford.

If Excel does not recognise the name it brings up a question mark. Click on this and use the Data Selector to find the right stock.

Step 3: Choose the Stock Information You Want

In this example I have chosen:

Open – High – Low – Close – % Change – Volume – P/E

Type in the column headings and then highlight the stock names. Click on the table icon that appears at the top right-hand corner of the selection. 

This data is provided by Morningstar Inc. and you can choose from 31 different types of information.

Step 4: Convert the Portfolio into a Table

This step is not necessary but it gives you more control over the portfolio.

Highlight the entire area, including the column headings. Then select the Insert tab and click Table. 

Step 5: Use the Table Settings to Display Your Portfolio How You Want

Now you can be creative and use the full resources of Excel to optimize how your portfolio looks.

You can use the table sorters at the top to organise your data by any of the categories.

If you have a large portfolio you might want to create a Slicer. You do this by clicking in the table and then selecting Table Tools and Insert Slicer. This allows you to filter the data, select individual stocks or by category. 

Some Further Ideas

Once you have got your portfolio in the spreadsheet you can do lots more.

Add ETF Data

You can get ETFs for everything. Whether you want to track Gold Stocks, Large Caps or Emerging Markets there is an ETF. Trade the strongest or weakest global markets using Relative Strength. In the Video below I show how ETF data is imported in the same way as stock data.

For more information see my article on: Invest Using Relative Strength.

Store Live Price Data

You can build up your own library of EOD price and volume data by copying and pasting. I do this using Google Sheets and it means that I do not have to rely on a historical data provider. You can see my method for Google Sheets here: Monitor a Strategy with Google Sheets.

You can also automate the process using VBA.

Become a Better Trader

Tradinformed Backtest Models use Excel and let you test and optimize your trading strategies. Have a look at the models available now so that you can become more profitable and enjoy your trading.

Watch the Video

Sometimes it is easier to watch. See me demonstrate the steps in this video.

If you have any comments or suggestions for how to use this tool, let me know using the comment section below.