StockHistory Issue #Connect! Error

Copper 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?

13 Replies
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.

@djgabel Hi, I am doing something broadly similar, and also find STOCKHISTORY (,...) to be very hit or miss. I can enter XLON:AAL for example in a row of other stock tickers, and the function throws up an error such as #VALUE, or #CONNECT. Exactly the same formula pasted into a cell elsewhere on the same, or a different sheet, produces the expected column of dates and closing prices. My searches have so far failed to find explanations of the error messages. This is all very frustrating, as I am writing VBA macros to model efficient portfolios, and do not have the funds to buy all this data  separately. The model will fail on a GIGO basis with ill-conditioned data inputs.

@John_Weaver  Without examples it is tough for me to say what is happening. I use stock history on roughly 500 stocks (S & P 500) and each stock gets its own column which at the top has its own STOCKHISTORY formula typed in. Since these are large and well known companies I never type the exchange to use in the formula, it just figures it out and displays that in the header along with the ticker. The #Connect! error happens ocassionaly and in my experience is an issue between MSFT and Refinitive their data supplier. Waiting an hour or so always seems to fix that issue but if it doesn't on occasion just deleting the entire column and retyping the formula clears it up for me. That does not happen a lot but when it does I know it is frustrating.

 

The #Value! error is one that every single time I found I have made a mistake entering the formula.

 

Are you also using the STOCK function and having issues there?

 

@djgabel  Wow, that was quick, I thought your's was an old post.

Yes indeed. Imagine a row of Stock fields already showing the correct company names linked to the market code ("Ticker" in the UK). Then in the first cell under these headers is the filled out STOCKHISTORY function, resulting in two columns underneath containing the hoped-for dates and associated closing prices.  I am at the stage of building up a database of the FTSE100 companies, going back to 1988, so many gaps, companies come and go, new ones arrive etc. So fragmentation is to be expected. That successful formula in the first cell has been copied and pasted across the row directly under the headers, and that is where the trouble starts.  Most columns quickly light up with the desired info, but some do not.  It implies quite a bit of oversight and manual data cleaning on my part before I can trust it enough to feed into my model.

 

PS: regarding including the market ID (mostly "XLON:" in my case), it is pretty much essential in UK. Any ambiguity about the ticker normally defaults to a similar US stock.

I understand now. May I suggest that STOCKHISTORY is going to be to "unstable" for time frames looking back 35 years? When my files get too big I use an actual database like Filemaker (I use iMacs) to load in data say longer than a year ago, then just update it routinely. I have found Yahoo Finance to be about the best free stock history database out there. The Microsoft data has holes in it left and right where certain days for some tickers are skipped. When I discover those I go to yahoo and get the correct data for those dates and then in the next cell under them I type a new STOCKHISTORY formula without the heading. I just can't imagine what a mess it must be going that far back. This particular file I have starts 1-4-2021 and it still gives me fits. Also, once a quarter the S & P 500 is rebalanced which takes me most of a weekend to correct all the calculations fed from that. Good Luck.
Looks like you are running a shadow index tracker, somewhat different from my mission. Anyway, thanks for your interest, and Good Luck yourself...
I have an issue where I get #connect errors for stock symbols on some days but not others. I have ensured all days are days markets are open and tried retyping the equation in the column/new column and copying/pasting from a cell where equation is working.

Any suggestions on why I am getting this error for only certain dates would be appreciated, thank you.

@Acnitz 

 

I believe it is a data problem from Microsoft's data supplier Refinitiv. Just try it later, that always works for me. 

@djgabel 

 

Thanks, any other tips? I have been getting this error for the past few weeks every time I open the excel workbook.

You do have Microsoft 365 right? That I think is the only version that these dynamic arrays work on. One question, do some of your items connect while others don't? If so, try saving what you have and re-opening the workbook and see if the rest load.
Hi All, I'm trying the same thing - do we know why sometimes its really fast and sometimes it takes ages to refresh? also do you find always that in the daily price data there is a N/A in row 32 - id the 32nd day after today() ?

@IndigoOwl Paste a example and the formula you wrote. From what you asked I don’t know what you are talking about. 

@djgabel I think they have a lot of problem in terms of data accuracy. I tried to use it to track the historical price of vbal, which is a Canadian stock. But the closing price is wrong from the beginning. it is usually about $.20 above the actual price. They only provide correct price beginning from January 2023.