200 Limit on Stock Tracker

[jamesmanchester92]

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

[AB33]

James,
Welcome to the forum!
Please read forum's rule regarding wrapping your code with code tags.

j = Range("A7").End(xlDown).Row
You have not given us as to why the code stops at 200. Is it because the site has a limit or is it because there is a fault in your code? I suspect you have empty row in column A and code stops at 200.

Try to use this line

Please Login or Register  to view this content.

[AB33]

Looking again if there is empty row in column A, the code will stop here.

While Cells(i, 1) <> ""

Please attach a sample so as to test why the code stops at 200.

[jamesmanchester92]

Hi AB33, Thanks for your reply. I am using a template which I downloaded from this website http://www.marketindex.com.au/yahoo-finance-api - That contains an unedited version of the source code in the macro. Does that help in solving why the macro stops updating at 200?

Thanks,

James

[AB33]

Hi James,
One of the caveat is "You’re limited to calling 200 codes at a time"
I do not know what does this sentence mean. What happens when you reach 200, does the code stops? Is the restriction built-in the site itself. For instance, Google's distance map code puts a restriction on how many one can download per day. Once you exceed the daily limit, the code fails.