Monday, October 17, 2005

New web query-related $50 Project

James (a much-appreciated repeat customer) requested a project on Friday that involved automating a weekly process of going to four different websites (one for each of four different mutual funds) and retrieving the effective 7-day yields for the funds and placing them in a newly added column in an Excel worksheet.

My initial plan was to use VBA to create web calls that returned the text for each of the four pages, then use four different regular expressions to match the data desired and return it, placing in the worksheet. However, I ran into a bit of trouble with the regular expression part of it, so I instead utilized the web query functionality built into Excel to retrieve the entire tables of data on each web page, then simply used cell referencing to place the data where it is to be stored in the worksheet.

In the end the results are identical, though I think performance suffered a bit. Total processing time (on broadband) takes about 5-6 seconds, while if I'd gotten my initial plan to work, it would likely have been 1-3 seconds.

I think I'll take another stab at doing those regular expressions (just for my own sake), and I'll share them with James if I make them work.

Thanks, James.

Everyone else, keep those $50 Project requests coming!

No comments: