0 votes
in VBA by Beginner (13 points)

I'm finding a way to speed up the time when I run my VBA code as fast as possible. The code is actually used to obtain the "TrailingPE" and "current price" for the stocks from yahoo.finance. However, if I have few hundred stocks inside the input column, it takes a lot of time to run the code to give the output. I had search for the solution in google but I have no idea how to add the code suggest by other people into my VBA code because I'm very new to VBA coding. I attached my code below. Any help will be greatly appreciated! Thanks a lot! :)

Sub qTest_3()
    
    Call clear_data
    
    Dim myrng As Range
    Dim lastrow As Long
    Dim row_count As Long
    Dim ws As Worksheet
    Set ws = Sheets("DataBase")
    
    col_count = 2
    row_count = 2
    
    'Find last row
    With ws
     lastrow = .Range("A" & .Rows.Count).End(xlUp).Row
    End With
    
    'set ticker range
    Set myrng = ws.Range(Cells(2, 1), Cells(lastrow, 1))
 
    'llop through tickers
    For Each ticker In myrng
    
        'Send web request
        Dim URL2 As String: URL2 = "https://finance.yahoo.com/quote/" & ticker & "/key-statistics?p=" & ticker
        Dim Http2 As New WinHttpRequest
    
        Http2.Open "GET", URL2, False
        Http2.Send
    
        Dim s As String
        'Get source code of site
        s = Http2.ResponseText
            
            Dim metrics As Variant
            '**** Metric fields here
            metrics = Array("trailingPE", "currentPrice")
            

            'Split string here
            For Each element In metrics
    
    
                firstTerm = Chr(34) & element & Chr(34) & ":{" & Chr(34) & "raw" & Chr(34) & ":"
                secondTerm = "," & Chr(34) & "fmt" & Chr(34)
                
                nextPosition = 1
            
                On Error GoTo err_hdl
                
                Do Until nextPosition = 0
                    startPos = InStr(nextPosition, s, firstTerm, vbTextCompare)
                    stopPos = InStr(startPos, s, secondTerm, vbTextCompare)
                    split_string = Mid$(s, startPos + Len(firstTerm), stopPos - startPos - Len(secondTerm))
                    nextPosition = InStr(stopPos, s, firstTerm, vbTextCompare)
                    
                    Exit Do
                Loop
                
                On Error GoTo 0
                
                
                Dim arr() As String
                arr = Split(split_string, ",")
                metric = arr(0)
                
                'Output to sheet
                ws.Range(Cells(row_count, col_count), Cells(row_count, col_count)).Value = metric
                col_count = col_count + 1
                 
getData:
            
            Next element
            
            Dim symbol As String
            symbol = ticker
            
            col_count = 2
            row_count = row_count + 1
        
    Next ticker
    
    MsgBox ("Done!")
    
    Exit Sub

err_hdl:
    ws.Range(Cells(row_count, col_count), Cells(row_count, col_count)).Value = "N/A"
    Resume getData
    
    Application.ScreenUpdating = False
    
End Sub



Sub clear_data()

    Dim ws As Worksheet
    Set ws = Sheets("DataBase")
    Dim lastrow, lastcol As Long
    Dim myrng As Range
    
    With ws
     lastrow = .Range("A" & .Rows.Count).End(xlUp).Row
    End With
    
    lastcol = ws.Cells(1, Columns.Count).End(xlToLeft).Column
    
    Set myrng = ws.Range(Cells(2, 2), Cells(lastrow, lastcol))
    
    myrng.Clear
    
End Sub

 

1 Answer

0 votes
by Super Expert (2.4k points)

There are couple things you can do to speed up your VBA HTTP GET requests. The advice I'm going to give is similar to the suggestions I gave in a recent question about quicker alternatives for VBA VLookup

A good way to speed up all macros is to set Application.ScreenUpdating=False at the beginning of your macro. I see you set it to False at the end, but you'll need to set it to False at the beginning and set it back to True at the end.

There are other application level properties you can change to make the macro faster, too. We outline the other ways to speed up your macro in this tutorial. Take a look at this and see if helps.

Another thing you should do is abort your HTTP request once you're done with it. Basically, you'll need to add the following line right before your Next ticker line or preferably right after your s = Http2.ResponseText line.

Http2.abort

I was going to suggestion pulling your Dim (variable declaration) statements outside of your For loop as well, but I'm not sure if that'll speed it up. That's more of a good general practice.

HTTP GET Requests take time with VBA so even after implementing these changes, dont get your hopes up too high. These suggestions will speed the macro up, for sure, but it won't make it run in a blink of an eye.

Welcome to wellsr Q&A
wellsr Q&A is the VBA and Python programming community that rewards you for learning how to code.

Getting Started
Register
VBA Cheat Sheets (On Sale Now)

Earn free prizes for asking VBA and Python questions and for answering questions asked by others in our community.

What makes us different?
Our points system rewards you with a chance for free gifts based on the quality of your questions and answers. All you have to do is post and you could get rewarded, like these members:

runfunke $10 Amazon Gift Card
coolag $10 Amazon Gift Card
Siew Hun $10 Amazon Gift Card

So, why don't you join us? It really is an encouraging way to motivate members in our VBA and Python community.

Register

For more programming tips visit the VBA Tutorials Blog and the Python Tutorials Blog.

...