Well, it's happened: I have received a project that I will not be able to produce as specified.
Tom wanted a way to update a system of Excel worksheets using MSN's Stock Quote toolbar add-in to refresh current stock quote data. He uses this system to track and produce billing information for his investment management clients' accounts. The problem with the add-in is that it only allows users to get updates every five minutes. I cannot find any way around this limitation on refreshing stock quote data. I've tried everything I can think of: taking over refreshing with VBA commands, using different "instances" of Excel (as opposed to opening multiple files in the same instance of Excel), etc.
In addition, none of the other Excel gurus out there have published anything (that I can find) on the topic about getting around this limitation. Most of them actually balk at the idea of using the toolbar at all, "...when getting data via web querying Yahoo is available..."
In the end, I've had to suggest to Tom two alternatives, neither of which, I'm sure, are all that attractive, given the amount of time, effort, and familiarity he's got with his system as designed now:
- Redesign the system so that there's one "master" Excel file that contains all current quote data for all client-account-holdings, and then have each of the account worksheets reference data from that file. (This creates only one place that has to be refreshed, so doing one master refresh will ripple down to each of the other worksheets when you click "update links" upon opening them.)
- Redesign each of the client-account worksheets incorporating automated web queries (using VBA) to enable automatic and ad hoc/on the fly "refreshes" as often as needed.
We'll see what he wants to do. I, of course, won't be asking for $50 until he's satisfied that he's gotten a solution he can live with.
Aany readers out there that have experience with the MSN Stock Quotes toolbar add-in, and have some suggestions for getting around this 5-minute limitation, please send them to me or post a comment on this blog. Thanks in advance!