PDA

View Full Version : Double data and runtime error when running macro



jadajos
07-25-2019, 01:27 AM
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"):

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:

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

Leith Ross
07-25-2019, 05:26 PM
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.



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

jadajos
07-26-2019, 02:58 AM
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:

24679
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/key-statistics?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

Bob Phillips
07-26-2019, 03:54 AM
I have to say, why bother? There are tons of apps out there that do that for you, and will be developed.

jadajos
07-26-2019, 05:41 AM
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.

Leith Ross
07-26-2019, 09:48 AM
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?

jadajos
07-28-2019, 11:39 PM
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!

Leith Ross
07-29-2019, 10:03 AM
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.