Double data and runtime error when running macro

Hi!

I only started playing around with Excel VBA like a week ago (self-teaching online). I learned some basics of the syntax and VBA commands and then started out my project to build a stock watchlist via Yahoo Finance.

The code seems to work when I go through it step by step by hitting F8. My variable "Aktie" gives me the stock tickers I wrote in column 1. I set an array of 103 tickers through the variable "size" and "Zahl". Lastly, after navigating to Yahoo Finance the variable "Preis" gives me the current price of the stock. However when I start the Macro as a whole I get a runtime error (screenshot in German, it says "runtime error 91 vba object variable block variable not set"):

Attachment 24673

And actually, while it gave the correct stock prices when I scipped through the code via F8 slowly, this changed when I hit F8 faster: it then started to use the price of the previous stock for the next one just like in the screenshot above.
So maybe there would need to be some sort of buffer or loop? This is the Excel file with the Macro:

Attachment 24672

Thanks already for any help, always appreciate experts helping out a noobie. Hope I'll quickly get a better understanding of VBAs as I work on this project. :friends:

Lastly here's a copy of the code:

Sub Aktienscreener()

Dim appIE As Object
Set appIE = CreateObject("internetexplorer.application")

Dim Aktie As String
Dim size As Integer
Dim Zahl As Integer
Dim Preis As String

size = WorksheetFunction.CountA(Worksheets(1).Columns(1))

For Zahl = 2 To size
Aktie = Range("A" & Zahl & "")

With appIE
.Navigate "https://finance.yahoo.com/quote/" & Aktie & "?p=" & Aktie & ""
.Visible = False
End With
Do While appIE.Busy
DoEvents
Loop

Set allRowOfData = appIE.document.getElementsByClassName("Ta(end) Fw(600) Lh(14px)")

Preis = allRowOfData(0).innerText


Range("B" & Zahl & "").Value = Preis

Next

Range("A1:M103").Sort Key1:=Range("A2"), Header:=xlYes

End Sub

Hello jadajos,

This macro is attached to button on the worksheet and has been added to the attached workbook. This method is faster and more reliable than using Internet Explorer.

Code:

---

Sub Aktienscreener1()


    Dim Cell    As Range
    Dim LoHi    As Variant
    Dim HTMLdoc As Object
    Dim n      As Long
    Dim op      As String
    Dim oTable  As Object
    Dim oTables As Object
    Dim PageSrc As String
    Dim pc      As String
    Dim pe      As Variant
    Dim RngBeg  As Range
    Dim RngEnd  As Range
    Dim URL    As String
    Dim yr      As String
    Dim Wks    As Worksheet
   
        Set Wks = ActiveSheet
        Set RngBeg = Wks.Range("A2")
        Set RngEnd = Wks.Cells(Rows.Count, RngBeg.Column).End(xlUp)
       
        If RngEnd.Row < RngBeg.Row Then Exit Sub
   
        Set HTMLdoc = CreateObject("htmlfile")
       
        With CreateObject("MSXML2.ServerXMLHTTP")
            For Each Cell In Wks.Range(RngBeg, RngEnd)
                DoEvents    ' Pressing Ctrl+Break will interrupt the macro.
               
                URL = "https://finance.yahoo.com/quote/" & Cell & "?p=" & Cell & ""
                .Open "GET", URL, False
                .Send
                If .Status <> 200 Then
                    MsgBox "Error: " & .Status & " - " & .statusText
                    Exit Sub
                End If
           
                PageSrc = .responseText
       
                HTMLdoc.Write PageSrc
                HTMLdoc.Close
           
                Set oTables = HTMLdoc.GetElementsByTagName("table")
                Set oTable = oTables(0)
       
                pc = oTable.Rows(0).Cells(1).innerText  ' Previous Close
                op = oTable.Rows(1).Cells(1).innerText  ' Opening Price
                yr = oTable.Rows(5).Cells(1).innerText  ' 52 week Range
               
                LoHi = Split(yr, " - ") ' Element (0) is the 52 week low and element (1) is the 52 week High.
               
                Set oTable = oTables(1)
                pe = oTable.Rows(2).Cells(1).innerText  ' PE Ratio
               
                Cell.Offset(0, 1).Resize(1, 4).Value = Array(pc, op, LoHi(1), pe)
            Next Cell
        End With
       
End Sub

---

Thanks a lot for your reply and taking the time setting up the new macro, looks very neat and clean. I downloaded the file and unfortunately still got a runtime error though.

Going through the macro it marks the line ".Send" right after the ".Open" command for the URL:

Attachment 24679
Attachment 24680


About the syntax in general: I was going to add more stock variables such as RoE, PEG, EBITDA margin etc. later. So in doing that I would just add to one of the tables you set up, define them as variables with the DIM command on top, assign the location in Yahoo Finance table and resize the array at the bottom accordingly, correct? It would probably be necessary to work with another link in that case (https://finance.yahoo.com/quote/ABBV...tistics?p=ABBV or https://finance.yahoo.com/quote/" & Cell & "/key-statistics?p=" & Cell & ""). How do I find the table system of the Yahoo finance tables on that a web page though, or any web page for that matter (i. e. which rows and cells to write in the VBA syntax for each variable)? I get how to find and assign XML locations but didn't get how you work with the HTML.doc command and assign locations such as "oTable.Rows(0).Cells(1).innerText".

By the way, do you have recommendations for VBA books or online tutorials?

Cheers and thanks again for the support!
Jan

I have to say, why bother? There are tons of apps out there that do that for you, and will be developed.

Quote:

---

Originally Posted by xld

I have to say, why bother? There are tons of apps out there that do that for you, and will be developed.

---

Well so far I don't know about any app that does what I am looking for (creating a watchlist of hundreds of stocks, set a target entry price and list around 10 variables for each stock such as price, PE, PEG, EBITDA margin, RoE, Free Cash Flow, dept/equity, dividend yield, payout ratio). If you can recommend something that would of course be very much appreciated. There are actually a few useful financial monitoring tools I know like FastGraphs but that one costs around 50$/month and that's money I would currently rather put into stocks as my monthly savings are currently not big enough to count 50$ monthly as peanuts.

Hello Jan,

If I read the error message correctly it is COM error number 80004005. My German is not very good but the description looks like it says "Unknown Error", which is really not helpful. Your computer has the MSXML2.dll or we would have gotten the error message "Unable to create object". It is not the URL protocol (http:,https:) or the message would have been "Unknown protocol". If Send had worked and there was no response the error would have been either a timeout or server failed to respond.

Is this being run on network computer?
Does your computer have a firewall?

Quote:

---

Originally Posted by Leith Ross

Hello Jan,

If I read the error message correctly it is COM error number 80004005. (...)

Is this being run on network computer?
Does your computer have a firewall?

---

Hi, yeah, it's run on a network computer with a firewall. The error message actually said "runtime error - 2147012867 (80072efd): connection to server cannot be established". I'll try it out on another computer. Thanks for the continued support!

Hello Jan,

You may need to check with your network administrator about accessing web sites using VBA macros. Many sites disallow this for security reasons.