STOCKHISTORY PUZZLE (#CONNECT! turned into an ERROR)

[mookyon]

My Dear Experts,

Changes in how Excel 365 handled #BUSY! Situations puzzles me. Please help.



A while ago, when one retrieved data (e.g., calling Excel’s STOCKHISTORY function), one could see either #BUSY! or #CONNECT! “errors” displayed in an alternating manner.

These were not really ERRORS since one could not fetch those situations with IFERROR, ISERROR, ISNUMBER and such functions. When you referred to the STOCKHISTORY with ISERROR you neither received TRUE, nor FALSE. A replicated alternating #CONNECT!/#BUSY! returned signaling "calculation incomplete", until a numeric response, or a “REAL ERROR” such as #VALUE! was obtained and retrieved.

Recently #BUSY! stopped alternating with #CONNECT!, and #CONNECT! had turned from a temporary--“error” into a real Excel ERROR (and ERROR.TYPE is responding with 10).

In the past when retrieving stock history #CONNECT! never cause ISERROR to return TRUE, now it does.

Moreover, occasionally retrieving STOCKHISTORY creating #CONNECT! errors and stops. However, if you re-enter the formula again (F2 on the cell, and Enter), you find out that it was certainly a temporary situation (simultaneously, many other stocks responded normally, and the same stock ticker responded okay at different time frames).

Despite all these new stuff, nothing had changed on Microsoft documentation and I found no hint pertaining to #CONNECT! error anywhere in the online documentation.

I do not know the difference between #BUSY! and #CONNECT!. They were alternating (I thought it was MS way to notify “calculation isn’t over yet”). Changing #CONNECT! to an ERROR should be done reliably, ERRORs should not be corrected by simply hitting F2/Enter a minute later.

Do me a favor… please advise or tell me where to look.



THX!

 

[DRSteele]

I have some observations.

The thing with BUSY is just that, the system is getting data from the web, processing it and depositing it into the Data Type cell. It happens every time I open a large file full of stocks, but then does not happen when it refreshes during use.

The CONNECT problem is probably down to the user's practice of overloading the system. For instance, if you use STOCKHISTORY to retrieve data for each of a large column of dates, CONNECT will arise. Even more calls for retrieval will create an irretrievable error. So try to use STOCKHISTORY differently. Here in Post#2 of that thread is a small example of what I mean.