Why cannot Excel find this url? (1 Viewer)

Gasman

Enthusiastic Amateur
Local time
Today, 14:32
Joined
Sep 21, 2011
Messages
14,044
Hi all,
I have an Excel sheet that used to download a few stock quotes. Yahoo have now stopped access.
So I am tring another method, this time using Google finance.

From a few YouTube videos I have pieced together the code below.

If I take the connection string and paste manually (which is how I recorded the macro) it works. it also works if the ticker is hardcoded in the connection string.

However soon as I try and parameterise the string it says it cannot find the file.?

debug of string is
TEXT;http://finance.google.co.uk/finance/historical?q=LON:SSE&output=csv

Code:
Sub GetStockPrices()
Dim rngTicker As Range, rngRow As Range
Dim strTicker As String, strPrice As String

Call RemoveConnections

Sheets("Output").Select
Set rngTicker = Range("A2:A3")

For Each rngRow In rngTicker
    strTicker = Range("A" & rngRow.row).Value
    Sheets("Data").Select
    Cells.Delete

    Call Macro3(strTicker)
    'Do
    DoEvents
    strPrice = Sheets("Data").Range("B2").Value
    Range("B" & rngRow.row).Value = strPrice
Next rngRow



Set rngTicker = Nothing
Set rngRow = Nothing

End Sub

Sub Macro3(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 = "TEXT;http://finance.google.co.uk/finance/historical?q=LON:" & pstrTicker & "&output=csv"

Debug.Print strConnection

    Sheets("Data").Select
    Cells.Clear
    With ActiveSheet.QueryTables.Add(Connection:=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
 

Ranman256

Well-known member
Local time
Today, 10:32
Joined
Apr 9, 2015
Messages
4,339
it may be because it not a site, but rather a download link.
The link moves data, rather than display html.
 

Gasman

Enthusiastic Amateur
Local time
Today, 14:32
Joined
Sep 21, 2011
Messages
14,044
Hi Ranman,

What I cannot get my head around is that the Youtuber appears to do the same thing (albeit on a a later version of Excel) and it works fine.

Been trying this for an hour now. :banghead:

https://www.youtube.com/watch?v=naYMz6nUqTQ&t=1s
 

Gasman

Enthusiastic Amateur
Local time
Today, 14:32
Joined
Sep 21, 2011
Messages
14,044
Just recreated the code at home and had it working with a hard coded string, created via Record macro as before.
Then tried with a parameter for the ticker, still working.
Then I needed a parameter for the market and that broke it.

Back with a hardcoded string and now not working?

Recreated again from macro, replaced with both parameters and working???:banghead::banghead::banghead:
 

Gasman

Enthusiastic Amateur
Local time
Today, 14:32
Joined
Sep 21, 2011
Messages
14,044
Now posted in the MrExcel forum for any additional help.
I believe I have proved that the concatenation of the parameters in the string is not the problem, as the string has the correct syntax and works for one or two attempts.?
 

Gasman

Enthusiastic Amateur
Local time
Today, 14:32
Joined
Sep 21, 2011
Messages
14,044
Well not solved, but I am able to run for several tickers in succession before it fails.
Now believe to many calls within a period are blocked by Google.
 

Users who are viewing this thread

Top Bottom