Track Excel Cell Changes After an Internet Refresh with VBA
Question details
Track changes to Excel stock data after it is updated by an external refresh.

- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- A workbook refreshes stock values from an internet data source, and the user wants to record changed values in new rows.
- Observed behavior
- Worksheet_Change may not detect values updated by an external data refresh.
Identify the worksheet, query-backed table, and fixed range containing the stock values to monitor. Save the workbook as an Excel Macro-Enabled Workbook (.xlsm) and enable macros if your organization permits them.
Compare stock values after a synchronous query refresh
Use this approach when the stock data is loaded into an Excel table with a QueryTable that supports synchronous refresh.
Worksheet_Change is intended for changes to worksheet cells and is not a reliable notification for every external refresh or recalculation. This macro captures the values before it starts the refresh, waits for the refresh to finish, and logs differences.
The example monitors Data!B2:B10 and skips comparisons involving Excel error values. Adjust the range if your stock values are elsewhere; it does not automatically expand when rows are added.
In Excel, name the data worksheet Data and its query-backed table StockTable, or change those names in the code. Add a worksheet named Log and enter Time, Cell, Previous value, and New value in cells A1:D1.
Open the VBA editor with Alt+F11, choose Insert > Module, and paste this code: Sub RefreshAndLogStockChanges() Dim dataSheet As Worksheet, logSheet As Worksheet Dim watched As Range, qt As QueryTable Dim beforeValues As Variant, afterValues As Variant Dim i As Long, nextRow As Long Set dataSheet = ThisWorkbook.Worksheets("Data") Set logSheet = ThisWorkbook.Worksheets("Log") Set watched = dataSheet.Range("B2:B10") Set qt = dataSheet.ListObjects("StockTable").QueryTable beforeValues = watched.Value2 If Not qt.Refresh(BackgroundQuery:=False) Then MsgBox "The refresh was canceled or did not complete." Exit Sub End If afterValues = watched.Value2 For i = 1 To UBound(beforeValues, 1) If Not IsError(beforeValues(i, 1)) And Not IsError(afterValues(i, 1)) Then If CStr(beforeValues(i, 1)) <> CStr(afterValues(i, 1)) Then nextRow = logSheet.Cells(logSheet.Rows.Count, "A").End(xlUp).Row + 1 logSheet.Cells(nextRow, 1).Value = Now logSheet.Cells(nextRow, 2).Value = watched.Cells(i, 1).Address(False, False) logSheet.Cells(nextRow, 3).Value = beforeValues(i, 1) logSheet.Cells(nextRow, 4).Value = afterValues(i, 1) End If End If Next i End Sub
In Excel, open Developer > Macros, select RefreshAndLogStockChanges, and click Run. After the refresh finishes, open the Log sheet to see any changed values appended below the headers.

Review stock spreadsheets with WPS Office
WPS Office is a free, lightweight alternative for working with spreadsheets in a familiar interface. Its Excel format compatibility helps you move everyday workbook tasks to WPS, although it does not fix Microsoft Excel refresh events or guarantee that this VBA workflow will run.

Frequently Asked Questions
Why doesn't Worksheet_Change catch my refreshed stock values?
External refreshes and recalculations do not always trigger Worksheet_Change in the same way as direct edits. Compare a saved snapshot after the refresh, or use a refresh-completion event supported by your connection.
Can I detect a query refresh automatically?
For a supported QueryTable, VBA can handle its AfterRefresh event through a WithEvents variable in a class module. Initialize a snapshot before the refresh and compare it when AfterRefresh reports success. Other connection types may require a different approach.
Will the sample macro work with every Excel data source?
No. It requires a query-backed table accessible through ListObjects("StockTable").QueryTable and a connection that supports synchronous refresh. Linked stock data types and other connection types may need a different method.
Where are the changed values recorded?
The macro appends the time, cell address, previous value, and new value to the Log worksheet. Check cells A1:D1 for the headers and look below them after running Developer > Macros > RefreshAndLogStockChanges.




