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. 

 

The basic procedure is not working . Please Help

 

Thankyou

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 

     

     

     

     

     

     

7 Replies

  • 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
      • 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.

         

         

Resources