Dynamically Pass Ticker Symbol To Web Queries: Trying to build something complicated Excel help response (18 Sep 2006)

You can automate downloading the list of ticker codes from the first URL using the following code.

VBA Code:

With ActiveSheet.QueryTables.Add(Connection:= _
        "URL;http://www.tradingroom.com.au/apps/mkt/industrylisting.ac?code=10&next=all" _
        , Destination:=Range("A1"))
        .Name = "industrylisting.ac?code=10&next=all"
        .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 = "15"
        .WebPreFormattedTextToColumns = True
        .WebConsecutiveDelimitersAsOne = True
        .WebSingleBlockTextImport = False
        .WebDisableDateRecognition = False
        .WebDisableRedirections = False
        .Refresh BackgroundQuery:=False
End With

You can then filter the data for stocks with market capital over 50,000,000 and select only the tickers metting this criteria using the following code.

VBA Code:

Range("A2:K3").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.AutoFilter Field:=4, Criteria1:=">=50000000", Operator:=xlAnd
Range("A5").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.SpecialCells(xlCellTypeVisible).Select

Finally, you can loop through each ticker in the selection and use it dymanically in each subsequent web query to populate the worksheets by using a For...Next loop such as:

VBA Code:

For each tkr in Selection
queryurl = "http://au.finance.yahoo.com/q/ais?s=" & tkr & ".AX"
'code here to insert new worksheet and populate with web query
Next

Dynamically pass ticker symbol to web queries

Excel Help for Dynamically Pass Ticker Symbol To Web Queries in Valuing Investments and Businesses

Dynamically Pass Ticker Symbol To Web Queries