Forum Discussion

poswald's avatar
poswald
New Contributor III
4 years ago

Foreign currency translation error?

Source:   Migrated from Champions

I asked this question from OneStream staff at the Splash conference and OneStream support. I thought perhaps someone in this group has run into the same issue:

The standard that appears to be used for calculating FX does not appear to be correct. It appears from the snippet that FX is calculating taking the month to date amount X month end closing rate - month to date amount X month average rate. From the Flow FX (Calculated) snippet:

If (api.Cons.IsCurrency() And Not api.Cons.IsLocalCurrencyforEntity()) Then

Dim timeId As Integer = api.Pov.Time.MemberPk.MemberId
Dim rateTypeRev As FxRateType = api.FxRates.GetFxRateTypeForRevenueExp()
Dim rateTypeClo As FxRateType = api.FxRates.GetFxRateTypeForAssetLiability()
Dim closingRate As Decimal =  api.FxRates.GetCalculatedFxRate(rateTypeClo,timeId)
Dim averageRate As Decimal =  api.FxRates.GetCalculatedFxRate(rateTypeRev,timeId)

'Calculate FX activity for current movement between Average and Closing Rate       
api.data.calculate("F#FX_CurMvmt= (F#Activity:C#Local:V#PER * " &  api.Data.DecimalToText(closingRate)  & ")  - (F#Activity:C#Local:V#PER * " &  api.Data.DecimalToText(averageRate)  & ")")

End If

I think this is wrong because we have an account that does not change other than amortization expense. The only change in the account on the cash flow should be amortization expense and FX, but what we have is amortization expense, FX and other.

It seems to me that the formula should be: Year to date amount X month end closing rate - Year to date amount X YTD average rate.

How do you calculate a YTD average rate in OneStream?

If there is a way to calculate a YTD average rate then I think this could be fixed.

Finance
  • MikeG's avatar
    MikeG
    Contributor III
    4 years ago

    The Snippet is doing only what it is says it is doing in the comment above the api.data.calculate(). 'Calculate FX activity for current movement between Average and Closing Rate. It’s assigning that value to a member called F#FX_CurMvmt. What you are describing may be better applied for your use case by doing an FX Override, or using an Account filter in the api.data.calculate(), for that Account or group of Accounts separately.

  • MarkMatson's avatar
    MarkMatson
    New Contributor III
    4 years ago

    Not sure if this is what you want, and it's clunky, but you can also cycle through the YTD months with this;

    For Each timeMember In api.Members.GetMembersUsingFilter(dimpk.GetTimeDimPk, "T#" + api.Pov.Time.name + ".AllPriorInYearInclusive")
    ' Perform math here using  timeMember.Member.memberId
    Next

    Cheers

  • poswald's avatar
    poswald
    New Contributor III
    4 years ago

    I figured out how to do this correctly.  Note: our fiscal year runs from Nov to Oct.  November is the 1st month of our fiscal year.  I calculated each of these in a separate flow member:

    YTD balance X Closing rate  (FX_Activity_Closing_rate)

    - Periodic balance X average rate  (Cur_Activity_Avg_rate)

    - YearSumToDatePrior(Periodic balance X average rate) (FX_Pr_Activity_Avg_rate)

     

    If (api.Cons.IsCurrency() And Not api.Cons.IsLocalCurrencyforEntity()) Then

    Dim timeId As Integer = api.Pov.Time.MemberPk.MemberId
    Dim rateTypeRev As FxRateType = api.FxRates.GetFxRateTypeForRevenueExp()
    Dim rateTypeClo As FxRateType = api.FxRates.GetFxRateTypeForAssetLiability()
    Dim closingRate As Decimal = api.FxRates.GetCalculatedFxRate(rateTypeClo,timeId)
    Dim averageRate As Decimal = api.FxRates.GetCalculatedFxRate(rateTypeRev,timeId)

    'Calculate Balance sheet activity at the Average rate for each month
    api.data.calculate("F#FX_Activity_Closing_rate= ( (F#Activity_Calc:C#Local * " & api.Data.DecimalToText(closingRate) & "))", "A#BalSheet.Base")

    End If

     

    If (api.Cons.IsCurrency() And Not api.Cons.IsLocalCurrencyforEntity()) Then

    Dim timeId As Integer = api.Pov.Time.MemberPk.MemberId
    Dim rateTypeRev As FxRateType = api.FxRates.GetFxRateTypeForRevenueExp()
    Dim rateTypeClo As FxRateType = api.FxRates.GetFxRateTypeForAssetLiability()
    Dim closingRate As Decimal = api.FxRates.GetCalculatedFxRate(rateTypeClo,timeId)
    Dim averageRate As Decimal = api.FxRates.GetCalculatedFxRate(rateTypeRev,timeId)

    'Calculate Balance sheet activity at the Average rate for each month
    api.data.calculate("F#FX_Cur_Activity_Avg_rate= ( (F#Activity_Calc:C#Local:V#Periodic * " & api.Data.DecimalToText(averageRate) & "))", "A#BalSheet.Base")

    End If

     

    Dim curTime As String = api.Pov.Time.Name 'Name of time period being processed
    Dim timeId As Integer = api.Pov.Time.MemberPk.MemberId 'Member ID of time period being processed
    Dim curMonth As Integer = api.Time.GetPeriodNumFromId(api.Pov.Time.MemberId)
    Dim RateTypeClo As FxRateType = api.FxRates.GetFxRateTypeForAssetLiability() 'Rate type of Balance Sheet accounts
    Dim Closingrate As Decimal = api.FxRates.GetCalculatedFxRate(RateTypeClo,timeId) 'Closing Rate


    'Do not run in November
    If (api.Cons.IsCurrency() And Not api.Cons.IsLocalCurrencyforEntity()) Then
    If (Not curMonth = 1) Then

    api.Data.Calculate("F#FX_Pr_Activity_Avg_rate:V#Periodic = F#FX_Cur_Activity_Avg_rate:V#YearSumToDatePrior","A#BalSheet.Base.remove(PLUG_EQIC, 31000, 32000, 37000)")
    End If
    End If