Grayson Lee
Board Regular
- Joined
- Jan 15, 2006
- Messages
- 60
I am attempting to create a new web query which retrieves data from Yahoo Finance. It is based a VBA program from Bill's book to build a web query with VBA. The code is as follows:
Sub CreateNewQuery()
Dim WSD As Worksheet
Dim WSW As Worksheet
Dim QT As QueryTable
Dim FinalRow As Long
Dim i As Integer
Dim ConnectString As String
Dim FinalResultRow As Long
Dim RowCount As Long
Set WSD = Worksheets("Portfolio")
Set WSW = Worksheets("Workspace")
' Read column A of Portfolio to find all stock symbols
FinalRow = WSD.Cells(Rows.Count, 1).End(xlUp).Row
For i = 2 To FinalRow
Select Case i
Case 2
ConnectString = "URL;http://finance.yahoo.com/q/cq?d=v1&s=" & WSD.Cells(i, 1).Value
Case Else
ConnectString = ConnectString & ",+" & WSD.Cells(i, 1).Value
End Select
Next i
' On the workspace worksheet, clear all existing query tables
For Each QT In WSW.QueryTables
QT.Delete
Next QT
' Define a new Web Query
Set QT = WSW.QueryTables.Add(Connection:=ConnectString, Destination:=WSW.Range("A1"))
With QT
.Name = "portfolio"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = False
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.WebSelectionType = xlSpecifiedTables
.WebFormatting = xlWebFormattingNone
.WebTables = "20"
.WebPreFormattedTextToColumns = True
.WebConsecutiveDelimitersAsOne = True
.WebSingleBlockTextImport = False
.WebDisableDateRecognition = False
.WebDisableRedirections = False
End With
' Refresh the query
QT.Refresh BackgroundQuery:=False
' Define a named range for the results
FinalResultRow = WSW.Cells(Rows.Count, 1).End(xlUp).Row
WSW.Cells(1, 1).Resize(FinalResultRow, 7).Name = "WebInfo"
' Build a VLOOKUP to get quotes from WSW to WSD
RowCount = FinalRow - 1
WSD.Cells(2, 2).Resize(RowCount, 1).FormulaR1C1 = "VLOOKUP(RC1,WebInfo,3,False)"
WSD.Cells(2, 3).Resize(RowCount, 1).FormulaR1C1 = "VLOOKUP(RC1,WebInfo,4,False)"
WSD.Cells(2, 4).Resize(RowCount, 1).FormulaR1C1 = "VLOOKUP(RC1,WebInfo,5,False)"
WSD.Cells(2, 5).Resize(RowCount, 1).FormulaR1C1 = "VLOOKUP(RC1,WebInfo,6,False)"
WSD.Cells(2, 6).Resize(RowCount, 1).FormulaR1C1 = "VLOOKUP(RC1,WebInfo,2,False)"
MsgBox "Data Updated"
End Sub
I get the following data in the porfolio spreadsheet:
Market
DNA VLOOKUP(RC1,WebInfo,3,False) VLOOKUP(RC1,WebInfo,4,False) VLOOKUP(RC1,WebInfo,5,False) VLOOKUP(RC1,WebInfo,6,False) VLOOKUP(RC1,WebInfo,2,False)
CAT VLOOKUP(RC1,WebInfo,3,False) VLOOKUP(RC1,WebInfo,4,False) VLOOKUP(RC1,WebInfo,5,False) VLOOKUP(RC1,WebInfo,6,False) VLOOKUP(RC1,WebInfo,2,False)
BOL VLOOKUP(RC1,WebInfo,3,False) VLOOKUP(RC1,WebInfo,4,False) VLOOKUP(RC1,WebInfo,5,False) VLOOKUP(RC1,WebInfo,6,False) VLOOKUP(RC1,WebInfo,2,False)
TIE VLOOKUP(RC1,WebInfo,3,False) VLOOKUP(RC1,WebInfo,4,False) VLOOKUP(RC1,WebInfo,5,False) VLOOKUP(RC1,WebInfo,6,False) VLOOKUP(RC1,WebInfo,2,False)
COP VLOOKUP(RC1,WebInfo,3,False) VLOOKUP(RC1,WebInfo,4,False) VLOOKUP(RC1,WebInfo,5,False) VLOOKUP(RC1,WebInfo,6,False) VLOOKUP(RC1,WebInfo,2,False)
YHOO VLOOKUP(RC1,WebInfo,3,False) VLOOKUP(RC1,WebInfo,4,False) VLOOKUP(RC1,WebInfo,5,False) VLOOKUP(RC1,WebInfo,6,False) VLOOKUP(RC1,WebInfo,2,False)
Can anyone please point out where I am going wrong? I previously have been using the Download to Spreadsheet link and manually retrieving the data from the CSV file and copying and pasting it into my spreadsheet, but I would like to automate the data retrieval process. Also, when I manually create the web query, I obtain the quote data from the table. However, when I use the "Download to Spreadsheet" link on the Yahoo Finance web page, I obtain different data than what I retrieve using the web query. That is, the "Download to Spreadsheet" link provides me with a CSV file which contains open, high, low, closing and volume data, which is the data which I require, and which is missing from the manual web query.
Many thanks,
Grayson Lee
Sub CreateNewQuery()
Dim WSD As Worksheet
Dim WSW As Worksheet
Dim QT As QueryTable
Dim FinalRow As Long
Dim i As Integer
Dim ConnectString As String
Dim FinalResultRow As Long
Dim RowCount As Long
Set WSD = Worksheets("Portfolio")
Set WSW = Worksheets("Workspace")
' Read column A of Portfolio to find all stock symbols
FinalRow = WSD.Cells(Rows.Count, 1).End(xlUp).Row
For i = 2 To FinalRow
Select Case i
Case 2
ConnectString = "URL;http://finance.yahoo.com/q/cq?d=v1&s=" & WSD.Cells(i, 1).Value
Case Else
ConnectString = ConnectString & ",+" & WSD.Cells(i, 1).Value
End Select
Next i
' On the workspace worksheet, clear all existing query tables
For Each QT In WSW.QueryTables
QT.Delete
Next QT
' Define a new Web Query
Set QT = WSW.QueryTables.Add(Connection:=ConnectString, Destination:=WSW.Range("A1"))
With QT
.Name = "portfolio"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = False
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.WebSelectionType = xlSpecifiedTables
.WebFormatting = xlWebFormattingNone
.WebTables = "20"
.WebPreFormattedTextToColumns = True
.WebConsecutiveDelimitersAsOne = True
.WebSingleBlockTextImport = False
.WebDisableDateRecognition = False
.WebDisableRedirections = False
End With
' Refresh the query
QT.Refresh BackgroundQuery:=False
' Define a named range for the results
FinalResultRow = WSW.Cells(Rows.Count, 1).End(xlUp).Row
WSW.Cells(1, 1).Resize(FinalResultRow, 7).Name = "WebInfo"
' Build a VLOOKUP to get quotes from WSW to WSD
RowCount = FinalRow - 1
WSD.Cells(2, 2).Resize(RowCount, 1).FormulaR1C1 = "VLOOKUP(RC1,WebInfo,3,False)"
WSD.Cells(2, 3).Resize(RowCount, 1).FormulaR1C1 = "VLOOKUP(RC1,WebInfo,4,False)"
WSD.Cells(2, 4).Resize(RowCount, 1).FormulaR1C1 = "VLOOKUP(RC1,WebInfo,5,False)"
WSD.Cells(2, 5).Resize(RowCount, 1).FormulaR1C1 = "VLOOKUP(RC1,WebInfo,6,False)"
WSD.Cells(2, 6).Resize(RowCount, 1).FormulaR1C1 = "VLOOKUP(RC1,WebInfo,2,False)"
MsgBox "Data Updated"
End Sub
I get the following data in the porfolio spreadsheet:
Market
DNA VLOOKUP(RC1,WebInfo,3,False) VLOOKUP(RC1,WebInfo,4,False) VLOOKUP(RC1,WebInfo,5,False) VLOOKUP(RC1,WebInfo,6,False) VLOOKUP(RC1,WebInfo,2,False)
CAT VLOOKUP(RC1,WebInfo,3,False) VLOOKUP(RC1,WebInfo,4,False) VLOOKUP(RC1,WebInfo,5,False) VLOOKUP(RC1,WebInfo,6,False) VLOOKUP(RC1,WebInfo,2,False)
BOL VLOOKUP(RC1,WebInfo,3,False) VLOOKUP(RC1,WebInfo,4,False) VLOOKUP(RC1,WebInfo,5,False) VLOOKUP(RC1,WebInfo,6,False) VLOOKUP(RC1,WebInfo,2,False)
TIE VLOOKUP(RC1,WebInfo,3,False) VLOOKUP(RC1,WebInfo,4,False) VLOOKUP(RC1,WebInfo,5,False) VLOOKUP(RC1,WebInfo,6,False) VLOOKUP(RC1,WebInfo,2,False)
COP VLOOKUP(RC1,WebInfo,3,False) VLOOKUP(RC1,WebInfo,4,False) VLOOKUP(RC1,WebInfo,5,False) VLOOKUP(RC1,WebInfo,6,False) VLOOKUP(RC1,WebInfo,2,False)
YHOO VLOOKUP(RC1,WebInfo,3,False) VLOOKUP(RC1,WebInfo,4,False) VLOOKUP(RC1,WebInfo,5,False) VLOOKUP(RC1,WebInfo,6,False) VLOOKUP(RC1,WebInfo,2,False)
Can anyone please point out where I am going wrong? I previously have been using the Download to Spreadsheet link and manually retrieving the data from the CSV file and copying and pasting it into my spreadsheet, but I would like to automate the data retrieval process. Also, when I manually create the web query, I obtain the quote data from the table. However, when I use the "Download to Spreadsheet" link on the Yahoo Finance web page, I obtain different data than what I retrieve using the web query. That is, the "Download to Spreadsheet" link provides me with a CSV file which contains open, high, low, closing and volume data, which is the data which I require, and which is missing from the manual web query.
Many thanks,
Grayson Lee