Skip to Content
avatar image
Former Member

script logic to calculate periodic values in a YTD model

script-v1.txtHi Experts,

We write a script to take GBP values and multiplies by the rate in the rate model and post it to JPY currency in a YTD model.

As it is a ytd model, the script is working fine for first time period 2016.P01(For Apr YTD & periodic are same), however if i run the script for period 2016.p02, it is converting May ytd(Apr+may) value into JPY currency which is not we are expecting, we want to convert may periodic value into JPY for period 2.

Please see attached for the script.

Please help on this.

Thanks.

script-v1.txt (1.1 kB)
Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

3 Answers

  • Best Answer
    Mar 22, 2017 at 12:47 PM

    Sorry, just forget to add correct time and currency destination:

    //%TIME_SET%-single month!andNotfirst month of the year!
    *LOOKUP Rates
    *DIM JPYCURR: INPUTCURRENCY = JPY
    *DIM GBPCURR: INPUTCURRENCY = GBP
    *DIM R_ACCOUNT = AVG
    *DIM R_ENTITY = GLOBAL
    *DIM TIME = %TIME_SET%
    *DIM MEASURES = PERIODIC
    *ENDLOOKUP
    *XDIM_MEMBERSET ACCOUNT = 489030
    *XDIM_MEMBERSET AUDITTRAIL = <ALL>
    *XDIM_MEMBERSET CONSPARTNER = <ALL>
    *XDIM_MEMBERSET FLOW = PL99
    *XDIM_MEMBERSET MEASURES = YTD
    *XDIM_MEMBERSET CONSUNIT = <ALL>
    *XDIM_MEMBERSET RPTCURRENCY = GBP
    *XDIM_MEMBERSET TIME=%TIME_SET%,TMVL(-1,%TIME_SET%) //%TIME_SET% - single month!
    *WHEN TIME
    *IS %TIME_SET%
    *REC(EXPRESSION=%VALUE%*LOOKUP(JPYCURR)/LOOKUP(GBPCURR),RPTCURRENCY=JPY)
    *IS TMVL(-1,%TIME_SET%)
    *WHEN TIME.MONTHNUM
    *IS <> 12 //not the last month of previous year!
    *REC(EXPRESSION=-%VALUE%*LOOKUP(JPYCURR)/LOOKUP(GBPCURR),RPTCURRENCY=JPY,TIME=%TIME_SET%)
    *ENDWHEN
    *ENDWHEN

    Look on changed lines:

    *REC(EXPRESSION=%VALUE%*LOOKUP(JPYCURR)/LOOKUP(GBPCURR),RPTCURRENCY=JPY)

    and

    *REC(EXPRESSION=-%VALUE%*LOOKUP(JPYCURR)/LOOKUP(GBPCURR),RPTCURRENCY=JPY,TIME=%TIME_SET%)
    Add comment
    10|10000 characters needed characters exceeded

  • Mar 22, 2017 at 09:18 AM

    Absolutely strange script!

    What for:

    *WHEN FLOW
    *IS *
    
    *REC(EXPRESSION=1,RPTCURRENCY=%RPTCURRENCY_SET%,TIME=%TIME_SET%)
    *ENDWHEN

    Second - to perform currency conversion on periodic value try:

    *REC(EXPRESSION=([MEASURES].[PERIODIC]*LOOKUP(JPYCURR)/LOOKUP(GBPCURR)))
    Add comment
    10|10000 characters needed characters exceeded

  • Mar 22, 2017 at 12:07 PM

    If the script is launched with %TIME_SET%= first month of the year then TMVL(-1,%TIME_SET%) will be the last month of the previous year and we do not want to subtract it! Then:

    //%TIME_SET% - single month! and Not first month of the year!
    *LOOKUP Rates
    *DIM JPYCURR: INPUTCURRENCY = JPY
    *DIM GBPCURR: INPUTCURRENCY = GBP
    *DIM R_ACCOUNT = AVG
    *DIM R_ENTITY = GLOBAL
    *DIM TIME = %TIME_SET%
    *DIM MEASURES = PERIODIC
    *ENDLOOKUP
    
    *XDIM_MEMBERSET ACCOUNT = 489030
    *XDIM_MEMBERSET AUDITTRAIL = <ALL>
    *XDIM_MEMBERSET CONSPARTNER = <ALL>
    *XDIM_MEMBERSET FLOW = PL99
    *XDIM_MEMBERSET MEASURES = YTD
    *XDIM_MEMBERSET CONSUNIT = <ALL>
    *XDIM_MEMBERSET RPTCURRENCY = GBP
    
    *XDIM_MEMBERSET TIME=%TIME_SET%,TMVL(-1,%TIME_SET%) //%TIME_SET% - single month!
    *WHEN TIME
    *IS %TIME_SET%
    *REC(EXPRESSION=%VALUE%*LOOKUP(JPYCURR)/LOOKUP(GBPCURR))
    *IS TMVL(-1,%TIME_SET%)
    *WHEN TIME.MONTHNUM
    *IS <> 12 //not the last month of previous year!
    *REC(EXPRESSION=-%VALUE%*LOOKUP(JPYCURR)/LOOKUP(GBPCURR))
    *ENDWHEN
    *ENDWHEN
    Add comment
    10|10000 characters needed characters exceeded