Forum Discussion
StockHistory Issue #Connect! Error
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?
- 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 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.
