How to Get Stock Quotes in Excel using VBA or Fetch Data from a Web Page using a Macro

I am not a stockbroker, neither have I invested in stocks in recent years. All right, I did maintain a small portfolio in the past, until the market came crashing in the year 2008. It was very scary then. However, if you are still actively investing in the stock market and use Ms-Excel, then here’s an example on how to get current stock quotes, every few seconds, in your Excel file from a Web page using VBA Macro.

Get Stock Quotes in Excel from Rediff using VBA Macro

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 other columns. The second column is important, since I have named each cell in that column. See the image.

Name a Cell in Excel

Also Read: 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), recognized by Rediff Money. In fact, its link to a web page with the company name. Each page will have 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).

Excel as Database

The VBA Macro

Before, writing the code, I 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 database in my workbook. I’ll use the ActiveX Data Object to extract data.

Add Reference to Internet Explorer in Excel

Now, create a Macro and choose Module from project explorer.

Add a Module in Excel VBA

Copy the below code in the Module.

Option Explicit

Const sSiteName = "http://money.rediff.com/companies/"
Const tickerID = "ltpid"    ' THE ID OF THE SPAN 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:

    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

Set Connection

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
Start Timer and Get Stock Price

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
Set a Timer to Call Update Method

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.

Conclusion

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.

Thanks for reading.

Previous - Loop Through All Textboxes in UserForm and Clear the Values in VBANext - Create Named Cells or Named Range in Excel and Use the Names in Formula or for Data Validation



Like this Article? Subscribe now, and get all the latest articles and tips, right in your inbox.

Enter your email id

Delivered by FeedBurner
Tweet this article Facebook Google+

Related Posts:

Join our Google Plus Community and be a part of a discussion!