Sticky question, how to download csv charts with ticker that contain hyphens or dots from Morningstar and Yahoo finance

[Axel2]

Hi!

I've come up with a VBA that works well to download CSV chart from Morningstar and Yahoo Finance for Canadian stock. However, it always gives me a "1004 run time error" (which points on .Refresh BackgroundQuery:=False) for stocks that contain hyphens or dots i.e. like ATD-B.TO (ATD.B) or RCI-B.TO (RCI.B). All help is greatly appreciated!

VBA CODE FOR KEY RATIOS (from Morningstar)

Sub IMPORT_KeyRatios()

Dim ConnString As String
Dim Cell As Range
Dim Ticker As String, Pays As String, Bourse As String
Sheets("Vierge").Activate
Ticker = Sheets("Vierge").Cells(13, "G").Value
Pays = Sheets("Vierge").Cells(6, "G").Value
Bourse = Sheets("Vierge").Cells(5, "G").Value
ConnString = "TEXT;http://financials.morningstar.com/ajax/exportKR2CSV.html?&callback=?&t=X" + Bourse + ":" + Ticker + "&region=" + Pays + "&culture=en-US&cur=&order=asc"
    Sheets.Add.Name = "KeyRatios"
    With ActiveSheet.QueryTables.Add(Connection:=ConnString, Destination:=Range("A1"))
        .Name = _
        " "
        .FieldNames = True
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .RefreshOnFileOpen = False
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .TextFilePromptOnRefresh = False
        .TextFileStartRow = 1
        .TextFileParseType = xlDelimited
        .TextFileTextQualifier = xlTextQualifierDoubleQuote
        .TextFileConsecutiveDelimiter = False
        .TextFileTabDelimiter = True
        .TextFileSemicolonDelimiter = False
        .TextFileCommaDelimiter = True
        .TextFileSpaceDelimiter = False
        .TextFileColumnDataTypes = Array(1)
        .TextFileDecimalSeparator = ","
        .Refresh BackgroundQuery:=False

    End With
    
End Sub

VBA CODE FOR PRICES (from yahoo finances)

Sub IMPORT_Prices()

Dim ConnString As String
Dim TEXT As String
Dim StartDay As String
Dim StartMonth As String
Dim StartYear As String
Dim EndDay As String
Dim EndMonth As String
Dim EndYear As String
Dim Ticker As String
Dim Cell As Range

Sheets("Vierge").Activate
Ticker = Sheets("Vierge").Cells(13, "G").Value
StartMonth = Sheets("Vierge").Cells(8, "G").Value
StartDay = Sheets("Vierge").Cells(8, "H").Value
StartYear = Sheets("Vierge").Cells(8, "I").Value
EndMonth = Sheets("Vierge").Cells(9, "G").Value
EndDay = Sheets("Vierge").Cells(9, "H").Value
EndYear = Sheets("Vierge").Cells(9, "I").Value

ConnString = "TEXT;http://real-chart.finance.yahoo.com/table.csv?s=" + Ticker + "&a=" + StartMonth + " &b=" + StartDay + " &c=" + StartYear + "&d=" + EndMonth + "&e=" + EndDay + "&f=" + EndYear + "&g=m&ignore=.csv"
    Sheets.Add.Name = "Prices"
    With ActiveSheet.QueryTables.Add(Connection:=ConnString, Destination:=Range("A1"))
        .Name = _
        " "
        .FieldNames = True
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .RefreshOnFileOpen = False
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .TextFilePromptOnRefresh = False
        .TextFileStartRow = 1
        .TextFileParseType = xlDelimited
        .TextFileTextQualifier = xlTextQualifierDoubleQuote
        .TextFileConsecutiveDelimiter = False
        .TextFileTabDelimiter = True
        .TextFileSemicolonDelimiter = False
        .TextFileCommaDelimiter = True
        .TextFileSpaceDelimiter = False
        .TextFileColumnDataTypes = Array(1)
        .TextFileDecimalSeparator = ","
        .Refresh BackgroundQuery:=False

    End With

Sheets("Prices").Activate
Range("B1").EntireColumn.Delete
Range("D1:F1").EntireColumn.Delete

End Sub

 

[John_w]

You might need to URL-encode the query string parameter values.

Could you post a complete example URL so we don't have to guess your cell values?

 

[Axel2]

Hi John,

Sorry for that part, I completed forgot!

For example the URL for RCI-B.TO should be :

Morningstar : http://financials.morningstar.com/a...RCI.B&region=can&culture=en-US&cur=&order=asc

Yahoo Finance : http://real-chart.finance.yahoo.com...0&e=15&f=2015&g=d&a=0&b=12&c=1995&ignore=.csv

Thanks!

 

[John_w]

Both URLs worked for me. Maybe you have spaces in your cell values, causing the problem.

Option Explicit

Sub Macro1()
'
' Macro1 Macro
'

'
    With ActiveSheet.QueryTables.Add(Connection:= _
        "TEXT;http://financials.morningstar.com/ajax/exportKR2CSV.html?&callback=?&t=XTSE:RCI.B&region=can&culture=en-US&cur=&order=asc" _
        , Destination:=Range("$A$1"))
        .Name = "RCI.B&region=can&culture=en-US&cur=&order=asc"
        .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 = 1
        .TextFileParseType = xlDelimited
        .TextFileTextQualifier = xlTextQualifierDoubleQuote
        .TextFileConsecutiveDelimiter = False
        .TextFileTabDelimiter = False
        .TextFileSemicolonDelimiter = False
        .TextFileCommaDelimiter = True
        .TextFileSpaceDelimiter = False
        .TextFileColumnDataTypes = Array(1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1)
        .TextFileTrailingMinusNumbers = True
        .Refresh BackgroundQuery:=False
    End With
End Sub


Sub Macro2()
'
' Macro2 Macro
'

'
    With ActiveSheet.QueryTables.Add(Connection:= _
        "TEXT;http://real-chart.finance.yahoo.com/table.csv?s=RCI-B.TO&d=10&e=15&f=2015&g=d&a=0&b=12&c=1995&ignore=.csv" _
        , Destination:=Range("$A$1"))
        .Name = "table.csv?s=RCI-B.TO&d=10&e=15&f=2015&g=d&a=0&b=12&c=1995&ignore="
        .FieldNames = True
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .TextFilePromptOnRefresh = False
        .TextFilePlatform = 850
        .TextFileStartRow = 1
        .TextFileParseType = xlDelimited
        .TextFileTextQualifier = xlTextQualifierDoubleQuote
        .TextFileConsecutiveDelimiter = False
        .TextFileTabDelimiter = False
        .TextFileSemicolonDelimiter = False
        .TextFileCommaDelimiter = True
        .TextFileSpaceDelimiter = False
        .TextFileColumnDataTypes = Array(1, 1, 1, 1, 1, 1, 1)
        .TextFileTrailingMinusNumbers = True
        .Refresh BackgroundQuery:=False
    End With
End Sub

 

[Axel2]

Following this string, I'm looking to update my vba excel sheet, but Morningstar seems to have changed the URL...! Anyone can help me out!
Thx