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.

http://download.finance.yahoo.com/d/quotes.csv?s=MKL&f=sl1d1t1c1ohgv&e=.csv&d=v5

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:

MKL,308.50,"8/13/2009","4:01pm",0.00,N/A,N/A,N/A,0

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:

=VALUE(RIGHT(LEFT(T31,SEARCH(",",T31,SEARCH(",",T31)+1)-1),SEARCH(",",T31,SEARCH(",",T31)+1)-SEARCH(",",T31)-1))
*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:

Investment Performance March 2012 (+2.35%) (Apr 18, 2012) This is an ongoing monthly update on how our equity investments are performing. Please see this background on the investment tool I developed and how I am using it to track our performance against a benchmark to measure our progress...

Investment Performance February 2012 (+4.68%) (Mar 21, 2012) This is an ongoing monthly update on how our equity investments are performing. Please see this background on the investment tool I developed and how I am using it to track our performance against a benchmark to measure our progress...

How Well Has Our Investment Portfolio Performed? (Mar 02, 2012) I've now been closely tracking our investment portfolio on a monthly basis for over 3 years! Its been a lot of work and valuable for me personally as I can see the bigger picture each month. One realization that has...

Comments (4)

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

http://www.microsoft.com/downloads/details.aspx?FamilyID=485FCCD8-9305-4535-B939-3BF0A740A9B1&displaylang=en

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

Posted by Steve | August 15, 2009 3:04 PM

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.

Posted by jay | August 15, 2009 3:50 PM

I'm using this excel addin for Yahoo Quotes.
http://finance.groups.yahoo.com/group/smf_addin/files/Templates%20and%20Examples/RCHGetYahooQuotes/

Posted by wealthjourney | August 16, 2009 7:33 AM

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.

Posted by Geoffrey | August 16, 2009 9:06 AM