Sort CSV file from Yahoo Finance Query

[Jon]

I got the following bit of code on this discussion forum(which I have
modified slightly). It is supposed to pull the historical stock prices off of
yahoo finance. The problem is that it imports entire rows of .csv info into a
single cell. How do I sort the data into columns? I have tried the
textfilecommadelimiter property with no success. Thanks a lot.

Jon


'Retrieve Variables to place inside a URL String
Sub getVars()
'Declare Variables for URL
Dim myURL As String
Dim Ticker As String
Dim a As String
Dim b As String
Dim c As String
Dim d As String
Dim e As String
Dim f As String
Dim g As String

'Assign values from Cells to variables
Ticker = Cells(1, 1).Value
a = Cells(2, 1).Value
Cells(2, 1).Select
b = Cells(3, 1).Value
c = Cells(4, 1).Value
d = Cells(5, 1).Value
e = Cells(6, 1).Value
f = Cells(7, 1).Value
g = Cells(8, 1).Value

'Append variables of URL to one string myURL
myURL = "http://table.finance.yahoo.com/table.csv?s="
myURL = myURL + Ticker + "&a="
myURL = myURL + a + "&b="
myURL = myURL + b + "&c="
myURL = myURL + c + "&d="
myURL = myURL + d + "&e="
myURL = myURL + e + "&f="
myURL = myURL + f + "&g="
myURL = myURL + g

'Test Display string myURL in a cell
'Cells(15, 1).Value = myURL

'Some code i copied that will display the WHOLE HTML page in sheet 2
Dim WebCopy As Object
Set WebCopy = Sheets("Sheet2")

WebCopy.Cells.Clear

With WebCopy.QueryTables.Add(Connection:="URL;" & myURL,
Destination:=WebCopy.Range("A1"))
..BackgroundQuery = True
..TablesOnlyFromHTML = True
.TextFileParseType = xlDelimited
.TextFileCommaDelimiter = True

..Refresh BackgroundQuery:=False
..SaveData = True
End With

'With Application
'.ScreenUpdating = True
'.DisplayAlerts = True
'.Calculation = xlCalculationAutomatic
'.Goto Range("A1"), True
'End With

End Sub

[Gary Keramidas]

did you experiment with the text to columns menu selection? if you get that
to work, you can record a macro while you do it. you need to know how the
file is delimited.

--


Gary


"Jon" <[email protected]> wrote in message
news:[email protected]...
>I got the following bit of code on this discussion forum(which I have
> modified slightly). It is supposed to pull the historical stock prices off
> of
> yahoo finance. The problem is that it imports entire rows of .csv info
> into a
> single cell. How do I sort the data into columns? I have tried the
> textfilecommadelimiter property with no success. Thanks a lot.
>
> Jon
>
>
> 'Retrieve Variables to place inside a URL String
> Sub getVars()
> 'Declare Variables for URL
> Dim myURL As String
> Dim Ticker As String
> Dim a As String
> Dim b As String
> Dim c As String
> Dim d As String
> Dim e As String
> Dim f As String
> Dim g As String
>
> 'Assign values from Cells to variables
> Ticker = Cells(1, 1).Value
> a = Cells(2, 1).Value
> Cells(2, 1).Select
> b = Cells(3, 1).Value
> c = Cells(4, 1).Value
> d = Cells(5, 1).Value
> e = Cells(6, 1).Value
> f = Cells(7, 1).Value
> g = Cells(8, 1).Value
>
> 'Append variables of URL to one string myURL
> myURL = "http://table.finance.yahoo.com/table.csv?s="
> myURL = myURL + Ticker + "&a="
> myURL = myURL + a + "&b="
> myURL = myURL + b + "&c="
> myURL = myURL + c + "&d="
> myURL = myURL + d + "&e="
> myURL = myURL + e + "&f="
> myURL = myURL + f + "&g="
> myURL = myURL + g
>
> 'Test Display string myURL in a cell
> 'Cells(15, 1).Value = myURL
>
> 'Some code i copied that will display the WHOLE HTML page in sheet 2
> Dim WebCopy As Object
> Set WebCopy = Sheets("Sheet2")
>
> WebCopy.Cells.Clear
>
> With WebCopy.QueryTables.Add(Connection:="URL;" & myURL,
> Destination:=WebCopy.Range("A1"))
> .BackgroundQuery = True
> .TablesOnlyFromHTML = True
> .TextFileParseType = xlDelimited
> .TextFileCommaDelimiter = True
>
> .Refresh BackgroundQuery:=False
> .SaveData = True
> End With
>
> 'With Application
> '.ScreenUpdating = True
> '.DisplayAlerts = True
> '.Calculation = xlCalculationAutomatic
> '.Goto Range("A1"), True
> 'End With
>
> End Sub
>