Home > Forum Home > Valuing Investments and Businesses > Trying to build something complicated Share

Trying to build something complicated

Excel Help for Trying To Build Something Complicated in Valuing Investments and Businesses


Forum TopicPost Reply Login

Trying To Build Something Complicated

Rate this:
(3/5 from 1 vote)
ConfusedHi,

I'm hoping someone may be able to help me with some code.

I have a workbook with multiple worksheets.
The first 5 worksheets collect Share Market Data (from yahoo site) for each sector of the Australian Stock exchange. This is done via a simple Web Query.


The next couple of  worksheets, which I cannot figure out how to do need to filter out from the first worksheets the shares with >50,000,000 Market Capital and copy the Tikker Code and dump the Data from another URL via another WebQuery. I.e. WorkSheet 1 -> Worksheet 7, then Worksheet 2 -> Worksheet 8 and so on.
e.g. Worksheet 1 = Energy Stocks, Worksheet 7 = Energy Stock (Balance Sheet and Earning Summary.
Worksheet 1 URL = http://www.tradingroom.com.au/apps/mkt/industrylisting.ac?code=10&next=all
Worksheet 7 Earning Summ URL = http://au.finance.yahoo.com/q/ais?s=ABJ.AX
Balance Sheet URL = http://au.finance.yahoo.com/q/abs?s=ABJ.AX 
Hope it's not too difficult to understand what I'm trying to acheive. 
 Posted by on
 
Replies - Displaying 1 to 1 of 1Order Replies By: Most Recent | Chronological | Highest Rated
Confused
Dynamically pass ticker symbol to web queries
Rate this:
(3/5 from 1 vote)
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
 Excel Business Forums Administrator
 Posted by on
 Displaying page 1 of 1 

Find relevant Excel templates and add-ins for Trying to build something complicated in the

© Business Spreadsheets   Legal   Follow us: