0 votes
in VBA by Beginner (13 points)

I found a VBA script to extract the stocks data from yahoo.finance. I download the excel workbook from the website and tried to run the code and it works perfectly well if I use the downloaded workbook. However, when I copy the VBA code to a new excel file, and I run the code in the new excel file, it shows some error and I have no idea how to solve it. It highlighted a line of code for me and pop up a window to show the error. I will attach the screenshot on what error I obtained. I'm new to VBA coding. I had tried to change the sheet name for my new excel file to "Main" which is align to the code

 but it still doesn't work. I attached the link below to download the worksheet. Any help will be greatly appreciated! Thanks a lot ! :)

Website to download the worksheet: 


1 Answer

+1 vote
by Expert (887 points)
selected by
Best answer


You have to activate some libraries ..so from VBE go to Tools >> References >> Check the libraries as in the downoaded file

by Beginner (13 points)

Hello, thanks for your solution. Yes, it works for me already without any error. However, after I run the code it shows something different (N/A) on excel to me. I attached 2 diagram below. (1st diagram is original excel before I run the code, and the 2nd diagram is what the code did to my excel ). Please give me some advise, thanks! :)


by Expert (887 points)
You have to enter yahoo tickers in column A as in the original file .. N/A mean there are no tickers in the cells in the column A ...
by Beginner (13 points)
I thought I added the tickers in column A already as shown in the first diagram on the comment above. ( AAPL in column A row 2 and FB in column A row 3) So what does you actually mean? Thanks!
by Expert (887 points)
I didn't get what you mean ..
Have a look if there are any defined ranges at the original workbook
by Beginner (13 points)
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("Main")
    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
        Dim s As String
        'Get source code of site
        s = Http2.ResponseText
            Dim metrics As Variant
            '**** Metric fields here
            metrics = Array("trailingPE", "forwardPE", "beta", "marketCap", "fiftyTwoWeekHigh")

            '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
                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
            Next element
            Dim symbol As String
            symbol = ticker
            col_count = 2
            row_count = row_count + 1
    Next ticker
    MsgBox ("Done")
    Exit Sub

    ws.Range(Cells(row_count, col_count), Cells(row_count, col_count)).Value = "N/A"
    Resume getData
End Sub

Sub clear_data()

    Dim ws As Worksheet
    Set ws = Sheets("Main")
    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))
End Sub

The VBA code above are doing 2 tasks. One is loading the data to excel and the lower part is clear the data. 

I'm not sure are you able to see the diagram that I attached in the comment above, but what I mean is my input for the stocks are already in the first column which is column A (Based on the setting in the VBA code) but it still give me NA as the output, could you give me some advise on that?

Thanks for your help! :)  

by Expert (887 points)
Have you put the headers in your workbook as in the original workbook?
by Beginner (13 points)
Thanks for the reminder! It works for me know. I thought the header is not important and won't affect the output so I didn't put it. Really appreciate your solution for me! Thank you. :)
by Expert (887 points)
You're welcome. Glad I can offer little help.

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

Looking for something else? Hire our professional VBA Help, instead.

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:

ParserMonster $25 Amazon Gift Card
Hightree $10 Amazon Gift Card
Thales1 $10 Amazon Gift Card
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.


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