A few days ago I received an email telling me that the spreadsheet that got prices from the Yahoo finance website no longer worked. One clear downside of using web queries is that they sometimes require work if the publisher changes the way the website is presented or built. In this case, the “last price” is not as easy to access. He mentioned a MSN plugin but I thought I’d try to build a new one using MSN Finance and it proved fairly easy to do. First off, I did a manual web query to see if the last price of any stock would appear (it should) and it did. One problem is that it is not written “Last Price” so what would be the best way to “find it”? I decided to look at the prior line (which in all cases is “Print Report”
So I decided to do the query in column A:
=IF(C1=”Print Report”,1,0)
I added this in A2. This simply verifies what the prior line had. Then, I need the price only, not the variation so I decided to take whatever was before the “space”. I did the following:
=IF(A2=1,FIND(” “,C2))
For that specific line, this gives me the last price.
Then, I simply needed a query that would go through each line of the page, adjust the query, find the price and add it to the page. It’s very similar to what I had done in the previous file. Here is my code:
You can of course download my entire spreadsheet here.
***************************************************
Look Good at Work and Become Indispensable Become an Excel Pro and Impress Your Boss
***************************************************
