QueryTable of Google Finance Appends New Tickers and Prices

[ESH]

Hi All,

Below is a macro I use to get real time price quotes from Google Finance. Each time I change the stock ticker in cell B3 and run the macro the query table appends the new ticker to the returned data. For instance, the first time I ran the macro with ticker AAPL, it put AAPL in E44 and the price of AAPL in F44. When I changed the ticker in B3 to GOOG, I expected it to replace the old data and put GOOG in E44 and the price of GOOG into F44. Instead, it still had AAPL in E44 and the price of AAPL in F44, and it put GOOG in E45 and the price of GOOG of F45. Changing B3 to a third ticker resulted in this new ticker and price showing in E46 and F46.

Why is this happening and where is the growing list of tickers stored?

Option Explicit

Sub GetData()
    Dim DataSheet As Worksheet
    Dim Symbol As String
    Dim qurl As String

    Application.ScreenUpdating = False

    Range("Data").Cells.Clear 'uses named range "Data"

    Set DataSheet = ActiveSheet

    Symbol = DataSheet.Range("B3").Value

    qurl = "http://finance.google.com/finance?q=" & Symbol

    With Sheets("Data").QueryTables.Add(Connection:="URL;" & qurl, Destination:=Sheets("Data").Range("E1"))
        .BackgroundQuery = True
        .TablesOnlyFromHTML = False
            .FieldNames = True
            .RowNumbers = False
            .FillAdjacentFormulas = False
            .PreserveFormatting = True
            .RefreshOnFileOpen = False
            .RefreshStyle = xlInsertDeleteCells
            .SavePassword = False
            .SaveData = True
            .AdjustColumnWidth = True
            .RefreshPeriod = 0
        .Refresh BackgroundQuery:=False
        .SaveData = True
    End With

    Application.ScreenUpdating = True

End Sub

Thanks,
Eric

 

[welshgasman]

Would it be something to do with

QueryTables.Add

I use google as well for around 10 stocks, but took a different approach.
I can post my macro if it helps when I get home?

I use a sheet for the stocks and market, a sheet for the prices and a sheet for the download.

Built from an example at https://www.youtube.com/watch?v=naYMz6nUqTQ&t=163s

 

[ESH]

I think you're right. It looks like QueryTables.Add adds a new connection each time (which can be seen in Connections on the Data tab of the ribbon).

 

[welshgasman]

This is my code

and here is the workbook

https://drive.google.com/file/d/1ickPrW_lqPnannz3xOVVdHAuGqbAaV1i/view?usp=sharing

Option Explicit
Sub GetStockPrices()
Dim ie As Object
Dim rngTicker As Range, rngRow As Range
Dim strTicker As String, strPrice As String, strOutFile As String, strMainFile As String
Dim blnLondon As Boolean
Dim iDivisor As Integer


Application.ScreenUpdating = False
Application.DisplayStatusBar = True
Call RemoveConnections


Sheets("Output").Select
strOutFile = Range("K1").Value
strMainFile = Range("K2").Value


Set rngTicker = Range("A2:A12")


For Each rngRow In rngTicker
    strTicker = Range("A" & rngRow.Row).Value
    blnLondon = Left(strTicker, 3) = "LON"
    If blnLondon Then
        iDivisor = 100
    Else
        iDivisor = 1
    End If
    Sheets("Data").Select
    Cells.Delete
    Application.StatusBar = "Getting data for " & strTicker
    Call GetGoogleData(strTicker)
    DoEvents
    'Call GetBNC
    'Do
'    DoEvents
    Sheets("Output").Select
    strPrice = Sheets("Data").Range("E1").Value / iDivisor
    Range("B" & rngRow.Row).Value = strPrice
Next rngRow
Range("C2").Value = Sheets("Data").Range("A1").Value


Application.DisplayAlerts = False


Application.StatusBar = "Saving csv file as " & strOutFile
' Save the CSV version
ActiveWorkbook.SaveAs Filename:=strOutFile, FileFormat:=xlCSV, _
    CreateBackup:=False
' And save as an Excel file
' Need to rename Sheet 1 as csv saves makes it the csv name
Application.StatusBar = "Saving Excel file as " & strMainFile
Sheets(1).Name = "Output"
ActiveWorkbook.SaveAs Filename:=strMainFile, FileFormat:=xlNormal


Set rngTicker = Nothing
Set rngRow = Nothing
Application.ScreenUpdating = True
Application.DisplayAlerts = True
Application.StatusBar = ""


End Sub
Sub GetGoogleData(pstrTicker As String)
'
' Macro3 Macro
Dim strConnection


'strConnection = "TEXT;http://finance.google.co.uk/finance/historical?q=LON:" & pstrTicker & "&startdate=Nov+7,+2017&enddate=Nov+7,+2017&num=30&ei=vAcDWsCoMYeEU6fDgdgJ&output=csv"
strConnection = "http://finance.google.co.uk/finance/historical?q=" & pstrTicker & "&output=csv"


'Debug.Print strConnection


    Sheets("Data").Select
    Cells.Clear
    With ActiveSheet.QueryTables.Add(Connection:= _
        "TEXT;" & strConnection & "" _
        , Destination:=Sheets("Data").Range("$A$1"))
        .Name = ""
        .FieldNames = True
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .TextFilePromptOnRefresh = False
        .TextFilePlatform = 65001
        .TextFileStartRow = 2
        .TextFileParseType = xlDelimited
        .TextFileTextQualifier = xlTextQualifierDoubleQuote
        .TextFileConsecutiveDelimiter = False
        .TextFileTabDelimiter = False
        .TextFileSemicolonDelimiter = False
        .TextFileCommaDelimiter = True
        .TextFileSpaceDelimiter = False
        .TextFileColumnDataTypes = Array(1, 1, 1, 1, 1, 1)
        .TextFileTrailingMinusNumbers = True
        .Refresh BackgroundQuery:=False
    End With
End Sub
Sub RemoveConnections()
Dim xConnect As Object
For Each xConnect In ActiveWorkbook.Connections
    xConnect.Delete
Next xConnect
Set xConnect = Nothing
End Sub

 

[ESH]

The clean up code at the end is very helpful. Thanks!