Splunk Search

Finding the ratio between fields in different events

minikatz
Engager

I have the a search  (picture below) which is calculating the open option interest on several ticker symbols.  I was able to figure out how to calculate the sum of the "Call/Put Open Interest" field for each ticker, I can't figure out how to calculate the ratio between them. For example the Apple (AAPL) symbol should have a field with value 1.53. This was found by manually dividing the call open interest (1825974) by the put open interest (1193360). How can I create a new field that calculates this for me? 

minikatz_1-1601158527249.png

 

My search is:

index=raw 
| eventstats max(_time) as maxtime 
| where _time=maxtime 
| stats sum(open_interest) as OI by ul_symbol, put_call
| stats list(put_call) as "Option Type" , list(OI) as "Call/Put Open Interest", sum(OI) as "Total Open Interest" by ul_symbol
| sort -"Total Open Interest"
| rename ul_symbol as "Symbol"

And an example of an event is:

{ [-]
   ask: 8.9
   bid: 6.5
   delta: -0.46
   dte: 42
   expiration_date: Nov 6
   gamma: 0.02
   high_price: 9.85
   last: 7.85
   low_price: 7.75
   net_change: -2.59
   open_interest: 6
   percent_change: -24.83
   put_call: PUT
   rho: -0.07
   strike: 112
   symbol: AAPL_110620P112
   theta: -0.091
   time_value: 7.85
   ul_symbol: AAPL
   vega: 0.153
   volume: 55
}

I'm definitely new to all this. Appreciate the help!

Labels (2)
0 Karma
1 Solution

renjith_nair
Legend

Not clear how you are differentiating between call and put. However, from your existing search, you may try

Your search
|eval call=mvindex('Call/Put Open Interest',0),put=mvindex('Call/Put Open Interest',1)|eval ratio=call/put

If its possible to identify call and put from the event, you may do that in the stats as well

e.g.

Your search
| stats list(put_call) as "Option Type" , list(OI) as "Call/Put Open Interest", sum(OI) as "Total Open Interest",
first(eval(if(<condition for call>),OI,null()) ) as call, first(eval(if(<condition for put>),OI,null()) ) as put by ul_symbol
|eval ratio=call/put

 

---
What goes around comes around. If it helps, hit it with Karma 🙂

View solution in original post

0 Karma

renjith_nair
Legend

Not clear how you are differentiating between call and put. However, from your existing search, you may try

Your search
|eval call=mvindex('Call/Put Open Interest',0),put=mvindex('Call/Put Open Interest',1)|eval ratio=call/put

If its possible to identify call and put from the event, you may do that in the stats as well

e.g.

Your search
| stats list(put_call) as "Option Type" , list(OI) as "Call/Put Open Interest", sum(OI) as "Total Open Interest",
first(eval(if(<condition for call>),OI,null()) ) as call, first(eval(if(<condition for put>),OI,null()) ) as put by ul_symbol
|eval ratio=call/put

 

---
What goes around comes around. If it helps, hit it with Karma 🙂
0 Karma

minikatz
Engager

That did the trick! I read up on mvindex yesterday but it didn't click with me that it was what I needed. I've got a better understanding of it now. Thank you!

0 Karma
Get Updates on the Splunk Community!

Enterprise Security Content Update (ESCU) | New Releases

In November, the Splunk Threat Research Team had one release of new security content via the Enterprise ...

Index This | Divide 100 by half. What do you get?

November 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with this ...

Stay Connected: Your Guide to December Tech Talks, Office Hours, and Webinars!

❄️ Celebrate the season with our December lineup of Community Office Hours, Tech Talks, and Webinars! ...