Web query to obtain historical stock data using Google finance

[artz]

I am already using a Web query macro to obtain historical for stock data using Yahoo finance. Unfortunately, Yahoo is lax in updating the daily database and sometimes, I don't get the data until the following day.

For a fallback, I'd like to try getting historical stock data using Google finance instead of Yahoo finance.

I searched online, however, couldn't find VBA code to do a Google Web query. Also, I tried simply modifying my Yahoo Web query downloader using the Google Web query site and got lots of errors. The code for Yahoo doesn't work for Google. Surprise, surprise.

Does anyone in the Forum have code which will perform a Web query to download a specified number of days of historical data for given stock symbol, for a specified period from the Google finance website?

Thanks,

Art

 

[John_w]

You don't need a web query. Each symbol page has a 'Download to spreadsheet' link, for example:

http://www.google.com/finance/histo...e=Jan+1,+2010&enddate=May+28,+2010&output=csv

which you can open in Excel via File - Open. Do this with the macro recorder running and you will have the basis for importing the data with VBA. You will need to construct the URL with the required symbol and dates; to make it easier, the date format can be YYYY-MM-DD. I'm sure something similar must have been done before, which you could adapt.

 

[a-drift]

I have exactly the same problem.

In the reply, the example shows q=MSFT for the stock symbol. What would the syntax be for the Dow Jones Industrial Average?

 

[artz]

I did figure out the Google webquery. Here's the complete code from my workbook. You can modify it to suit your needs.

Art

Sub GetData()
'   thanks to Ron McEwan :^)

    Dim QuerySheet As Worksheet
    Dim DataSheet As Worksheet
    Dim EndDate As Date
    Dim StartDate As Date
    Dim Symbol As String
    Dim qurl As String
    Dim nQuery As Name
    Dim LastRow As Long
    Dim X As Range
    Dim B4 As String
    Dim i As Long, endRow As Long, j As Integer
    
    Application.ScreenUpdating = False
    Application.DisplayAlerts = False
    Application.Calculation = xlCalculationManual
    complete = False
    bSymbolNotFound = False 'Greg Lovern
    
    Set DataSheet = ActiveSheet
  
        StartDate = DataSheet.Range("B2").Value
        EndDate = DataSheet.Range("B3").Value
        Symbol = DataSheet.Range("B4").Value
        Range("C7").CurrentRegion.ClearContents
        
'construct the URL for the query
        
        'Google
        qurl = "http://finance.google.com/finance/historical?q=" & Symbol
        qurl = qurl & "&startdate=" & MonthName(Month(StartDate), True) & _
               "+" & Day(StartDate) & "+" & Year(StartDate) & _
               "&enddate=" & MonthName(Month(EndDate), True) & _
               "+" & Day(EndDate) & "+" & Year(EndDate) & "&output=csv"

        'Yahoo
'        qurl = "http://ichart.finance.yahoo.com/table.csv?s=" & Symbol
'        qurl = qurl & "&a=" & Month(StartDate) - 1 & "&b=" & Day(StartDate) & _
'            "&c=" & Year(StartDate) & "&d=" & Month(EndDate) - 1 & "&e=" & _
'            Day(EndDate) & "&f=" & Year(EndDate) & "&g=" & Range("P2") & "&q=q&y=0&z=" & _
'            Symbol & "&x=.csv"
        Range("b5") = qurl
                   
QueryQuote:

            'Web query
            With ActiveSheet.QueryTables.Add(Connection:="URL;" & qurl, Destination:=DataSheet.Range("C7"))
                .BackgroundQuery = True
                .TablesOnlyFromHTML = False
                On Error GoTo BadSymbol 'Greg Lovern
                .Refresh BackgroundQuery:=False
                On Error GoTo 0 'Greg Lovern
                .SaveData = True
            End With
            
            Range("C7").CurrentRegion.TextToColumns Destination:=Range("C7"), DataType:=xlDelimited, _
                TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
                Semicolon:=False, Comma:=True, Space:=False, other:=False
            
            Range(Range("C7"), Range("C7").End(xlDown)).NumberFormat = "mmm d/yy"
            Range(Range("D7"), Range("G7").End(xlDown)).NumberFormat = "0.00"
            Range(Range("H7"), Range("H7").End(xlDown)).NumberFormat = "0,000"
            Range(Range("I7"), Range("I7").End(xlDown)).NumberFormat = "0.00"
            
            'If Google doesn't return "Adjusted Close", fill col I with "Close" values
            endRow = Range("G65536").End(xlUp).Row
            If DataSheet.Cells(endRow, "I") = "" Then
               For i = 7 To endRow
                   DataSheet.Cells(i, "I").Value = DataSheet.Cells(i, "G").Value
               Next
            End If


    With ThisWorkbook
        For Each nQuery In Names
            If IsNumeric(Right(nQuery.Name, 1)) Then
                nQuery.Delete
            End If
        Next nQuery
    End With
    
'turn calculation back on
    Application.Calculation = xlCalculationAutomatic
    Application.DisplayAlerts = True
    
'    Range("C7:I2000").Select
'    Selection.Sort Key1:=Range("C8"), Order1:=xlAscending, Header:=xlGuess, _
'        OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
    Range("C7:I2000").Sort Key1:=Range("C8"), Order1:=xlAscending, Header:=xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom 'Greg Lovern

'    Range("C1").Select
'    Selection.ColumnWidth = 12
    Range("C1").ColumnWidth = 12 'Greg Lovern
    
    UpdateScale
    UpdateScale2
    UpdateScale3
    Range("B4").Select
  
LastRow = Cells(Rows.Count, "I").End(xlUp).Row
Range("BG7").FormulaR1C1 = "=AVERAGE(R" & LastRow - Range("P5") + 1 & "C[-50]:R" & LastRow & "C[-50])"

'On Error Resume Next
'Range("H4").ClearContents
'Set x = Range("I" & Rows.Count).End(xlUp)
'Range("H4") = x / x.Offset(-Range("L6").Value)
'On Error Resume Next

If Sheets("Candles").Range("B4").Value = "DIA" Then

    Sheets("Candles").Range("F4").ClearContents 'entry date dow return.
    Set X = Sheets("Candles").Range("I" & Rows.Count).End(xlUp)
    
    Sheets("Candles").Range("F4") = X / X.Offset(-Sheets("Candles").Range("L6").Value) - 1
    Sheets("Candles").Range("H4").ClearContents
    Set X = Sheets("Candles").Range("I" & Rows.Count).End(xlUp)
    Sheets("Candles").Range("H4") = X / X.Offset(-Sheets("Candles").Range("L6").Value) - 1
    
    Sheets("Candles").Range("D2").ClearContents
    Sheets("Candles").Range("D2") = Sheets("Candles").Range("F3")
    
    Sheets("Candles").Range("D3").ClearContents
    Sheets("Candles").Range("D3") = Sheets("Candles").Range("G3")
   
ElseIf Sheets("Candles").Range("B4").Value <> "DIA" Then

    Sheets("Candles").Range("H4").ClearContents 'last close dow return.
    Set X = Sheets("Candles").Range("I" & Rows.Count).End(xlUp)
    Sheets("Candles").Range("H4") = X / X.Offset(-Sheets("Candles").Range("L6").Value) - 1

End If



'With ActiveSheet
    'LastRow = .Cells(.Rows.Count, "I").End(xlUp).Row
    '.Range("H4").Value = .Cells(LastRow, "I").Value / .Cells(LastRow - Range("L6").Value, "I").Value
'End With

Exit Sub 'Greg Lovern

BadSymbol: 'Greg Lovern
bSymbolNotFound = True
MsgBox "Symbol " & Symbol & " not found.", vbCritical + vbOKOnly, "Symbol Not Found" 'Greg Lovern
Application.Calculation = xlCalculationAutomatic 'Greg Lovern
Application.DisplayAlerts = True 'Greg Lovern
    
End Sub

 

[a-drift]

I just discovered that Google Finance does not provide the option to download DJIA historical data, for some unknown reason. Google provides actual historical daily Highs and Lows, which is apples-to-apples with their intraday data. Yahoo provides theoretical historical daily Highs and Lows, which is apples-to-oranges with their intraday data.

Google Finance is the only site I can find with enough free actual downloadable history for my purposes, so thank you for the code. Hopefully I will be able to use it when/if Google makes the DJIA historical data downloadable.