I have code which downloads data from web and save in main workbook but data is not in format as table kindly look through and help me to correct problem.
my code is below
and my output is as
table is not showing excel ...
<tbody>
</tbody>
my code is below
Code:
Sub DownloadStockFuturesQuotes(ByVal stockTicker As String, ByVal StartDate As Date, ByVal EndDate As Date, ByVal DestinationCell As String, ByVal frequency As String)
Dim qurl As String
Dim StartMonth, StartDay, StartYear, EndMonth, EndDay, EndYear, ExpiryDate, Period As String
StartMonth = Format(Month(StartDate), "00")
StartDay = Format(Day(StartDate), "00")
StartYear = Format(Year(StartDate), "00")
EndMonth = Format(Month(EndDate), "00")
EndDay = Format(Day(EndDate), "00")
EndYear = Format(Year(EndDate), "00")
ExpiryDate = Worksheets("Parameters").Range("D7")
Period = Worksheets("Parameters").Range("D8")
Symbol = Worksheets("Parameters").Range("D9")
qurl = "URL;http://www.nseindia.com/live_market/dynaContent/live_watch/get_quote/getFOHistoricalData.jsp?underlying=" + stockTicker + "&instrument=" + Symbol + "&expiry=" + ExpiryDate + "&type=-&strike=-&fromDate=&toDate=&datePeriod=" & Period & "&fileDnld=undefined"
'MsgBox qurl
On Error GoTo ErrorHandler:
With ActiveSheet.QueryTables.Add(Connection:=qurl, Destination:=Range(DestinationCell))
.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 = "20"
.WebPreFormattedTextToColumns = True
.WebConsecutiveDelimitersAsOne = True
.WebSingleBlockTextImport = False
.WebDisableDateRecognition = False
.WebDisableRedirections = False
.Refresh BackgroundQuery:=False
End With
ErrorHandler:
End Sub
Sub DownloadFuturesData()
Dim frequency As String
Dim numRows As Integer
Dim lastRow As Integer
Dim stockTicker As String
Application.ScreenUpdating = False
lastRow = 42 'ActiveSheet.Cells(Rows.Count, "a").End(xlUp).Row
frequency = Worksheets("Parameters").Range("b7")
'Delete all sheets apart from Parameters sheet
Dim ws As Worksheet
Application.DisplayAlerts = False
For Each ws In Worksheets
If ws.Name <> "Parameters" Then ws.Delete
Next
Application.DisplayAlerts = True
'Loop through all tickers
For ticker = 11 To lastRow
stockTicker = Worksheets("Parameters").Range("A" & ticker)
ExpiryDate = Worksheets("Parameters").Range("D7")
Period = Worksheets("Parameters").Range("D8")
Symbol = Worksheets("Parameters").Range("D9")
If stockTicker = "" Then
GoTo NextIteration
End If
Sheets.Add After:=Sheets(Sheets.Count)
ActiveSheet.Name = stockTicker
Cells(1, 1) = "Stock Quotes for " & stockTicker
Call DownloadStockFuturesQuotes(stockTicker, Worksheets("Parameters").Range("$b$5"), Worksheets("Parameters").Range("$b$6"), "$a$2", frequency)
Columns("a:a").TextToColumns Destination:=Range("a1"), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
Semicolon:=False, Comma:=True, Space:=False, Other:=False, FieldInfo:=Array(Array(1, 1), Array(2, 1), Array(3, 1), Array(4, 1), Array(5, 1), Array(6, 1), Array(7, 1))
Sheets(stockTicker).Columns("A:I").AutoFit
lastRow = Sheets(stockTicker).UsedRange.Row - 2 + Sheets(stockTicker).UsedRange.Rows.Count
If lastRow < 3 Then
Application.DisplayAlerts = False
Sheets(stockTicker).Delete
GoTo NextIteration
Application.DisplayAlerts = True
End If
Sheets(stockTicker).Sort.SortFields.Add Key:=Range("A10:A" & lastRow), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
With Sheets(stockTicker).Sort
.SetRange Range("A10:I" & lastRow)
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
NextIteration:
Next ticker
If Sheets("Parameters").Range("exportToCSV") Then
On Error GoTo ErrorHandler:
Call CopyToCSV
End If
ErrorHandler:
Worksheets("Parameters").Select
Application.ScreenUpdating = True
End Sub
Sub DownloadStockQuotes(ByVal stockTicker As String, ByVal StartDate As Date, ByVal EndDate As Date, ByVal DestinationCell As String, ByVal freq As String)
Application.ScreenUpdating = False
Dim qurl As String
Dim StartMonth, StartDay, StartYear, EndMonth, EndDay, EndYear, Period As String
StartMonth = Month(StartDate)
If StartMonth = 1 Then StartMonth = "Jan"
If StartMonth = 2 Then StartMonth = "Feb"
If StartMonth = 3 Then StartMonth = "Mar"
If StartMonth = 4 Then StartMonth = "Apr"
If StartMonth = 5 Then StartMonth = "May"
If StartMonth = 6 Then StartMonth = "Jun"
If StartMonth = 7 Then StartMonth = "Jul"
If StartMonth = 8 Then StartMonth = "Aug"
If StartMonth = 9 Then StartMonth = "Sep"
If StartMonth = 10 Then StartMonth = "Oct"
If StartMonth = 11 Then StartMonth = "Nov"
If StartMonth = 12 Then StartMonth = "Dec"
StartDay = Day(StartDate)
StartYear = Year(StartDate)
StartD = StartDay & "-" & StartMonth & "-" & StartYear
EndMonth = Month(EndDate)
If EndMonth = 1 Then EndMonth = "Jan"
If EndMonth = 2 Then EndMonth = "Feb"
If EndMonth = 3 Then EndMonth = "Mar"
If EndMonth = 4 Then EndMonth = "Apr"
If EndMonth = 5 Then EndMonth = "May"
If EndMonth = 6 Then EndMonth = "Jun"
If EndMonth = 7 Then EndMonth = "Jul"
If EndMonth = 8 Then EndMonth = "Aug"
If EndMonth = 9 Then EndMonth = "Sep"
If EndMonth = 10 Then EndMonth = "Oct"
If EndMonth = 11 Then EndMonth = "Nov"
If EndMonth = 12 Then EndMonth = "Dec"
EndDay = Day(EndDate)
EndYear = Year(EndDate)
EndD = EndDay & "-" & EndMonth & "-" & EndYear
qurl = "URL;http://www.nseindia.com/live_market/dynaContent/live_watch/get_quote/getHistoricalData.jsp?symbol=" & stockTicker & "&series=EQ&""&fromDate=" & StartD & "&toDate=" & EndD & "&datePeriod=unselected&hiddDwnld=true"""
On Error GoTo ErrorHandler:
With ActiveSheet.QueryTables.Add(Connection:=qurl, Destination:=Range(DestinationCell))
.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 = "20"
' .WebPreFormattedTextToColumns = True
' .WebConsecutiveDelimitersAsOne = True
' .WebSingleBlockTextImport = False
' .WebDisableDateRecognition = False
' .WebDisableRedirections = False
.Refresh BackgroundQuery:=False
End With
ErrorHandler:
End Sub
'http://www.nseindia.com/products/dynaContent/common/productsSymbolMapping.jsp?symbol=TATASTEEL&segmentLink=3&symbolCount=1&series=EQ&dateRange=3month&fromDate=&toDate=&dataType=PRICEVOLUMEDELIVERABLE
'http://www.nseindia.com/products/dynaContent/common/productsSymbolMapping.jsp?symbol=TATASTEEL&segmentLink=3&symbolCount=1&series=EQ&dateRange=+&fromDate=01-01-2012&toDate=16-04-2012&dataType=PRICEVOLUME
Sub DownloadData()
Dim frequency As String
Dim numRows As Integer
Dim lastRow As Integer
Dim stockTicker As String
Application.ScreenUpdating = False
lastRow = 42
frequency = Worksheets("Parameters").Range("b7")
'Delete all sheets apart from Parameters sheet
Dim ws As Worksheet
Application.DisplayAlerts = False
For Each ws In Worksheets
If ws.Name <> "Parameters" Then ws.Delete
Next
Application.DisplayAlerts = True
'Loop through all tickers
For ticker = 11 To lastRow
stockTicker = Worksheets("Parameters").Range("$a$" & ticker)
If stockTicker = "" Then
GoTo NextIteration
End If
Sheets.Add After:=Sheets(Sheets.Count)
ActiveSheet.Name = stockTicker
Cells(1, 1) = "Stock Quotes for " & stockTicker
Call DownloadStockQuotes(stockTicker, Worksheets("Parameters").Range("$b$5"), Worksheets("Parameters").Range("$b$6"), "$a$2", frequency)
'Sheets(stockTicker).Columns("A:H").AutoFit
Selection.TextToColumns Destination:=Range("A1"), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, _
Semicolon:=False, Comma:=False, Space:=False, Other:=True, OtherChar:="|", _
FieldInfo:=Array( _
Array(1, 1), Array(2, 1), Array(3, 1), Array(4, 1), Array(5, 1), _
Array(6, 1), Array(7, 1), Array(8, 1), Array(9, 1), Array(10, 1), _
Array(11, 1), Array(12, 1), Array(13, 1), Array(14, 1), Array(15, 1), _
Array(16, 1), Array(17, 1), Array(18, 1), Array(19, 1), Array(20, 1), _
Array(21, 1), Array(22, 1), Array(23, 1), Array(24, 1), Array(25, 1), _
Array(26, 1), Array(27, 1), Array(28, 1) _
), _
TrailingMinusNumbers:=True
lastRow = Sheets(stockTicker).UsedRange.Row - 1 + Sheets(stockTicker).UsedRange.Rows.Count
If lastRow < 3 Then
Application.DisplayAlerts = False
Sheets(stockTicker).Delete
GoTo NextIteration
Application.DisplayAlerts = True
End If
Sheets(stockTicker).Sort.SortFields.Add Key:=Range("A3:A" & lastRow), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
With Sheets(stockTicker).Sort
.SetRange Range("A2:H" & lastRow)
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
Rows("2:9").Insert
NextIteration:
Next ticker
If Sheets("Parameters").Range("exportToCSV") Then
On Error GoTo ErrorHandler:
Call CopyToCSV
End If
ErrorHandler:
Worksheets("Parameters").Select
Application.ScreenUpdating = True
End Suband my output is as
table is not showing excel ...
| showing something like this < tbody > < th > Date < <tbody> </tbody> |
<tbody>
</tbody>
sir,
