StockHistory Issue #Connect! Error

New Contributor

I built a workbook around the StockHistory function to track the stocks in the S&P 500. I did this a few months ago and I update it once a day. Each stock is presented according to which SPDR Sector the stock resides in. The workbook keeps track of various moving averages, gives me the percentage of stocks by market cap below those various averages. It also computes relative strength for each stock as it compares to the SPDR Sector it is in. Frankly the StockHistory function has been tough to work with and requires a lot of TLC. Refinitiv's data can be pretty hit or miss. A number of the tickers are missing data for a day in the past year or so. It requires a workaround.... no big deal but you would think they would clean the data they distribute so it is correct.

 

I frequently run out of resources but just saving the spreadsheet and reopening usually clears it up.

 

This week I have come across a new error and that is StockHistory returning a #Connect! error. I have spent way too much time looking on the Internet for what is happening and there are only a few posts on various boards that even mention this. One post says that it means the server is busy and try again later. I have done that and it still doesn't work after multiple tries.

 

One other post I read states it is the data provider (I am guessing Refinitiv) throttling the data flow. If so that is very disappointing as more and more users discover this function.

 

I use Office 365 so I pay for the subscription and it is up to date. I use the Excel for Mac version on my desktop, I have the latest OS system and it automatically updates. 

 

To be clear, there have been no changes to my spreadsheet and this error just started appearing. I would have thought that if Microsoft has Excel return an error they would have an easy to find explanation of the error and steps to correct it but if it is out there I can't find it.

 

Can anyone help?

1 Reply
Well, since no one has replied I thought I would update those that come after me what I have done to fix the problem. While I can't say for sure that my fix is what did it, it seems to work now.

Each Column of StockHistory which had the #Connect! error seemed to be corrupted. I tried to use earlier versions from time machine but I would guess I did not go back far enough. Still getting the the error after that.

The end result is that I totally started over for each of the "corrupted" columns by deleting and then retyping the formula into the cell. Just checking it or re-entering did not do it but completely deleting and retyping the formula seemed to cure the problem.