Forum Discussion
StockHistory Issue #Connect! Error
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.
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.
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.
- Looks like you are running a shadow index tracker, somewhat different from my mission. Anyway, thanks for your interest, and Good Luck yourself...
