Excel: stock prices from MSN Money
Here’s how to grab stock price and indexes into Excel via MSN Money.
It’s part of our series on getting ‘live’ data into Excel – Introduction and the Google Finance option.
Microsoft dropped the MSN stock quotes option from Excel but they are still available. Use the ‘web page scraping’ part of Excel to do it.
Go to Data | Get External Data | From Web
Enter a web link in the address bar at the top and click Go. We’ll look at the exact web link a little later, for the moment you can use http://www.msn.com/en-us/money/quoteslookup?MSFT,AMZN,DJIA to show two US stocks and the ‘Dow Jones’.
Click Yes if you get any script errors.
Click on the yellow arrow at top left to select the entire web page. Alas, there’s no other table on the web page to select.
Then click Import and select a New worksheet as the destination. The entire page you selected is dropped into the worksheet like this:
From there, you can use VLOOKUP() to grab the last price (or one of the other data cells) for a stock or index. Sadly, the Microsoft data doesn’t include the stock code so you have to match VLOOKUP() by the full name.
Reminder: VLOOKUP() requires an alphabetically sorted list so try this: Order the quote symbols in alphabetical order by name eg http://www.msn.com/en-us/money/quoteslookup?AMZN,DJIA,MSFT not the order shown above. Use a specific range for VLOOKUP() such as A4:A6 not the entire column A.
Finding quote codes
You’ll want to add your own codes to the link http://www.msn.com/en-us/money/quoteslookup? Separating each code with a comma.
There’s a limit of around 35 codes to each page link. The exact number is uncertain so we suggest sticking to a maximum of 30 codes to be safe. Simply create more than one web page connection and use a second (or third) link to get all you need.
To find the right code go to the MSN Money lookup list starting at http://www.msn.com/en-us/money/Entities/A/ That list is long and not always complete.
You can find other stock or index codes by searching the main MSN Money site. When you find something, look on the browser address line. On that line is the quote code MSN uses. For the Aussie ‘All Ordinaries’ index the code is shown on the address bar: XAO
You can test the code by using this link
http://www.msn.com/en-us/money/quoteslookup? followed by the code.
For example: http://www.msn.com/en-us/money/quoteslookup?XAO appears like this:
