Thread
:
pulling stock quotes into a spreadsheet
View Single Post
#
4
Posted to microsoft.public.excel.misc
Don Guillett
external usenet poster
Posts: 10,124
pulling stock quotes into a spreadsheet
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
--
Don Guillett
SalesAid Software
wrote in message
oups.com...
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:=connectstr ing,
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 a
' 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 wrote:
go here. subscribelook in the files section. Others and I have free
files
there. Look for mine under donalb36 as the author.
--
Don Guillett
SalesAid Software
wrote in message
oups.com...
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
Reply With Quote
Don Guillett
View Public Profile
Find all posts by Don Guillett