VBA > Finance.Yahoo > Ticker Symbol List

[xSoldOutx]

I have a list of Stock ticker symbols starting on Sheet1 A1, however it is not pulling any information. I was wondering if I'm missing a simple fix or if it is slightly more complicated? Thank you in advanced for any and all your help!

Sub Macro3()
    Dim conString As String
    Dim conName As String
    Dim LastRow As Long
    Dim i As Integer
    Dim ticker As String
          
    
    For i = 1 To LastRow = ThisWorkbook.Sheets("Sheet1").Range("A" & ThisWorkbook.Sheets("Sheet1").Rows.Count).End(xlUp).Row + 1
    
[COLOR=#008000]'Example For i = To 10

[/COLOR] ticker = ThisWorkbook.Sheets("Sheet1").Range("A1").Offset(0, i + 1).Value

[COLOR=#008000]'I thought this would cycle through each Cell in column A and pull the value then continue with the code
[/COLOR]    
        Sheets.Add After:=Sheets(Sheets.Count)
        ActiveSheet.Name = ticker
        
    
        conString = "URL;http://finance.yahoo.com/q/ks?s=" & ticker & "+Key+Statistics"
        conName = "ks?s=" & ticker & "+Key+Statistics"
   
        
        With ActiveSheet.QueryTables.Add(Connection:=conString, Destination:=Range("$A$1"))
            .Name = conName
            .FieldNames = True
            .RowNumbers = False
            .FillAdjacentFormulas = False
            .PreserveFormatting = True
            .RefreshOnFileOpen = False
            .BackgroundQuery = True
            .RefreshStyle = xlInsertDeleteCells
            .SavePassword = False
            .SaveData = True
            .AdjustColumnWidth = True
            .RefreshPeriod = 0
            .WebSelectionType = xlSpecifiedTables
            .WebFormatting = xlWebFormattingNone
            .WebTables = """yfncsubtit"",8,10,11,13,15,17,19,21,23"
            .WebPreFormattedTextToColumns = True
            .WebConsecutiveDelimitersAsOne = True
            .WebSingleBlockTextImport = False
            .WebDisableDateRecognition = False
            .WebDisableRedirections = False
            .Refresh BackgroundQuery:=False
        
        End With
    Next i
End Sub

 

[xSoldOutx]

I orginally found this code (see below), however I wanted something more functional. If someone could help I would appreciate it.

Sub Macro2()
    Dim conString As String
    Dim conName As String
    Dim txtsymbols(0, 2) As String
    
    txtsymbols(0, 1) = "MSFT"
    txtsymbols(0, 2) = "CSCO"
    
    For i = 1 To 2
       
        Sheets.Add After:=Sheets(Sheets.Count)
        ActiveSheet.Name = txtsymbols(0, i)
        
    
        conString = "URL;http://finance.yahoo.com/q/ks?s=" & txtsymbols(0, i) & "+Key+Statistics"
        conName = "ks?s=" & txtsymbols(0, i) & "+Key+Statistics"
        
        With ActiveSheet.QueryTables.Add(Connection:=conString, Destination:=Range("$A$1"))
            .Name = conName
            .FieldNames = True
            .RowNumbers = False
            .FillAdjacentFormulas = False
            .PreserveFormatting = True
            .RefreshOnFileOpen = False
            .BackgroundQuery = True
            .RefreshStyle = xlInsertDeleteCells
            .SavePassword = False
            .SaveData = True
            .AdjustColumnWidth = True
            .RefreshPeriod = 0
            .WebSelectionType = xlSpecifiedTables
            .WebFormatting = xlWebFormattingNone
            .WebTables = """yfncsubtit"",8,10,11,13,15,17,19,21,23"
            .WebPreFormattedTextToColumns = True
            .WebConsecutiveDelimitersAsOne = True
            .WebSingleBlockTextImport = False
            .WebDisableDateRecognition = False
            .WebDisableRedirections = False
            .Refresh BackgroundQuery:=False
        End With
    Next i
End Sub

 

[Simulationconsultant]

The multiple quote download at Multiple Stock Quote Downloader for Excel works pretty well. Perhaps you can adapt its VBA