Hi,
The code was working fine with earlier URL. once the modification was done by NSE then the code stopped working. Need help to fix this issue. I had given the new URL in the code itself. If someone has the code already, please share the same. Thanks in advance.
The code was working fine with earlier URL. once the modification was done by NSE then the code stopped working. Need help to fix this issue. I had given the new URL in the code itself. If someone has the code already, please share the same. Thanks in advance.
PHP:
Sub GetData()
'
' GetData Macro
'
' Keyboard Shortcut: Ctrl+q
'
Dim strSymbol As String
Dim dtStartDate As Date
Dim dtEndDate As Date
Dim RowNumber As Long
Dim bAllData As Boolean
bAllData = False
strSymbol = ActiveSheet.Cells(1, 2).Value
While bAllData = False
If (Len(ActiveSheet.Cells(6, 1).Value) = 0) Then
If ActiveSheet.Cells(2, 2).Value = 0 Then
dtStartDate = CDate("01-Jun-2014")
Else
dtStartDate = CDate(ActiveSheet.Cells(2, 2))
End If
dtEndDate = CDate(Now())
Range("A6").Select
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Cells.ClearContents
RowNumber = 6
Else
On Error Resume Next
Range("B6").Select
Selection.End(xlDown).Select
dtStartDate = CDate(ActiveCell.Value)
RowNumber = ActiveCell.Row
If Err.Number > 0 Then
Err.Clear
dtStartDate = CDate("01-Jun-2014")
End If
dtEndDate = CDate(Now())
ActiveSheet.Cells(3, 2) = CDate(Now())
On Error GoTo 0
End If
If dtEndDate < dtStartDate Then
MsgBox "To Date should be higher than From Date"
End If
ActiveSheet.Cells(3, 2) = CDate(Now())
FetchData strSymbol, dtStartDate, dtEndDate, RowNumber
If DateDiff("D", dtStartDate, dtEndDate) < 140 Then
bAllData = True
End If
' Range("B6").Select
' Selection.End(xlDown).Select
' dtStartDate = CDate(ActiveCell.Value)
Wend
PlotChart 2, 3, 6, ActiveSheet.Name, 8, 6, ActiveSheet.Name, 3
ActiveSheet.Cells(3, 8).Interior.Color = 5287936
ActiveSheet.Cells(3, 9) = "Buy"
ActiveSheet.Cells(4, 8).Interior.Color = 255
ActiveSheet.Cells(4, 9) = "Sell"
ActiveSheet.Cells(5, 8) = "Price"
End Sub
Sub GetFullData()
Dim strSymbol As String
Dim dtStartDate As Date
Dim dtEndDate As Date
Dim RowNumber As Long
Dim bAllData As Boolean
bAllData = False
strSymbol = ActiveSheet.Cells(1, 2).Value
dtStartDate = CDate("01-Jun-2014")
dtEndDate = CDate(Now())
Range("A6").Select
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Cells.ClearContents
RowNumber = 6
ActiveSheet.Cells(2, 2) = CDate(dtStartDate)
ActiveSheet.Cells(3, 2) = CDate(Now())
While bAllData = False
If (Len(ActiveSheet.Cells(6, 1).Value) = 0) Then
If ActiveSheet.Cells(2, 2).Value = 0 Then
dtStartDate = CDate("01-Jun-2014")
Else
dtStartDate = CDate(ActiveSheet.Cells(2, 2))
End If
dtEndDate = CDate(Now())
Range("A6").Select
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Cells.ClearContents
RowNumber = 6
Else
On Error Resume Next
Range("B6").Select
Selection.End(xlDown).Select
dtStartDate = CDate(ActiveCell.Value)
RowNumber = ActiveCell.Row
If Err.Number > 0 Then
Err.Clear
dtStartDate = CDate("01-Apr-2014")
End If
dtEndDate = CDate(Now())
ActiveSheet.Cells(3, 2) = CDate(Now())
On Error GoTo 0
End If
FetchData strSymbol, dtStartDate, dtEndDate, RowNumber
If DateDiff("D", dtStartDate, dtEndDate) < 140 Then
bAllData = True
End If
Wend
PlotChart 2, 3, 6, ActiveSheet.Name, 8, 6, ActiveSheet.Name, 3
ActiveSheet.Cells(3, 8).Interior.Color = 5287936
ActiveSheet.Cells(3, 9) = "Buy"
ActiveSheet.Cells(4, 8).Interior.Color = 255
ActiveSheet.Cells(4, 9) = "Sell"
ActiveSheet.Cells(5, 8) = "Price"
End Sub
Public Sub FetchData(strSymbol As String, dtStartDate As Date, dtEndDate As Date, RowNumber As Long)
Dim strURL As String
Dim strStartDate As String
Dim strEndDate As String
Dim shtData As Worksheet
Dim wbData As Workbook
Dim wbMain As Workbook
Set wbMain = ActiveWorkbook
strSymbol = UCase(strSymbol)
strStartDate = Format(dtStartDate, "dd-MM-yyyy")
strEndDate = Format(dtEndDate, "dd-MM-yyyy")
' strURL = "/marketinfo/sym_map/symbolMapping.jsp?dataType=priceVolume&symbol=" & strSymbol & "&segmentLink=3&symbolCount=2&series=EQ&dateRange=day&fromDate=" & strStartDate & "&toDate=" & strEndDate
strURL = "/products/content/equities/equities/eq_security.htm?dataType=priceVolume&symbol=" & strSymbol & "&segmentLink=3&symbolCount=2&series=EQ&dateRange=day&fromDate=" & strStartDate & "&toDate=" & strEndDate
'If OpenWebPage("www.nseindia.com", strURL) Then
If OpenNewWebPage("www.nseindia.com" & strURL) Then
Application.ScreenUpdating = False
Application.DisplayAlerts = False
Workbooks.Open "e:\NSE.xls"
'Workbooks.Open "http://www.nseindia.com/content/equities/scripvol/datafiles/" & strStartDate & "-TO-" & strEndDate & strSymbol & "ALLN.csv"
Set wbData = ActiveWorkbook
Set shtData = wbData.ActiveSheet
shtData.Columns(10).Delete
shtData.Columns("D:H").Delete
shtData.Columns(2).Delete
shtData.Columns(5).Delete
shtData.Columns(4).Delete
Range("A2").Select
Range(Selection, Selection.End(xlToRight)).Select
If (Len(ActiveSheet.Cells(3, 1).Value) > 0) Then
Range(Selection, Selection.End(xlDown)).Select
End If
Selection.Copy
wbMain.Activate
Cells(RowNumber, 1).Select
ActiveSheet.Paste
wbData.Close SaveChanges:=False
Application.DisplayAlerts = True
Application.ScreenUpdating = True
Else
MsgBox "Network issue"
End If
End Sub
Function OpenNewWebPage(strURL As String) As Boolean
Dim webdata As String
Dim StartPosition As Long
Dim ie As Object
OpenNewWebPage = False
'create ie object
Set ie = CreateObject("InternetExplorer.Application")
'set the ie properties
ie.Toolbar = 0
ie.StatusBar = 1
ie.Width = 999
ie.Height = 999
ie.Left = 0
ie.Top = 0
ie.Visible = 1
'navigate to a web page
ie.Navigate (strURL)
'wait until the page has loaded before continuing
'WaitForLoad (ie)
Do While ie.Busy: Loop
Do While ie.readyState <> 4: Loop
webdata = ie.document.body.innerHTML
StartPosition = InStr(1, webdata, "<TD class=tablehead")
If StartPosition > 0 Then
webdata = Mid(webdata, StartPosition, InStr(StartPosition, webdata, "</TABLE>") - StartPosition + 8)
End If
MyFile = "e:\" & "nse.xls"
'set and open file for output
fnum = FreeFile()
Open MyFile For Output As fnum
'use Print when you want the string without quotation marks
Print #fnum, "<html><table><tr>"
Print #fnum, Replace(webdata, "<BR>", "")
Print #fnum, "</html>"
Close #fnum
'quit ie
ie.Quit
OpenNewWebPage = True
End Function
Last edited by a moderator: