How to Get stock prices in Excel? – Best Live Stock Ticker

Get Stock Quotes in Excel

Use this simple technique to get free stock market data as an Excel stock price list. 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.

How to download Stock data to Excel?

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

Open a new workbook, save it as Excel_Stock_Quotes.xlsm.
In sheet 1, cells A2 enter "NASDAQ" and in B2 enter "MSFT" (in Row 2)
Press Alt+F11 to view VB editor.
Copy paste the below code.
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 Quotes from Google Finance
Sub Get_Stock_Quotes_in_Excel_From_Google_Finance()
    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 'download stock prices into Excel' 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 quotes in Excel 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. 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.