Insert Function Into Equation

This dialog is used to insert a function into an equation for a user-defined custom report field.  All available functions are documented here.  The bottom of this page also has notes on using date type arguments, and special notes on certain functions.

 ABS AVERAGE DATE2DAYS DAYS2NWDAYS DELTADAYS ENDDAYS EVAL IF LOG LOG10 MIN MAX NWDAYS2DAYS REPORT_PORTFOLIO REPORT_SUBTOTAL STARTDAYS SUM

ABS(number)

Returns the absolute value of "number".

Examples:

ABS(End_NAV - Beg_NAV)

Returns the absolute value of the difference between the ending and beginning share price.

LOG10(ABS(End_NAV - EVAL(DELTADAYS(ENDDAYS() , 0, -1, 0), DELTADAYS(ENDDAYS() , 0, -1, 0), End_NAV)))

First evaluates the ending share price 1 month prior to the ending report date, and subtracts this from the ending share price.  Takes the absolute value of this difference, and then the base 10 log.

AVERAGE(days, days, number, countweekends)

Returns an average of the evaluated "number" for every day between the start and end days, inclusive.  The first "days" argument is the start date, and the second is the end date.  If "countweekends" is 0, the returned average excludes weekends.  Nested functions are not allowed in the "numbers" argument.  (See the "Notes" section at the bottom of this page for more details.)

Example:

AVERAGE(STARTDAYS(), ENDDAYS(), 100 * (Value_change / End_value) ^ 2, 0)

Calculates the average of "100 * (Value_change / End_value) ^ 2" for every weekday from the report start date through the report end date.  Note, the "number" argument can contain fields, numbers, and operators, but it cannot contain other functions.

DATE2DAYS(date)

Returns a "days" value representing the number of days between January 1, 0 and the specified date.  "date" is specified in the Month/Day/Year format.  Use this function to create a "days" type of value from an actual date.  (See the "Notes" section on date type of arguments.)

Example:

AVERAGE(DATE2DAYS(1/1/2010), DATE2DAYS(12/31/2010), 100 * (Value_change / End_value) ^ 2, 0)

Calculates the average of "100 * (Value_change / End_value) ^ 2" for every weekday from January 1, 2010 through December 31, 2010.

DAYS2NWDAYS(days)

Returns a "nwdays" value representing the number of non-weekend days between January 1, 0 and "days", where "days" is a number of total days since January 1, 0.  Use to convert a "days" value into a "nwdays" value.  (See the "Notes" section on date type of arguments.)

Example:

DAYS2NWDAYS(DATE2DAYS(12/31/2010)) - DAYS2NWDAYS(DATE2DAYS(1/1/2010)) + 1

Calculates the number of weekdays in 2010.

DELTADAYS(days, years, months, days)

Returns a "days" value offset from the supplied "days" argument by the specified number of years, months, and days.  The years, months, and days numbers can be positive or negative.  The returned days value represent a non-inclusive date, meaning that if you go back 1 year from 12/31/2010, the result is 12/31/2009, not 1/1/2010.  (See the "Notes" section on date type of arguments.)

Examples:

EVAL(DELTADAYS(ENDDAYS() , 0, 0, -7), ENDDAYS(), ROI_btw)

Calculates ROI yield for the week ending on the ending report date.

EVAL(DELTADAYS(DATE2DAYS(12/31/2010) , -1, 0, 0) + 1, DATE2DAYS(12/31/2010), ROI_btw)

Calculates ROI yield for 1 year, ending on 12/31/2010.  This is the yield from 1/1/2010 - 12/31/2010, inclusive.  To get the yield start date we go back 1 year from 12/31/2010, which gets us 12/31/2009, and to move forward 1 day to 1/1/2010 we add 1 to this result.

ENDDAYS()

Returns a "days" value representing the number of days between January 1, 0 and the ending report date.  (See the "Notes" section on date type of arguments.)

Example:

SUM(STARTDAYS(), ENDDAYS(), 100 * (Divdist_btw - FrgnTaxdist_btw), 1)

Calculates the sum of the daily difference between dividend and foreign tax type of distributions for every day between the report starting and ending date.

EVAL(days, days, number)

Returns the evaluated "number" using report starting and ending dates as specified in the "days" arguments.  The first "days" argument will be the report starting date, and the second is the report ending date.  Both "days" arguments are the total number of days since January 1, 0.

Examples:

EVAL(DELTADAYS(ENDDAYS(), 0, 0, -7), ENDDAYS(), ROI_btw ^ 2)

Calculates the square of the ROI yield for the week ending on the ending report date.

EVAL(Inv_incptn, ENDDAYS(), ROI_btw)

Calculates the ROI yield from the date of the first transaction up through the ending report date.

IF(comparison, true_result, false_result)

Evaluates the "comparison" for true or false, and returns "true_result" or "false_result" accordingly.  Any of the following comparison operators can be used:

 >= greater than or equal to <= less than or equal to > greater than < less than != not equal == equal

Examples:

IF(End_value > 1000, (0.01 * AVERAGE(STARTDAYS(), ENDDAYS(), End_value, 0)), 25)

Determines if the ending value is more than 1000, and if so, it returns 1% of the average daily value over the report date range.  Otherwise, the return value is a fixed 25.

IF(End_value > 1000, IF(End_value > 5000, (0.005 * End_value), (0.01 * End_value)), 25)

If the ending value is more than 5000, returns 0.5% of the ending value.  If the ending value is more than 1000 it returns 1% of the ending value.  Otherwise, the return value is a fixed 25.

LOG(number)

Returns the natural logarithm (base e) of "number".

Example:

LOG(End_NAV)

Calculates the natural log of the ending share price.

LOG10(number)

Returns the base-10 logarithm of "number".

Example:

LOG10(End_NAV)

Calculates the base 10 log of the ending share price.

MIN(days, days, number, countweekends)

Returns the minimum of the evaluated "number" for every day between the start and end days, inclusive.  The first "days" argument is the start date, and the second is the end date.  If "countweekends" is 0, the returned minimum excludes weekends.  Nested functions are not allowed in the "numbers" argument.  (See the "Notes" section at the bottom of this page for more details.)

Example:

MIN(DELTADAYS(ENDDAYS() , 0, 0, -7), ENDDAYS(), End_volume, 0)

Calculates the minimum trading volume over the last week.

MAX(days, days, number, countweekends)

Returns the maximum of the evaluated "number" for every day between the start and end days, inclusive.  The first "days" argument is the start date, and the second is the end date.  If "countweekends" is 0, the returned maximum excludes weekends.  Nested functions are not allowed in the "numbers" argument.  (See the "Notes" section at the bottom of this page for more details.)

Example:

MAX(DELTADAYS(ENDDAYS() , -1, 0, 0), ENDDAYS(), (End_high - End_low) ^ 2, 0)

Calculates the maximum of the square of daily price range over the last year.

NWDAYS2DAYS(nwdays)

Returns a "days" value representing the number of total days between January 1, 0 and "nwdays", where "nwdays" is a number of non-weekend days since January 1, 0.  Use to convert a "nwdays" value into a "days" value.  (See the "Notes" section at the bottom of this page for more details.)

Example:

EVAL(NWDAYS2DAYS(DAYS2NWDAYS(ENDDAYS())), NWDAYS2DAYS(DAYS2NWDAYS(ENDDAYS())), End_value)

Calculates the end value on the last weekday on or prior to the report ending date.  For example, if your report end date is a weekend, the ending value on Friday will be reported.  ENDDAYS returns a "days" value, which is converted into a "nwdays" value using DAYS2NWDAYS.  This ensures we are looking at a weekday on/before the ending report date.  Since the EVAL function takes "days" arguments, this date is then converted from a "nwdays" value to a "days" value using NWDAYS2DAYS.

REPORT_PORTFOLIO(number)

Evaluates "number" for the report's top-most sub-portfolio.

Example:

100 * Gain_btw / REPORT_PORTFOLIO(End_value)

Calculates a percentage by dividing the OOP gain between the report starting/ending dates by the total portfolio end value.

REPORT_SUBTOTAL(number)

Evaluates "number" for the line item's immediate sub-total.  If this report is not sub-totaling, the report's top-most sub-portfolio is used.

Example:

100 * Gain_btw / REPORT_SUBTOTAL(End_value)

Calculates a percentage by dividing the OOP gain between the report starting/ending dates by the end value of this item's sub-total.

STARTDAYS()

Returns a "days" value representing the number of days between January 1, 0 and the starting report date.  (See the "Notes" section on date type of arguments.)

Example:

SUM(STARTDAYS(), ENDDAYS(), 100 * (Divdist_btw - FrgnTaxdist_btw), 1)

Calculates the sum of the daily difference between dividend and foreign tax type of distributions for every day between the report starting and ending date.

SUM(days, days, number, countweekends)

Returns a sum of the evaluated "number" for every day between the start and end days, inclusive.  The first "days" argument is the start date, and the second is the end date.  If "countweekends" is 0, the returned sum excludes weekends.  Nested functions are not allowed in the "numbers" argument.  (See the "Notes" section at the bottom of this page for more details.)

Example:

SUM(STARTDAYS(), ENDDAYS(), 100 * (Divdist_btw - FrgnTaxdist_btw), 1)

Calculates the sum of the daily difference between dividend and foreign tax type of distributions for every day between the report starting and ending date.

Notes on "days", "date", and "nwdays" arguments:

"days" type of arguments are the total number of days since January 1, 0, which counts weekends.  The "nwdays" type of arguments are a count of days since January 1, 0, but they do not count weekends.  Any "date" type of argument is specified in text format as Month/Day/Year.  You can convert between these argument types using the functions: DATE2DAYS(date), DAYS2NWDAYS(days), and NWDAYS2DAYS(nwdays).  You can also do math on the "days" and "nwdays" types of numbers.  For example, to add one day, simply add 1 to a "days" argument.  All built-in custom report date fields (like Inv_incptn) return a date in the "days" format.

Notes on AVERAGE, MIN, MAX, and SUM:

Each of these functions evaluates the "number" argument for however many days there are between the specified start and end "days" arguments.  Each "number" evaluation is done for a date range of a single day.  This means that any "between" type of built-in custom report field will return a number appropriate for that single day of evaluation.  For example, if you have this function:

AVERAGE(DATE2DAYS(9/22/2010), DATE2DAYS(9/24/2010), Divdist_btw, 1)

the field Divdist_btw will be evaluated 3 times, each time with dates of:

9/22/2010 - 9/22/2010

9/23/2010 - 9/23/2010

9/24/2010 - 9/24/2010

and the AVERAGE function will return the average of these 3 evaluations.