0

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!

Florian Faucher
  • 1
  • 1
  • When I open the given query results with a text editor I get the below, so I think your issue may be somewhere else or use of TextToColumns. `"iShares J,P. Morgan USD Emergin","NGM",N/A,116.55,-0.09,118.14,107.74,N/A,N/A,0.00,N‌​/A,N/A,N/A ` – Liss Sep 01 '17 at 19:11
  • I agree. But when I run the following Macro the stock name appears without quotes in the excel. I've added the full macro code in the description – Florian Faucher Sep 02 '17 at 07:26
  • i confirmed the issue, when viewed in excel worksheet, the first string is without quotes, and the second string retains quotes .... weird ..... probably something to do with the comma in the name .... excel thinks that the double quotes are unmatched, so it drops them – jsotola Sep 03 '17 at 06:07

0 Answers0