Optimizing Web Scraping/Looping

[mattyj7183]

For starters, I am relatively new to VBA and adjusted the below code from a former co-workers code that pulled different info. I have looked around for a day or so and finally need the help from the masters.

In col A I have a list of mutual funds and stocks that I need to pull the category information from a public website. I have two different macros (one for stocks and one for mutual funds) to get the category tags - the URLs are different hence the 2 Macros. The below macro is for Mutual Funds, but they are essentially the same. For testing, I have truncated the list to go from A2:A5, and it takes in upwards of 2 minutes to pull those 3 data points. The final list will be in the 1,500ish and will take a ton of time. I know part of the time is the (Now + TimeValue("0:00:05"). I initially had (Now + TimeValue("0:00:02") but the macro was skipping tickers in col A. There must be a better way to optimize this code?

TIA!

Sub Mutual_Funds()
Application.ScreenUpdating = False
Application.DisplayStatusBar = False
Application.EnableEvents = False
Dim IE As Object
Set Rng = Range("A2:A5")


Set Row = Range(Rng.Offset(1, 0), Rng.Offset(1, 0).End(xlDown))


Set IE = CreateObject("InternetExplorer.Application")
With IE
.Visible = False
        
For Each Row In Rng
.navigate "http://quotes.morningstar.com/chart/fund/chart?t=" & Range("A" & Row.Row).Value & "&region=usa&culture=en_US"
Application.Wait (Now + TimeValue("0:00:05"))
Do
DoEvents
Loop Until IE.readyState = READYSTATE_COMPLETE
Dim doc As HTMLDocument
Set doc = IE.document
While IE.readyState <> 4
        
Wend
        
      
On Error Resume Next

Set Rng = Range("c2:c5")
Range("C" & Row.Row).Value = doc.getElementsByClassName("gr_text1")(10).innerText
        
Next Row
        
IE.Quit
Set IE = Nothing
 
        
End With
MsgBox (" Macro Completed ")
'
Application.ScreenUpdating = True
Application.DisplayStatusBar = True
Application.EnableEvents = True
End Sub

 

[Kyle123]

Yes, this can be optimized. Haze you got a sample fund code that returns some categories

 

[mattyj7183]

Thanks for the reply. An example would be if A2 contained the 5 character symbol "FCNTX" the result in C2 would be "Large Growth". Alternatively (if you wanted to bypass the list of looping tickers, and go right to the URL) the site for FCNTX is http://quotes.morningstar.com/chart/fund/chart?t=FCNTX&region=usa&culture=en_US

 

[Kyle123]

How's this:

Sub Mutual_Funds()

Dim rng As Range, cl As Range

Application.ScreenUpdating = False
Application.DisplayStatusBar = False
Application.EnableEvents = False

Set rng = Range("A2:A5")

For Each cl In rng
    cl.Offset(, 1).Value2 = getCategory(cl.Value2)
Next cl



Application.ScreenUpdating = True
Application.DisplayStatusBar = True
Application.EnableEvents = True

End Sub

Public Function getCategory(ByVal ticker As String) As String
    
    Const URL = "http://quotes.morningstar.com/chart/fund/c-banner?&t=XNAS:{{ticker}}&region=usa&culture=en-US&cur="
    Dim dom As HTMLDocument
    Static request As Object
    
    
    If request Is Nothing Then Set request = CreateObject("msxml2.xmlhttp")
    
    With request
        .Open "GET", Replace(URL, "{{ticker}}", ticker), False
        .send
        If Len(.statusText) > 0 Then
            Set dom = New HTMLDocument
            dom.body.innerHTML = .responseText
        Else
            getCategory = "N/A"
            Exit Function
        End If
    End With
        
    getCategory = Trim(dom.querySelector("span[vkey=[color=green]'MorningstarCategory']").innerText)[/color]
    

End Function

You will need to set a reference to the Microsoft HTML Object Library

 

[mattyj7183]

It works! Thank you so much!

 

[mattyj7183]

So one more thing, if you don't mind. Over the last few months, Morningstar has touted an update to their website for stocks, and lo and behold, they rolled it out today.

I don't think they will update their mutual fund pages so I can thankfully use what you have already provided. This is the link to their new stock page: http://www.morningstar.com/stocks/xnys/ge/quote.html and, if I am understanding what you wrote, the code would look more like this: http://www.morningstar.com/stocks/xnys/:{{ticker}}&/quote.html

I would need to scrape the investment style, so the result would be "Large Value". I tried to follow your code but I do not see a tag named "vkey" although I found this corresponding class: class="dp-value ng-binding"

I assume the new stock code will be 90% the same as the mutual fund code, but I am not entirely sure how to account for the differences in web design and incorporate them with the code you wrote.

Thanks again in advance!