Limited to 200 stocks with Yahoo Finance API

[Asif2013]

Hello all!

I was here a few years ago and got an answer to my question then so I am hoping someone can help me now.

I am trying to download data from the Yahoo Finance website using their API.

I have one Excel sheet which downloads everything as I want it but only allows 200 stocks as Yahoo limits you to 200 stocks.

The other Excel sheet which I've got doesn't download everything as I want it but it allows over 200 stocks and even over 1000 stocks which is what I want.

I don't know how to change the first one so it allows over 1000 stocks...

I got the second file from this website - it's file Yahoo3.xls but I have modified it slightly.

The two Excel worksheets are shared here below but I've included the code as well just in case you don't want to download the worksheets...

https://docs.google.com/file/d/0Bw8wqAZhmgwaYm1lTHRUTkhITWc/edit?usp=sharing

https://docs.google.com/file/d/0Bw8wqAZhmgwacU5JaWlGVzVYams/edit?usp=sharing

Thanks in advance for any help you can provide me.

Private Sub buttonRefesh_Click()
Dim Worksheet As Worksheet:  Set Worksheet = ActiveSheet
Dim LastRow As Integer:  LastRow = Worksheet.Range("C10000").End(xlUp).Row      ' Find the last row used
If LastRow = 1 Then Exit Sub
Dim Symbols As String                                                           ' Stock Tickers
Dim i As Integer
For i = 3 To 202 'It Should Be 'LastRow' Instead Of 202                         ' Stock Tickers Starting In Row #3 To The Last row
    Symbols = Symbols & Worksheet.Range("C" & i).Value & "+"                    ' Stock Tickers Starting In Column C
Next i
Symbols = Left(Symbols, Len(Symbols) - 1)                                       ' Remove The Trailing "+" Which Causes Problems With The URL
Dim URL As String:  URL = "[URL]http://download.finance.yahoo.com/d/quotes.csv?s[/URL]=" & Symbols & "&f=snxd1vj1jkl1dr"      ' The Yahoo Finance URL Used
Dim HTTP As New WinHttpRequest
HTTP.Open "GET", URL, False                                                     ' Download The CSV File
HTTP.Send
Dim Response As String:  Response = HTTP.ResponseText
Dim Lines As Variant:  Lines = Split(Response, vbNewLine)
Dim sLine As String
Dim Values As Variant
For i = 0 To UBound(Lines)
    sLine = Lines(i)
    If InStr(sLine, ",") > 0 Then
        Values = Split(sLine, ",")
        Worksheet.Cells(i + 3, 4).Value = Split(Split(sLine, Chr(34) & "," & Chr(34))(1), Chr(34))(0)  ' Column D (Name)
        Worksheet.Cells(i + 3, 5).Value = Replace(Values(UBound(Values) - 8), Chr(34), "")             ' Column E (Exchange) - Removed " from the beginning & end
        Worksheet.Cells(i + 3, 6).Value = Replace(Values(UBound(Values) - 7), Chr(34), "")             ' Column F (Last Trading Date) - Removed " from the beginning & end
        Worksheet.Cells(i + 3, 7).Value = Values(UBound(Values) - 6)                                   ' Column G (Volume)
        Worksheet.Cells(i + 3, 8).Value = Replace(Values(UBound(Values) - 5), "B", "")                 ' Column H (Market Cap) - Removed "B" from the end
        Worksheet.Cells(i + 3, 9).Value = Values(UBound(Values) - 4)                                   ' Column I (52 Week Low)
        Worksheet.Cells(i + 3, 10).Value = Values(UBound(Values) - 3)                                  ' Column J (52 Week High)
        Worksheet.Cells(i + 3, 11).Value = Values(UBound(Values) - 2)                                  ' Column K (Latest Price)
        Worksheet.Cells(i + 3, 12).Value = Values(UBound(Values) - 1)                                  ' Column L (Dividend/Share)
        Worksheet.Cells(i + 3, 13).Value = Values(UBound(Values) - 0)                                  ' Column M (P/E Ratio)
    End If
Next i
End Sub

Sub GetData()
    Dim QuerySheet As Worksheet
    Dim DataSheet As Worksheet
    Dim qurl As String
    Dim i As Integer, iMax As Integer
    
    Clear
    
    Application.ScreenUpdating = False
    Application.DisplayAlerts = False
    Application.Calculation = xlCalculationManual
    
    Set DataSheet = ActiveSheet
  
    
    For iMax = 0 To 1000 Step 200
    
    i = 7 + iMax
    If Cells(i, 1) = "" Then
        GoTo stopHere
    End If
    
    qurl = "[URL]http://download.finance.yahoo.com/d/quotes.csv?s[/URL]=" + Cells(i, 1)
    i = i + 1
    While Cells(i, 1) <> "" And i < iMax + 207
        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("N7"))
                .BackgroundQuery = True
                .TablesOnlyFromHTML = False
                .Refresh BackgroundQuery:=False
                .SaveData = True
            End With
    Range("N7:N207").Select
    Selection.TextToColumns Destination:=Range("N7"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
        Semicolon:=False, Comma:=True, Space:=False, Other:=False, FieldInfo _
        :=Array(Array(1, 1), Array(2, 1), Array(3, 1), Array(4, 1), Array(5, 1), Array(6, 1), _
        Array(7, 1), Array(8, 1), Array(9, 1), Array(10, 1))
        
    Range("N7:W207").Select
    Selection.Copy
    Cells(7 + iMax, 3).Select
    Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
        False, Transpose:=False
        
'   Range("N7:W207").Select
'    Selection.ClearContents
Next iMax
    With ThisWorkbook
        For Each nQuery In Names
            If IsNumeric(Right(nQuery.Name, 1)) Then
                nQuery.Delete
            End If
        Next nQuery
    End With
    
'turn calculation back on
    Application.Calculation = xlCalculationAutomatic
    Application.DisplayAlerts = True
'    Range("C7:H2000").Select
'    Selection.Sort Key1:=Range("C8"), Order1:=xlAscending, Header:=xlGuess, _
'        OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
'    Columns("C:C").ColumnWidth = 25.43
'    Range("h2").Select
 
stopHere:
Clear2
End Sub


Sub Clear()
'
' Clear Macro
' Macro recorded 23/01/2008 by pjPonzo
'
'
    Range("C7:L1200").Select
    Selection.ClearContents
    
End Sub


Sub Clear2()
'
' clear2 Macro
' Macro recorded 25/03/2008 by pjPonzo
'
'
    Columns("N:AA").Select
    Application.CutCopyMode = False
    Selection.Delete Shift:=xlToLeft
    Range("A1").Select
End Sub


Sub doALL()

Sheets("Yahoo1").Select
GetData
Sheets("Yahoo2").Select
GetData
Sheets("Yahoo3").Select
GetData

End Sub

 

[chuckchuckit]

Welcome back to the board.
Been a while since I have worked with the Yahoo data for stocks but from what I remember here is some info that may assist you:

Yahoo has various limits on how much you can download per query (such as in your 2nd example GetData() download). It seems to be limited by qty of data being returned (perhaps number of data items or perhaps by qty characters). The way around that is to first find the limit for your type of query by sending test requirements of different sizes. Once that Yahoo limit is known, then do multiple queries of say no more than 90% of that limit (so you do not error by doing a query that might be too large). Store each resulting query returned to your "quotes.cvs" workbook by copying the returned data into a different workbook table until you have done enough individual queries to give you your 1000+ requirements. Excel/VBA is a good tool for working with such data.

Yahoo is good for research but I think they are delayed 20 minutes in all their data. Even if you pay the $15/month etc for real time, they still have certain aspects such as a stock's current volume (if I remember correctly) which is still delayed. For real time use, there are quite a number of other providers out there but they cost more than $15/month.

Above info may have changed some since I was working with it. Hope that is helpful.