Creating an automatic updating database of the S&P universe from yahoo/google finance

Hi everyone.

Not sure exactly how to go about doing this but I thought someone on here may be able to help.

I have a list of all the symbols in the S&P 500. What I want to do is link all of their cells to a data source like yahoo or google finance where the prices will automatically update each and every day,

Does anyone know how I could go about completing this?

Hey thanks a bunch for this, really appreciate it!

is this vba code?

you said it only pulls up previous days close... is there any way to pull up todays close at say 5 oclock every day in order to run some tests at the close of the market?

Yes. It's VBA code. for what it's worth, I just ran it on all 500 tickers and it took 19 minutes. That's pure trash, but it would probably be only 6 or 7 on a better connection - which I think is still trash.

The issue is with the table on the website. You will have to find a website that gives quotes where excel will recognize the closing price (or maybe even current price to account for after hours trading). Excel, to my knowledge, won't recognize that field on yahoo or google to pull in by query table. I looked around for one that did, but I didn't find one off hand.

As I mentioned before, the ideal thing would be to find a website that lists all 500 tickers in one table, along with their current prices. That way excel would only have to make one query and you could have the data pulled in within seconds.

Alrighty that makes a lot of sense!

I'll be honest, I don;t know vba other than how to access it...

I copy and pasted your code into vba in my spreadsheet and have been trying to run a test on all the symbols but every time it do it highlights the first line in red "Sub stockimport A2:A500 End Sub" and an error saying "compile error: expected:end of statement" comes up

Hate to be a pest but i'm not to sure what to do in order to get this thing going

I'm not sure you copied the code over right based on that error.

Either way, I have a better solution for you that doesn't require VBA.
Bring up a new version of excel, and put your list of tickers in say, sheet1. Go to sheet to. In the top bar, click data, then the existing connections button. You will (hopefully) see "MSN MoneyCentral Stock Quotes" in there. Highlight that and click open. It will ask where you want the data, and then what symbols (you have to select the range of cells where the symbols are). It seems to error out if you try and pull in too many at a time, but I've gotten it to pull in 125 tickers with last price, close, high, low, etc in about 2 seconds. I'm not sure you can record a macro to drive this because the VBA seems to rely on a "MSN MoneyCentral Investor Stock Quotes.iqy" file that conceals the range selection operation.

That might help you for now.