Excel STOCKHISTORY() function repeatedly encounters #CONNECT! error.

I was having the same problem for a while and agree that this is even worse than the function not responding at all. It was then pointed out that the symbols that were generating completely erroneous quotes might be confused with similar symbols from another stock market. Eg the symbol FT exists on the NY market (XNYS:FT) but also on another market (I forget which one). This happened with 4 or 5 other ticker symbols.
If the STOCKHISTORY function is specified using "FT" as the symbol (either directly or by reference to another cell), Excel decided to pick quotes from the other market FT. This can be corrected either by specifying the exact market/ticker symbol (XNYS:FT) or by referral to a cell containing XNYS:FT using a Data>Stock type.
I didn't have this problem when STOCKHISTORY was working properly a couple of weeks ago. I had to go through all my 54 symbols and ensure they used the market:ticker format in every case. They now seem to be ok.
Microsoft's guidance for STOCKHISTORY states: "Enter a ticker symbol in double quotes (e.g., "MSFT") or a reference to a cell containing the Stocks data type. This will pull data from the default exchange for the instrument. You can also refer to a specific exchange by entering a 4-character ISO market identifier code (MIC), followed by a colon, followed by the ticker symbol (e.g., "XNAS:MSFT")"

It could be that the system is no longer pulling data from the default exchange and is being arbitrary about which exchange to use. By following the last sentence of the guidance, the correct ticker should be identified.

I am still having probems with the STOCKHISTORY function, but not in terms of producing ERRORs I am no longer getting "Excel ran out of resources" that others have mentioned either. I have posted the following under a new thread per your suggestion, but wanted to also post it here FWIW to others in the thread.

My new problem is that STOCKHISTORY is returning incorrect prices for random tickers. And the specific ticker(s) for which it happens is/are different every time I re-open the file containing the errors. I've tried overwriting the incorrect cells with the same formula from a different cell that is correct and re-calcing, but it just reproduces the same incorrect result for the erroneous ticker in question. I have also been re-opening the file, which usually results in the previously erroneous results being corrected, but then an erroneous results shows up for a different ticker. I've tried this both with automatic re-calc and manual re-calc, but it is rarely and very inconconsistently correcting the problem for ALL tickers at one time in either case.

Though a less widespread problem than before in terms of the number of tickers affected at one time, this is actually worse in a sense since it is not producing error messages. It is producing results that are incorrect and those erroneous results are not readily aparent without digging to find them. I only discovered that it was happening because some higher level calculations using multiple ticker results were not making sense. This all but renders my analyses useless and takes up a lot of time to have to go in ticker by ticker to identify the erroneous results and account for them accordingly in my decision-making because the occurence of the problem is seemingly completely random.

Please make this a very high priority to correct.

Thank you.

Ok, thank you very much, Thomas. I understand there isn't much you can do. It's harder to understand MS's somewhat cavalier attitude, at least in my opinion.

Thanks for your effort.

Hi Jacques,

We have contacted them via email, but their response to us is "do not advise users to roll back their Office version, as it won't help" and "reassure the user and wait for the development team to fix it." I really wish I could help you but please forgive me there is nothing I can do at this time.

Thomas

I feel your pain. This issue has cost me several thousand $ in lost productivity. I switched over to a paid data service with an Excel add-in: Ycharts. The cost is around $250 a month but it is well worth it. STOCKHISTORY() was an amazing function while it worked. I hope they get the bugs figured out.