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 ...
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.
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.
- 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?
ThanksNstat1560 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
- Refinitive not Definitive
