How to get Stock Quotes in Excel using VBA

Last updated: 3rd June 2024

Note: The code still works, even if you have installed Windows 11.

➡️ I'll show you how to get real time data from nse in your Excel worksheet using vba.

The above image gives you an idea about what this example here is going to show you. The VBA macro will extract or fetch data (current stock price with changes) from Rediff Money every few seconds and show the figures against a given list of scripts or company (multiple scripts).

Create a Portfolio in Excel

Let’s create a small portfolio in Excel. Remember, it’s a Macro Enabled Excel. I have two sheets. The first is "Portfolio" and second is "Scripts".

In the first sheet, I have "three" columns named Script, Current Price and Change. You can add many more columns. The second column is important, since I have named each cell in that column. See the image.

Similar example: How to Create Named Cells and Named Range in Excel and use it in Formulas

Similarly, name all the cells against each script. Since, I have multiple scripts, I’ll a create a small database in the workbook itself. Therefore, I have another sheet named Scripts.

The second sheet (Scripts) has the company names (in the first column) with a unique id. The company name and id is attached to Rediff Money URL. Each web page has stock details such as "current price", "changes", "volume of stocks" and some historical data. The second column in the sheet has names of each cell (in the first sheet, column 2).

Before, writing the code, you need to add few references.

1) I am using "Internet Explorer" to connect to the web page.
2) I’ll extract HTML data from the web pages.
3) I have a small database in my workbook. I’ll use an ActiveX Data Object to extract data.

Now, open the VBA editor and choose Module from project explorer.

Copy the code in the Module.

Option Explicit

Const sSiteName = "https://money.rediff.com/companies/"
Const tickerID = "ltpid"    ' THE ID OF THE <span> element ON THE WEB PAGE (THIS ELEMENT WILL HAVE THE PRICE OF EACH SCRIPT)
Const sChangeID = "change"

Dim Timer

Public myConn As New ADODB.Connection
Public rs As New ADODB.Recordset
Public sQuery As String

' SET A CONNECTION. WE'LL READ DATA FROM WORKBOOK.
Sub SetConn()
    If myConn.State = adStateOpen Then
        myConn.Close
    End If

    Dim sConnString As String
    sConnString = "Driver={Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb)};" & _
        "DBQ=" & ActiveWorkbook.Path & Application.PathSeparator & ActiveWorkbook.Name

    myConn.ConnectionString = sConnString
    myConn.Open         ' OPEN THE CONNECTION.
End Sub

' CALL METHODS WHEN THE FILE OPENS.
Sub Auto_Open()
    setConn
    Call setUpdateTimer
End Sub

' REFRESH PAGE WITH NEW PRICES EVREY 10 SECONDS.
Sub setUpdateTimer()
    Timer = Now + TimeValue("00:00:10")
    Application.OnTime Timer, "updateStockPrice"
End Sub

Public Sub updateStockPrice()

    ' CREATE AN Internet Explorer OBJECT.
    Dim IE As Object
    Set IE = CreateObject("InternetExplorer.Application")
    IE.Visible = False          ' KEEP THIS HIDDEN.
    
    ' CREATE HTML OBJECTS.
    Dim oHDoc As HTMLDocument
    Dim oHDiv As HTMLDivElement
    
    Dim sPrice As String
    Dim sChange As String
    
    Dim rng As Excel.Range      ' THE RANGE (OR CELL) WHERE WE'LL SHOW THE PRICE.

    On Error GoTo Error:

    ' show the progress of the process.
    Application.StatusBar = "Loading data. Please wait ... "

    ' QUERY TO FETCH DATA FROM A LOCAL SHEET ("Scripts").
    sQuery = "SELECT *from [Scripts$]"
    
    If rs.State = adStateOpen Then
        rs.Close
    End If
    rs.CursorLocation = adUseClient
    
    rs.Open sQuery, myConn, adOpenKeyset, adLockOptimistic
    If rs.RecordCount > 0 Then
        Do While Not rs.EOF             ' FETCH ALL THE RECORDS IN SHEET "Scripts"
            If Trim(rs.Fields("Script").Value) <> "" Then
                ' THE URL OF THE WEB PAGE WITH SCRIPT NAME.
                Dim URL As String
                URL = sSiteName & rs.Fields("Script").Value
            
                IE.navigate URL
    
                ' WAIT TILL INTERNET EXPLORER IS FULLY LOADED.
                While IE.readyState <> 4
                    DoEvents
                Wend
            
                ' GET THE CURRENT STOCK PRICE, CHANGE.
                Set oHDoc = IE.Document
                Set oHDiv = oHDoc.getElementById(tickerID)
                sPrice = oHDiv.innerHTML
            
                Set oHDiv = oHDoc.getElementById(sChangeID)
                sChange = oHDiv.innerHTML
            
                ' SHOW PRICE IN THEIR RESPECTIVE CELL NAME.
                Set rng = Range(rs.Fields("CellName").Value)
                rng.Value = sPrice
            
                ' SHOW THE CHANGES IN THE CURRENT STOCK PRICES, WITH COLORS.
                Cells(rng.row, rng.Column + 1) = sChange
                If Val(sChange) < 0 Then
                    Cells(rng.row, rng.Column + 1).Font.ColorIndex = 3  ' RED, IF ITS IN NEGATIVE.
                Else
                    Cells(rng.row, rng.Column + 1).Font.ColorIndex = 10 ' GREEN, IF POSITIVE.
                End If
            End If

            rs.MoveNext
        Loop

        ' CLOSE AND RELEASE.
        rs.Close
        Set rs = Nothing
        
        myConn.Close
        Set myConn = Nothing
        
        Application.StatusBar = "Price Updated"         ' UPDATE  STATUS BAR.
    Else
        Application.StatusBar = "No data found. Please check sheet 'Scripts'."
        Exit Sub
    End If
Error:
    On Error Resume Next

    Set IE = Nothing            ' EXIT INTERNET EXPLORER.
    Call setUpdateTimer         ' RESET TIMER.
End Sub

I’ve declared three Const in the beginning of the Module.

a) Const sSiteName: The link to the Rediff Money website
b) Const tickerID: The id of the <span> element on the web page, which shows the current stock price
c) Const sChange: For the change in stock price

I have many scripts in my workbook and I am using the second sheet as database, I’ll create an ADO connection and a Recordset (to fetch records in the sheet).

Sub SetConn()
…
End Sub

I wish to get the current stock prices and update the prices every few seconds, the moment I open my Portfolio (Excel) file. Therefore, I’ll use Auto_Open() method. This is first method that will be called after you open the file.

Sub Auto_Open()
…
End Sub

I have set a time of 10 seconds for my Macro to update the stocks. You can set a time according to your convenience.

Every 10 seconds, it will call a method to fetch the current stock price. To do this, I am using Excel’s Application.OnTime() Method. Using this method, you can schedule a procedure to run at a specified time. The procedure name is updateStockPrice.

Sub setUpdateTimer()
    Timer = Now + TimeValue("00:00:10")
    Application.OnTime Timer, "updateStockPrice"
End Sub

Therefore, now the Macro will call the procedure updateStockPrice every 10 seconds.

In the update procedure, I am creating an object of Internet Explorer and using various HTML objects, I’ll read the data from the web page. It runs a loop (every 10 sec) through each script in the workbook, opens Internet Explorer and fetches current price and change from the web page.

Thought you should know

The macro uses Internet Explorer object to communicate with a web page. It opens the IE browser on your computer, but remains hidden. In some cases it may not update the figures on your Excel worksheet. Therefore, please make sure that TLS and SSL protocols are enabled in your IE browser.

➡️ Note: This macro still works, even if you have upgraded to Windows 11.

Go to Tools > Internet Options > Advanced > Settings > Security and enable the protocols.

You can extract a variety of data from Rediff Money for your portfolio. Honestly, I am not sure if there are any restrictions imposed by Rediff. I am just using the information provided (as it is) for my example here, on how to get current stock price using a Macro in Excel.

Since, I am extracting data from a third party website; it is always possible that they will make changes to the web pages. In that case, you too might have to make changes in the Macro, accordingly.

The process of getting stock prices using Internet Explorer may be slow. However, it works nicely for a small portfolio.

← PreviousNext →