Pulling current stock prices into Excel

A question from Yahoo! Answers:

Pulling current stock prices into Excel?

What’s the best way to pull yesterday’s closing prices and/or current stock prices into Excel? I want to do this for *every* traded stock in S&P500 *every* morning, rather than for just a subset.

Here’s a possibility:

  1. Put together a comma-separated list of stocks constituting S&P 500 (no blank spaces, just symbols and commas).
  2. Construct a long URL from three parts:
    • http://finance.yahoo.com/d/quotes.csv?s=
    • Your list
    • &f=sl1d1t1c1ohgv&e=.csv
  3. Download the file from the URL you just constructed. This will be a CSV file containing current prices of all stocks in S&P 500.

It’s possible that Yahoo will not be able to return 500 symbols at once, because there may be limits on the length of a URL it can accept. If so, you’ll have to create several sublists and download them one at a time.

Another possibility is to write an Excel macro, which would retrieve quotes one by one…

This entry was posted in Answers, Finance and Investments, Technology. Bookmark the permalink.

Leave a Reply

Your email address will not be published. Required fields are marked *