VBA - Yahoo finance double quotes in CSV but missing in import

181 Views Asked by At

I have a VBA macro importing stock information from Yahoo finance. From the URL i can manually download the CSV where the stock name has double quotes (e.g. "iShares J,P. Morgan USD Emergin").

The code runs the following:

    Sub GetDatafull()
QueryQuote:
             With ActiveSheet.QueryTables.Add(Connection:="URL;http://download.finance.yahoo.com/d/quotes.csv?s=EMB&f=nxj1ac1kjerr5dyr1", Destination:=ActiveSheet.Range("A1"))
                .BackgroundQuery = True
                .TablesOnlyFromHTML = False
                .Refresh BackgroundQuery:=False
                .SaveData = True
            End With

'Range("C5").CurrentRegion.TextToColumns Destination:=Range("C5"), DataType:=xlDelimited, _
'    TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
'    Semicolon:=False, Comma:=True, Space:=False, Other:=False
End Sub

When I check the result I have the stock info, but the double quotes are missing for the stock name:

iShares J,P. Morgan USD Emergin,"NGM",N/A,116.56,-0.08,118.14,107.74,N/A,N/A,0.00,N/A,N/A,N/A

So when I run the TextToColumns function, it splits in the middle of the stock name.

Anybody knows what is missing to keep the double quotes around the stock name? For reference, the yahoo query URL is: http://download.finance.yahoo.com/d/quotes.csv?s=EMB&f=nxj1ac1kjerr5dyr1

Thanks!

0

There are 0 best solutions below