The article will show you all possible methods on how to automatically update stock prices in Excel. This is a very cool feature for those who work in stock exchange organizations. You don’t have to seek prices or percentages to enter them in your database if you use this feature.
In the following picture, we have stock information about various companies.
How Do You Automatically Update Stock Prices in Excel: 3 Ways
1. Right Click to Automatically Update Stock Prices in Excel
All of the methods to automatically update stock prices in Excel are simple and easy. In this section, I’m showing you how to use a command from right-clicking options. Let’s go through the description below.
Steps:
- First, make a chart like the following picture. You should type the correct abbreviation of the company name.
- Select the range and go to Data >> Stock
After that, you will see the company names converted to Stock format.
- Now, click on the number 1 marked icon in the image and select Price.
- Then again click on that icon and select Change.
- After that, select Change (%).
- Finally, select Last Trade Time.
Now you will see all the current information about the stock companies.
- Select the range B5:B10 and right-click on any of the selected cells.
- Go to Data Type >> Refresh Settings
You can also just select Refresh to update stock prices instantly.
- In the Data Type Refresh Settings window, click on Stock and you will see 3 Refresh Options. Choose any one of them. In this case, I chose Automatically every 5 minutes.
Now, you are all set. Your stock prices will be updated every 5 minutes from now on. To show you the change in the dataset, I’m giving here two images.
I took this screenshot 2 hours ago.
And this is the current data.
The other data didn’t change so we don’t see any update. By following this approach, you can automatically update stock prices.
Read More: How to Get Stock Prices in Excel
2. Using VBA to Automatically Update Stock Prices in Excel
Another way to automatically update stock prices in excel is to use a simple VBA code. Let’s see the procedure below.
Steps:
- First, go to Developer >> Visual Basic to open the VBA
- Then open ThisWorkbook from the sheets.
- Type the following code in it.
Private Sub Workbook_Open()
ThisWorkbook.RefreshAll
MsgBox "Stock Information are Updated."
End Sub
- Press CTRL+S to save the code and it will update the data of stock prices with a Message Box.
You can notice that there’s another change in the 10th row.
Thus you can automatically update stock prices and other information using VBA.
Read More: How to Import Stock Prices into Excel from Yahoo Finance
3. Applying Refresh Command to Automatically Update Stock Prices
Last but not least, you can also update stock prices from the Refresh ribbon. Let’s see the process below.
Steps:
- Select Data >> Refresh All or simply just press CTRL+ALT+F5
- This operation will update all the stock prices and other information.
Thus you can automatically update stock prices simply using the Refresh command.
Read More: How to Import Stock Prices into Excel from Google Finance
Practice Section
In this section, I’m giving you the dataset of this article so that you can practice these methods on your own.
Download Practice Workbook
Conclusion
In the end, you will know about the basic ways to automatically update stock prices in excel after reading this article. If you have any questions or feedback regarding this article, feel free to share them in the comment box. This will help me enrich my article content and also my vision of Excel.
