How to Insert Stock Prices in Excel

Excel is getting more advanced every day. You can now insert real-time stock prices in Excel using the data types stocks option. By inserting stock prices in Excel you can easy analyze and come up with strategies to invest in the stock market. It supports all the major markets such as the NSE, NASDAQ, and London Stock Exchange. 

Apart from stocks, you can pull other data such as currency Exchange Rate, bonds, and crypto. MS Excel can pull such types of data through a feature called data types with the help of sources like Refiitiv and Nasdaq. 

Data Types

Data types in Excel are a feature that links to an online resource to provide real-time data from the source. For example, you can use a data source to pull data such as the latest population of a certain country. Besides, you can also dig deep to pull more metrics on the population such as literacy levels, etc. 

Currently, Excel offers two main types of data types.

  • Stocks
  • Geography 

How to insert Stock prices in Excel 

Now let’s see how you can pull the data into your spreadsheets.

1. Type the stock names 

Convert To Stocks

Type the names of the stocks that you wish to get metrics for in one column.

2. Convert To Stocks

Once you type about three names, the Excel smart feature will ask whether to convert to stock in the next cell.

If this option appears you can drag down the fill handle to a few more cells that will handle the stocks 

The previous cells will change like this

As you can see from the image above Excel has added the stocks icon and more info. You can continue typing more stocks. The cells that you selected earlier will automatically convert any name of a company that you type into a stock data type. 

How to insert stocks in Excel

3. Highlight to add metrics

How to add stock prices in Excel

Highlight all the stocks to add metrics in the next column. Once you highlight, click on the fill handle that appears in the next column. A data card will launch with several metrics such as:

  • prices
  • market cap
  • ticker symbol
  • Beta
  • 52 week high
  • 52 Week low
  • Headquarter
  • Exchange abbreviation
  • Employees
  • Change %
  • High
  • Industry
  • Instrument type
  • Low
  • Official Name
  • Open/PE
  • Previous Close
  • Shares Outstanding
  • Volume
  • Year incorporated
  • Volume average

In this example, I will use prices for illustration purposes 

Once you click on price the prices of the stocks will updated in the next column as follows

4. Add more metrics

How to add market cap, volume, and ticker in Excel

Now that you have added one metric. You can more from the data card by following the same steps. But this time choose a different metric from the previous one so that it can show in the next column. 

Just highlight the stock names then click on the fill button and select the type you wish to get. 

Refresh

You can use VBA code to refresh the data after every few seconds or minutes depending on your needs. Copy and paste the following code.

VBA Code to refresh stock prices every 5 seconds

Sub RefreshSheetEvery5Seconds()
    On Error Resume Next
    Application.OnTime Now + TimeValue("00:00:05"), "RefreshSheetEvery5Seconds"
    On Error GoTo 0
    
    ActiveSheet.RefreshAll
End Sub

VBA Code to Refresh spreadsheet on open

You can also refresh the stock prices whenever the Excel file is launched. This means it will start as the last saved data but will start with the current real-time price.  

Private Sub Workbook_Open()
    ' Call the RefreshAll method to refresh all external data connections and calculations
    ActiveWorkbook.RefreshAll
End Sub

Errors

##### – If you get this error after pulling stock data then it means that the cell is smaller than the data itself. All you need to do to solve this error is to expand the cell. To avoid such errors in the future you can always set your cells to adjust according to the size of the data

– This Icon means Excel is currently fetching the data about your stock

– This icon means that Excel does not recognize the name you typed as a stock. You can continue typing the rest of the name to help it pick up. 

Final Thoughts

That’s how you can insert real-time stock prices in Excel. You can use the same method to insert currency exchange prices, crypto prices, and so on. Currently, only the stock information of the big markets is available. 

Leave a Reply

Videos

Discover more from Excel Wizard

Subscribe now to keep reading and get access to the full archive.

Continue reading