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: 

https://programmingforfinance.com/2018/06/web-scraping-fundamental-data-for-stocks-no-api/

1 Answer

+1 vote
by Skilled (605 points)
selected by
 
Best answer

Hello

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 Skilled (605 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 Skilled (605 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
        Http2.Send
    
        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
                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
    
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))
    
    myrng.Clear
    
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 Skilled (605 points)
Have you put the headers in your workbook as in the original workbook?
by Beginner (13 points)
+1
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 Skilled (605 points)
+1
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
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.

...