extract IDs from iFrame

[Cool_Pacific]

Hello there,

I have just started writing VBA scripts, I want to scrap a web page source which is using iFrame. I need help getting the data extracted from below web page
A F Enterprises Stock Charts, A F Ent Live Market Technical Charts

I want to read 52 week high, low, historic data, moving average etc from that web page. But I am stuck with iFrame.

I tried below after googling out about iFrame, anybody can help me get going?

Dim baseURL As String
Dim IE As InternetExplorer
Dim HTMLdoc As HTMLDocument
Dim profileFrame As HTMLIFrame
Dim slotsDiv As HTMLDivElement

baseURL = "http://www.moneycontrol.com"

Set IE = New InternetExplorer
With IE
.Visible = True

'Navigate to the main page

.Navigate baseURL & "/stock-charts/" & Ticker & "/charts/" & subTicker & ""
While .Busy Or .READYSTATE <> READYSTATE_COMPLETE: DoEvents: Wend

'Get the profileFrame iframe and navigate to it

Set profileFrame = .Document.getElementById("charts")
.Navigate baseURL & profileFrame.src
While .Busy Or .READYSTATE <> READYSTATE_COMPLETE: DoEvents: Wend

Set HTMLdoc = .Document
End With

'Display all the text in the profileFrame iframe

MsgBox HTMLdoc.body.innerText

'Display just the text in the slots_container div

Set slotsDiv = HTMLdoc.getElementById("slots_container")
MsgBox slotsDiv.innerText

 

[Domenic]

In the following code, you'll notice that I use the XMLHTTP object instead of Internet Explorer. You'll see that it's a lot faster. As such, you'll need to set a reference to the following libraries...

1) Microsoft XML, v6.0 (or whatever version you have)

2) Microsoft HTML Object Library

Then, place the following code in a regular module...

[COLOR=darkblue]Option[/COLOR] [COLOR=darkblue]Explicit[/COLOR]

[COLOR=darkblue]Sub[/COLOR] GetStockData()

    [COLOR=darkblue]Dim[/COLOR] XMLReq [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]New[/COLOR] MSXML2.XMLHTTP60
    [COLOR=darkblue]Dim[/COLOR] HTMLDoc [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]New[/COLOR] MSHTML.HTMLDocument
    [COLOR=darkblue]Dim[/COLOR] HTMLTable [COLOR=darkblue]As[/COLOR] MSHTML.IHTMLElement
    [COLOR=darkblue]Dim[/COLOR] HTMLTables [COLOR=darkblue]As[/COLOR] MSHTML.IHTMLElementCollection
    [COLOR=darkblue]Dim[/COLOR] HTMLRow [COLOR=darkblue]As[/COLOR] MSHTML.IHTMLElement
    [COLOR=darkblue]Dim[/COLOR] HTMLRows [COLOR=darkblue]As[/COLOR] MSHTML.IHTMLElementCollection
    [COLOR=darkblue]Dim[/COLOR] HTMLCell [COLOR=darkblue]As[/COLOR] MSHTML.IHTMLElement
    [COLOR=darkblue]Dim[/COLOR] sText [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]String[/COLOR]
    [COLOR=darkblue]Dim[/COLOR] r [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]Long[/COLOR]
    [COLOR=darkblue]Dim[/COLOR] c [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]Long[/COLOR]
    
    XMLReq.Open "GET", "http://www.moneycontrol.com/stock-charts/afenterprises/charts/AFE01#AFE01", [COLOR=darkblue]False[/COLOR]
    XMLReq.send
    
    [COLOR=darkblue]If[/COLOR] XMLReq.Status <> 200 [COLOR=darkblue]Then[/COLOR]
        MsgBox "Error " & XMLReq.Status & ":  " & XMLReq.statusText
        [COLOR=darkblue]Exit[/COLOR] [COLOR=darkblue]Sub[/COLOR]
    [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]If[/COLOR]
    
    HTMLDoc.body.innerHTML = XMLReq.responseText
    
    Worksheets.Add
    
    [COLOR=green]'Current Data[/COLOR]
    sText = HTMLDoc.getElementsByClassName("FL MR10")(0).Children(0).innerText
    Range("A1").Value = sText
    r = 2
    c = 1
    [COLOR=darkblue]Set[/COLOR] HTMLTables = HTMLDoc.getElementsByClassName("FL MR10")(0).getElementsByTagName("table")
    [COLOR=darkblue]For[/COLOR] [COLOR=darkblue]Each[/COLOR] HTMLTable [COLOR=darkblue]In[/COLOR] HTMLTables
        [COLOR=darkblue]For[/COLOR] [COLOR=darkblue]Each[/COLOR] HTMLRow [COLOR=darkblue]In[/COLOR] HTMLTable.getElementsByTagName("tr")
            [COLOR=darkblue]For[/COLOR] [COLOR=darkblue]Each[/COLOR] HTMLCell [COLOR=darkblue]In[/COLOR] HTMLRow.getElementsByTagName("td")
                Cells(r, c).Value = HTMLCell.innerText
                c = c + 1
            [COLOR=darkblue]Next[/COLOR] HTMLCell
            r = r + 1
            c = 1
        [COLOR=darkblue]Next[/COLOR] HTMLRow
        r = r + 1
    [COLOR=darkblue]Next[/COLOR] HTMLTable
    
    [COLOR=green]'Historic Prices[/COLOR]
    Cells(r, "A").Value = "Historic Prices"
    r = r + 1
    [COLOR=darkblue]Set[/COLOR] HTMLRows = HTMLDoc.getElementById("priceret_bse").getElementsByTagName("tr")
    [COLOR=darkblue]For[/COLOR] [COLOR=darkblue]Each[/COLOR] HTMLRow [COLOR=darkblue]In[/COLOR] HTMLRows
        [COLOR=darkblue]For[/COLOR] [COLOR=darkblue]Each[/COLOR] HTMLCell [COLOR=darkblue]In[/COLOR] HTMLRow.getElementsByTagName("td")
            Cells(r, c).Value = HTMLCell.innerText
            c = c + 1
        [COLOR=darkblue]Next[/COLOR] HTMLCell
        r = r + 1
        c = 1
    [COLOR=darkblue]Next[/COLOR] HTMLRow

    [COLOR=green]'Simple Moving Averages[/COLOR]
    r = r + 1
    Cells(r, "A").Value = "Simple Moving Averages"
    r = r + 1
    [COLOR=darkblue]Set[/COLOR] HTMLRows = HTMLDoc.getElementsByClassName("table5")(2).getElementsByTagName("tr")
    [COLOR=darkblue]For[/COLOR] [COLOR=darkblue]Each[/COLOR] HTMLRow [COLOR=darkblue]In[/COLOR] HTMLRows
        [COLOR=darkblue]For[/COLOR] [COLOR=darkblue]Each[/COLOR] HTMLCell [COLOR=darkblue]In[/COLOR] HTMLRow.getElementsByTagName("td")
            Cells(r, c).Value = HTMLCell.innerText
            c = c + 1
        [COLOR=darkblue]Next[/COLOR] HTMLCell
        r = r + 1
        c = 1
    [COLOR=darkblue]Next[/COLOR] HTMLRow
    
    [COLOR=darkblue]Set[/COLOR] XMLReq = [COLOR=darkblue]Nothing[/COLOR]
    [COLOR=darkblue]Set[/COLOR] HTMLDoc = [COLOR=darkblue]Nothing[/COLOR]
    [COLOR=darkblue]Set[/COLOR] HTMLTable = [COLOR=darkblue]Nothing[/COLOR]
    [COLOR=darkblue]Set[/COLOR] HTMLTables = [COLOR=darkblue]Nothing[/COLOR]
    [COLOR=darkblue]Set[/COLOR] HTMLRow = [COLOR=darkblue]Nothing[/COLOR]
    [COLOR=darkblue]Set[/COLOR] HTMLRows = [COLOR=darkblue]Nothing[/COLOR]
    [COLOR=darkblue]Set[/COLOR] HTMLCell = [COLOR=darkblue]Nothing[/COLOR]
    
[COLOR=darkblue]End[/COLOR] [COLOR=darkblue]Sub[/COLOR]

Hope this helps!

 

[Cool_Pacific]

Domenic,

Thank you so much for your help! That works perfect
Sorry for the late reply, I was occupied with some other work so could not try this.
Once again thank you so much!

 

[Domenic]

You're very welcome! Glad I could help!

And thanks for your feedback!

Cheers!

 

[Cool_Pacific]

Sorry for bothering again!

Another page has very different source code, it is not using tables. I am not getting how to parse Market Cap, P/E, Book Value, Div, Div YIELD, PRICE/BOOK etc from below web page

A F Enterprises Stock Price, Share Price, Live BSE/NSE, A F Enterprises Bids Offers. Buy/Sell A F Enterprises news & tips, & F&O Quotes

Any help is appreciated!

 

[Domenic]

First, in future, please start a new thread for any new question. Now, to get the market details for that particular page, try...

[COLOR=darkblue]Sub[/COLOR] GetMarketDetails()

    [COLOR=darkblue]Dim[/COLOR] XMLReq [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]New[/COLOR] MSXML2.XMLHTTP60
    [COLOR=darkblue]Dim[/COLOR] HTMLDoc [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]New[/COLOR] MSHTML.HTMLDocument
    [COLOR=darkblue]Dim[/COLOR] HTMLDiv [COLOR=darkblue]As[/COLOR] MSHTML.IHTMLDivElement
    [COLOR=darkblue]Dim[/COLOR] r [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]Long[/COLOR]
    [COLOR=darkblue]Dim[/COLOR] i [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]Long[/COLOR]
    
    XMLReq.Open "GET", "http://www.moneycontrol.com/india/stockpricequote/miscellaneous/afenterprises/AFE01", [COLOR=darkblue]False[/COLOR]
    XMLReq.send
    
    [COLOR=darkblue]If[/COLOR] XMLReq.Status <> 200 [COLOR=darkblue]Then[/COLOR]
        MsgBox "Error " & XMLReq.Status & ":  " & XMLReq.statusText
        [COLOR=darkblue]Exit[/COLOR] [COLOR=darkblue]Sub[/COLOR]
    [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]If[/COLOR]
    
    HTMLDoc.body.innerHTML = XMLReq.responseText
    
    [COLOR=darkblue]Set[/COLOR] HTMLDiv = HTMLDoc.getElementById("mktdet_1")
    
    Worksheets.Add
    
    Range("A1").Value = "Standalone"
    
    r = 2
    [COLOR=darkblue]For[/COLOR] i = 0 [COLOR=darkblue]To[/COLOR] HTMLDiv.Children(0).Children.Length - 1
        Cells(r, "A").Value = HTMLDiv.Children(0).Children(i).Children(0).innerText
        Cells(r, "B").Value = HTMLDiv.Children(0).Children(i).Children(1).innerText
        r = r + 1
    [COLOR=darkblue]Next[/COLOR] i
    
    r = 2
    [COLOR=darkblue]For[/COLOR] i = 1 [COLOR=darkblue]To[/COLOR] HTMLDiv.Children(1).Children.Length - 1
        Cells(r, "D").Value = HTMLDiv.Children(1).Children(i).Children(0).innerText
        Cells(r, "E").Value = HTMLDiv.Children(1).Children(i).Children(1).innerText
        r = r + 1
    [COLOR=darkblue]Next[/COLOR] i
    
    [COLOR=darkblue]Set[/COLOR] XMLReq = [COLOR=darkblue]Nothing[/COLOR]
    [COLOR=darkblue]Set[/COLOR] HTMLDoc = [COLOR=darkblue]Nothing[/COLOR]
    [COLOR=darkblue]Set[/COLOR] HTMLDiv = [COLOR=darkblue]Nothing[/COLOR]
    
[COLOR=darkblue]End[/COLOR] [COLOR=darkblue]Sub[/COLOR]

Hope this helps!

 

[Cool_Pacific]

Domenic,

Sorry for not starting a new thread, I didn't know about, I will keep it in mind in future.

Thanks again for your quick response and help. Code you provided is working perfectly

 

[siddwivedi]

http://www.moneycontrol.com/india/stockpricequote/steel-large/tatasteel/TIS

based on the above solution... I have been trying to extract table however I am getting an error
Obviously I am not an expert with VBA but could you please help with this

I am trying to extract Class "FR nseStDtl"... due to error it is just giving limited data
also I need to extract data from the table where moving averages are given

Could you please help.

Thanks in advance