How to Automatically Record API Stock Prices in Excel Every 5 Minutes
Question details
The user needs to capture continuously updating API stock prices in Excel at regular five-minute intervals using VBA or formulas.
- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- Tracking and recording live financial market data received via API at regular intervals.
- Observed behavior
- The goal is to automatically append or capture each reported stock price into adjacent cells every 5 minutes to build a historical log.
Ensure you have the Developer tab enabled in Excel and have saved your workbook as an Excel Macro-Enabled Workbook (.xlsm) so that your automation scripts can run properly.
Automate Data Capture Using VBA Application.OnTime
Use a VBA macro with the Application.OnTime method to trigger a data recording script repeatedly every 5 minutes.
This method utilizes Excel's built-in timer to execute a specific subroutine at regular intervals. The script reads the live API value from your designated cell and copies it to the next available row in a tracking column, ensuring an ongoing historical log.
Press ALT + F11 to open the Visual Basic for Applications (VBA) editor. Click 'Insert' in the top menu and select 'Module' to create a new script area.
Create a subroutine (e.g., 'RecordPrice') that finds the last used row in your target column, moves one row down, and pastes the current value from your live API cell.
At the end of your 'RecordPrice' subroutine, add the line: Application.OnTime Now + TimeValue("00:05:00"), "RecordPrice". This tells Excel to run the exact same macro again in 5 minutes.
To prevent the macro from running indefinitely, write a separate 'StopTimer' subroutine that uses the Application.OnTime method with the 'Schedule:=False' parameter to cancel the pending execution.
Return to your Excel worksheet. Insert two shape buttons (one for Start, one for Stop), right-click them, and choose 'Assign Macro' to link them to your respective VBA subroutines.
Fetch API Data at Regular Intervals via Power Query
Configure Power Query to automatically refresh a web API connection every five minutes to display the latest stock price.
Try WPS Office for Seamless Spreadsheet Management
While complex API polling often requires extensive Microsoft-specific frameworks, WPS Office provides an incredibly lightweight, free, and highly compatible alternative for your daily spreadsheet and macro needs. Experience a familiar interface with outstanding support for complex formulas and data processing.
- 1. Download the Software: Navigate to the official WPS website and download the free installation package for your operating system.
- 2. Install WPS Office: Run the downloaded installer and follow the quick on-screen instructions to complete the setup.
- 3. Open Your Spreadsheets: Launch WPS Spreadsheets and open your existing Excel workbooks seamlessly with full formatting retention.
Frequently Asked Questions
Can I record historical API stock prices in Excel without using VBA?
Using standard formulas or Power Query alone will typically overwrite the previous data on each refresh. To keep a running historical log without VBA, you would need to use Office Scripts (on Excel for Web) or link your Excel workbook to an external database or flow automation tool like Power Automate.
Does Excel's Application.OnTime run if the computer goes to sleep?
No, the Application.OnTime macro will pause if the computer goes to sleep or if Excel is closed. To ensure continuous data tracking, you must adjust your PC's power settings to prevent sleep mode while the script is active.
How do I stop an Application.OnTime loop once it starts?
You need to run the Application.OnTime method again with the exact same time parameter, but add 'Schedule:=False' at the end of the statement. It is best practice to store the scheduled execution time in a public variable so it can be precisely targeted and canceled by your stop macro.
