Limitation on MSN's Stock Quote toolbar add-in

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:

1. 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.)


2. 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!

Excel_Geek

Interesting New $50 Project - Excel as Tool for Investment Management

Tom from New Jersey recently requested a project involving the system of Excel spreadsheets that he uses to track and manage his investor clients' portfolios, as well as provide billing for his services.

He's using Smart Tags to retrieve current stock quote information (from MSN Money Central), but is having some trouble. MSN Money Central only lets him refresh the quotes every five minutes (per spreadsheet). This is somewhat problematic because if a single spreadsheet has more than one worksheet (for example, if a client has more than one IRA account, he might have them each in a different worksheet within the same spreadsheet) he has to wait five minutes between refreshing each client account.

Tom would like to know if there's some "master switch" he can throw to get all stocks in every worksheet updated, even if it's once per day, say at 6 pm or 9 am, or anytime. Further, he's looking for some kind of macro that would automatically open all of these files and refresh all stock quote info at a particular time. Further, he's spent a great deal of time developing his system and has around 200 of these worksheets, so any wholesale change -- like switching the source of his data from MSN Money, etc. -- would be very problematic.

This is going to be a fun one. I love investment-related projects. Makes me want to get my old day trading automator back out and dust it off...memories...

I'll keep you all posted.

BTW, my Excel_Geek Blog_Mail subscriber list keeps growing! I want to, again, pass along my humblest appreciation to all of you who make me feel as though what I'm doing really helps people. Thank you.