Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel web query does not import table (from yahoo finance) correct | Excel Discussion (Misc queries) | |||
Pulling stock quotes from Yahoo Finance into a spreadsheet | Excel Discussion (Misc queries) | |||
Web queries & Yahoo! Finance | Excel Discussion (Misc queries) | |||
Historical Stock Quotes - Yahoo Finance | Excel Programming | |||
vba yahoo! finance | Excel Programming |