Download Stock Quotes Into MS Excel

I recently ran into a problem downloading stock quotes into my investments spreadsheet. I used this technique to pull stock quotes from Yahoo!Finance directly into my investment tracker. There are alternatives, but this was the cleanest way I found to extract just the trade quote iteself without a lot of the other stats the come along with stock quotes elsewhere.

However, I ran into a problem over the past month. The web query stopped working - it looks like Yahoo has redesigned their Yahoo!Finance web page and the Excel web query tool no longer see the stock data in a table format is therefore is unable to import it properly.

I was able to string up a way to extract the quotes, and still put them in a cell like I have have been doing, with a little extra background work.

First I used the same technique to pull in the stock quotes, but I changed from importing the web page to importing the comma seperated version of the quotes via the link at the bottom of the page.

This gave me a cell for each stock with all the stats stock symbol, last quote, last trade, etc. Here is an example of the string:


Then I just had to use some functions to whittle it down to just the last trade value and convert it to a number in my spreadsheet:

*Where T31 points to the cell with the stock information string above

Takes an extra cell to do the parsing, but it still allows me to get the data I need to maintain my investment tracking stats.

Anyone found easier ways to get stock quotes into tracking spreadsheets?

Related in Stocks:

Chairmen Letters to Shareholders (Mar 09, 2014) Its that time of year again --the close of fiscal years means an overload of annual reports including Letters to Shareholders. Two annual letters that I read each year are those from Berkshire Hathaway (Warren Buffet) and Fairfax Financial (Prem...

Investment Performance January 2014 (-2.94%) (Feb 23, 2014) January 2014 Investment Report: January Highlights: January was a bad way to start out the year, but our portfolio performed slightly better thank our benchmark (-2.94% vs -3.17%). We made our regular monthly investments in our Roth IRAs, and some...

Investment Performance December 2013 (+2.20%) (Jan 10, 2014) December 2013 Investment Report: December Highlights: December was another subpar for us as our portfolio performed poorly compared to our benchmark (+2.20% vs +2.58%). We made our regular monthly investments in our Roth IRAs, and some dividends & dividend reinvestments....

Comments (5)

The MSN Stock Quote add-in for MS Excel works well and pulls data directly from the web into your spreadsheet.

It's made for Excel 2002/2003, but some seem to indicate it works for Excel 2007.

I find Google spreadsheets to be perfect for this. They include functions to automatically fetch quotes, anything from volume, price, change, etc.

If you want a "hardcopy" (i.e. import it into Excel) you can download almost format.

I'm using this excel addin for Yahoo Quotes.

I used to use the MSN Stock Add-in for Excel, but switched to Google Docs. A good thing is that I don't have to carry the excel file with me. I can edit the document anywhere and I publish charts on my Google Page.

You can have a look at this tool (for Excel) to track intraday quotes in an easy way:

Post a comment

(Comment moderation enabled.)


A personal finance weblog of my journey to reach my goal of $2 million + the value of my primary residence.
Current Net Worth: $1,938,393


New Personal Finance Articles

PF Blogs