Cumulative sum of quarterly data, reset if fiscal year changed.

requiem202

Is it possible to have a Eikon formula with (cumulative) sum of a quarterly data item (such as TR.TotalRevenue) which reset every time fiscal year has changed?

For example:

At Q1 of 2015, expects TR_1FQ2015.

At Q2 of 2015, expects TR_1FQ2015+TR_2FQ2015

... and so on to Q4

Then again,

At Q1 of 2016, expects TR_1FQ2016.

Or should I be better off calculate them manually?

Thank you.

jorge.santos

I don't think you can make a request and for it to come back in that form. However this is doable with pandas.

# Request data for TRI.TO for the last 12 financial peiods (Quarterly)

df=ek.get_data('tri.to',['tr.revenue','tr.revenue.date','tr.revenue.fperiod'],parameters={'SDate':'0','EDate':'-11','Period':'FQ0','Frq':'FQ'},field_name=True)[0]

# We get the stamp of the financial Year (you could do this on smarter ways - but leveraging just the existing strings). 

df['period']=df['TR.REVENUE.fperiod'].apply(lambda x:x[:6])

# delete some unnecessary data
del df['Instrument']
del df['TR.REVENUE.fperiod']

# reindexing by financial quarters
df=df.set_index('TR.REVENUE.DATE')
df.index=pd.to_datetime(df.index)
df=df.sort_index()

#doing the rolling cumulative sum
df.groupby('period').cumsum()

And that should give you the cummulative sum quarter by quarter within each Financial Year.