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.
Disclaimer:
The opinions and ideas presented herein are for informational and educational purposes only and should not be construed to represent trading or investment advice tailored to your investment objectives. You should not rely solely on any content herein and we strongly encourage you to discuss any trades or investments with your broker or investment adviser, prior to execution. None of the information contained herein constitutes a recommendation that any particular security, portfolio, transaction, or investment strategy is suitable for any specific person. Option trading and investing involves risk and is not suitable for all investors.
All opinions are based upon information and systems considered reliable, but we do not warrant the completeness or accuracy, and such information should not be relied upon as such. We are under no obligation to update or correct any information herein. All statements and opinions are subject to change without notice.
Past performance is not indicative of future results. We do not, will not and cannot guarantee any specific outcome or profit. All traders and investors must be aware of the real risk of loss in following any strategy or investment discussed herein.
Owners, employees, directors, shareholders, officers, agents or representatives of ORATS may have interests or positions in securities of any company profiled herein. Specifically, such individuals or entities may buy or sell positions, and may or may not follow the information provided herein. Some or all of the positions may have been acquired prior to the publication of such information, and such positions may increase or decrease at any time. Any opinions expressed and/or information are statements of judgment as of the date of publication only.
Day trading, short term trading, options trading, and futures trading are extremely risky undertakings. They generally are not appropriate for someone with limited capital, little or no trading experience, and/ or a low tolerance for risk. Never execute a trade unless you can afford to and are prepared to lose your entire investment. In addition, certain trades may result in a loss greater than your entire investment. Always perform your own due diligence and, as appropriate, make informed decisions with the help of a licensed financial professional.
Commissions, fees and other costs associated with investing or trading may vary from broker to broker. All investors and traders are advised to speak with their stock broker or investment adviser about these costs. Be aware that certain trades that may be profitable for some may not be profitable for others, after taking into account these costs. In certain markets, investors and traders may not always be able to buy or sell a position at the price discussed, and consequently not be able to take advantage of certain trades discussed herein.
Be sure to read the OCCs Characteristics and Risks of Standardized Options to learn more about options trading.
Related Posts
