Hi Everyone,
This is my first post so I hope I put it in the right place. I am trying to build a stock tracker which can track around 1400 stocks. I have had the benefit of using an existing macro which I have downloaded. In C2, there is a list of the stock information I am seeking to download (price, highs, lows, p/e etc) - For example "nd1t1l1ohgc1......". Then in Column A7 onwards, there is the Yahoo Stock symbols (eg: AAPL, ITV.L etc). The spreadsheet uses the following Macro to pull the data from Yahoo finances:
Sub GetData()
Dim QuerySheet As Worksheet
Dim DataSheet As Worksheet
Dim qurl As String
Dim i As Integer
Dim j As Integer
Dim k As Integer
Application.ScreenUpdating = False
Application.DisplayAlerts = False
Application.Calculation = xlCalculationManual
Set DataSheet = ActiveSheet
Range("C7").CurrentRegion.ClearContents
i = 7
qurl = "http://download.finance.yahoo.com/d/quotes.csv?s=" + Cells(i, 1)
i = i + 1
While Cells(i, 1) <> ""
qurl = qurl + "+" + Cells(i, 1)
i = i + 1
Wend
qurl = qurl + "&f=" + Range("C2")
Range("c1") = qurl
QueryQuote:
With ActiveSheet.QueryTables.Add(Connection:="URL;" & qurl, Destination:=DataSheet.Range("C7"))
.BackgroundQuery = True
.TablesOnlyFromHTML = False
.Refresh BackgroundQuery:=False
.SaveData = True
End With
j = Range("A7").End(xlDown).Row
For k = 7 To j
Cells(k, "C").Value = Replace(Cells(k, "C").Value, ", Inc. Common Stoc", "")
Cells(k, "C").Value = Replace(Cells(k, "C").Value, ", Inc. Common St", "")
Cells(k, "C").Value = Replace(Cells(k, "C").Value, ", Inc. Co St", "")
Cells(k, "C").Value = Replace(Cells(k, "C").Value, ", Inc. Co", "")
Cells(k, "C").Value = Replace(Cells(k, "C").Value, ", Inc. (The)", "")
Cells(k, "C").Value = Replace(Cells(k, "C").Value, ", Inc. Com", "")
Cells(k, "C").Value = Replace(Cells(k, "C").Value, ", Inc.", "")
Cells(k, "C").Value = Replace(Cells(k, "C").Value, ", Incorporated C", "")
Next
Range("C7").CurrentRegion.TextToColumns Destination:=Range("C7"), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
Semicolon:=False, Comma:=True, Space:=False, other:=False
'turn calculation back on
Application.Calculation = xlCalculationAutomatic
Application.DisplayAlerts = True
' Range("C7:H200000").Select
' Selection.Sort Key1:=Range("C8"), Order1:=xlAscending, Header:=xlGuess, _
' OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
Columns("C:C").ColumnWidth = 25
Rows("7:200000").RowHeight = 16
Columns("J:J").ColumnWidth = 8.5
Range("h2").Select
End Sub
=================================
That macro enables me to pull the data for around 200 shares, but I am trying to compare around 1400 stocks. If there is a limit on 200 per search, is it possible to provide that the macro will search up to 200 in 'Search 1', and then 201-400 in 'Search 2', then 401-600 in 'Search 3', etc up to the limit? That way I have a consolidated list of data. Otherwise, I have to have several separate sheets containing 200 searches each which makes it difficult to compare.
Thanks very much for your help in advance,
James
Bookmarks