Change VBA Code,Stock look up
 

I have this code that downloads stock quotes into Excel
from Yahoo.It was posted some time ago on the newsgroup
boards by a poster named, 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

This code uses a list of symbols in the first column of
the first sheet in the workbook and gets basic quotes from
Yahoo.Instead of the first column I would like to be able
to assign a column(range?)for the symbols that are to be
looked up. For example; column("B") or range(B1:B20).
Would I need to change the destination range too? I new to
this gave it a shot and failed. Please help

thanks in advance,
Paul
P.S.using Office 2000

Change VBA Code,Stock look up
 

Change


LastRow = Cells(65536, 1).End(xlUp).Row
For i = 1 To LastRow
SymString = SymString & Cells(i, 1) & " "
Next i


to

Dim cell as Range
for each cell in worksheets("Sheet1").Range("B1:B20")
SymString = SymString & Cell & " "
Next

The destination is a new workbook, so you don't need to change that.

--
Regards,
Tom Ogilvy


paul wrote in message
...
I have this code that downloads stock quotes into Excel
from Yahoo.It was posted some time ago on the newsgroup
boards by a poster named, 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

This code uses a list of symbols in the first column of
the first sheet in the workbook and gets basic quotes from
Yahoo.Instead of the first column I would like to be able
to assign a column(range?)for the symbols that are to be
looked up. For example; column("B") or range(B1:B20).
Would I need to change the destination range too? I new to
this gave it a shot and failed. Please help

thanks in advance,
Paul
P.S.using Office 2000

Change VBA Code,Stock look up
 

-----Original Message-----
Change


LastRow = Cells(65536, 1).End(xlUp).Row
For i = 1 To LastRow
SymString = SymString & Cells(i, 1) & " "
Next i


to

Dim cell as Range
for each cell in worksheets("Sheet1").Range("B1:B20")
SymString = SymString & Cell & " "
Next

The destination is a new workbook, so you don't need to
change that.

--
Regards,
Tom Ogilvy


paul wrote in
message
...
I have this code that downloads stock quotes into Excel
from Yahoo.It was posted some time ago on the newsgroup
boards by a poster named, 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

This code uses a list of symbols in the first column of
the first sheet in the workbook and gets basic quotes
from
Yahoo.Instead of the first column I would like to be
able
to assign a column(range?)for the symbols that are to be
looked up. For example; column("B") or range(B1:B20).
Would I need to change the destination range too? I new
to
this gave it a shot and failed. Please help

thanks in advance,
Paul
P.S.using Office 2000


Thanks so very much Tom.
Happy holidays to you an your family.

Paul

one more question.
 

The change worked great.
When I put a second range of symbols below the first the
data go up to the first open cell. In my case that would
be B1.How do I make keep the data aligned next to my
second set of symbols(Mysymbols=A30:A47)I know that I will
have to make a second macro then run them sequentially.But
how do I go about keeping them aligned? It looks like this
part to me.
Workbooks.Open sURL
Set rngSource = Cells(1).CurrentRegion
x = rngSource.Rows.Count
y = rngSource.Columns.Count
With ThisWorkbook.Sheets(2)
Set rngDest = Range(.Cells(1, 1), .Cells(x, y))
But again I am lost.

Thanks again,
Paul
-----Original Message-----
Change


LastRow = Cells(65536, 1).End(xlUp).Row
For i = 1 To LastRow
SymString = SymString & Cells(i, 1) & " "
Next i


to

Dim cell as Range
for each cell in worksheets("Sheet1").Range("B1:B20")
SymString = SymString & Cell & " "
Next

The destination is a new workbook, so you don't need to
change that.

--
Regards,
Tom Ogilvy


paul wrote in
message
...
I have this code that downloads stock quotes into Excel
from Yahoo.It was posted some time ago on the newsgroup
boards by a poster named, 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

This code uses a list of symbols in the first column of
the first sheet in the workbook and gets basic quotes
from
Yahoo.Instead of the first column I would like to be
able
to assign a column(range?)for the symbols that are to be
looked up. For example; column("B") or range(B1:B20).
Would I need to change the destination range too? I new
to
this gave it a shot and failed. Please help

thanks in advance,
Paul
P.S.using Office 2000


.