Rate

Calculates the relative exchange rate between a parent and child and returns the value as a multiplier.

Return Value

This function returns a value to be used as part of an HS.EXP function, usually in the translation section.

Syntax

Rate (ExchangeRate, TriangulationCurrency)

Table 12-13 Syntax of Rate Function

Parameter	Valid Values
ExchangeRate	A main account of the "CurrencyRate" type specified as an account string, without reference to custom or intercompany dimensions, for example, "A#EOP_RATE"
TriangulationCurrency	Either a valid currency label as a string or double quotes ( " "). When specifying a currency, it is not necessary to reference any custom dimension.

Detailed Description

This function calculates the relative exchange rate between a parent and child, returning a value as a multiplier. The value is calculated based on the TriangulationCurrency parameter.
If the TriangulationCurrency parameter is a valid currency label, the cross rate is based on this currency.
If the TriangulationCurrency parameter is blank ( " " ), the function first searches for a valid direct rate, and if none is found, uses Triangulation against the application currency.
If no rate values are found, the function returns 1.

These tables show the methods of searching for the data and the order in which the search is made. The order is represented by a number in parentheses, for example (1). In each case, the search is made first in the child entity and, if no data is found, then from the "[None]" entity.

In the following table, either the currency of the child or of the parent is the same as the Triangulation currency, or if Triangulation is blank, the application currency.

Table 12-14 Rate Example — Triangulation Currency Same

Custom 2 dimension rates		Custom 1 dimension rates
		Child	Parent
Custom 2 dimension rates	Child		(2)
Parent	(1)

In the following table, Triangulation has been specified and is not the same as either the child or parent currencies.

Table 12-15 Rate Example — Triangulation Currency Different

Custom 2 dimension rates		Custom 1 dimension rates
		Child	Parent	Triangulation
Custom 2 dimension rates	Child			(2)
Parent
Triangulation		(1)

In the following table, Triangulation has not been specified and the application currency is different from both the child and parent currencies.

Table 12-16 Rate Example — Triangulation Not Specified

Custom 2 dimension rates		Custom 1 dimension rates
		Child	Parent	Application
Custom 2 dimension rates	Child		(2)	(4)
Parent	(1)
Application		(3)

Example

The application currency is Euros, and you need to translate a French child entity to a US parent entity using these rates entered in the [None] entity against the C2#EURO:

Table 12-17 Example of Rate Function

Rate	Opening Rate	Closing Rate
C1#FFR	0.16000	0.16500
C1#USD	1.15862	1.15785

The following function multiplies the opening balance account by the difference between the relative ending and opening rates. This is useful when calculating movement analyses if the translation is not consistent between the local and application currencies.

HS.EXP "A#FXO = A#OPEN * (" & RATE("A#EOP_RATE"," ") & "-" & RATE("A#OPE_RATE"," ") &")"

For the previous example, if the value in the OPEN account for the child is FFR 10,000,000, the value in the US parent FXO account will be USD 44,102 [10,000,000 * (0.165 /1.15785 - 0.16 /1.15862)].

Sample Script

' sample statement written in the calling routine

SUB TRANSLATE()

HS.TRANS "A#FXO","A#FXO","A#EOP_RATE",""
HS.EXP "A#FXO = A#OPEN * (" & RATE("A#EOP_RATE"," ") & "-" & RATE("A#OPE_RATE"," ") &")"

END SUB

' programming of the RATE function

FUNCTION RATE(sRATE,sTRI)

DIM sCCUR, sPCUR, sACUR, bRET, retValue, s3rdCUR
DIM i

sRATE = UCASE(sRATE)
sTRI = UCASE(sTRI)
sCCUR = UCASE(HS.ENTITY.DEFCURRENCY(""))
sPCUR = UCASE(HS.VALUE.CURRENCY)
sACUR = UCASE(HS.APPSETTINGS.CURRENCY)
retValue = 0

' check whether there is a triangulation specified, or if triangulation or application currencies are the same as either parent or child and set up the select case

IF sTRI = sCCUR OR sTRI = sPCUR OR (sTRI = " " AND (sACUR = sCCUR OR sACUR = sPCUR)) THEN

i = 1

ELSEIF sTRI <> " " THEN

i = 2

ELSE

i = 3

END IF

SELECT CASE i

CASE 1

' bRET is a boolean that returns true if data is found. First search the child...
' ...then search the [None] entity

bRET = GETVALUECP(".V#<Entity Currency>",retValue,sRATE,sCCUR,sPCUR)

IF NOT bRET THEN

bRET = GETVALUECP(".E#[None]",retValue,sRATE,sCCUR,sPCUR)

END IF

CASE 2

' use a dynamic parameter name for ease of writing the triangulation checks

s3rdCUR = sTRI

bRET = GETVALUE3(".V#<Entity Currency>",retValue,sRATE,sCCUR,sPCUR,s3rdCUR)

IF NOT bRET THEN

bRET = GETVALUE3(".E#[None]",retValue,sRATE,
sCCUR,sPCUR,s3rdCUR)

END IF

CASE 3

' this case is used when the 2nd parameter is blank and is the most complex.

' first check direct rates in the child…

' … then check triangulation against application currency in the child

' then check direct rates in [None].

'… finally check triangulation in [None]

s3rdCUR = sACUR

bRET = GETVALUECP(".V#<Entity Currency>",retValue,sRATE,sCCUR,sPCUR)

IF NOT bRET THEN

bRET = GETVALUE3(".V#<Entity Currency>",retValue,sRATE,sCCUR,sPCUR,s3rdCUR)

IF NOT bRET THEN

bRET = GETVALUECP(".E#[None]",retValue,sRATE,sCCUR,sPCUR)

IF NOT bRET THEN

bRET = GETVALUE3(".E#[None]",retValue,
sRATE,sCCUR,sPCUR,s3rdCUR)

END IF

END IF

END IF

END SELECT

IF bRET THEN

RATE = retValue

ELSE

RATE = 1

END IF

END FUNCTION

FUNCTION GETVALUECP(sENTITY,sVALUE,sRATE,sCCUR,sPCUR)

' this sub-function is used when comparing direct rates between child and parent

GETVALUECP = FALSE

' check if data exists for direct rate child to parent. If it does return it.
' if no direct child to parent rate check for indirect parent to child rate...
' return the inverse of the indirect rate.

IF HS.GETCELL(sRATE & ".C1#" & sCCUR & ".C2#" & sPCUR & sENTITY) <> 0 THEN

sVALUE = CDBL(HS.GETCELL(sRATE & ".C1#" & sCCUR & ".C2#" & sPCUR & sENTITY))

GETVALUECP = TRUE

ELSEIF HS.GETCELL(sRATE & ".C1#" & sPCUR & ".C2#" & sCCUR & sENTITY) <> 0 THEN

sVALUE = CDBL(1 / HS.GETCELL(sRATE & ".C1#" & sPCUR & ".C2#" & sCCUR & sENTITY))

GETVALUECP = TRUE

END IF

END FUNCTION

FUNCTION GETVALUE3(sENTITY,sVALUE,sRATE,sCCUR,sPCUR,s3rdCUR)

' this sub-function is used when triangulating
' check if data exists for direct rate child to triangulation…
' … if it does return the direct relative rate child to parent…
' if no direct child to triangulation rate check for indirect triangulation to child rate…
' … return the inverse of the indirect relative rates.

GETVALUE3 = FALSE

IF HS.GETCELL(sRATE & ".C1#" & sCCUR & ".C2#" & s3rdCUR & sENTITY) <> 0 THEN

sVALUE = CDBL(HS.GETCELL(sRATE & ".C1#" & sCCUR & ".C2#" & s3rdCUR & sENTITY) / HS.GETCELL(sRATE & ".C1#" & sPCUR & ".C2#" & s3rdCUR & sENTITY))

GETVALUE3 = TRUE

ELSEIF HS.GETCELL(sRATE & ".C1#" & s3rdCUR & ".C2#" & sCCUR & sENTITY) <> 0 THEN

sVALUE = CDBL(HS.GETCELL(sRATE & ".C1#" & s3rdCUR & ".C2#" & sPCUR & sENTITY) / HS.GETCELL(sRATE & ".C1#" & s3rdCUR & ".C2#" & sCCUR & sENTITY))

GETVALUE3 = TRUE

END IF

END FUNCTION