Stock Data Type Not Refreshing Properly

Question

I created a table in Excel for the stocks I want to track. It finds most of them and I have to select on others. But, the next day, or when I refresh the table, the tool no longer finds all of the stocks (Circled ? appears) and I have to search for them all over again. For example...

ABBOTT LABORATORIES (XNYS:ABT)

AMAZON.COM, INC. (XNAS:AMZN)

Abbott Labs is recognized each time but Amazon is not. Amazon was found the day before, after a search, and the tool filled in the field with the values shown below and with the bank symbol. Next day, it doesn't recognize its own value.

How do I fix this so I don't have to search each time I refresh? I encounter 15 stocks in my table that are not recognized each time. Very obnoxious.

Answer 1

Hi, Dean Kern

This looks less like a problem with your formula and more like Excel losing the match for certain linked stock records after refresh. It can happen more often when the cell stores the long company name plus exchange text instead of a simple ticker.

Besides the solutions provided by the Q&A Assist, here are some suggestions you can try:

It is recommended that you keep a plain ticker column, for example AMZN, ABT, etc., then convert that column to the Stock data type instead of using the full company name. If Amazon keeps failing, delete only that linked cell, type AMZN again as plain text, then convert it to Stocks and pick the Nasdaq result.

For the 15 problem stocks, you can also keep the ticker as your “master” value and pull fields from the converted stock cell next to it. That way, if Excel loses the linked record, you still have the exact ticker to rebuild from.

If you mainly need prices, try using STOCKHISTORY("AMZN",TODAY()-1,TODAY()) as a backup, since it uses the ticker directly and avoids the linked-data matching step.

Thank you for your patience in reading, I hope this information has been helpful to you. 

---

If the answer is helpful, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment."    

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread. 

Answer 2

The Stocks data type is a linked data type that connects to an online source (via Bing). When the source cannot be matched or temporarily fails, Excel shows the circled ? and #FIELD! errors, and the affected cells may need to be re‑matched. This behavior is usually transient and not something that can be permanently “fixed” per symbol from within the workbook.

Use these steps to minimize issues and recover when it happens:

1. Confirm requirements
   • Make sure the account in Excel is a Microsoft 365 or free Microsoft account that supports linked data types.
   • Ensure an appropriate editing language (English, Spanish, French, German, Italian, or Portuguese) is added in Office Language Preferences so Stocks data types are fully supported.
2. Refresh correctly
   • To refresh a single stock, right‑click the cell that contains the Stocks data type (the one with the building icon) and select Data Type > Refresh.
   • To refresh all linked data types in the workbook at once, use Data > Refresh All or press Ctrl+Alt+F5. This also refreshes other external connections and PivotTables.
3. Re‑resolve symbols when the ? appears
   • If the circled ? appears, click it to open the selector pane, search for the correct Amazon entry again, and choose Select.
   • After re‑selecting, use Data > Refresh All so all dependent fields (Price, % Change, etc.) are updated from the new data type instance.
4. Use the card to verify the data type
   • Click the Stocks icon in the Amazon cell to open the card and confirm the symbol and exchange are correct.
   • Scroll to the bottom of the card to see the provider information and verify that the data type is recognized and powered by the online source.

Because the data comes from an external service, occasional mismatches or temporary outages can cause some tickers (like AMZN) to fail to resolve after a refresh. When this occurs, re‑selecting the correct item in the selector pane and then using Refresh or Refresh All is the supported recovery path.

---

References:

• Excel data types: Stocks and geography
• Linked data types FAQ and tips