Automating the retrieval of stock market data in Excel using VBA

Automating the retrieval of stock market data in Excel using VBA can be extremely useful for investors and financial analysts. Below is a detailed guide and code to retrieve stock market data using an API (e.g., Alpha Vantage) and display it in an Excel spreadsheet.

Step 1: Obtain an Alpha Vantage API Key

1. Visit Alpha Vantage and create an account.
2. Obtain your free API key.

Step 2: Write VBA Code to Retrieve Stock Data

Follow these steps to set up your VBA code.

1. Open the VBA Editor

• Press Alt + F11 to open the VBA editor.
• Go to Insert > Module to add a new module.

2. Add VBA Code to Retrieve Stock Data

Here’s an example of VBA code to retrieve stock data from Alpha Vantage. This code fetches the closing price of a specific stock and displays it in an Excel sheet.

Sub GetStockData()
    ' Declare variables
    Dim Symbol As String
    Dim APIKey As String
    Dim URL As String
    Dim httpRequest As Object
    Dim JSON As Object
    Dim ClosePrice As Double
    Dim LastDate As String   
    ' Enter the stock symbol and your API key
    Symbol = "AAPL" ' Example: Apple Inc. (change as needed)
    APIKey = "your_api_key" ' Replace with your Alpha Vantage API key   
    ' Alpha Vantage API URL (Daily Time Series)
    URL = "https://www.alphavantage.co/query?function=TIME_SERIES_DAILY&symbol=" & Symbol & "&apikey=" & APIKey   
    ' Create an HTTP request to fetch the data
    Set httpRequest = CreateObject("MSXML2.XMLHTTP")
    httpRequest.Open "GET", URL, False
    httpRequest.Send   
    ' Check if the request was successful
    If httpRequest.Status = 200 Then
        ' Parse the JSON response
        Set JSON = JsonConverter.ParseJson(httpRequest.responseText)       
        ' Get the last date and the closing price
        LastDate = JSON("Time Series (Daily)").Keys(1)
        ClosePrice = JSON("Time Series (Daily)")(LastDate)("4. close")       
        ' Display the results in Excel cells
        Range("A1").Value = "Symbol"
        Range("B1").Value = Symbol
        Range("A2").Value = "Last Close Date"
        Range("B2").Value = LastDate
        Range("A3").Value = "Closing Price"
        Range("B3").Value = ClosePrice       
    Else
        MsgBox "Error retrieving data: " & httpRequest.Status
    End If   
End Sub

Detailed Explanation of the Code

1. Variable Declarations:
   • Symbol: The stock symbol (e.g., « AAPL » for Apple).
   • APIKey: Your Alpha Vantage API key.
   • URL: The API URL to fetch daily time series data for the specified stock symbol.
   • httpRequest: An object to send an HTTP request to the API.
   • JSON: An object to parse the JSON response returned by the API.
   • ClosePrice and LastDate: Variables to store the closing price and the date of the last stock closing.
2. Fetching Data from the API:
   • The code sends an HTTP GET request to the Alpha Vantage API to retrieve daily stock data.
   • If the request is successful (status = 200), it parses the JSON response and extracts the date and closing price of the most recent stock data.
3. Displaying Data in Excel:
   • The stock symbol, last close date, and closing price are then displayed in cells A1, B1, A2, B2, etc.

Step 3: Add a JSON Parser for VBA

Alpha Vantage returns data in JSON format, but Excel VBA doesn’t natively understand JSON. To parse the response, you need a JSON parser like VBA-JSON.

Here’s how to add it:

1. Download the VBA-JSON library from GitHub.
2. Copy the JsonConverter.bas file and import it into your VBA editor (via File > Import File).
3. The code above uses this module to parse the JSON data.

Step 4: Test and Run the Code

1. Close the VBA editor.
2. In Excel, press Alt + F8, select GetStockData, and click Run.
3. You will see the stock symbol, last close date, and closing price displayed in the specified cells of your Excel sheet.

Step 5: Customization

• You can customize the stock symbol (e.g., « GOOGL » for Google) or even automate retrieving multiple stock symbols listed in your Excel sheet.
• You can also extend the code to retrieve other financial data like open price, high, low, or volume.

Note

The free Alpha Vantage API allows a limited number of requests per minute (5 requests per minute for free accounts). If you need more frequent data retrieval, you can either upgrade to a paid plan or consider using another service or API that suits your needs.

Troubleshooting

• Ensure that your API key is valid and hasn’t expired.
• Ensure that the API URL is correct and the symbol you’re using is supported.