Excel - Stock Data - Constant Crash/Lock Up

Question

I have an Excel file I use for monitoring Stock Prices of about 300 stock symbols using the Data>Stocks feature. The main sheet of this workbook uses VLOOKUPs, Conditional Formatting, and simple match calculations to display information to help me track Buy/Sell targets. The supporting sheets (2 sheets) contain data from a Quicken export (plain data) and Cut/Paste data from Yahoo Finance (plain data, dividend info, price targets). There is only one simple Macro used in this worksheet for pasting and sorting the data from the Quicken export.

My issue is that this file locks up about 15 times a day requiring me to hit the Save icon, close the file and reopen it. Any other operation I try will display either a blank pop up window or no functionality. I can't for the life of me figure out what is causing this spreadsheet to crash. I never had this issue when I was using the StockConnector Add In. I had to switch to the Data>Stocks feature when the StockConnector Add In data source became unreliable.

Anyone have similar issues with the Data>Stocks feature?

Answer 1

It sounds like you are experiencing performance issues with your Excel workbook that utilizes the Data > Stocks feature. Here are some potential reasons and solutions to consider:

1. Excessive Formatting: If your workbook has excessive formatting (like formatting whole rows or columns), it can lead to performance issues. Consider using the format cleaner add-in to eliminate unnecessary formatting.
2. Unused Styles: Having many unused styles can also bloat the file size and impact performance. Use a style cleaner tool to remove these styles.
3. Shapes and Objects: Check for any hidden shapes or objects in your workbook that may be consuming memory. You can use the Selection Pane to identify and remove any unwanted shapes.
4. Conditional Formatting: If the conditional formatting is corrupted, it can cause the file to grow unnecessarily. Try clearing the conditional formatting rules and see if that resolves the issue.
5. Formulas: Some formulas, especially array formulas or those referencing whole columns, can take up significant memory. Review your formulas and ensure they reference only the necessary ranges.
6. External Links: Since your workbook links to external data sources, ensure that these links are not causing delays or crashes. Test the workbook without these links to see if performance improves.
7. Macro Efficiency: If your macro is not optimized, it could also contribute to the locking up of the workbook. Review the macro code for any inefficiencies.

If the problem persists after trying these methods, consider breaking your workbook into smaller files or upgrading to a 64-bit version of Excel, which can handle larger datasets more efficiently.

---

References:

• How to clean up an Excel workbook so that it uses less memory

Answer 2

Hello,

Given the blank pop up windows and repeated freezes while the Stocks data type is active, try first to disable hardware graphics acceleration in Excel.

Close every Excel window.

Start Excel without opening your workbook. Go to File > Options > Advanced. In the Display section turn on Disable hardware graphics acceleration. Click OK. Fully exit Excel, including the Excel icon in the system tray, then reopen Excel and load your workbook. Work as usual for a while to confirm stability.

If Excel will not stay responsive long enough to change the option, start Excel in Safe Mode to flip the same setting. Press Windows + R, type excel /safe, press Enter, then go to File > Options > Advanced and enable Disable hardware graphics acceleration. Close Excel and reopen it normally.

Let me know how this behaves after a day of use. If the lockups persist, I will guide you through the next step.