How to Automatically Save Daily Stock Portfolio Balances in Excel
Question details
The user needs a method to automatically record and preserve daily snapshots of their stock portfolio balance in Excel.
- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- Tracking historical stock portfolio balances day by day without manual data entry.
- Observed behavior
- Current Excel formulas dynamically update stock prices and total values, which causes historical daily balances to be overwritten and lost.
Ensure your portfolio workbook has a dedicated 'History' worksheet with column headers for the Date and Portfolio Balance, ready to store the extracted daily records.
Use a VBA Macro to Save Daily Snapshots
Create a VBA script that copies the current date and portfolio balance, pasting them as values into a historical tracking sheet to prevent data from being overwritten.
Since Excel does not have a standard built-in function to capture a static daily snapshot of changing formula outputs, a VBA macro is the most reliable way to automate this process.
Press ALT + F11 on your keyboard to open the Visual Basic for Applications (VBA) editor, then click Insert > Module.
Write a short VBA script that copies your current balance cell and finds the next empty row in your 'History' sheet.
Use the PasteSpecial xlPasteValues command within your script so that only the raw numbers (not the live formulas) are stored in the history log.
Save your file as a Macro-Enabled Workbook (.xlsm). You can assign this macro to a button, or use the Workbook_BeforeClose event to run it automatically whenever you close the file.
Use the STOCKHISTORY Function
If you only need historical market data for individual stock tickers rather than capturing a custom portfolio calculation, use Excel's built-in dynamic function.
Schedule Data Logging with Power Automate
Use Microsoft Power Automate to run a daily scheduled cloud flow that extracts your portfolio balance and adds it to a new row in an Excel table.
Try WPS Office for Seamless Portfolio Tracking
Looking for a cost-effective way to track your financial data? WPS Office provides a free, lightweight alternative to Microsoft Office, fully supporting advanced spreadsheet functions, historical data analysis, and VBA macro automation without expensive subscriptions.
- 1. Download WPS Office: Visit the official WPS website to download and install the free WPS Office suite on your device.
- 2. Open your portfolio workbook: Launch WPS Spreadsheet and open your existing .xlsx or .xlsm file to continue where you left off.
- 3. Enable macros: If you are using VBA scripts to record daily balances, navigate to the Developer tab and enable macros to run your automations.
Frequently Asked Questions
Why do my Excel formulas overwrite my previous portfolio balances?
Excel formulas update dynamically based on live or current data feeds. When a stock price changes, the formula recalculates immediately. This means the previous day's balance is lost unless you manually copy and paste it as a static value or use an automation script to save it.
Can I use the STOCKHISTORY function in older versions of Excel?
No, the STOCKHISTORY function is a dynamic array function available exclusively in Microsoft 365 (Office 365) subscriptions and Excel for the Web. Older standalone versions like Excel 2019 or Excel 2016 do not support it.
How do I save a copied formula value as static text?
After copying the cell containing your current balance, right-click the destination cell in your history sheet and select 'Paste Values' (the clipboard icon with a 123 on it), or use the keyboard shortcut Ctrl + Shift + V. This strips away the formula and leaves only the historical number.
