Excel VBA Get Stock Price – Google Finance – Best Live Stock Ticker

How to Get Stock Prices in Excel

This Excel VBA get stock price for any quote right from Google Finance. This uses web data extraction technique to get free live stock quote prices into Excel.

Have you been a stock market investor dumping a part of your income in long term, short term investments or intra-day trading. Then the Excel template & code in this article will be very helpful for you to get stock market index.

With this you can do your daily market trend analysis.

If not Warren buffet ideas, this Excel will guide you for better financial investment quotes.

Get Google Finance Stock quotes to Excel

To get live stock ticker quotes from Google Finance, follow these steps.

1. Open a new workbook, save it as Excel_Stock_Quotes.xlsm.
2. In sheet 1, cells A2 enter “NASDAQ” and in B2 enter “MSFT” (in Row 2)
3. Press Alt+F11 to view VB editor.
4. Copy paste the below code.
5. Press F5 to execute the code.

Once the code is executed you can get stock quotes in Excel updated from Google finance and displayed.

'Officetricks.com - Live Stock Ticker Quotes from Google Finance
Sub Google_Finance_Live_STock_Ticker_Quotes_To_Excel()
    Dim oGoogleFinHTTP As Object, sGoogleFinHTML  As String
    Dim sURL As String, sStockQuote As String, sStockExchange As String
    Dim pos1 As Double, pos2 As Double, pos3 As Double, iRow As Double
     
    'Download Stock Quotes - How to track stocks in Excel?
    iRow = 2
    Set oGoogleFinHTTP = CreateObject("MSXML2.ServerXMLHTTP")
    sStockExchange = VBA.UCase(ThisWorkbook.Sheets(1).Cells(iRow, 1))
    sStockQuote = VBA.UCase(ThisWorkbook.Sheets(1).Cells(iRow, 2))
    ThisWorkbook.Sheets(1).Cells(1, 3) = VBA.Now
    
    While sStockExchange <> ""
        'Get webpage data into Excel
        sURL = "http://www.google.com/finance?q=" & sStockExchange & "%3A" & sStockQuote
        oGoogleFinHTTP.Open "GET", sURL, False
        oGoogleFinHTTP.send
        sGoogleFinHTML = oGoogleFinHTTP.responseText
 
        'Set Regular Expression to Extract Google Finance Market HTML Data
        Reg_Exp_1 = "{u:" & """" & "/finance?q=" & sStockExchange & ":" & sStockQuote & """" & ",name:" & """" & sStockQuote & """" & ",cp:"
        Reg_Exp_2 = """" & ",p:" & """"
        Reg_Exp_3 = """" & ",cid:" & """"
    
        'Find Each Stock Quote and Get Quote Value to Excel
        pos1 = InStr(sGoogleFinHTML, Reg_Exp_1)
        If pos1 > 0 Then
            pos2 = InStr(pos1, sGoogleFinHTML, Reg_Exp_2)
            pos3 = InStr(pos1, sGoogleFinHTML, Reg_Exp_3)
            ThisWorkbook.Sheets(1).Cells(iRow, 3) = Mid(sGoogleFinHTML, pos2 + 5, pos3 - (pos2 + 5))
        End If
        
        'Read Next Stock Quote
        iRow = iRow + 1
        sStockExchange = VBA.UCase(ThisWorkbook.Sheets(1).Cells(iRow, 1))
        sStockQuote = VBA.UCase(ThisWorkbook.Sheets(1).Cells(iRow, 2))
    Wend
    MsgBox "Stock Market Index - Fetch Completed"

End Sub

If the code above is not working good for you, then try this code. This actually works on the idea of pulling any HTML data from website to Excel.

All that you need to do is, to strip those html tags and get only the data part that is required.

How ‘Excel VBA Get Stock Price’ works?

This VBA code first send a request to Google Finance for a Stock quote symbol. Then it get the HTML response and stores it in a variable field. From this field, it uses regular expressions to search for the quote value and then we get stock price to Excel VBA for that mentioned symbol.

You can enter as many Stock symbols in the Column ‘A’ and even get quotes value for a list of symbols. Ensure that there is no blank row in between the stock symbols.

If you are using MS Office 365, here is a related video that explains the similar concepts to fetch Stockhistory:

As you can see that Microsoft has added build in options to extract stock market data to Excel directly. If you are using any of the advanced version of MS Office then You dont have to worry about coding a custom VBA module. You can enter the formula right away in the worksheet and get the live stock quotes almost immediately.

Also Read: Read this article on how to implement a timer in Excel. This will help in upgrading this code to a best live stock ticker by yourself.