T
tmann
Hi all -
I think this would be an easy one. Very new to writing Macros...still
in the stage where I'm taking other folk's code and tweaking it for my
own needs. I'm trying to pull stock quotes from Yahoo, and have found
a great (almost) script to do this (on Google groups) -- see below.
Here's my problem: when I enter a stock symbol (say msft) into A2 (or
any row in col A), it places the stock quote in row 1. How I can I get
it to begin the list of quotes in row 2 (or 3, or 4, etc) ???
Thanks,
TMann
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
I think this would be an easy one. Very new to writing Macros...still
in the stage where I'm taking other folk's code and tweaking it for my
own needs. I'm trying to pull stock quotes from Yahoo, and have found
a great (almost) script to do this (on Google groups) -- see below.
Here's my problem: when I enter a stock symbol (say msft) into A2 (or
any row in col A), it places the stock quote in row 1. How I can I get
it to begin the list of quotes in row 2 (or 3, or 4, etc) ???
Thanks,
TMann
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