Forum Discussion

Kunal Sharma's avatar
Kunal Sharma
Copper Contributor
May 17, 2018
Solved

Scrap Data to excel

Hello, I want to scrap data to excel from this link "https://www.nseindia.com/live_market/dynaContent/live_market.htm" I need those two Advances & Declines Numbers which is coloured green & red.   ...
BI & Data Analytics
Need Help
  • Sachin Jain's avatar
    Sachin Jain
    Jun 02, 2018

    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 

     

     

     

     

     

     

Matt Mickle's avatar
Matt Mickle
Bronze Contributor
May 18, 2018

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 Sub
Kunal Sharma's avatar
Kunal Sharma
Copper Contributor
May 23, 2018

It is showing runtime error 13 : Type Mismatch

  • Matt Mickle's avatar
    Matt Mickle
    Bronze Contributor
    May 23, 2018

    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.

     

     

    • Sachin Jain's avatar
      Sachin Jain
      Brass Contributor
      Jun 02, 2018

      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 

       

       

       

       

       

       

      • Kunal Sharma's avatar
        Kunal Sharma
        Copper Contributor
        Jun 06, 2018

        thanks it worked but after two refreshes.. it shows error 

         

        DataFormat.Error: We couldn't convert to Number.
        Details:
        -

    • Sachin Jain's avatar
      Sachin Jain
      Brass Contributor
      Jun 02, 2018

      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 

       

       

       

       

       

       

Resources