Splunk Search

How to search the percentage change between multiple dates?

jihape
Path Finder

So I loaded some old stock market data into Splunk and now I'm trying to make a big table that shows the percentage change from 1 week ago, 1 month ago etc.

What I want to end up with is something like this:
** symbol | 1w change | 1m change **
SPY | 5% | 10%
SPLK | 3% | 15%

All events have the fields like this:

Adj Close = 210.039993
Close = 210.039993
Date = 2015-11-06
High = 210.320007
Low = 208.460007
Open = 209.740005
Volume = 105423100
category = Large Blend
index = quote
name = SPDR S&P 500 ETF
sourcetype = his_quote
symbol = SPY 

Is it possible to do what I want to? I have not been able to get close to what I want.

I tried to do something like this (pseudo code), but it is not working for me.

search get all quote data | save latest as close_now
| subsearch [ search earliest=-1w | save latest as close_1w_ago
| eval diff_pct=(close_now-close_1w_ago)/close_1w_ago*100
| table symbol diff_pct as "1w change"

Any help would be appreciated

0 Karma
1 Solution

jihape
Path Finder

I figured it out.

index=quote earliest=-1w latest=now | stats earliest(Close) as e_close latest(Close) as l_close by symbol | eval 1w=(l_close-e_close)/e_close*100 
| appendcols [ search index=quote earliest=-2w latest=now | stats earliest(Close) as e_close latest(Close) as l_close by symbol | eval 2w=(l_close-e_close)/e_close*100 ] 
| fields symbol 1w 2w

View solution in original post

0 Karma

jihape
Path Finder

I figured it out.

index=quote earliest=-1w latest=now | stats earliest(Close) as e_close latest(Close) as l_close by symbol | eval 1w=(l_close-e_close)/e_close*100 
| appendcols [ search index=quote earliest=-2w latest=now | stats earliest(Close) as e_close latest(Close) as l_close by symbol | eval 2w=(l_close-e_close)/e_close*100 ] 
| fields symbol 1w 2w
0 Karma

sundareshr
Legend
Get Updates on the Splunk Community!

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...

What's new in Splunk Cloud Platform 9.1.2312?

Hi Splunky people! We are excited to share the newest updates in Splunk Cloud Platform 9.1.2312! Analysts can ...