Yahoo Finance Web Query Using VBA

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
 

Excel Facts

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.
There are less complicated alternatives. Here is a simple example of grabbing the CSV data for several symbols.

Place three valid stock symbols in range A1 to A3
Copy and paste the code example into the worksheet class.
Run Example.

<table border="1" bgcolor="White"><caption ALIGN=left><font size="2" face=Courier New>Example VBA Code:</FONT></caption><tr><td><font size="2" face=Courier New>  <font color="#0000A0">Private</font> HttpReq <font color="#0000A0">As</font> <font color="#0000A0">Object</font>
  
  <font color="#0000A0">Sub</font> Example()
       <font color="#0000A0">Dim</font> r <font color="#0000A0">As</font> Range
      
       <font color="#0000A0">Set</font> HttpReq = CreateObject("WinHttp.WinHttpRequest.5.1")
      
       <font color="#0000A0">For</font> <font color="#0000A0">Each</font> r <font color="#0000A0">In</font> Range("A1:A3")
           HttpReq.Open "GET", "http://finance.yahoo.com/d/quotes.csv?s=" _
               & r & "&f=sl1d1t1c1ohgv&e=.csv"
           HttpReq.Send
           r.Offset(, 1) = Replace(WorksheetFunction.Clean(HttpReq.ResponseText), """", "")
           r.Offset(, 1).TextToColumns r.Offset(, 1), Comma:=True, _
             FieldInfo:=Array(Array(1, 9), Array(2, 1), Array(3, 3), Array(4, 1), _
             Array(5, 1), Array(6, 1), Array(7, 1), Array(8, 1), Array(9, 1))
       <font color="#0000A0">Next</font>
       Columns("B:J").EntireColumn.AutoFit
      
  <font color="#0000A0">End</font> <font color="#0000A0">Sub</font>
</FONT></td></tr></table>

The code would likely need to respond to changes in the URL. Especially this portion (f=sl1d1t1c1ohgv&e) which likely changes periodically to discourage spidering. Post back with more details for a better example.

Tom
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,215
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top