VBA & Macro Problems

How to Use VBA to Record Live Stock Prices in Excel at Fixed Intervals

Natalie Taylor Sep 28, 2026 869 views

Question details

The user needs a VBA macro to automatically record and preserve live updating stock prices into specific time-interval columns (e.g., 13:00, 13:10) without overwriting historical data.

Record Live Stock Prices in Excel at Fixed Intervals Using VBA

Product: Microsoft Excel
Device & OS: not provided
Scenario: Tracking live financial data where prices update continuously, requiring historical snapshots at scheduled time intervals.
Observed behavior: Live data continuously overwrites the current cell in column B. Previous VBA code used to handle the archiving task but stopped working after recent workbook changes.

Before you start

Ensure your workbook is saved as a Macro-Enabled Workbook (.xlsm) and that you have enabled macros in your Trust Center settings so the scheduling code can run without interruption.

Solution 1Recommended

Use Application.OnTime to Schedule Live Price Recording

Create a scheduled VBA procedure that copies live prices to a designated time column and reschedules itself automatically.

The Application.OnTime method allows you to run a specific macro at a designated time. By placing this method at the end of your recording script, you can create an infinite loop that runs every few minutes to archive your live data.

It is crucial to create a mechanism to stop this loop when closing the workbook; otherwise, the application may reopen the file automatically to run the scheduled macro.

Open the VBA Editor

Press Alt + F11 to open the Visual Basic for Applications Editor, then click Insert > Module to create a new script area.

Create the Recording Procedure

Write a macro that reads the current time, identifies the corresponding time-interval column heading (e.g., 13:10), and copies the live prices from Column B. Use PasteSpecial with xlPasteValuesAndNumberFormats to paste the static data into the matching column.

Add the Scheduling Command

At the end of your recording macro, add the line: Application.OnTime Now + TimeValue("00:10:00"), "YourMacroName" to schedule the script to run again in 10 minutes.

Create a Stop Procedure

In the same module, create a separate macro (e.g., StopMacro) that runs: Application.OnTime EarliestTime:=ScheduledTime, Procedure:="YourMacroName", Schedule:=False to cancel the timer.

Automate on Open and Close

Double-click 'ThisWorkbook' in the Project Explorer. Add a Workbook_Open event to call your recording macro, and a Workbook_BeforeClose event to call your StopMacro procedure.

Use Application.OnTime to Schedule Live Price Recording

Best Practice for Time Headers: Test the code in a backup workbook first. Ensure that your worksheet column headings exactly match the time format your VBA script searches for (e.g., '13:00').

Advanced Financial Data Tracking

Run Scheduled Macros Seamlessly with WPS Spreadsheet

WPS Spreadsheet offers robust VBA and macro support, allowing you to run complex Application.OnTime scripts to track live stock prices just as efficiently as Microsoft Excel, within a lightweight and user-friendly interface.

1. Open your Workbook: Launch WPS Spreadsheet and open your .xlsm file containing the live stock data.
2. Access Developer Tools: Navigate to the 'Developer' tab on the ribbon and click on 'VBA Editor'.
3. Insert the Code: Paste your Application.OnTime scheduling logic into a new module.
4. Run and Track: Run the macro to begin automatically logging your stock prices into the time-interval columns.

Fully compatible with Microsoft Excel (.xlsm) macro-enabled workbooks.Built-in Developer tools and VBA editor to write, run, and debug financial macros.Lightweight architecture ensures fast execution without bogging down real-time data calculations.

microsoft office alternative - wps office

Frequently Asked Questions

Why does my Application.OnTime macro reopen the workbook after I close it?

This occurs when a scheduled macro has not been explicitly canceled. You must include a Stop procedure that sets the Schedule parameter to False, and trigger it using the Workbook_BeforeClose event.

How can I change the recording interval to 5 minutes instead of 10?

In your VBA scheduling line, change the TimeValue function parameter. For a five-minute interval, update the code to: Application.OnTime Now + TimeValue("00:05:00").

Will the scheduled macro run if I am actively typing in a cell?

No, Excel and WPS Spreadsheet suspend background VBA execution while you are in cell edit mode. The scheduled macro will run immediately after you press Enter or exit the cell.

How do I ensure the live formulas are not copied into my historical columns?

Use the PasteSpecial method in your VBA code. Setting Paste:=xlPasteValuesAndNumberFormats ensures that only the static numbers and their formatting are recorded, leaving the live updating formulas behind.

Excel & Data Problems