Tuesday, October 18, 2005

Another web query-related $50 Project

James liked the last project so well, he requested another one right away. Thank you, James. For your reward for being a loyal customer, I placed your project at the front of the line (plus I was still in that web query mindset.)

For this project, he had many more different securities to lookup and return values for. I used Excel's built-in web query functionality, again, to do some of them, but also got a regular expression and the HTTP object to directly query some of the pages (ones on Yahoo Finance) directly, which is faster and just plain cooler.

James wanted the query to run every X minutes, which he wanted to be able to set himself. I was banging my head against the wall trying to figure out how to have one button launch a never-ending loop macro that paused for the set duration and have another button accessible to the user to stop the process. None of the methods used by others were working for me (perhaps due to the nature of the web querying going on). James settled for having one button to run the query once, and he'd click it again to run it again.

I believe I've figured out how to do his initial request, though. I was thinking about the problem wrong (as is the case in most/all such instances). Instead I needed a macro that would automatically run on file open, then stop completely for a set duraction (giving the user access to manipulate the file/run other macros), then run again, and repeat. I used the Application.OnTime method, and I think it's pretty slick. One thing, though, is that the time it takes to run all of the queries is included in the delay time, so plan accordingly.

Who else has $50?

No comments: