Forum Discussion
STOCKHISTORY function
hi Dacrim You’re definitely not the only one seeing this. When STOCKHISTORY returns #CONNECT! intermittently across multiple symbols, it usually points to a temporary connectivity or backend data service issue, rather than anything wrong with the formula or workbook.
The fact that:
- the same formulas sometimes work and sometimes fail,
- multiple symbols are affected at the same time, and
- reopening or recalculating Excel temporarily resolves it,
strongly suggests that Excel is having trouble reaching the underlying market data service at certain times.
There doesn’t appear to be any known restriction related to Spain or specific Microsoft 365 account types. If it was working fine until a couple of days ago, this is most likely a service-side degradation or throttling rather than a regional or licensing problem.
Workarounds that may help in the short term:
- Force a full refresh (Ctrl + Alt + F5)
- Switch calculation to Manual and back to Automatic
- Split large date ranges into smaller STOCKHISTORY calls
- Use IFERROR to handle temporary failures gracefully
Retry via helper cell logic
You can wrap STOCKHISTORY in an IFERROR that retries or falls back:
=LET(
result, IFERROR(STOCKHISTORY("EUR/USD", start_date, end_date), ""),
IF(result="", "RETRY", result)
)
Then trigger a refresh manually or via a macro.
Manual refresh strategy
- Select the range
- Press Ctrl+Alt+F5 (requery external connections)
- Or toggle calculation mode (Manual → Automatic)
These sometimes force a fresh service call.
Split calls into smaller date ranges
If the service is occasionally overloaded, shorter ranges reduce the request size.
Example:
- Instead of one big range, break:
- Jan–Mar
- Apr–Jun
- etc.
This often helps stabilize intermittent backend timeouts.
If the issue persists, it may be worth raising a support ticket and including the timeframe when the errors started, since that can help Microsoft correlate it with any backend service events.
Hopefully this stabilizes soon, the formulas themselves look fine.