topic Re: How to search the percentage change between multiple dates? in Splunk Search

How to search the percentage change between multiple dates?

jihape — Tue, 10 Nov 2015 05:39:55 GMT

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

Re: How to search the percentage change between multiple dates?

sundareshr — Tue, 10 Nov 2015 17:03:05 GMT

Have you looked at appendcols?

http://docs.splunk.com/Documentation/Splunk/6.3.1/SearchReference/Appendcols

Re: How to search the percentage change between multiple dates?

jihape — Fri, 13 Nov 2015 03:16:33 GMT

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