For a deeper look into our Eikon Data API, look into:

Overview |  Quickstart |  Documentation |  Downloads |  Tutorials |  Articles

question

Upvotes
Accepted
1 1 2 3

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

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.

eikoneikon-data-apiworkspaceworkspace-data-apirefinitiv-dataplatform-eikonpython
icon clock
10 |1500

Up to 2 attachments (including images) can be used with a maximum of 5.0 MiB each and 10.0 MiB total.

Upvotes
Accepted
434 1 2 2

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.

icon clock
10 |1500

Up to 2 attachments (including images) can be used with a maximum of 5.0 MiB each and 10.0 MiB total.

Upvotes
1 1 2 3

Will try that. Thank you!

icon clock
10 |1500

Up to 2 attachments (including images) can be used with a maximum of 5.0 MiB each and 10.0 MiB total.

Click below to post an Idea Post Idea