Get Option Quote

[Av8tordude]

I would like to retrieve the Option Price last quote from Yahoo. Writing a vba code to retrieve this information from a website is far beyond my novice abilities, so I would like the kindly request from the VBA community if you could offer a vba code that would accomplish this task.

The url for all the queries are the same: https://finance.yahoo.com/quote/DIS181123C00101000?p=DIS181123C00101000, except for (DIS181123C00101000). I manage to understand the url so I would substitute the parts below to lookup other Stock Options query. Could anyone assist in my request.

DIS - Stock Ticker
181123 - Date (ddmmyy)
C or P - Call or Put
00101000 - Strike Price

Thank you kindly.

 

[Kamolga]

Maybe this would be a good starting point?

 ActiveWorkbook.Queries.Add Name:="Table 0", Formula:= _        "let" & Chr(13) & "" & Chr(10) & "    Source = Web.Page(Web.Contents(""https://finance.yahoo.com/quote/DIS181123C00101000?p=DIS181123C00101000&guccounter=1""))," & Chr(13) & "" & Chr(10) & "    Data0 = Source{0}[Data]," & Chr(13) & "" & Chr(10) & "    #""Changed Type"" = Table.TransformColumnTypes(Data0,{{""Column1"", type text}, {""Column2"", type number}})" & Chr(13) & "" & Chr(10) & "in" & Chr(13) & "" & Chr(10) & "    #""Changed Type"""
    ActiveWorkbook.Queries.Add Name:="Table 1", Formula:= _
        "let" & Chr(13) & "" & Chr(10) & "    Source = Web.Page(Web.Contents(""https://finance.yahoo.com/quote/DIS181123C00101000?p=DIS181123C00101000&guccounter=1""))," & Chr(13) & "" & Chr(10) & "    Data1 = Source{1}[Data]," & Chr(13) & "" & Chr(10) & "    #""Changed Type"" = Table.TransformColumnTypes(Data1,{{""Column1"", type text}, {""Column2"", type text}})" & Chr(13) & "" & Chr(10) & "in" & Chr(13) & "" & Chr(10) & "    #""Changed Type"""
    ActiveWorkbook.Worksheets.Add
    With ActiveSheet.ListObjects.Add(SourceType:=0, Source:= _
        "OLEDB;Provider=Microsoft.Mashup.OleDb.1;Data Source=$Workbook$;Location=""Table 0"";Extended Properties=""""" _
        , Destination:=Range("$A$1")).QueryTable
        .CommandType = xlCmdSql
        .CommandText = Array("SELECT * FROM [Table 0]")
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .BackgroundQuery = True
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .PreserveColumnInfo = True
        .ListObject.DisplayName = "Table_0"
        .Refresh BackgroundQuery:=False
    End With
    ActiveWorkbook.Worksheets.Add
    With ActiveSheet.ListObjects.Add(SourceType:=0, Source:= _
        "OLEDB;Provider=Microsoft.Mashup.OleDb.1;Data Source=$Workbook$;Location=""Table 1"";Extended Properties=""""" _
        , Destination:=Range("$A$1")).QueryTable
        .CommandType = xlCmdSql
        .CommandText = Array("SELECT * FROM [Table 1]")
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .BackgroundQuery = True
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .PreserveColumnInfo = True
        .ListObject.DisplayName = "Table_1"
        .Refresh BackgroundQuery:=False
    End With

 

[Av8tordude]

With my limited understanding of the code, I have no idea how to implement this. However, I currently using this code below. Can this be alter to suit my needs. This code looks yahoo quotes...

Function StockQuote(ticker As String)    On Error GoTo Err


    URL = "https://query2.finance.yahoo.com/v7/finance/quote?symbols=" & Trim(ticker)
    Set Request = CreateObject("WinHttp.WinHttpRequest.5.1")
    With Request
        .Open "GET", URL, True
        .SetRequestHeader "Content-Type", "application/x-www-form-urlencoded; charset=UTF-8"
        .Send
        .WaitForResponse
        Response = .responseText
    End With


    If InStr(Response, """result"":[]") <> 0 Then GoTo Err              ' ticker not found
    
    Stripped = Replace(Replace(Replace(Replace(Replace(Response, "[", ""), "]", ""), "{", ""), "}", ""), """", "")


    Stripped = Replace(Stripped, ":", ":,")                             ' keep colons for readability, but make them delimit
    inbits = split(Stripped, ",")                                       ' split
    
    i = LBound(inbits)
    Do While inbits(i) <> "regularMarketPrice:" And i <= UBound(inbits) ' find "regularMarketPrice:" tag
        i = i + 1
    Loop
    
    If i > UBound(inbits) Or Not IsNumeric(inbits(i + 1)) Then          ' not found; look for previous close
        i = LBound(inbits)
        Do While inbits(i) <> "regularMarketPreviousClose:" _
                                            And i <= UBound(inbits)
            i = i + 1
        Loop
        If i > UBound(inbits) Or Not IsNumeric(inbits(i + 1)) Then GoTo Err
    End If
        
    StockQuote = CDbl(inbits(i + 1))
    Exit Function
    
Err:
    StockQuote = ""
End Function

 

[Kamolga]

This code is a function, so if you have it in a module, write

dis

in a cell A1, then you can type

=[COLOR=#333333]StockQuote(A1)[/COLOR]

in any cell to have a value.

Option 2 is to type

dis

in a2, click on a2 and then go to data tab and click on stock (in data type portion). a2 is transformed into

Walt Disney Co

.

Then when yo click on a2, you have a menu that comes and you can select what you want (open, close, ticker symbol,...). Note that you can also use

=a2.open

or

=A2.[Ticker symbol]

to get the values.

The last option, and this is my macro above is to go in datatab, import from web amd paste the url. you can select the tables, then in load button arrow, you can "load into" and get them in your workbook. You can also get a connection and use them in your datamodel. If you don't want a connection, simply use the macro recorder and assign it to a button for example