pulling stock quotes into a spreadsheet
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 |
pulling stock quotes into a spreadsheet
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 |
pulling stock quotes into a spreadsheet
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 |
All times are GMT +1. The time now is 11:47 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com