VB in Excel with Web Queries
I do extensive stock evaluation for investment purposes. I've developed several Excel spreadsheets to capture historical and current stock prices. Historical prices may be found on many free web sites (I use Yahoo! and Bigcharts), but updating two hundred or more prices requires a lot of clicking and keying onto the spreadsheets.
I'm trying to use VB code in Excel to automate the process.
Example: A stock ticker symbol is found in, say, cell B4, and the historical date for which I need a price is found in cell D4. I can capture the Yahoo! (or any other web site service hypertext) URL for historical quotes and its parameters, but they're static and can't be altered except manually. Is there a way to set a VB macro to dynamically alter the hypertext to pull its parameters from a cell? Therefore, if I change the data in the cell (i.e. stock symbol or historical date), I don't have to manually retype each hypertext item in the spreadsheet for 100 stocks or more.
I've been researching this for a couple of months and can't find a solution, if one exits. Any help would be greatly appreciated.
Top DevX Stories
Easy Web Services with SQL Server 2005 HTTP Endpoints
JavaOne 2005: Java Platform Roadmap Focuses on Ease of Development, Sun Focuses on the "Free" in F.O.S.S.
Wed Yourself to UML with the Power of Associations
Microsoft to Add AJAX Capabilities to ASP.NET
IBM's Cloudscape Versus MySQL