35
Plug-In Package Functions
MicroStrategy Functions Reference
4
258
Data mining functions
© 2011 MicroStrategy, Inc.
• An example of the function in use; this can be either a report example or a
simple text description of the data returned based on the specified input
See Appendix A, MicroStrategy and Database Support for Functions for a
list of the databases and the functions they support.
The following categories of functions are covered:
• Data mining functions, page 258
• Financial functions, page 259
• Mathematical functions, page 313
• Statistical functions, page 334
Data mining functions
Data mining generally refers to examining a large amount of data to extract
valuable information. The data mining process involves using the predictive
models based on existing and historical data to project potential outcome for
business activities and transactions. MicroStrategy Data Mining Services
facilitates the development and deployment of these predictive models.
Data mining is covered in the Data Mining Services chapter of the
MicroStrategy Advanced Reporting Guide. The Data Mining Services
chapter introduces MicroStrategy Data Mining Services, which includes
these features:
• Using MicroStrategy to create multi-variable regression predictive
models
• Support for importing third-party predictive models using the PMML
industry standard
• A Predictive Model Viewer that visualizes the predictive model
• A set of sample predictive metrics and reports incorporated into
Customer Analysis Module (CAM)
In addition, the Data Mining Services chapter of the Advanced Reporting
Guide describes the process of how to create and use predictive models with
MicroStrategy and provides a business case for illustration.
29
MicroStrategy Functions Reference
Plug-In Package Functions
4
© 2011 MicroStrategy, Inc.
Financial functions
259
Financial functions
The financial functions plug-in package in MicroStrategy provides access to
many standard financial calculations. All finance-related calculations are
performed by the MicroStrategy Analytical Engine, regardless of the
database environment.
Accrint (accrued interest)
Accrued interest is interest that has accumulated over a period of time, but
has not yet been paid. This function returns the amount of accrued interest
on a security that pays periodic interest.
Syntax
Accrint <Par = 1000, Basis> (Issue, FirstInterest,
Settlement, Rate, Frequency)
Where:
• Issue is the issue date.
• FirstInterest is the first date on which interest is accrued.
• Settlement is the settlement date. This is the date, after issue, on which
the security is traded.
• Rate is the annual coupon rate.
• Par is a parameter that indicates the par value. The default, and only
valid value is 1000.
• Frequency is the number of coupon payments per year. The valid values
are, 1, 2, and 4 where annual payments =1, semiannual payments =2, and
quarterly payments =4.
63
Plug-In Package Functions
MicroStrategy Functions Reference
4
260
Financial functions
© 2011 MicroStrategy, Inc.
• Basis is a parameter that indicates the time-count basis to be used. The
default value for Basis is 0, which is typically used by American agencies
and assumes 30-day months and 360-day years (30/360). Possible
values for this parameter are listed in the following table.
Expression
Where:
• Ai is the number of accrued days for the ith quasi-coupon period within
an odd period
• NC is the number of quasi-coupon periods that fit an odd period (if this
period contains a fraction, that fraction is rounded up to the nearest
integer)
• NLi is the normal length, in days, of the ith quasi-coupon period within
an odd period
Usage notes
• If Issue, FirstInterest, Settlement, or Frequency is not an integer, it is
truncated
• The engine returns an empty cell if:
Issue, FirstInterest, or Settlement is not a valid date
Par
≠
1000
Issue ≥ Settlement
Frequency is a value other than 1, 2, or 4
Basis value
Application
0 (30/360)
Assumes 30 days in each month, 360 days in each year.
1 (actual/actual)
Assumes actual number of days in each month, actual number of days in each year.
2 (actual/360)
Assumes actual number of days in each month, 360 days in each year.
3 (actual/365)
Assumes actual number of days in each month, 365 days in each year.
4 (30/60)
Used by European agencies, assumes the same values as “0” for American
institutions.
ACCRINT
par
rate
frequency
-----------------------------------
×
A
i
NL
i
-----
i
=1
NC
∑
×
=
33
MicroStrategy Functions Reference
Plug-In Package Functions
4
© 2011 MicroStrategy, Inc.
Financial functions
261
• The Issue date, the FirstInterest date and the Settlement date should be
included within single quotations in the expression for the expression to
be considered as a valid expression
Example
This example displays the expression built using Accrint for a treasury bond
with the following terms:
• March 22, 2003, issue date
• June 20, 2003, first interest date
• September 16, 2003, settlement date
• 10.0 percent coupon
• $1,000 par value
• Frequency is semiannual
• Basis is 30/360
The accrued interest is defined as:
Accrint <Par=1000, Basis=0>
(‘3/22/2003’,’9/16/2003’,’6/20/2003’,0.1,2) {~+}
Accrintm (accrued interest at maturity)
Accrued interest is interest that has accumulated over a period of time, but
has not yet been paid. This function returns the accrued interest amount on a
security that pays periodic interest at maturity.
Syntax
Accrintm <Par = 1000, Basis> (Issue, Maturity, Rate)
Where:
• Issue is the issue date.
• Maturity is the maturity date. This is the date on which the coupon
expires.
• Rate is the annual coupon rate.
54
Plug-In Package Functions
MicroStrategy Functions Reference
4
262
Financial functions
© 2011 MicroStrategy, Inc.
• Par is a constant that indicates the par value. The default, and only valid,
value is 1000.
• Basis is a parameter that indicates the time-count basis to be used. The
default value for Basis is 0, which is typically used by American agencies
and assumes 30-day months and 360-day years (30/360). Possible
values for this parameter are listed in the following table.
Expression
Where:
• A is the accrued time (for interest-at-maturity items, the value used is the
number of days from issue to maturity)
• D is the annual-yield basis
Usage notes
• If Issue or Maturity is not an integer, it is truncated.
• The engine returns an empty cell if
Issue or Maturity is not a valid date
Rate
≤
0
Par
≠
1000
• The Issue date and the Maturity date should be included within single
quotations in the expression for the expression to be considered as a valid
expression.
Basis value
Application
0 (30/360)
Assumes 30 days in each month, 360 days in each year.
1 (actual/actual)
Assumes actual number of days in each month, actual number of days in each year.
2 (actual/360)
Assumes actual number of days in each month, 360 days in each year.
3 (actual/365)
Assumes actual number of days in each month, 365 days in each year.
4 (30/60)
Used by European agencies, assumes the same values as “0” for American
institutions.
ACCRINTM
par rate
A
D
-
×
×
=
31
MicroStrategy Functions Reference
Plug-In Package Functions
4
© 2011 MicroStrategy, Inc.
Financial functions
263
Example
This example displays the expression built using the Accrintm function for a
note with the following terms:
• March 22, 2001, issue date
• June 20, 2003, maturity date
• 10.0 percent coupon
• $1,000 par value
• Frequency is semiannual
• Basis is Actual/365
The accrued interest at maturity is defined as:
Accrintm <Par=1000, Basis=3>
(‘3/22/2001’,’6/20/2001’,0.1){~+}
Coupdaybs (coupon period, beginning to settlement)
Returns the number of days from the beginning of a coupon period to the
settlement date.
Syntax
Coupdaybs <Basis> (Settlement, Maturity, Frequency)
Where:
• Settlement is the settlement date. This is the date, after issue, on which
the security is traded.
• Maturity is the maturity date. This is the date on which the security
expires.
• Frequency is the number of coupon payments per year. The valid values
are 1,2 and 4 where annual payments =1, semiannual payments =2, and
quarterly payments =4.
41
Plug-In Package Functions
MicroStrategy Functions Reference
4
264
Financial functions
© 2011 MicroStrategy, Inc.
• Basis is a parameter that indicates the time-count basis to be used. The
default value for Basis is 0, which is typically used by American agencies
and assumes 30-day months and 360-day years (30/360). Possible
values for this parameter are listed in the following table.
Usage notes
• If an argument is not an integer, it is truncated.
• The Settlement date and the Maturity date should be included within
single quotations in the expression for the expression to be considered as
a valid expression.
Coupdays (coupon period, number of days with settlement)
Returns the number of days in the coupon period that contains the
settlement date.
Syntax
Coupdays <Basis> (Settlement, Maturity, Frequency)
Where:
• Settlement is the settlement date. This is the date, after issue, on which
the security is traded.
• Maturity is the maturity date. This is the date on which the security
expires.
• Frequency is the number of coupon payments per year. The valid values
are 1, 2, and 4 where annual payments =1, semiannual payments =2, and
quarterly payments =4.
Basis value
Application
0 (30/360)
Assumes 30 days in each month, 360 days in each year.
1 (actual/actual)
Assumes actual number of days in each month, actual number of days in each year.
2 (actual/360)
Assumes actual number of days in each month, 360 days in each year.
3 (actual/365)
Assumes actual number of days in each month, 365 days in each year.
4 (30/60)
Used by European agencies, assumes the same values as “0” for American
institutions.
41
MicroStrategy Functions Reference
Plug-In Package Functions
4
© 2011 MicroStrategy, Inc.
Financial functions
265
• Basis is a parameter that indicates the time-count basis to be used. The
default value for Basis is 0, which is typically used by American agencies
and assumes 30-day months and 360-day years (30/360). Possible
values for this parameter are listed in the following table.
Usage notes
• The number of days between milestones is computed depending on the
chosen day basis
• Coupon functions are defined against the maturity day, depending on
frequency
• The Settlement date and the Maturity date should be included within
single quotations in the expression for the expression to be considered as
a valid expression
Coupdaysnc (coupon period, settlement to next coupon)
Returns the number of days from the settlement date to the next coupon
date.
Syntax
Coupdaysnc <Basis = 0> (Settlement, Maturity, Frequency)
Where:
• Settlement is the settlement date. This is the date, after issue, on which
the security is traded.
• Maturity is the maturity date. This is the date on which the security
expires.
Basis value
Application
0 (30/360)
Assumes 30 days in each month, 360 days in each year.
1 (actual/actual)
Assumes actual number of days in each month, actual number of days in each year.
2 (actual/360)
Assumes actual number of days in each month, 360 days in each year.
3 (actual/365)
Assumes actual number of days in each month, 365 days in each year.
4 (30/60)
Used by European agencies, assumes the same values as “0” for American
institutions.
44
Plug-In Package Functions
MicroStrategy Functions Reference
4
266
Financial functions
© 2011 MicroStrategy, Inc.
• Frequency is the number of coupon payments per year. The valid values
are 1, 2, and 4 where annual payments =1, semiannual payments =2, and
quarterly payments =4.
• Basis is a parameter that indicates the time-count basis to be used. The
default value for Basis is 0, which is typically used by American agencies
and assumes 30-day months and 360-day years (30/360). Possible
values for this parameter are listed in the following table.
Usage notes
• If an argument is not an integer, it is truncated.
• The engine returns an empty cell if
Settlement or Maturity is not a valid date.
Frequency is a number other than 1, 2, or 4.
Settlement
≥
Maturity.
• The Settlement date and the Maturity date should be included within
single quotations in the expression for the expression to be considered as
a valid expression.
Coupncd (next date after settlement)
Returns a number that represents the next coupon date after settlement.
Syntax
Coupncd <Basis> (Settlement, Maturity, Frequency)
Basis value
Application
0 (30/360)
Assumes 30 days in each month, 360 days in each year.
1 (actual/actual)
Assumes actual number of days in each month, actual number of days in each year.
2 (actual/360)
Assumes actual number of days in each month, 360 days in each year.
3 (actual/365)
Assumes actual number of days in each month, 365 days in each year.
4 (30/60)
Used by European agencies, assumes the same values as “0” for American
institutions.
Documents you may be interested
Documents you may be interested
