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.
- Open a new workbook, save it as Excel_Historical_Stock_Quotes.xlsm.
- In sheet 1, cells A2 enter “NASDAQ” and in B2 enter “GOOG” (in Row 2)
- In sheet 1, cells C2 enter “1/1/2016” and in D2 enter “5/19/2017″ (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 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.
3 thoughts on “How to Get Historical Stock Quotes In Excel – Google Finance”
Thanks! This worked for me. However, using the code above, the stock ticker needed to be in cell B5 (not B2). In order to use cell B2, I updated:
in both instances.
*Also, don’t forget to go to Tools–>References and add the Microsoft HTML object library
Thanks for taking time to the corrections. I will update the page accordingly.
I’ve added the Microsoft HTML object library. I’m getting the following error when running the macro “subscript out of range”. Any help is appreciated. I’m looking to download 32 stocks so I’d like to automate my process