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