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…