I've been working on a mock stock portfolio in Excel, and I've been looking for ways to automatically update the data, eg. Stock price and P/E ratio. I have tried using a web query to MSN Money, but that just brings up the whole stock quote across multiple cells, I want data to be updated in individual cells only.
The only web query solution I can think of is if someone hosted a website where each value in the stock quote was saved on a different HTML file. I could then WebQuery to that file for each cell requiring that value. However, no website offers this. So in essence, is there any tool on Excel 2011 Mac that will let me pull individual values from a stock quote and assign them to a single cell?
Using the Stock Connector add-in with Excel 2010. Excel 2010 does not support Stock Connector or any other web-based add-ins. If you're using Stock Connector with a workbook and then open that workbook in Excel 2010, you will still be able to use all the normal Excel features, but the stock prices will not update.
![Stock Stock](http://kerbcraft.org/wp-content/uploads/2018/02/excel-stock-quotes-new-import-historical-stock-prices-from-yahoo-udf-of-excel-stock-quotes.png)
![Excel Excel](https://i.ytimg.com/vi/iMO4Shnj1kI/maxresdefault.jpg)
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 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 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? 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 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? Followed by the code.
For example: appears like this.