StockHistory Issue #Connect! Error

[djgabel]

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?

[#2]

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.

[#3]

@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.

[#4]

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

[#5]

@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.

[#6]

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.

[#7]

Looks like you are running a shadow index tracker, somewhat different from my mission. Anyway, thanks for your interest, and Good Luck yourself...

[#8]

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.

[#9]

@Acnitz 

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

[#10]

@djgabel 

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

[#11]

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.

[#12]

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() ?

[#13]

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

[#14]

@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.

[#15]

@AlanCaiOttawa 

Upon experimenting with the date field for the STOCKHISTORY function I find that some dates yield good data and others don't (CONNECT Errors). If this data is coming from Refinitiv or some other database it feels like a sparse or incomplete matrix like problem, where some dates are not populated with the stock prices and return an error.  That's not acceptable for a general-purpose function like STOCKHISTORY. It seems like the problem is with the database and not the function.

[#16]

@srv97229 

My opinion is that it is a combination of problems. The "connect" issue seems to fix itself while bad data is just that....... bad data. There is no way to change it because Microsoft never answers you when you make suggested changes or point out the bad data. Definitive, who I have emailed also ignores you.

I end up for the dates that are missing data just ending STOCKHISTORY the day before, then used YAHOO data and hand enter it, then the next day after the bad data I enter STOCKHISTORY again.

[#17]

Refinitive not Definitive

[#18]

I have been using StockHistory for 2 years in a lay or non-profesional capacity. Over the last 3 days I have been experiencing #Connect! Error in StockHistory cells that have not changed or been modified in 2 years. If I reopen the book the Error Cell may be populated with correct data but a different cell that was data correct prior the Workbook close is now showing an Error. I can repeat this open and close of the Workbook and get different results each time.

By the sounds of it there is no recourse with Microsoft or their data provider so what does one do? Manual entry is not an option as the sells have dynamic date references and there are thousands of cells to check and or input data.

Maybe I move away from the excel platform but where do you go?

Does anyone have any suggestion how I can fix this and or recommend a better platform that has a similar feature to StockHistory?

Thanks

[#19]

Also I am running Office 365 on a Mac platform, the OS is up to date and so are all the apps.

Does anyone know if there is a premium data subscription for Office or Excel that can priorotise requests?

Thanks again

[#20]

@Nstat1560  I have pretty much resolved all my old issues with Stock History, which I now use at the end of each cal month to update prices for my c.1620 UK listed stocks (I have been developing a portfolio optimisation model).

My macro(s) works by:

1. entering the bare codes, without reference to the exchange they are listed on

2. forcing  a 5-10 sec pause after entering each new code, to give the streaming time to catch up, followed by "calculate" (this is important). 

3. copying values of prices over to a separate sheet, before inputting the next code

4. once all the codes have been downloaded, I run another macro which replaces all # type errors with a nul character i.e. an empty cell

5. I then run a second macro which looks for empty cells, or up to two adjacent empty cells, and insert dummy prices by linear interpolation 

Any stocks not amenable to this process, I just dump from my model as too much hard work!

Hope this helps a bit?

John