I know the exact three lines where the problem is -- I cannot figure out for the life of me why it's taking data from the very bottom line and inserting it into the cells at the top.
The Yahoo Finance CSV is here: finance.yahoo.com/d/quotes.csv?s=GSPC&f=snl1hp
It's right here in the code that it's suddenly not working:
If InStr(sLine, ",") > 0 Then
Values = Split(sLine, ",")
W.Cells(i + 2, 2).Value = Split(Split(sLine, Chr(34) & "," & Chr(34))(1), Chr(34))(0)
W.Cells(i + 2, 3).Value = Values(UBound(Values) - 2)
W.Cells(i + 2, 4).Value = Values(UBound(Values) - 1) W.Cells(i + 2, 5).Value = Values(UBound(Values))
End If
Next i
W.Cells.Columns.AutoFit
It's the ,3 ....,4....,5 lines that are causing issues. If I have a list of say 15 Tickers in Column A in Excel, column A pulls in fine. Then in column B it's pulling in value for 14th ticker, and then in Column C it's pulling in value for 15th ticker and doing some really weird spacing issue and then not updating anything else below it. That's why I think it's some weird setting in Excel that's not letting it scroll down through the data and insert the proper values into the right columns.
'http://www.youtube.com/watch?v=iSlBE3CWg5Q
Dim W As Worksheet: Set W = ActiveSheet Dim Last As Integer: Last = W.Range("A1000").End(xlUp).row 'If there are no symbols, next line catches the error If Last = 1 Then Exit Sub Debug.Print Last Dim Symbols As String Dim i As Integer For i = 2 To Last Symbols = Symbols & W.Range("A" & i).Value & "+" Next i 'Take care of the last + sign in the symbols Symbols = Left(Symbols, Len(Symbols) - 1) 'Call the Website and generate CSV Dim URL As String: URL = "http://finance.yahoo.com/d/quotes.csv?s=" & Symbols & "&f=snl1hp" 'Download the Data as a String Dim Http As New WinHttpRequest Http.Open "GET", URL, False Http.Send
Dim Resp As String: Resp = Http.ResponseText Dim Lines As Variant: Lines = Split(Resp, vbNewLine) Dim sLine As String Dim Values As Variant For i = 0 To UBound(Lines) sLine = Lines(i)
'Last line is blank, so let's get rid of that error If InStr(sLine, ",") > 0 Then Values = Split(sLine, ",") W.Cells(i + 2, 2).Value = Split(Split(sLine, Chr(34) & "," & Chr(34))(1), Chr(34))(0) W.Cells(i + 2, 3).Value = Values(UBound(Values) - 2) W.Cells(i + 2, 4).Value = Values(UBound(Values) - 1) W.Cells(i + 2, 5).Value = Values(UBound(Values)) End If Next i W.Cells.Columns.AutoFit
End Sub