Excel vba - yahoo stock options quotes not being downloaded in a csv format

Question

I am able to get a single option quote from yahoo.finance with the data being delivered vertically over two columns (one headers, one data), but all my attempts to receive the data horizontally has been unsuccessful. I'm ultimately trying to write a command that will download a series option quotes that will be delivered as one per row. Currently, I can't even get one horizontal line of data. Please help!

If there is a way of doing this with some other free service (e.g., CBOE, Google), then please let me know. Any and all help would be appreciated!

Sub getOption()
    '
    ' Attempting to return a stock option in a csv format with all datat in either one cell or accross 5 cells in one row
    ' data desired to Retrieve:
    '           name = n
    '           previous close = p
    '           ask = a
    '           bid = b
    '           open interest =  o1
    '
     Const YAHOO_FINANCE_URL = "http://finance.yahoo.com/q/?s=SKX160122C00035000" 'retrieves option data vertically with a header, but adding &f=npbao1, /d/quotes.csv or &e=.csv makes it not work at all. 
    On Error Resume Next
    With ActiveSheet.QueryTables.Add(Connection:="URL;" & YAHOO_FINANCE_URL, Destination:=ActiveCell)
        .Name = "qtActiveRange" & Rnd()
        .RefreshStyle = xlOverwriteCells
        .AdjustColumnWidth = False
        .BackgroundQuery = False
        .Refresh
    End With

End Sub

score 0 · Answer 1 · answered Jan 04 '16 at 05:29

0

I am not sure of a way to transpose the query at one go, but you can first place the vertical data on a temporary range then transpose it horizontal to your destination range.

Range("D4:N5") = WorksheetFunction.Transpose(Range("A4:B14"))

Range("A4:B14") is the temporary range. ( where I selected cell A1 and run your getOption)

Range("D4:N5") is the destination range I am assuming.

answered Jan 04 '16 at 05:29

Rosetta

2,665
1
13
29

Thank you so much for your suggestion KS and I will try that, but I still suspect there some yahoo command to do this (e.g., &e=.csv or .../d/quotes.csv/...). – Robert Mitchell Jan 04 '16 at 14:39

score 0 · Answer 2 · answered Jan 10 '16 at 21:44

I don't know what SKX is, but you can try this. Put your tickers in ColumnA, starting in A7, something like this.

GOOG YHOO BIDU IACI MSFT AOL YNDX INSP REDF SINA ADBE

Then, run your script.

Sub GetData()


Dim yahoourl As String
    Dim QuerySheet As Worksheet
    Dim DataSheet As Worksheet
    Dim qurl As String
    Dim i As Integer

    Set DataSheet = ActiveSheet

    i = 7
    yahoourl = "http://quote.yahoo.com/d/quotes.csv?s=" + Cells(i, 1)
    i = i + 1
    While Cells(i, 1) <> ""
        yahoourl = yahoourl + "+" + Cells(i, 1)
        i = i + 1
    Wend
    yahoourl = yahoourl + "&f=" + "l1"

QueryQuote:
             With ActiveSheet.QueryTables.Add(Connection:="URL;" & yahoourl, Destination:=DataSheet.Range("C7"))
                .BackgroundQuery = True
                .TablesOnlyFromHTML = False
                .Refresh BackgroundQuery:=False
                .SaveData = True
            End With

    Application.Calculation = xlCalculationAutomatic
    Application.DisplayAlerts = True
    Columns("C:C").ColumnWidth = 28#
    Cells(2, 3).Select
End Sub

Thank you for this ryguy. Let me ask you, were you able to retrieve option datat with:
http://quote.yahoo.com/d/quotes.csv?s=
When I try it, I receive a series of "N/A,N/A,..." for any series of &f= that I add. If you actually got Options data, please tell me how you did it with that command. Thanks — Robert Mitchell, Jan 13 '16 at 02:36

Excel vba - yahoo stock options quotes not being downloaded in a csv format

2 Answers2