How To Get Stock Data In Excel By Using VBA

Introduction

In this tutorial, I will show you how to retrieve stock price information using Excel VBA. I will demonstrate the process through a simple macro that pulls the current stock price from Yahoo Finance and pastes it into a worksheet. When the operation is complete, a message box will pop up to inform you that the macro was successful.

While I will focus on retrieving the current stock price, the code can be easily adjusted to extract other information like earnings per share, dividend yield, market cap, and more.

Setting Up the Environment

To create the bot, you need to have Selenium Chrome installed along with the Google Chrome Web Driver. Here’s how to set it up:

1. Open your browser and search for “VBA Selenium download”. You will find a GitHub page for Selenium Basic.
2. Click on “Download Selenium Basic” and follow the installation instructions.
3. Next, you need to install the correct Chrome Web Driver. To find your version, go to Chrome, click on “Help”, and then “About Google Chrome”.

Once you have the version, search for “Chrome Driver download” and select the appropriate version to download. After downloading, extract the zip file and replace the existing Chrome Driver in your Selenium folder located at:

C:\Users\\AppData\Local\SeleniumBasic

Writing the VBA Code

Now, let’s open Excel and ensure that the Developer tab is enabled. You can enable it by going to File > Options > Customize Ribbon and checking the box for Developer.

Once the Developer tab is visible, click on it and then select Visual Basic. We will insert a new module, which acts as a container for our code.

Linking the Selenium Library

To use Selenium in Excel, go to Tools > References and check the box for the Selenium Type Library. Click OK to link it to your Excel project.

Creating the Stock Retrieve Procedure

Let’s create a new procedure named StockRetrieve. Start by declaring a new Web Driver instance:

Dim bot As New WebDriver

Next, start the Chrome browser with:

bot.Start "chrome"

To open a new tab in Chrome, execute a JavaScript command:

bot.ExecuteScript "window.open(0)"

Accessing Yahoo Finance

Next, I will specify the Yahoo Finance page for the stock I want to retrieve. For example, for Apple, the URL would be:

https://finance.yahoo.com/quote/AAPL

Use the following code to open the page:

bot.Get "https://finance.yahoo.com/quote/AAPL"

To interact with elements on the webpage, you can use the XPath location. Right-click on the stock price element and select Inspect. Copy the XPath for the stock price.

Extracting the Stock Price

Store the stock price in a variable:

Dim stockPrice As String
stockPrice = bot.FindElementByXPath("XPath_here").Text

To display the retrieved stock price, you can use:

MsgBox stockPrice

Retrieving Multiple Stock Prices

To extend this to multiple stocks, prepare your Excel sheet with the stock URLs, ticker symbols, and where to paste the stock prices. Use a loop to iterate through each stock and retrieve the prices:

For i = 2 To 6
    ' Retrieve link and stock price
Next i

After retrieving each price, paste it into the respective worksheet cell:

Sheets(1).Range("C" & i).Value = stockPrice

Final Touches

Finally, create a button in Excel to run the macro easily. You can find buttons under the Developer tab. Once inserted, assign the macro to the button.

To run the macro in the background (headless), use:

bot.AddArgument "--headless"

Conclusion

In this tutorial, I showed you how to create a simple stock retrieval bot using Excel VBA and Selenium. You can easily adapt the code to scrape various types of information from Yahoo Finance or other websites by adjusting the XPath accordingly. I hope you found this tutorial useful!

Thanks for reading.

Further Links

• Download the Excel file incl. source code here