STOCKHISTORY PUZZLE (#CONNECT! turned into an ERROR)

mookyon

New Member
Joined
Dec 23, 2021
Messages
31
Office Version
  1. 365
Platform
  1. Windows
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!
 

Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.

DRSteele

Well-known Member
Joined
Mar 31, 2015
Messages
2,575
Office Version
  1. 365
Platform
  1. Windows
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.
 
Upvote 0

Forum statistics

Threads
1,186,627
Messages
5,958,873
Members
438,379
Latest member
impuce

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top