Trying to build something complicated

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

Trying To Build Something Complicated

Rate this: (3/5 from 1 vote)

	Hi, 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 johnpreacher on 18 Sep 2006

Replies - Displaying 1 to 1 of 1

Order Replies By: Most recent | Chronological | Highest Rated

	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 Excel Helper on 18 Sep 2006

Displaying page 1 of 1