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