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
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.
3. Highlight to add metrics
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
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
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.