VBA Code not refresh sheet

[VAer]

I have a list of about 1800 companies (SP 1500 & some other companies), and I use VBA code to write the formula STOCKHISTORY and pull historical data for each symbol.

For some symbols, the data cannot be pulled and it shows #CONNECT! (or BUSY), so I save the file and close/reopen to file, occasionally, it does pull all data for each symbol in the list, so all symbols are valid. But sometimes it does not, sometimes it misses pulling data for a few symbols(maybe 2 or 3), sometimes it misses pulling data for quite some symbols (maybe more than 20). Every time when I open the file, it ends up differently, sometimes it misses pulling data for some symbols, sometimes it misses pulling data for some other symbols. It is not really the matter of time, if it cannot be pulled at the moment of opening file, the data still cannot be pulled after 20 minutes.

How to make sure excel pulls all data for each formula? Can I write extra code and make sure each formula working as expected? Actually, it is not really about VBA code, since VBA code already writes the formula into each cell, the issue is that excel cannot pull data for each formula, maybe there are too many symbols, but "too many" cannot be an excuse.

Without completely pulling the data, I am not able to analyze the data.

Thanks.

Please Login or Register  to view this content.