What technique is required to punch details in website through VBA

[ThrottleWorks]

Hi,

I am not looking for ready answers for below mentioned problem. However please help me with broader guidelines.

Please check below mentioned link. I am using this as an example only. Not my genuine requirement.

https://www.amfiindia.com/net-asset-value

You will see multiple drop down options on this page. I will need to navigate manually to get NAV for particular fund.

Can we submit the options through VBA ? can I derive NAV by selecting options through VBA. Is it possible ? how do I do this.

What techniques I should learn to perform these tasks ?
Can anyone please help me in this ?

 

[Chihiro]

It's possible. One way to do it is to POST form data through XMLHTTP.

I'd first suggest getting FireFox and an extension called FireBug. If you don't already.

For a test I used Axis Mutual Fund with Balanced "Open Scheme" and Growth "Close Scheme".

Edit for clarity: Open URL you provided and then turn on FireBug. Make sure you enable "Net" tab. Then use parameter above (or any that produces valid result) and hit "Get NAV". Navigate to "Net" and look at what's generated there.


You'll notice that POST NAVList (https://www.amfiindia.com/modules/NAVList) as location where final request is sent and where HTML code for table is generated.

In Post section of NAVList, you will see string needed to generate the table.


Below is sample code where I used POST method to generate response and then wrote it to IE to test resulting HTML.

Sub test()
    Dim objIE As Object, xmlhttp As Object
    Dim PostData As String, response As String
    Set objIE = CreateObject("InternetExplorer.Application")
    objIE.navigate "about:blank"
    objIE.Visible = True

    Set xmlhttp = CreateObject("MSXML2.ServerXMLHTTP")

    PostData = "MFName=0-53&OpenScheme=Balanced&CloseScheme=Growth&IntervalFund="
    xmlhttp.Open "Post", "https://www.amfiindia.com/modules/NAVList", False
    xmlhttp.setRequestHeader "Content-Type", "application/x-www-form-urlencoded"
    xmlhttp.Send PostData

    response = xmlhttp.responseText
    objIE.document.Write response

    Set xmlhttp = Nothing
End Sub

From there, you can use other methods to extract table data only.

Edit: Methods such as... String manipulation; MSXML2.DOMDocument etc.
Refer to link for how to load XML from a string.
http://analystcave.com/vba-xml-working-xml-files/

 

[Marc L]

Hi !

After a request, instead to write to IE, a DOM object can directly be used,
an HTML file for example like in these threads :
• VBA Internet Explorer Automation - Insert value from cell to input
• Capturing Web Data as per the Financial Year …

Differents methods to use, it depends on the Web page itself
and the observation & HTML skills of the programmer …

A great overview in Web Scraping tutorial
as a part of Web Scraping archives …
​

 

[Chihiro]

Yep. Using DOM without write to IE is more efficient. Write to IE was just for test to see what's taken from response is indeed the data/xml string needed. For demo purpose.

 

[ThrottleWorks]

Hi @Chihiro sir and @Marc L sir, thanks a lot for these details.
Please give me some time to go through it in detail before posting further.

Have a nice day ahead.

 

[Chihiro]

Additional notes on the site (https://www.amfiindia.com/net-asset-value)

1. Drop-down menu in the page is dependent on MFName. This becomes bit tricky if you want to replicate. I'd personally just query all data and do manipulation once imported into Excel.
2. Response is actually HTML, so you can utilize "htmlfile" to parse the table by Element Tag Name.
3. Unfortunately, table column varies in size (i.e. some rows only has 1 column, where some has more), so array isn't feasible to transpose the data. You can loop through each row and loop through columns within each row and set cell value. Which is slower, but entire table (13620 rows) took about 97 sec to process.

Here's the test code that I ran.

Sub test()
    Dim xmlhttp As Object
    Dim PostData As String
    Dim myHtml As Object, tElement As Object, tRow As Object, tCel As Object
    Dim x As Long, y As Long
    Dim StartTime As Double, secElapsed As Double
  
    StartTime = Timer
    y = 1: x = 1
  
    Set xmlhttp = CreateObject("MSXML2.ServerXMLHTTP")

    PostData = "MFName=-1&OpenScheme=All&CloseScheme=All&IntervalFund=All"
    xmlhttp.Open "Post", "https://www.amfiindia.com/modules/NAVList", False
    xmlhttp.setRequestHeader "Content-Type", "application/x-www-form-urlencoded"
    xmlhttp.Send PostData

    Set myHtml = CreateObject("htmlfile")
    myHtml.body.innerHtml = xmlhttp.responseText
  
    Set tElement = myHtml.getElementsbyTagName("Table")(0)
  
    Application.ScreenUpdating = False
    For Each tRow In tElement.Rows
        For Each tCel In tRow.Cells
            Sheet1.Cells(x, y) = tCel.innerText
            y = y + 1
        Next tCel
        y = 1
        x = x + 1
    Next tRow
    Application.ScreenUpdating = True
    secElapsed = Round(Timer - StartTime, 2)
    MsgBox "This code took " & secElapsed & " seconds", vbInformation
    Set myHtml = Nothing
    Set xmlhttp = Nothing
End Sub

 

[ThrottleWorks]

Hi @Chihiro sir, thanks a lot for the help. Extremely sorry for delay. (unwell from last week hence delay).

Have a nice day ahead.