Get 1-Minute Options Data in Excel

You can access the ORATS 1-minute API via Excel VBA and get updating options information in your worksheet.

Below, with one API call, I pulled every minute of summarized implied volatility data for January 12th, 2022 and plotted moving averages and stock price.

The data is part of the Summaries History endpoint https://api.orats.io/datav2/hist/one-minute/summaries?token=DEMO&tradeDate=20210107&ticker=SPY. You can try the endpoint via the Dashboard at https://dashboard.orats.com/api-console

The VBA code looks like this:

Sub testoneminuteAPI()Dim apiURL As StringDim response As Stringdatetrade = "1/7/2022"tradeDate = Format(datetrade, "yyyymmdd")apiURL = "https://api.orats.io/datav2/hist/one-minute/summaries?token=YOURtoken&tradeDate=20210107&ticker=SPY"response = whataboutlivesummaries(apiURL)r = 0If Len(response) > 100 Thenresponse = Replace(response, "{""data"":", "")response = Replace(response, "}", "")c = c + 1ary = Split(response, vbLf)For x = 0 To UBound(ary)bry = Split(ary(x), ",")For y = 0 To UBound(bry)Range("A1").Offset(r + x, y) = bry(y)Next yNext xEnd IfEnd Sub

Public Function whataboutlivesummaries(Optional apiURL As String)Dim hReq As ObjectDim i As LongDim var As VariantDim ws As Worksheet

Set ws = Sheet1Dim Url As StringDim response As StringSet hReq = NothingintRand = Int(Rnd() * 100)Url = apiURLSet objHTTP = CreateObject("MSXML2.ServerXMLHTTP")objHTTP.SetTimeouts 19500, 19500, 19500, 19500objHTTP.Open "GET", Url, FalseobjHTTP.Sendresponse = "{""data"":" & objHTTP.ResponseText & "}"whataboutlivesummaries = responseSet hReq = NothingSet JSON = NothingEnd Function

Set the references in VBA as follows:

You can query both live and historical one-minute option data, composed of high quality greeks, the full options chain, and over 200 proprietary indicators. Get started exploring the data with a free demo token by going to dashboard.orats.com and clicking on the API Console. To read more, download this PDF. For pricing, check out our website.

The Intraday Data API is a remarkably responsive market data API enabled by innovative data retrieval and storage techniques. Here are some top features:

History: 

• One-minute intraday history available back to August 2020.

• Option OPRA information available back to January 2022 for 1,270 tickers.

• API requests return a minified CSV file for further parsing.

Granular Data: 

• Go back in time to the options chain for any minute during the trading day.

• Filter options by ticker, expiry, and strike.

• All options include greeks and ORATS smoothed values.

Implied Monies: 

• Seed volatilities for 21 different call deltas.

• ATM term structure IV with and without the earnings effect.

• Weighted market width and confidence levels.

SMV Summaries: 

• Interpolated IVs from 10-365 days at 4 different deltas.

• Forward and flat forward volatility.

• Borrow rate, contango, and other measures of confidence.

Curious about what to query? Here are some ideas to help you get started:

• Show me the options chain for AAPL on August 8th, 2022 at 10am EST: https://api.orats.io/datav2/hist/one-minute/strikes/chain?token=token&ticker=AAPL&tradeDate=202208081000

• Show me the latest greeks of the AAPL 15-SEP`23 $175 option: https://api.orats.io/datav2/one-minute/strikes/option?token=token&ticker=AAPL23091500175000

• Show me the call open interest of the AAPL 15-SEP`23 $175 option on August 8th, 2022 at 10am EST: https://api.orats.io/datav2/hist/one-minute/strikes/option?token=token&ticker=AAPL23091500175000&tradeDate=202208081000

• Show me how the ex-earnings IV 30d of AAPL changed over the month of July 2022: https://api.orats.io/datav2/hist/one-minute/summaries?token=token&tradeDate=20220701,20220731&ticker=AAPL

• Show me how the slope of the IV skew of AAPL changed over the last 30 minutes on August 8th, 2022: https://api.orats.io/datav2/hist/one-minute/monies/implied?token=token&ticker=AAPL&tradeDate=202208081530,202208081600

Subscribe to a 14-day trial of the intraday API here $29 then $399 per month.

Subscribe to a 14-day trial of the basic API here for $29 then $99 per month.

Email us at support@orats.com if you need help.