Sort CSV file from Yahoo Finance Query
 

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

Sort CSV file from Yahoo Finance Query
 

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" wrote in message
...
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