Keep Historical Website Prices When Refreshing Excel Data
Question details
The user needs to maintain historical website prices and dates during data refreshes without overwriting the existing records.

- Product
- Excel
- Device & OS
- not provided
- Scenario
- Importing and refreshing daily or periodic website prices into an Excel workbook to build a historical database.
- Observed behavior
- A standard data refresh overwrites the existing data with the current values, deleting the historical price and date records.
Ensure your initial web query is successfully loading the website data into an Excel table and that you have a designated table for storing historical records.
Use Power Query to Append Current Data to History
Create separate queries for your history and current website data, append them together, and remove duplicates to maintain a running historical log.
To prevent Excel from overwriting your data during a refresh, you must break the direct link between the web query and the final table. Instead, you can use Power Query to append the fresh data to your existing historical records.
This self-referencing query technique allows you to continuously stack new data on top of old data every time you refresh the worksheet.
Load the website data containing the current date and price into a standard Excel table.
Create a new query from the existing table containing your historical records. If necessary, break the direct data connection between the original web query and the final output table.
In the Power Query Editor, navigate to the Home tab and use the 'Append Queries' feature to combine your history query with the new current-price query.
Select the relevant identifying columns (such as Date and Product ID), right-click the column headers, and choose 'Remove Duplicates' to prevent redundant entries if multiple refreshes occur on the same day.
Click 'Close & Load' on the Home tab to output the combined, deduplicated dataset into your workbook. Each future refresh will now append the newest values while preserving the history.

Manage Data Effectively with WPS Office
While advanced Power Query self-referencing workflows are specific to Microsoft Excel, WPS Office provides a lightweight, highly compatible, and free alternative for managing large datasets, analyzing historical data, and cleaning duplicate records. It offers a familiar interface that makes migrating your spreadsheets completely seamless.
- 1. Download and install WPS Office: Visit the official WPS Office website to download the free suite and install it on your device.
- 2. Open your spreadsheet: Launch WPS Spreadsheet and open your existing Excel (.xlsx) file containing your historical data.
- 3. Manage duplicates easily: Navigate to the Data tab and use the 'Remove Duplicates' tool to clean your appended historical logs.

Frequently Asked Questions
Why does my Excel web query overwrite previous data?
By default, refreshing a web query in Excel replaces the destination table with the latest data from the source. It does not automatically store past snapshots unless you configure an append query or use a VBA macro.
Can I automate the historical data archiving in Excel?
Yes, you can use Power Query to append the current web data to your existing history table. Alternatively, you can write a simple VBA macro that copies the newly fetched values and pastes them at the bottom of a historical log table upon refresh.
How do I remove duplicate dates and prices after appending?
In Power Query, select the columns for Date and Product, right-click the column headers, and choose 'Remove Duplicates'. This ensures that only one record exists per product per day, even if you refresh multiple times in a single day.




