Results 1 to 2 of 2

Yahoo Finance Web Query Using VBA

This is a discussion on Yahoo Finance Web Query Using VBA within the Excel Questions forums, part of the Question Forums category; I am attempting to create a new web query which retrieves data from Yahoo Finance. It is based a VBA ...

  1. #1
    Board Regular
    Join Date
    Jan 2006
    Posts
    60

    Default Yahoo Finance Web Query Using VBA

    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

  2. #2
    Board Regular
    Join Date
    Mar 2002
    Location
    Cincinnati, Ohio, USA
    Posts
    6,824

    Default

    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.

    Example VBA Code:
    **Private HttpReq As Object
    **
    **Sub Example()
    ****** Dim r As Range
    ******
    ****** Set HttpReq = CreateObject("WinHttp.WinHttpRequest.5.1")
    ******
    ****** For Each r In 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))
    ****** Next
    ****** Columns("B:J").EntireColumn.AutoFit
    ******
    **End Sub


    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

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


DMCA.com