PDA

View Full Version : [SOLVED] Pull data from web



sujittalukde
08-01-2007, 03:31 AM
The following code found at excelforum, downloads data well from goolge finace.



Sub Macro1()
Dim sTS As String

sTS = Range("a1").Value
With ActiveSheet.QueryTables.Add(Connection:= _
"URL;http://finance.google.com/finance?fstype=ii&q=" _
& sTS, Destination:=Range("A3"))
.Name = "finance?fstype=ii&q=" & sTS
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlOverwriteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.WebSelectionType = xlSpecifiedTables
.WebFormatting = xlWebFormattingNone
.WebTables = "2,3"
.WebPreFormattedTextToColumns = True
.WebConsecutiveDelimitersAsOne = True
.WebSingleBlockTextImport = False
.WebDisableDateRecognition = False
.WebDisableRedirections = False
.Refresh BackgroundQuery:=False
End With

With ActiveSheet.QueryTables.Add(Connection:= _
"URL;http://finance.google.com/finance?fstype=bi&q=" _
& sTS, Destination:=Range("A50"))
.Name = "finance?fstype=bi&q=" & sTS
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlOverwriteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.WebSelectionType = xlSpecifiedTables
.WebFormatting = xlWebFormattingNone
.WebTables = "2,5"
.WebPreFormattedTextToColumns = True
.WebConsecutiveDelimitersAsOne = True
.WebSingleBlockTextImport = False
.WebDisableDateRecognition = False
.WebDisableRedirections = False
.Refresh BackgroundQuery:=False
End With
End Sub


When I am using the same from the rediffmail.com with slight variation to the code to suit the address but that is not pulling the data. I think the code needs some more changes. As I am unable to do it, could someone please help me in this.

The modified code for rediff is given below




Sub Macro1()
Dim sTS As String

sTS = Range("a1").Value
With ActiveSheet.QueryTables.Add(Connection:= _
"URL;http://money.rediff.com/money/jsp/co_results_q.jsp?companyCode=" _
& sTS, Destination:=Range("A3"))
.Name = "money/jsp/co_results_q.jsp?companyCode="" & sTS"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlOverwriteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.WebSelectionType = xlSpecifiedTables
.WebFormatting = xlWebFormattingNone
.WebTables = "2,3"
.WebPreFormattedTextToColumns = True
.WebConsecutiveDelimitersAsOne = True
.WebSingleBlockTextImport = False
.WebDisableDateRecognition = False
.WebDisableRedirections = False
.Refresh BackgroundQuery:=False
End With

With ActiveSheet.QueryTables.Add(Connection:= _
"URL;http://money.rediff.com/money/jsp/balancesheet.jsp?companyCode=" _
& sTS, Destination:=Range("A50"))
.Name = "money/jsp/balancesheet.jsp?companyCode=" & sTS
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlOverwriteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.WebSelectionType = xlSpecifiedTables
.WebFormatting = xlWebFormattingNone
.WebTables = "2,5"
.WebPreFormattedTextToColumns = True
.WebConsecutiveDelimitersAsOne = True
.WebSingleBlockTextImport = False
.WebDisableDateRecognition = False
.WebDisableRedirections = False
.Refresh BackgroundQuery:=False
End With
End Sub



Reddiff.com URL address:

The url for quarterly results is http://money.rediff.com/money/jsp/co...yCode=15510002 (http://money.rediff.com/money/jsp/co_results_q.jsp?companyCode=15510002)

and the url for balance sheet is http://money.rediff.com/money/jsp/ba...yCode=15510002 (http://money.rediff.com/money/jsp/ba...yCode=15510002)

I think modification is needed in table line ie [WebTables = "2,3"] etc. Can someone please help me on how to count the table in a website & what is 2,3? Is this row,column or something else.

I tried many combinations of table no but failed.

Can someone please help me on how to count the table?

ALso posted here http://www.excelforum.com/showthread.php?t=609095 but not getting any reply.Hope, here it will be solved?

anandbohra
08-20-2007, 01:55 AM
hi
this is for BALANCE SHEET

Sub Balance_Sheet()
Dim sTS As String
sTS = Range("a1").Value
Sheets("Balance Sheet").Select
With ActiveSheet.QueryTables.Add(Connection:= _
"URL;http://money.rediff.com/money/jsp/balancesheet.jsp?companyCode=" _
& sTS, Destination:=Range("A3"))
.Name = "money/jsp/balancesheet.jsp?companyCode=" & sTS
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlOverwriteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.WebSelectionType = xlSpecifiedTables
.WebFormatting = xlWebFormattingNone
.WebTables = "10,11,12,13,14,15"
.WebPreFormattedTextToColumns = True
.WebConsecutiveDelimitersAsOne = True
.WebSingleBlockTextImport = False
.WebDisableDateRecognition = False
.WebDisableRedirections = False
.Refresh BackgroundQuery:=False
End With
End Sub


and this one is for Quarterly results


Sub quarterly_results()
Dim sTS As String
sTS = Range("a1").Value
Sheets("Quarterly Results").Select
With ActiveSheet.QueryTables.Add(Connection:= _
"URL;http://money.rediff.com/money/jsp/co_results_q.jsp?companyCode=" _
& sTS, Destination:=Range("A3"))
.Name = "money/jsp/co_results_q.jsp?companyCode=" & sTS
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlOverwriteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.WebSelectionType = xlSpecifiedTables
.WebFormatting = xlWebFormattingNone
.WebTables = "10,11,12,13,14,15"
.WebPreFormattedTextToColumns = True
.WebConsecutiveDelimitersAsOne = True
.WebSingleBlockTextImport = False
.WebDisableDateRecognition = False
.WebDisableRedirections = False
.Refresh BackgroundQuery:=False
End With
End Sub