' 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