Forum Discussion
Scrap Data to excel
Kunal
Step 1 - iIn Data tabl > get data > other sources > blank query
Step 2 In view tab > click advance editor
Step 3 replace the content with this M code
let
Source = Web.Page(Web.Contents("https://www.nseindia.com/live_market/dynaContent/live_market.htm")),
Data2 = Source{2}[Data],
Children0 = Data2{0}[Children],
Children1 = Children0{1}[Children],
Children2 = Children1{1}[Children],
Children = Children2{2}[Children],
Children3 = Children{1}[Children],
Children4 = Children3{0}[Children],
Children5 = Children4{2}[Children],
Children6 = Children5{1}[Children],
Children7 = Children6{3}[Children],
Children8 = Children7{0}[Children],
Children9 = Children8{0}[Children],
Children10 = Children9{0}[Children],
#"Removed Other Columns" = Table.SelectColumns(Children10,{"Children"}),
#"Expanded Children" = Table.ExpandTableColumn(#"Removed Other Columns", "Children", {"Kind", "Name", "Children", "Text"}, {"Children.Kind", "Children.Name", "Children.Children", "Children.Text"}),
#"Removed Other Columns1" = Table.SelectColumns(#"Expanded Children",{"Children.Text"}),
#"Added Index" = Table.AddIndexColumn(#"Removed Other Columns1", "Index", 0, 1),
#"Inserted Modulo" = Table.AddColumn(#"Added Index", "Modulo", each Number.Mod([Index], 2), type number),
#"Pivoted Column" = Table.Pivot(Table.TransformColumnTypes(#"Inserted Modulo", {{"Modulo", type text}}, "en-IN"), List.Distinct(Table.TransformColumnTypes(#"Inserted Modulo", {{"Modulo", type text}}, "en-IN")[Modulo]), "Modulo", "Children.Text"),
#"Filled Up" = Table.FillUp(#"Pivoted Column",{"1"}),
#"Filtered Rows" = Table.SelectRows(#"Filled Up", each ([0] <> null)),
#"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"Index"}),
#"Changed Type" = Table.TransformColumnTypes(#"Removed Columns",{{"1", Int64.Type}}),
#"Renamed Columns" = Table.RenameColumns(#"Changed Type",{{"1", "Number"}, {"0", "Overall Adv Dec"}})
in
#"Renamed Columns"It may ask for privacy , select public
Try using this VBA code. It will scrape down the two values you need and put them in cells A1 and B1.
Sub extractDataFromTable()
Dim IE As Object
Dim myTable As Object
Dim doc As Object
Dim myValue As String
Dim Advances As Integer
Dim Declines As Integer
Set IE = CreateObject("InternetExplorer.Application")
' navigate to a web page
With IE
.Visible = False
.navigate ("https://www.nseindia.com/live_market/dynaContent/live_market.htm")
End With
'Make sure web page is fully loaded
While IE.ReadyState <> 4
DoEvents
Wend
Set doc = IE.document
'extract the data from tag
Set myTable = doc.getElementById("advanceDecline").getelementsbytagname("tr")
myValue = myTable(0).innerText
'Parse out values from this format....
'Advances - 464Declines - 1331Unchanged - 69Total - 1864
Advances = Int(Trim(Replace(Split(myValue, "-")(1), "Declines ", "")))
Declines = Int(Trim(Replace(Split(myValue, "-")(2), "Unchanged", "")))
Range("A1") = Advances
Range("B1") = Declines
'Get rid of IE Window
IE.Quit
End SubIt is showing runtime error 13 : Type Mismatch
Kunal-
Hmmm... That's odd, it ran fine for me.
Try adding these lines:
'Try adding this 2 second wait in.... I think the page 'isn't fully loading. Or the Javascript calls are processing after it's loaded. Application.Wait (Now + TimeValue("0:00:02"))To this portion of the procedure:
Sub extractDataFromTable() Dim IE As Object Dim myTable As Object Dim doc As Object Dim myValue As String Dim Advances As Integer Dim Declines As Integer Set IE = CreateObject("InternetExplorer.Application") ' navigate to a web page With IE .Visible = False .navigate ("https://www.nseindia.com/live_market/dynaContent/live_market.htm") End With 'Make sure web page is fully loaded While IE.ReadyState <> 4 DoEvents Wend 'Try adding this 2 second wait in.... I think the page 'isn't fully loading. Or the Javascript calls are processing after it's loaded. Application.Wait (Now + TimeValue("0:00:02")) Set doc = IE.document 'extract the data from tag Set myTable = doc.getElementById("advanceDecline").getelementsbytagname("tr") myValue = myTable(0).innerText 'Parse out values from this format.... 'Advances - 464Declines - 1331Unchanged - 69Total - 1864 Advances = Int(Trim(Replace(Split(myValue, "-")(1), "Declines ", ""))) Declines = Int(Trim(Replace(Split(myValue, "-")(2), "Unchanged", ""))) Range("A1") = Advances Range("B1") = Declines 'Get rid of IE Window IE.Quit End Subif that doesn't work try changing the:
.Visible = False
to:
.Visible = True
This will cause the IE window to briefly pop up.... but seemed to work with a few tests I just did. The first method would be preferable.
Kunal
Step 1 - iIn Data tabl > get data > other sources > blank query
Step 2 In view tab > click advance editor
Step 3 replace the content with this M code
let
Source = Web.Page(Web.Contents("https://www.nseindia.com/live_market/dynaContent/live_market.htm")),
Data2 = Source{2}[Data],
Children0 = Data2{0}[Children],
Children1 = Children0{1}[Children],
Children2 = Children1{1}[Children],
Children = Children2{2}[Children],
Children3 = Children{1}[Children],
Children4 = Children3{0}[Children],
Children5 = Children4{2}[Children],
Children6 = Children5{1}[Children],
Children7 = Children6{3}[Children],
Children8 = Children7{0}[Children],
Children9 = Children8{0}[Children],
Children10 = Children9{0}[Children],
#"Removed Other Columns" = Table.SelectColumns(Children10,{"Children"}),
#"Expanded Children" = Table.ExpandTableColumn(#"Removed Other Columns", "Children", {"Kind", "Name", "Children", "Text"}, {"Children.Kind", "Children.Name", "Children.Children", "Children.Text"}),
#"Removed Other Columns1" = Table.SelectColumns(#"Expanded Children",{"Children.Text"}),
#"Added Index" = Table.AddIndexColumn(#"Removed Other Columns1", "Index", 0, 1),
#"Inserted Modulo" = Table.AddColumn(#"Added Index", "Modulo", each Number.Mod([Index], 2), type number),
#"Pivoted Column" = Table.Pivot(Table.TransformColumnTypes(#"Inserted Modulo", {{"Modulo", type text}}, "en-IN"), List.Distinct(Table.TransformColumnTypes(#"Inserted Modulo", {{"Modulo", type text}}, "en-IN")[Modulo]), "Modulo", "Children.Text"),
#"Filled Up" = Table.FillUp(#"Pivoted Column",{"1"}),
#"Filtered Rows" = Table.SelectRows(#"Filled Up", each ([0] <> null)),
#"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"Index"}),
#"Changed Type" = Table.TransformColumnTypes(#"Removed Columns",{{"1", Int64.Type}}),
#"Renamed Columns" = Table.RenameColumns(#"Changed Type",{{"1", "Number"}, {"0", "Overall Adv Dec"}})
in
#"Renamed Columns"It may ask for privacy , select public