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?
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?