Last week we showed you how to get currency exchange rates into Excel as well as the overall method for grabbing info from the web to add to your worksheets.
We had a lot of feedback (thanks) and it’s clear that many readers want a simple way to import the latest stock prices into Excel. Here’s our suggestion using the same ‘rules’ as last week: simple, no code etc.
In addition, we looked for a source that could provide:
- US Stock quotes
- Overseas stock quotes – UK, European, etc.
- Major Indices – Dow Jones, FTSE, DAX etc.
The first step is to find a web page or feed that works with Excel. Some of the web pages you’ll see recommended on the web have changed and won’t work with Excel any more. They include money.cnn.com, Yahoo Finance and the MSN Money Watchlist.
We did find two sources to use as examples (as at October 2016). They use two different Excel methods so they’re examples you can apply in other contexts.
We’ll look at Microsoft’s MSN service which still works in Excel even though it was officially dropped. This uses Excel’s External Data link or ‘web scraping’.
The more comprehensive service is from Microsoft’s rival, Google. It’s a little more complicated but has a much wider range of global data. This uses the superior Query system in recent versions of Excel.
Bonus: also included are tips on finding the correct stock or index codes from each web site.
Why is this necessary?
Microsoft has the data source via MSN Money site. Why do their customers have to go to all this trouble to get stock prices and index data into Excel?
Excel did have a pre-installed data source to MSN Money but that’s been removed. It’s still working as we’ll demonstrate.
Google understands what’s possible with their excellent GoogleFinance() function which grabs current and historical stock, fund and index data into a worksheet. This works really well and makes Excel look quite clumsy by comparison.
With all the talk about ‘Internet first’, Microsoft is missing the obvious links between their existing products.
Disclaimers
Each of these sites has a disclaimer about using the data at your own risk. The price info is delayed by at least 20 minutes (faster or real-time data costs a lot more).
We also must tell you to use this information at your own risk.
We strongly recommend you check the data feed to ensure that the intended information is what you’re getting. A single company can have many listed securities (stocks, options, bonds etc.) and be listed in different exchanges and countries. Make very sure that you’re getting the right stock code quotation.
When testing, check the incoming price against another source. For example, check the MSN Money price on company ABC with the price for the same company and security on Google Finance.
We’ve posted this article in two separate sections:
Excel: stock prices from MSN Money
Excel: stock prices from Google Finance
