'%3e%3cpath%20d='M8%200C12.4183%200%2016%203.58172%2016%208C16%2012.4183%2012.4183%2016%208%2016C3.58172%2016%200%2012.4183%200%208C0%203.58172%203.58172%200%208%200ZM11.6162%204.38379C11.2257%203.99337%2010.5927%203.99338%2010.2021%204.38379L8%206.58594L5.79785%204.38379C5.40732%203.99334%204.77429%203.99329%204.38379%204.38379C3.99331%204.77429%203.99335%205.40733%204.38379%205.79785L6.58594%208L4.38379%2010.2021C3.99348%2010.5927%203.99341%2011.2257%204.38379%2011.6162C4.77426%2012.0066%205.40734%2012.0065%205.79785%2011.6162L8%209.41406L10.2021%2011.6162C10.5927%2012.0066%2011.2257%2012.0067%2011.6162%2011.6162C12.0067%2011.2257%2012.0066%2010.5927%2011.6162%2010.2021L9.41406%208L11.6162%205.79785C12.0066%205.40735%2012.0066%204.77429%2011.6162%204.38379Z'%20fill='%23080E17'%20fill-opacity='0.46'/%3e%3c/g%3e%3cdefs%3e%3cclipPath%20id='clip0_3761_713'%3e%3crect%20width='16'%20height='16'%20fill='white'/%3e%3c/clipPath%3e%3c/defs%3e%3c/svg%3e)
Fix Excel Stocks Data Type Disappearing After Query Refresh
Question details
The user needs to prevent an imported stock list from losing its Stocks data type conversion after every automatic Power Query refresh.
- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- Importing stock data via a URL query, converting the column to the Stocks data type, and setting the connection to auto-refresh periodically.
- Observed behavior
- After each automatic query refresh, the imported column loses its Stocks data type, causing dependent formulas for beta, history, and other stock data to stop working.
Ensure your internet connection is stable and temporarily disable the automatic query refresh to prevent the data types from continuously breaking while you troubleshoot.
Manually Reassign the Stock Data Source
Use the Data Selector search function to manually re-link the unlinked text back to the stock data source after a refresh.
Because Excel currently has a known limitation where it drops the background association between the imported query text and the proprietary Stocks data source during an automatic refresh, you must manually restore the link.
Click on the specific cell in your imported query column that has lost its Stocks data type icon.
Click on the 'Search' or question mark icon that appears next to the broken data type, or click the 'Data Type' alert in the cell.
Type the correct ticker symbol or company name into the Data Selector pane on the right side of your screen, then select the matching data source to restore the formulas.
Report the Issue via Excel Feedback
Since this is a reproducible product bug, submitting detailed feedback can help Microsoft developers issue a permanent patch.
Experience Stable Data Management with WPS Office
If recurring bugs in Excel's Power Query and proprietary data types disrupt your workflow, try WPS Office. It provides a lightweight, highly compatible spreadsheet environment for handling complex data and formulas smoothly.
Frequently Asked Questions
Why does my Excel Stocks data type disappear after a refresh?
This happens because Excel occasionally drops the background association between the imported query text and the proprietary Stocks data source during an automatic query refresh, reverting the cell back to plain text.
Can I automate the reassignment of the Stocks data type?
Currently, if Power Query drops the connection upon refresh, you cannot natively automate the reassignment using standard settings. You must manually click the Search icon in the cell to re-link the stock, or potentially use complex VBA macros to trigger the data type conversion after every refresh.
Does this issue affect other Excel linked data types?
Yes, similar auto-refresh connection drops can occasionally occur with Geography, Currencies, and other organizational linked data types imported via Power Query.
How do I stop Power Query from refreshing automatically?
Go to the Data tab, click 'Queries & Connections', right-click your active query, select 'Properties', and uncheck the 'Refresh every X minutes' option to prevent the data types from breaking continuously.
