How to Get Historical Stock Quotes In Excel – Google Finance

Get Historical Stock Quotes – Google Finance

Historical Stock Quotes are required as a first data item when you considering buying a stock. Lets say you are investigating GOOG stock listed in the NASDAQ Stock Exchange.  You can download the Historical Stock Quotes to Excel using Google Finance with the help of Easy VBA Macro.

If you are looking for downloading Live Stock Quotes in Excel, you could refer to this article.

How to Get Historical Stock Quotes in Excel

Get Historical Stock Quotes in Excel Using Google Finance

Historical Stock Quotes will return the values for parameters “Date, Open, High, Low, Close, Volume”. We need to pass the stock quote and the date range that we are looking for.

We need to construct Google Finance URL like this, “https://finance.google.com/finance/historical?q=NASDAQ:GOOG&startdate=Jan+1%2C+2016&enddate=May+19%2C+2017&num=500”.

• q  – Stock Quote
• startdate – Start Date
• enddate – End Date
• num – Number of data entries

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

1. Open a new workbook, save it as Excel_Historical_Stock_Quotes.xlsm.
2. In sheet 1, cells A2 enter “NASDAQ” and in B2 enter “GOOG” (in Row 2)
3. In sheet 1, cells C2 enter “1/1/2016” and in D2 enter “5/19/2017″ (in Row 2)
4. Press Alt+F11 to view VB editor.
5. Copy paste the below code.
6. Press F5 to execute the code.

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

'----------------------
'For Any issues in Code - Contact the Developer listed below
'Code Developed by Author of https://officetricks.com (Kumarapush)
'----------------------
Public Sub Get_Stock_Quotes_Range()
    'Declare Variables
    Dim oXMLHTTP As Object, htmlNew As HTMLDocument, sURL As String, i As Long
    Dim emmm As String, edd As Double, eyyyy As Double, smmm As String, sdd As Double, syyyy As Double
    Dim oSh As Worksheet, iSh As Worksheet, sDate As Date, eDate As Date, sQuote As String, dispCount As Double
    Dim iRow As Double, oRow As Double, ihtml
    
    'Change the URL before executing the code
    Set iSh = ThisWorkbook.Sheets("Sheet1")
    Set oSh = ThisWorkbook.Sheets("Sheet2")
    
     iRow = 2
     dispCount = 500  
     oRow = 1
            
    'Write Column Header to Output sheet
    oSh.Cells(1, 1) = "StockSymbol"
    oSh.Cells(1, 2) = "Date"
    oSh.Cells(1, 3) = "Open"
    oSh.Cells(1, 4) = "High"
    oSh.Cells(1, 5) = "Low"
    oSh.Cells(1, 6) = "Close"
    oSh.Cells(1, 7) = "Volume"

    'Loop thru Each quote in Input Sheet
    While VBA.Trim(VBA.UCase(iSh.Cells(iRow, 5))) <> ""

        'Get Quote & Date Values from Input sheet

        'Start Date
        sDate = iSh.Cells(iRow, 3)
        smmm = VBA.Format(sDate, "mmm")
        sdd = VBA.Format(sDate, "dd")
        syyyy = VBA.Format(sDate, "YYYY")
        
        'End Date
        eDate = iSh.Cells(iRow, 4)
        emmm = VBA.Format(eDate, "mmm")
        edd = VBA.Format(eDate, "dd")
        eyyyy = VBA.Format(eDate, "YYYY")
        
        'Get Quote Name
        sQuote = VBA.Trim(VBA.UCase(iSh.Cells(iRow, 5)))
        sURL = "https://finance.google.com/finance/historical?q=" & sQuote & "&startdate=" & smmm & "+" & sdd & "%2C+" & syyyy & "&enddate=" & emmm & "+" & edd & "%2C+" & eyyyy & "&num=" & dispCount
        rowStart = 0
        
        'Create Object for Fetching HTML Data from Server
        Set oXMLHTTP = CreateObject("MSXML2.ServerXMLHTTP")        
lbl_Fetch_Url_Data:
        'Extract data from website to Excel using VBA
        oXMLHTTP.Open "GET", sURL, False
        oXMLHTTP.send
      
        Set ihtmlNew = CreateObject("htmlfile")
        ihtmlNew.body.innerHTML = oXMLHTTP.responseText
        Set htmlNew = ihtmlNew
        DoEvents
        
        'Check whether History Data is available
        Set t0 = htmlNew.getElementsByTagName("table")
        For Each tablee In t0
            If (tablee.className = "gf-table historical_price") Then
                 'History Data Available - Fetch the Table to Excel
                With tablee
                    t = 0
                    For Each Tr In .Rows                        
                        ocol = 2
                        If t > 0 Then
                            oRow = oRow + 1
                            oSh.Cells(oRow, 1) = sQuote
                            For Each Td In Tr.Cells
                                    oSh.Cells(oRow, ocol) = Td.innerText
                                    ocol = ocol + 1
                            Next Td
                        End If
                        t = 1
                    Next Tr
                End With
                rowStart = rowStart + 200
                'Get to Next Page of same Quote
                sURL = "https://finance.google.com/finance/historical?q=" & sQuote & "&startdate=" & smmm & "+" & sdd & "%2C+" & syyyy & "&enddate=" & emmm & "+" & edd & "%2C+" & eyyyy & "&num=" & dispCount & "&start=" & rowStart
                GoTo lbl_Fetch_Url_Data:
            End If
        Next
     iRow = iRow + 1
     Wend
     
    'Process Completed
    Set oXMLHTTP = Nothing
    Set htmlNew = Nothing
    Application.StatusBar = "Ready"
    MsgBox "Process Completed"
End Sub

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. You could try this with various stock exchanges and stock symbols &  leave your valuable comments.