How to refresh stock data in Excel

by | Jun 25, 2019 | Excel Tips & Tricks

refresh stock data

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.

Note: Data Types have been released to all Windows Office 365 subscribers in March 28, 2019. Thus, you need to be an Office 365 subscriber to access this feature.

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:

  1. While you Excel workbook is open, press Alt+F11 to open the VBA window.
  2. Insert > Module (Alternatively, you can use the marked button in the toolbar. You may need to click arrow to select module though.
  3. Copy the code into the empty editor on the right.
  4. (Optional) Adjust the interval in the code. TimeSerial(0, 1, 0) means 0 hours, 1 minute, 0 seconds.
  5. Return to Excel and add an object that you can use as a button. A textbox or a rectangle will work.
  6. (Optional) Enter a label on the button, e.g. Start Auto-Refresh.
  7. Right-click the object and click Assign Macro command.
  8. Select AutoRefresh from the list and OK button to assign.
  9. 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.