Excel can get the stock market data from 60 different stock exchanges around the world. However, this information will not refresh automatically, and currently you need to refresh stock data manually, or write a VBA code to automate the process. In this article, we are going to show how to refresh stock data in Excel.
If you are not familiar with how to get stock quotes, please see our stock quotes guide.
Method 1: Refresh All
The Refresh All button under the Data tab can refresh all stock quote information, along with other connections and calculations in your workbook.
Method 2: Shortcut
This is essentially a shortcut for Method 1. Press Ctrl+Alt+F5 to update the stock data.
Method 3: Right-click (Context) Menu
This approach is easy to miss. You need to right-click on a cell with the stock icon. Excel will add a new category named Data Type under the Refresh command, along with other data type related actions.
Method 4: Auto-refresh using VBA
Although, Excel doesn't refresh stock data automatically, you can do so with a simple VBA macro. It is programmed as a refresh loop at specific intervals.
Sub AutoRefresh() ActiveWorkbook.RefreshAll 'Auto-Refresh in 0 hours, 1 minute, 0 seconds NextTime = Time + TimeSerial(0, 1, 0) Application.OnTime NextTime, "AutoRefresh" End Sub
If you are not familiar with using macros, let's go over the steps:
- While you Excel workbook is open, press Alt+F11 to open the VBA window.
- Insert > Module (Alternatively, you can use the marked button in the toolbar. You may need to click arrow to select module though.
- Copy the code into the empty editor on the right.
- (Optional) Adjust the interval in the code. TimeSerial(0, 1, 0) means 0 hours, 1 minute, 0 seconds.
- Return to Excel and add an object that you can use as a button. A textbox or a rectangle will work.
- (Optional) Enter a label on the button, e.g. Start Auto-Refresh.
- Right-click the object and click Assign Macro command.
- Select AutoRefresh from the list and OK button to assign.
- Save your file as XLSM which refers to Excel Macro-Enabled Workbook.
From now on, you can click the object to start the auto refreshing. Please note that auto refresh will continue until you close the workbook.