Excel macro to get stock quotes off the www

[Bob Benjamin]

How can I write a macro to go to
a stock quote web page on the Internet
and download to a Excel spreadsheet
the values?

Are there any already written macros somewhere
(in a book, on the internet etc) that I could modify to do this?

 

[shockley]

This procedure uses a list of symbols in the first column of the first sheet
in the workbook and gets basic quotes from Yahoo.
The max number of quotes is 20-30 per access-I keep the limit at 20. You
can loop the procedure to get as many quotes as you like.


HTH, Shockley


Sub GetYQuotes()
Base01 = "http://finance.yahoo.com/d/quotes.csv?s="
Base02 = "&f=sl1d1t1c1ohgv&e=.csv"

sURL = ""
SymString = ""
LastRow = Cells(65536, 1).End(xlUp).Row
For i = 1 To LastRow
SymString = SymString & Cells(i, 1) & " "
Next i
sURL = Base01 & SymString & Base02
Workbooks.Open sURL
Set rngSource = Cells(1).CurrentRegion
x = rngSource.Rows.Count
y = rngSource.Columns.Count
With ThisWorkbook.Sheets(1)
Set rngDest = Range(.Cells(1, 1), .Cells(x, y))
End With
rngDest.Value = rngSource.Value
ActiveWorkbook.Close SaveChanges:=False
End Sub