How To Get Stock Data In Excel By Using VBA
Language:
In this tutorial, I will show you how you can pull stock information in Excel by using VBA & Selenium for free. No API is required. The information will be pulled from Yahoo Finance using the Chrome Browser.
Sub StockRetrieve() Dim bot As New WebDriver, By As New By 'Open up Chrome in background (do not show chrome) 'bot.AddArgument "--headless" ' Get last row in column A lastrow = Cells(Rows.Count, 1).End(xlUp).Row 'Init New Chrome instance bot.Start "chrome" ' Wait for 500 ms bot.Wait (500) ' ### Loop through each link, retrieve stock price and write to cell For i = 2 To lastrow ' Wait for 500 ms bot.Wait (500) 'Link to stock link = Sheets(1).Range("a" & i).Value 'Open Up a new Tab in Chrome (javascript) bot.ExecuteScript "window.open(arguments[0])", link 'Switch to new Tab bot.SwitchToNextWindow ' Wait for 500 ms bot.Wait (500) 'If element is present, click on it 'Accept Button for cookies If bot.IsElementPresent(By.XPath("//*[@id='consent-page']/div/div/div/div[2]/div[2]/form/button")) Then bot.FindElementByXPath("//*[@id='consent-page']/div/div/div/div[2]/div[2]/form/button").Click End If 'Store Stockprice in variable (same position for all stocks) stockprice = bot.FindElementByXPath("//*[@id='quote-header-info']/div[3]/div[1]/div/span[1]").Text 'Write Stock price to worksheet Sheets(1).Range("C" & i).Value = stockprice Next i MsgBox "Done :)" End Sub