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
It 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 Sub
if 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
thanks it worked but after two refreshes.. it shows error
DataFormat.Error: We couldn't convert to Number.
Details:
-it sometimes does not work in live market , but if you will run after market hours it works smooth
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