Data API
Thursday, September 8th 2022
Get 1-Minute Options Data in Excel
Use the ORATS data API in Excel VBA to get options data including volatility, theoretical values, greeks and strikes information in your worksheet.
Summary
Use the ORATS data API in Excel VBA to get options data including volatility, theoretical values, greeks, and strikes information in your worksheet. The Intraday Data API offers one-minute intraday history available back to August 2020, granular data to filter options by ticker, expiry, and strike, implied monies, and SMV summaries. 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. Subscribe to a 14-day trial of the intraday API for $29 then $399 per month or the basic API for $29 then $99 per month.
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
Email us at support@orats.com if you need help.
Related Posts