Excel cannot fetch some stock data like it did last week.

Question

So, I have set up my portfolio in Excel 365 online, but some stock symbols that were working last week no longuer works : XTSE:ZBAL-T

Also, the very intricate function of SUM does not want to work for some reason.

Also, the data broker that Microsoft uses does not provide dividend payout and Excel online does not let you fetch data from the web...

Please advise

Answer 1

Hi Francis Brabant,

For the stock entry, try both ZBAL.T and ZBAL‑T, then convert the cell again using Data > Stocks, or use the question‑mark selector to manually choose the correct BMO Balanced ETF entry. Excel’s Stocks data type relies on an online financial data provider, so ticker symbol matching can change over time and symbols that worked previously may no longer resolve automatically.

If you need historical price data, STOCKHISTORY is often more reliable than relying only on the linked Stocks card, although it does not provide dividend payout information.

For the SUM issue, first test =SUM(1,2,3). If that returns 6, the function itself is working and the issue is usually with the source cells. Common causes include values stored as text, incorrect ranges, or merged cells. You can quickly check a value with =ISNUMBER(A1). If it returns FALSE, Excel is not treating that entry as a numeric value.

Regarding dividend payout data, Excel for the web has more limited data import options, and dividend fields are not always exposed through the built‑in Stocks data type. If you need more complete market or dividend data, opening the workbook in desktop Excel (where Power Query is available) is typically the better approach.

Please let me know if this proves useful to you, or if you would like further assistance.

I'm looking forward to your reply.

---

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

For the stock symbols and portfolio:

1. Stocks data type availability and issues
   • Excel’s Stocks data type is a linked data type that depends on an online provider (LSEG Data & Analytics) and Microsoft’s own service. Symbols that worked previously can temporarily fail if:
     • The provider changes or removes instruments.
     • There is a temporary service or connectivity issue.
   • If Excel cannot match the text to an online instrument, it shows a generic icon issue or #BLOCKED!/matching problems. For mismatched symbols:
     1. Check spelling and format of the ticker (e.g., remove extra characters or try just the base symbol if the prefix/suffix changed).
     2. Click the data type icon in the cell or use the selector pane (if Excel shows the “can’t match” icon) and search again by keyword, then choose the closest match and select Select.
     3. If you see #BLOCKED! or similar, check:
        • Signed-in status to Microsoft 365.
        • That the account has a valid Microsoft 365 license (Stocks data type requires Microsoft 365 or a free Microsoft account).
        • Region support and privacy settings (see below).
2. License, region, and privacy checks for linked data types
   • Stocks data type requires:
     • A Microsoft 365 subscription or free Microsoft account.
     • One of these editing languages added in Office Language Preferences: English, French, German, Italian, Spanish, or Portuguese.
   • If a #BLOCKED! error appears, possible causes include:
     • License required.
     • Not signed in.
     • Unsupported region.
     • Privacy settings blocking linked data types.
   • To fix privacy blocking in the desktop app (if you open the workbook there):
     1. Go to File > Options > Trust Center > Trust Center Settings.
     2. Under Security settings for linked data types, choose either:
        • Prompt user about linked data types, or
        • Enable all linked data types (not recommended).
   • If the message is “Unable to update,” open the workbook in Excel desktop or Excel for the web, as some versions cannot refresh connected data types.
3. Refreshing stock data
   • Stocks and Geography are connected to an online source and can be refreshed:
     • Right-click a cell with the Stocks data type and select Data Type > Refresh to update that cell and all cells using the same data type.
   • Stock information is delayed and provided “as-is” and is not for trading or advice.
4. Missing dividend payout data
   • The Stocks data type exposes only the fields that the provider and Microsoft make available. The context confirms that fundamental company data, historical data, real-time and delayed prices, and fund summary/performance are provided by LSEG Data & Analytics, but dividend payout is not listed as a guaranteed field.
   • If dividend fields are not present when clicking the Insert Data / Add Column button for a stock, they are not available from this data type and cannot be pulled directly in Excel for the web.
   • Excel for the web does not offer generic “Get Data from Web” (Power Query) like the desktop app, so web-scraping dividends directly in Excel for the web is not supported in this context.
5. SUM function not working
   • The context only provides general guidance for broken formulas:
     • If Excel cannot resolve a formula, it shows an error and highlights the problematic part when the cell is in edit mode.
     • To troubleshoot:
       1. Click the cell with the SUM formula; Excel will highlight where it detects an issue.
       2. If the formula is clearly wrong and you want to start over, press ESC or click Cancel in the formula bar.
   • Typical causes (based on the general guidance):
     • Incorrect function syntax.
     • Non-numeric values in the range.
     • Using a function that is blocked or requires a license (would show #BLOCKED!).
   • Correct SUM syntax example: =SUM(A2:A10).
6. Alternative for historical data
   • For historical financial data (e.g., prices over time), the context suggests using the STOCKHISTORY function, which also uses the same provider. This does not solve dividends, but can help with price history.

---

References:

• Get a stock quote
• Excel data types: Stocks and geography
• Excel for the web
• About the Stocks financial data sources
• How to correct a #BLOCKED! error
• How to avoid broken formulas in Excel