pulling stock quotes into a spreadsheet

[jgalt650-excel]

Excel's web query tool (Data/Import External Data) makes it very easy
to pull stock quote information into a spreadsheet.... if you have a
good site that supports and can accomodate it. Yahoo used to be able
to do handle this, but my queries generally fail now. Google works,
but only for one portfolio.

Does anyone have any suggestions as to best sites to use to pull stock
price data into a spreadsheet?

Thanks,

JG

 

[Don Guillett]

go here. subscribe>look in the files section. Others and I have free files
there. Look for mine under donalb36 as the author.
(e-mail address removed)

 

[jgalt650-excel]

This is what I came up with. Start with a vertical range of ticker
symbols named "tickers1". It relies on Yahoo's flexible format for
distributing stock quotes in csv format.


Sub QuoteY()
'
' This pulls quotes in from Yahoo one cell to the right of a vertical
column of ticker symbols
'
Dim qt As QueryTable
Dim tickerstring, connectstring As String

tickerstring = localconcat(Range("tickers1"), ",")
connectstring = "URL;http://finance.yahoo.com/d/quotes.csv?s=" &
tickerstring & "&f=l1"

Set qt = ActiveSheet.QueryTables.Add(Connection:=connectstring,
Destination:=ActiveSheet.Range("tickers1").Offset(0, 1))

With qt
.Name = "T1"
.RefreshStyle = xlOverwriteCells
.Refresh
End With

end sub


Function localconcat(avec As Variant, Optional CHAR2INS As String) As
String
'========================================================
' this improves on the =concatenate function
' Arguments are:
' AVEC - this is the vector to be concatenated
' CHAR2INS - is the charachter to insert between cell items. If this
is
' Left blank, Nothing Is inserted
'========================================================
Dim i As Integer
Dim j As Integer
Dim numrows As Integer
Dim numcols As Integer
Dim temp As String
temp = ""
numrows = avec.Rows.Count
numcols = avec.Columns.Count
For j = 1 To numrows
For i = 1 To numcols
If j < numrows Then
temp = temp & avec(j, i) & CHAR2INS
Else
temp = temp & avec(j, i)
End If
Next i
Next j

localconcat = Application.Trim(temp)

End Function



JG

 

[Don Guillett]

Nice,
You will want to delete the T1 name or it will build up & up & up(look in
defined names)
You need to change to something not similar to a cell name or just leave out
the .name=
you need to add BackgroundQuery:=False

For Each Name In Sheets("Data").Names
'Name.Delete
'if same sheet
if name<> yourdefinedrangename then name.delete
Next Name
or create the first time and refresh instead of adding each time

Here is one way I have used to get the symbol list within my query macro
Or, it could use for each c in a defined name range instead
x = Sheets(1).Range("a" & Rows.Count).End(xlUp).Row
For Each c In Sheets(1).Range(Cells(5, 1), Cells(x, 1))
'for each c in yourdefindenamerange
symbols = symbols & "+" & c
Next

you might also want to turn off screen updating during your code