I did, until a short while ago!

The Excel class in the Oracle Cloud EPM Groovy API has some truly remarkable base functionality that I’ve totally overlooked to date. These functions could save you a lot of time and effort.

For me these are the ones that stand out so far:

These represent a marked improvement in date-handling capabilities compared to essbase calcs

Excel.pbcsValueToDate

takes a date from a Oracle Cloud EPM cell (or YYYYMMDD format) and translates it into a java date for use in Groovy calcs

Date ThisPeriodEndDate = Excel.EOMONTH(ThisPeriodStartDate,0)

grabs the last day of any period

Excel.EOMONTH(PurchaseDate,36)

grabs the last day of the period 36 months from the purchase date

Excel.DATEDIF()

Calculates the number of days, months, or years between two dates.

Capex depreciation simplified significantly, especially when combined with the enhanced date handling 

Excel.SLN()

calculates straight-line depreciation

Excel.SYD()

Calculates the sum-of-years’ digits depreciation of an asset for a specified period.

Auto-calculate working days drivers for a given period

These also open the door to having automated routines that apply on WD-4, WD-3 each month without admin intervention at all

Excel.NETWORKDAYS

Returns the number of whole working days between start date and end date excluding specified holidays.

WORKDAY()

Returns a date that is the indicated number of working days before or after a date (the starting date)

And a couple more you should investigate

FVSCHEDULE

Applies compound interest rates to a value automatically

FV

Calculates a future value with constant payments and interest applying (eg. mortgage)

There are dozens more functions, many that are too high-finance for me – but they’re all built to match Excel functions perfectly. Therefore, you can use the amazing Microsoft documentation and play with them in Excel to figure them out before you use them. Let me know if you find any exceptional ones please.

For more on Oracle Cloud EPM Groovy API you can have a look at the Oracle website HERE to get started.

To all you Groovy people out there you can contact me via the website contact page to discuss all thing Oracle Cloud EPM and especially, of course, Groovy.

Until next time.

Mike